Common Table Expressions

Manipolazione dei dati in Snowflake

Jake Roach

Field Data Engineer

Common Table Expressions

Le Common Table Expressions (CTE) memorizzano temporaneamente il risultato di una query da riutilizzare in un’altra query

Le CTE si definiscono all’inizio della query
WITH <cte-name> AS ( <query> )
Simili alle sottoquery

-- Indica il nome della CTE,
-- poi la query tra parentesi
WITH <cte-name> AS (
    <query>
)


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

Report su studentɜ a rischio

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;

Risultati temporanei in at_risk
Seleziona un sottoinsieme di record
Interroga at_risk per creare un report

Report su studentɜ a rischio

Visualizzazione dei risultati di una CTE con il sottoinsieme e il report finale

Confronto: sottoquery vs 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;

Trovare il differenziale di temperatura

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      |

Il flusso è più naturale rispetto alle sottoquery.

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

Passons à la pratique !

Manipolazione dei dati in Snowflake