Voortschrijdende gemiddelden en totalen

Vensterfuncties in Snowflake

Jake Roach

Field Data Engineer

Doorlopende gemiddelden en totalen

Output met een "schuivend" vensterframe.

"Voortschrijdende" gemiddelden en totalen

Output met een voortschrijdend gemiddelde.

"Voortschrijdende" gemiddelden en totalen

Output met een voortschrijdend totaal.

Voortschrijdende berekeningen

SELECT
    ...

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

        -- Venster tussen X voorgaande
        -- en Y volgende records


        ROWS BETWEEN <X> PRECEDING 
            AND <Y> FOLLOWING

    )
...

Geef het aantal records op vóór en na de huidige rij

ROWS BETWEEN X PRECEDING AND Y FOLLOWING

"Voortschrijdende" berekening, geen doorlopende
CURRENT ROW kan nog steeds

<X>: # rijen terugkijken

<Y>: # rijen vooruitkijken

Een voortschrijdend gemiddelde maken

SELECT
    member_id, workout_date, calories_burned,

    -- Maak een voortschrijdend gemiddelde met een venster van vorige,
    -- huidige en volgende 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;

Een voortschrijdend gemiddelde maken

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

Leden informeren met voortschrijdende totalen

SELECT
    member_id, workout_date, calories_burned,

    ...

    -- Gebruik CURRENT ROW om vooruitkijken te voorkomen
    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;

Leden informeren met voortschrijdende totalen

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

Laten we oefenen!

Vensterfuncties in Snowflake