Common Table Expressions

Manipulación de datos en Snowflake

Jake Roach

Field Data Engineer

Common Table Expressions

Las Common Table Expressions (CTE) guardan temporalmente los resultados de una consulta para usarlos después en otra consulta

Las CTE se definen al inicio de la consulta
WITH <cte-name> AS ( <query> )
Parecidas a las subconsultas

-- Pasa el nombre de la CTE,
-- seguido de la consulta entre paréntesis
WITH <cte-name> AS (
    <query>
)


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

Informes sobre estudiantes en riesgo

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;

Resultados temporales guardados en at_risk
Selecciona un subconjunto de registros
Consulta at_risk para generar un informe

Informes sobre estudiantes en riesgo

Mostrando los resultados de una CTE con el subconjunto y el informe final

Comparando subconsultas y 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;

Encontrar el diferencial de 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      |

La progresión es más natural que con subconsultas.

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

¡Vamos a practicar!

Manipulación de datos en Snowflake