Обобщённые табличные выражения

Манипуляция данными в Snowflake

Jake Roach

Field Data Engineer

Обобщённые табличные выражения

Обобщённые табличные выражения (CTE) временно сохраняют результаты запроса для последующего использования в другом запросе

CTE определяются в начале запроса
WITH <cte-name> AS ( <query> )
Аналог подзапросов

-- Pass the name of the CTE, followed
-- by the query in parenthesis
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        |

Давайте потренируемся!

Манипуляция данными в Snowflake