CTE Lanjutan

Manipulasi Data di Snowflake

Jake Roach

Field Data Engineer

Mendefinisikan beberapa Common Table Expression

Lebih dari satu common table expression dapat didefinisikan dengan satu klausa WITH

Jumlah CTE bebas
CTE dapat di-JOIN
Tetap bisa melakukan operasi kompleks dalam CTE
Hasil satu CTE bisa dipakai di CTE lain

WITH <cte-name> AS (

    <query>

), <another-cte-name> (

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

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

Mata kuliah berperforma tinggi

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;

Kueri menjadi lebih mudah dipahami!

Mata kuliah berperforma tinggi

Himpunan hasil sementara digabung menjadi keluaran akhir berupa rata-rata nilai dan nama guru per mata kuliah

Guru bergelar tetap

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;

Guru bergelar tetap

Hasil kueri untuk menemukan masa jabatan guru dan rata-rata tertinggi mata kuliahnya

Ayo berlatih!

Manipulasi Data di Snowflake