Gleitende Durchschnitte und Summen

Window Functions in Snowflake

Jake Roach

Field Data Engineer

Laufende Durchschnitte und Summen

Ausgabe, die einen "gleitenden" Fensterrahmen zeigt.

"Gleitende" Durchschnitte und Summen

Ausgabe, die einen gleitenden Durchschnitt zeigt.

"Gleitende" Durchschnitte und Summen

Ausgabe, die eine gleitende Summe zeigt.

Gleitende Berechnungen

SELECT
    ...

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

        -- Fenster zwischen den X vorherigen
        -- und Y folgenden Datensätzen


        ROWS BETWEEN <X> PRECEDING 
            AND <Y> FOLLOWING

    )
...

Anzahl der Datensätze vor und nach der aktuellen Zeile angeben

ROWS BETWEEN X PRECEDING AND Y FOLLOWING

"Gleitende" Berechnung, nicht laufend
CURRENT ROW kann weiterhin verwendet werden

<X>: # der zurückzuschauenden Zeilen

<Y>: # der vorauszuschauenden Zeilen

Gleitenden Durchschnitt erstellen

SELECT
    member_id, workout_date, calories_burned,

    -- Erstelle einen gleitenden Durchschnitt mit einem Fenster über das vorherige, 
    -- aktuelle und nächste Training

    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;

Gleitenden Durchschnitt erstellen

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

Mitglieder mit gleitenden Summen informieren

SELECT
    member_id, workout_date, calories_burned,

    ...

    -- Verwende CURRENT ROW, um "Vorausschauen" zu vermeiden
    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;

Mitglieder mit gleitenden Summen informieren

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

Lass uns üben!

Window Functions in Snowflake