Bingkai Jendela

Fungsi Window di Snowflake

Jake Roach

Field Data Engineer

Bingkai Jendela

Output menunjukkan jendela yang terdiri dari semua catatan.

Bingkai Jendela

Output yang menunjukkan jendela yang dibangun menggunakan field member_id untuk mempartisi catatan.

Bingkai Jendela Dinamis

Output yang menunjukkan bingkai jendela "geser".

Menemukan Perhitungan Berjalan

SELECT
    ...

    AVG(<1>) OVER(
        PARTITION BY ...
        ORDER BY <2>

        -- Jendela antara baris pertama
        -- dan baris saat ini


        ROWS BETWEEN UNBOUNDED PRECEDING 
            AND CURRENT ROW

    )
...

ROWS BETWEEN memungkinkan kita membuat bingkai jendela dinamis

UNBOUNDED PRECEEDING AND CURRENT ROW
Baris pertama dalam urutan hingga baris saat ini
Perhitungan "berjalan", bukan bergulir
CURRENT ROW AND UNBOUNDED FOLLOWING

<1>: field untuk perhitungan

<2>: mengurutkan hasil, membangun bingkai jendela

Total Berjalan Kalori Terbakar

SELECT
    member_id AS m_id, 
    calories_burned AS cb,

    -- Total berjalan!
    SUM(calories_burned) OVER(
        PARTITION BY member_id
        ORDER BY workout_date


        ROWS BETWEEN UNBOUNDED PRECEDING
            AND CURRENT ROW


    ) AS running_total

FROM fitness.workouts;


         m_id  |    cb   |  running_total
       ------- | ------- | --------------
        m_192  |   105   |      105 
        m_192  |   156   |      261 
        m_192  |    69   |      330 
        m_192  |   102   |      432  

        m_74   |   374   |      374  
        m_74   |   396   |      770  
        m_74   |   504   |     1274  

        m_233  |    51   |       51  
        m_233  |    81   |      132

BARIS SAAT INI DAN UNBOUNDED FOLLOWING

SELECT
    member_id AS m_id, 
    calories_burned AS cb,

    SUM(calories_burned) OVER(
        PARTITION BY member_id
        ORDER BY workout_date

        -- Jendela antara baris saat ini dan terakhir

        ROWS BETWEEN CURRENT ROW
            AND UNBOUNDED FOLLOWING


    ) AS left_to_burn

FROM fitness.workouts;


         m_id  |    cb   |  left_to_burn
       ------- | ------- | --------------
        m_192  |   105   |      432 
        m_192  |   156   |      327 
        m_192  |    69   |      171 
        m_192  |   102   |      102  

        m_74   |   374   |     1274  
        m_74   |   396   |      900 
        m_74   |   504   |      504  

        m_233  |    51   |      132  
        m_233  |    81   |       81

Tren Pembakaran Kalori

SELECT
    member_id,
    workout_date,
    calories_burned,

    AVG(calories_burned) OVER(   -- Rata-rata berjalan kalori terbakar
        PARTITION BY member_id

        -- Buat jendela berdasarkan tanggal latihan, dari latihan pertama hingga latihan saat ini 
        ORDER BY workout_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

    ) AS avg_calories_burned

FROM FITNESS.workouts;

Tren Pembakaran Kalori


            member_id  |  workout_date  |  calories_burned  |  average_calories_burned
           ----------- | -------------- | ----------------- | -------------------------
              m_192    |   2025-01-01   |        105        |          105.0
              m_192    |   2025-01-03   |        156        |          130.5
              m_192    |   2025-01-04   |         69        |          110.0
              m_192    |   2025-01-10   |        102        |          108.0

               m_74    |   2025-02-10   |        374        |          374.0
               m_74    |   2025-02-13   |        396        |          385.0
               m_74    |   2025-02-14   |        504        |          426.7

              m_233    |   2025-03-05   |         51        |           51.0
              m_233    |   2025-03-12   |         81        |           66.0

Ayo berlatih!

Fungsi Window di Snowflake