Moving Averages และ Totals

Window Functions ใน Snowflake

Jake Roach

Field Data Engineer

Running averages และ totals

ผลลัพธ์ที่แสดง window frame แบบ "เลื่อน"

"Moving" averages และ totals

ผลลัพธ์ที่แสดง moving average

"Moving" averages และ totals

ผลลัพธ์ที่แสดง moving total

การคำนวณแบบ Moving

SELECT
    ...

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

        -- Window between the X preceding
        -- and Y following records


        ROWS BETWEEN <X> PRECEDING 
            AND <Y> FOLLOWING

    )
...

ระบุจำนวนแถว ก่อนและหลังแถวปัจจุบัน

ROWS BETWEEN X PRECEDING AND Y FOLLOWING

การคำนวณแบบ "Moving" ไม่ใช่แบบ running
ยังคงใช้ CURRENT ROW ได้

<X>: จำนวนแถวที่มองย้อนหลัง

<Y>: จำนวนแถวที่มองล่วงหน้า

สร้าง Moving Average

SELECT
    member_id, workout_date, calories_burned,

    -- Create a moving average using a window using the previous workout, 
    -- current workout, and next workout

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

        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

    ) AS moving_avg_cb

FROM FITNESS.workouts;

สร้าง Moving Average

             member_id  |  workout_date  |   calories_burned  |  moving_avg_cb  
            ----------- | -------------- | ------------------ | --------------- 
               m_192    |   2024-01-01   |         105        |      130.5      
               m_192    |   2024-01-03   |         156        |      110.0      
               m_192    |   2024-01-04   |          69        |      109.0      
               m_192    |   2024-01-10   |         102        |      120.0      
               m_192    |   2024-01-11   |         189        |      127.3     
               m_192    |   2024-01-12   |          91        |      145.0     
               m_192    |   2024-01-16   |         155        |      127.7      
               m_192    |   2024-01-19   |         137        |      133.7      
               m_192    |   2024-01-20   |         109        |      123.0      

               m_74     |   2024-02-10   |         374        |      385.0      
                                        ...

แจ้งข้อมูลสมาชิกด้วย Moving Totals

SELECT
    member_id, workout_date, calories_burned,

    ...

    -- Use CURRENT ROW to avoid "look ahead"
    SUM(calories_burned) OVER(
        PARTITION BY member_id
        ORDER BY workout_date

        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

    ) AS last_3_cb

FROM FITNESS.workouts;

แจ้งข้อมูลสมาชิกด้วย Moving Totals

      member_id  |  workout_date  |   calories_burned  |  moving_avg_cb  |  last_3_cb
     ----------- | -------------- | ------------------ | --------------- | -----------
        m_192    |   2024-01-01   |         105        |      130.5      |     105
        m_192    |   2024-01-03   |         156        |      110.0      |     261
        m_192    |   2024-01-04   |          69        |      109.0      |     330
        m_192    |   2024-01-10   |         102        |      120.0      |     327
        m_192    |   2024-01-11   |         189        |      127.3      |     360
        m_192    |   2024-01-12   |          91        |      145.0      |     382
        m_192    |   2024-01-16   |         155        |      127.7      |     435
        m_192    |   2024-01-19   |         137        |      133.7      |     383
        m_192    |   2024-01-20   |         109        |      123.0      |     401

        m_74     |   2024-02-10   |         374        |      385.0      |     374
                                            ...

มาฝึกกันเถอะ!

Window Functions ใน Snowflake