Common Table Expressions

Manipulasi Data di Snowflake

Jake Roach

Field Data Engineer

Common Table Expressions

Common Table Expressions (CTE) menyimpan hasil kueri sementara untuk dipakai oleh kueri lain

CTE didefinisikan di awal kueri
WITH <cte-name> AS ( <query> )
Mirip dengan subquery

-- Beri nama CTE, lalu
-- tuliskan kueri dalam tanda kurung
WITH <cte-name> AS (
    <query>
)


SELECT
    ...
FROM <cte-name>
... ;

Membuat laporan siswa berisiko

WITH at_risk AS (
    SELECT
        student_id
        course_name,
        teacher_name,
        grade
    FROM student_courses
    WHERE grade < 70 AND is_required
)


SELECT
    students.student_name,
    at_risk.*
FROM at_risk
JOIN students ON at_risk.student_id = students.id;

Hasil sementara disimpan di at_risk
Pilih subset data
Kueri at_risk untuk membuat laporan

Membuat laporan siswa berisiko

Menampilkan hasil CTE dengan subset hasil serta laporan akhir

Membandingkan Subquery dan CTE

SELECT
    month_num,
    AVG(differential) AS avg_differential
    MIN(differential) AS most_differential
FROM (

    SELECT
        month_num,
        windchill - temperature AS differential
    FROM weather
    WHERE 
        season = 'Winter' AND
        temperature < 32

)
GROUP BY month_num;

WITH daily_temperature_differential AS (
    SELECT
        month_num,
        windchill - temperature AS differential
    FROM weather
    WHERE 
        season = 'Winter' AND 
        temperature < 32
)

SELECT
    month_num,
    AVG(differential) AS avg_differential
    MIN(differential) AS most_differential
FROM daily_temperature_differential
GROUP BY month_num;

Menemukan selisih suhu

WITH daily_temperature_differential AS (
    SELECT
        month_num,
        windchill - temperature AS differential
    FROM weather
    WHERE 
        season = 'Winter' AND 
        temperature < 32
)


SELECT
    month_num,
    AVG(differential) AS avg_differential
    MIN(differential) AS most_differential
FROM daily_temperature_differential
GROUP BY month_num;

          | month_num | differential |
          | --------- | ------------ |
          |     12    |      -12     |
          |     1     |      -3      |
          |     1     |       0      |
          |     2     |      -7      |

Progresnya lebih natural dibanding subquery.

| month_num | avg_differential | most_differential |
| --------- | ---------------- | ----------------- |
|     12    |       -5.77      |        -14        |
|     1     |       -1.91      |        -8         |
|     2     |       -8.13      |        -22        |

Ayo berlatih!

Manipulasi Data di Snowflake