CTE avanzadas

Manipulación de datos en Snowflake

Jake Roach

Field Data Engineer

Definir varias Common Table Expressions

Se pueden definir varias common table expressions con una sola cláusula WITH

Se puede definir un nº arbitrario de CTE
Las CTE se pueden JOINear
Aún puedes hacer operaciones complejas en una CTE
Incluso usar los resultados de una CTE en otra

WITH <cte-name> AS (

    <query>

), <another-cte-name> (

    -- Add another query!
    <another-query>
)

-- These CTE's could be JOIN'd 
SELECT ... ;

Cursos con mejor rendimiento

WITH active_courses AS (
    SELECT
        id,
        course_name,
        teacher_name
    FROM courses
    WHERE is_active

), course_avgs (
    SELECT
        course_id,
        AVG(grade) AS avg_grade
    FROM student_courses
    GROUP BY course_id
)


SELECT
    a.course_name,
    a.teacher_name,
    c.avg_grade
FROM active_courses AS a

-- JOIN these CTEs together
JOIN course_avgs AS c
    ON a.id = c.course_id

ORDER BY avg_grade DESC;

¡La consulta es más fácil de entender!

Cursos con mejor rendimiento

Conjuntos de resultados temporales que se combinan para formar la salida final con medias y profesorado por curso

Profesorado con antigüedad

WITH active_courses AS (
    SELECT
        id,
        course_name,
        teacher_name,
        teacher_tenure
    FROM courses

    -- JOIN the teachers table to the courses 
    -- table to get teacher_tenure
    JOIN teachers 
        ON courses.teacher_id = teachers.id

    WHERE is_active
), 
...

...
), course_avgs (
    SELECT
        course_id,
        AVG(grade) AS avg_grade
    FROM student_courses
    GROUP BY course_id
)


SELECT
    a.teacher_name,
    a.teacher_tenure
    MAX(c.avg_grade) AS highest_grade
FROM active_courses AS a
JOIN course_avgs AS c ON a.id = c.course_id
GROUP BY a.teacher_name, a.teacher_tenure;

Profesorado con antigüedad

Conjunto de resultados de una consulta para obtener la antigüedad del profesorado y su media más alta por curso

¡Vamos a practicar!

Manipulación de datos en Snowflake