在 Snowflake 中应用条件逻辑

Snowflake 中的数据操作

Jake Roach

Field Data Engineer

使用 CASE 的聚合

包含 CASE 的查询仍可使用聚合函数。

SUM()
AVG()
MIN()
MAX()
...
MODE()

务必包含 GROUP BY！

包含聚合与分组的 CASE 语句骨架

使用 CASE 的聚合

SELECT
    CASE
        WHEN month_num IN (12, 1, 2) THEN 'Winter'
        WHEN month_num IN (3, 4, 5) THEN 'Spring'
        WHEN month_num in (6, 7, 8) THEN 'Summer'
        ELSE 'Fall'
    END AS season,


    AVG(temperature) AS average_temperature,  -- Manipulate data with AVG()


    year_num
FROM weather
GROUP BY season, year_num;  -- Remember to group by non-aggregated fields

使用 CASE 的聚合

按季节与年份的平均气温查询结果

聚合经 CASE 处理的字段

SELECT
    season,
    AVG(
        CASE
            WHEN season = 'Winter' THEN temperature - 30
            WHEN season IN ('Spring', 'Fall') THEN temperature - 60
            WHEN season = 'Summer' THEN temperature - 75
        END
    ) AS relation_to_average_temperature
FROM weather
GROUP BY season;

聚合经 CASE 处理的字段

按季节显示平均气温关系的表格

JOIN

SELECT
    ...

    -- Define a CASE statement
    CASE ...

FROM <left_table>

-- JOIN new records to existing table
LEFT JOIN <right_table> 
    ... 
;

可以使用来自两个不同表的联接数据构造 CASE 语句。

在 CASE 中使用多个表的列
LEFT、RIGHT、INNER、OUTER 等
可与聚合函数等工具结合

                                         让我们来看看！

判定大学学分资格

SELECT
    students.student_name,
    student_courses.course_name,


    -- Evaluate both the student's grade number and grade for the course
    CASE  
        WHEN students.grade_num = 12 AND student_courses.grade > 90
            THEN 'College Credit Eligible'
        ELSE 'Too Early for College Credit'
    END AS college_credit_status


FROM student_courses
LEFT JOIN students ON student_courses.student_id = students.student_id;

判定大学学分资格

对已 JOIN 的 students 与 student_courses 表查询的结果

让我们来练习！

Snowflake 中的数据操作