Geavanceerde Common Table Expressions

Gegevens manipuleren in Snowflake

Jake Roach

Field Data Engineer

Meerdere Common Table Expressions definiëren

Met één WITH-clausule kun je meerdere common table expressions definiëren

Een willekeurig aantal CTE's mogelijk
CTE's kun je onderling JOINen
Complexe bewerkingen in een CTE blijven mogelijk
Je kunt de output van de ene CTE in een andere gebruiken

WITH <cte-name> AS (

    <query>

), <another-cte-name> (

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

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

Best presterende cursussen

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;

Query wordt zo beter leesbaar!

Best presterende cursussen

De tijdelijke resultaatsets die samen de uiteindelijke output vormen: gemiddelde scores en docentnamen per cursus

Docenten met vaste aanstelling

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;

Docenten met vaste aanstelling

Resultaatset van een query om docentjaren en hun hoogste cursusgemiddelde te vinden

Laten we oefenen!

Gegevens manipuleren in Snowflake