進階 Common Table Expressions

Snowflake 中的資料操作

Jake Roach

Field Data Engineer

定義多個 Common Table Expressions

可以在單一 WITH 子句中定義多個 CTE

可定義任意數量的 CTE
CTE 之間可用 JOIN 結合
CTE 中仍可執行複雜操作
也能在另一個 CTE 中使用前一個的結果

WITH <cte-name> AS (

    <query>

), <another-cte-name> (

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

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

表現最佳的課程

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;

查詢更容易理解！

表現最佳的課程

臨時結果集合合併為每門課的平均分數與教師姓名的最終輸出

資深教師

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;

資深教師

查詢結果：找出教師任期與其最高課程平均分數

一起來練習吧！

Snowflake 中的資料操作