公用表表达式

Snowflake 中的数据操作

Jake Roach

Field Data Engineer

公用表表达式

公用表表达式（CTE）临时存储查询结果，供后续查询使用

CTE 定义在查询开头
WITH <cte-name> AS ( <query> )
类似于子查询

-- 先写 CTE 名称，
-- 再在括号中给出查询
WITH <cte-name> AS (
    <query>
)


SELECT
    ...
FROM <cte-name>
... ;

生成学业风险学生报告

WITH at_risk AS (
    SELECT
        student_id
        course_name,
        teacher_name,
        grade
    FROM student_courses
    WHERE grade < 70 AND is_required
)


SELECT
    students.student_name,
    at_risk.*
FROM at_risk
JOIN students ON at_risk.student_id = students.id;

临时结果存于 at_risk
选取记录子集
查询 at_risk 生成报告

生成学业风险学生报告

展示 CTE 的子集结果及最终报告

对比子查询与 CTE

SELECT
    month_num,
    AVG(differential) AS avg_differential
    MIN(differential) AS most_differential
FROM (

    SELECT
        month_num,
        windchill - temperature AS differential
    FROM weather
    WHERE 
        season = 'Winter' AND
        temperature < 32

)
GROUP BY month_num;

WITH daily_temperature_differential AS (
    SELECT
        month_num,
        windchill - temperature AS differential
    FROM weather
    WHERE 
        season = 'Winter' AND 
        temperature < 32
)

SELECT
    month_num,
    AVG(differential) AS avg_differential
    MIN(differential) AS most_differential
FROM daily_temperature_differential
GROUP BY month_num;

查找温差

WITH daily_temperature_differential AS (
    SELECT
        month_num,
        windchill - temperature AS differential
    FROM weather
    WHERE 
        season = 'Winter' AND 
        temperature < 32
)


SELECT
    month_num,
    AVG(differential) AS avg_differential
    MIN(differential) AS most_differential
FROM daily_temperature_differential
GROUP BY month_num;

          | month_num | differential |
          | --------- | ------------ |
          |     12    |      -12     |
          |     1     |      -3      |
          |     1     |       0      |
          |     2     |      -7      |

这种流程比子查询更自然。

| month_num | avg_differential | most_differential |
| --------- | ---------------- | ----------------- |
|     12    |       -5.77      |        -14        |
|     1     |       -1.91      |        -8         |
|     2     |       -8.13      |        -22        |

Passons à la pratique !

Snowflake 中的数据操作