Common Table Expressions

Datenmanipulation in Snowflake

Jake Roach

Field Data Engineer

Common Table Expressions

Common Table Expressions (CTEs) speichern vorübergehend das Ergebnis einer Abfrage, das später in einer anderen Abfrage genutzt wird

CTEs stehen am Anfang einer Abfrage
WITH <cte-name> AS ( <query> )
Ähnlich wie Unterabfragen

-- Gib den Namen der CTE an,
-- gefolgt von der Abfrage in Klammern
WITH <cte-name> AS (
    <query>
)


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

Bericht zu gefährdeten Schüler:innen

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;

Temporäre Ergebnisse in at_risk gespeichert
Teilmenge von Datensätzen auswählen
at_risk abfragen, um einen Bericht zu erstellen

Bericht zu gefährdeten Schüler:innen

Anzeige der Ergebnisse einer CTE mit der Teilmenge sowie dem finalen Bericht

Unterabfragen vs. CTEs

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;

Temperaturdifferenz ermitteln

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      |

Der Ablauf ist natürlicher als bei Unterabfragen.

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

Lass uns üben!

Datenmanipulation in Snowflake