Common Table Expressions avançadas

Manipulação de dados no Snowflake

Jake Roach

Field Data Engineer

Definindo várias Common Table Expressions

Mais de uma common table expression pode ser definida usando uma única cláusula WITH

Qualquer número de CTEs pode ser definido
CTEs podem ser combinadas com JOIN
Ainda dá para fazer operações complexas em uma CTE
Você pode usar o resultado de uma CTE em outra

WITH <cte-name> AS (

    <query>

), <another-cte-name> (

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

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

Cursos com melhor desempenho

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;

A consulta fica mais fácil de entender!

Cursos com melhor desempenho

Os conjuntos de resultados temporários se combinam para formar a saída final com médias e nomes dos professores por curso

Professores efetivos

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;

Professores efetivos

Conjunto de resultados de uma consulta para encontrar o tempo de casa do professor e sua maior média de curso

Vamos praticar!

Manipulação de dados no Snowflake