Trung bình và tổng di động

Window Functions trong Snowflake

Jake Roach

Field Data Engineer

Trung bình và tổng tích lũy

Đầu ra hiển thị khung cửa sổ "trượt".

Window Functions trong Snowflake

Trung bình và tổng "di động"

Đầu ra minh họa trung bình di động.

Window Functions trong Snowflake

Trung bình và tổng "di động"

Đầu ra minh họa tổng di động.

Window Functions trong Snowflake

Tính toán di động

SELECT
    ...

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

        -- Cửa sổ giữa X bản ghi trước
        -- và Y bản ghi sau


        ROWS BETWEEN <X> PRECEDING 
            AND <Y> FOLLOWING

    )
...

Chỉ định số bản ghi trước và sau hàng hiện tại

ROWS BETWEEN X PRECEDING AND Y FOLLOWING
  • Tính toán "di động", không phải tích lũy
  • Vẫn có thể dùng CURRENT ROW

$$

<X>: số hàng lùi lại

<Y>: số hàng nhìn tới

Window Functions trong Snowflake

Tạo trung bình di động

SELECT
    member_id, workout_date, calories_burned,

    -- Tạo trung bình trượt với cửa sổ gồm bài tập trước,
    -- hiện tại và tiếp theo

    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;
Window Functions trong Snowflake

Tạo trung bình di động

             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      
                                        ...
Window Functions trong Snowflake

Thông tin cho hội viên bằng tổng di động

SELECT
    member_id, workout_date, calories_burned,

    ...

    -- Dùng CURRENT ROW để tránh "nhìn trước"
    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;
Window Functions trong Snowflake

Thông tin cho hội viên bằng tổng di động

      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 trong Snowflake

Ayo berlatih!

Window Functions trong Snowflake

Preparing Video For Download...