Ковзні середні та підсумки

Window Functions in Snowflake

Jake Roach

Field Data Engineer

Наростальні середні та підсумки

Результат, що показує «ковзне» вікно.

«Ковзні» середні та підсумки

Результат, що зображує ковзне середнє.

«Ковзні» середні та підсумки

Результат, що зображує ковзний підсумок.

Ковзні обчислення

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

«Ковзне» обчислення, не наростальне
Можна використовувати CURRENT ROW

<X>: скільки рядків переглянути назад

<Y>: скільки рядків переглянути вперед

Створення ковзного середнього

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;

Створення ковзного середнього

             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      
                                        ...

Інформування учасників за допомогою ковзних підсумків

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;

Інформування учасників за допомогою ковзних підсумків

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