Common Table Expressions

Gegevens manipuleren in Snowflake

Jake Roach

Field Data Engineer

Common Table Expressions

Common Table Expressions (CTE’s) slaan tijdelijk het resultaat van een query op om later door een andere query te gebruiken

CTE’s staan aan het begin van een query
WITH <cte-name> AS ( <query> )
Vergelijkbaar met subqueries

-- Geef de naam van de CTE,
-- gevolgd door de query tussen haakjes
WITH <cte-name> AS (
    <query>
)


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

Rapportage over risicoleerlingen

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;

Tijdelijke resultaten in at_risk
Selecteer een subset van records
Query at_risk voor een rapport

Rapportage over risicoleerlingen

De resultaten van een CTE tonen: de subset én het eindrapport

Subqueries vs. CTE’s

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;

Temperatuurverschil bepalen

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      |

De opbouw is natuurlijker dan subqueries.

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

Laten we oefenen!

Gegevens manipuleren in Snowflake