Moyennes mobiles et totaux

Fonctions de fenêtre dans Snowflake

Jake Roach

Field Data Engineer

Moyennes et totaux en cours

Sortie montrant une fenêtre "glissante".

Moyennes et totaux "mobiles"

Sortie représentant une moyenne mobile.

Moyennes et totaux "mobiles"

Sortie représentant un total mobile.

Calculs mobiles

SELECT
    ...

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

        -- Fenêtre entre les X précédents
        -- et Y suivants enregistrements


        ROWS BETWEEN <X> PRECEDING 
            AND <Y> FOLLOWING

    )
...

Spécifiez le nombre d'enregistrements avant et après la ligne actuelle

ROWS BETWEEN X PRECEDING AND Y FOLLOWING

Calcul "mobile", pas en cours
Peut encore utiliser CURRENT ROW

<X> : # de lignes à regarder en arrière

<Y> : # de lignes à regarder en avant

Créer une moyenne mobile

SELECT
    member_id, workout_date, calories_burned,

    -- Créer une moyenne mobile en utilisant une fenêtre avec l'entraînement précédent, 
    -- l'entraînement actuel et le suivant

    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;

Créer une moyenne mobile

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

Informer les membres avec des totaux mobiles

SELECT
    member_id, workout_date, calories_burned,

    ...

    -- Utiliser CURRENT ROW pour éviter de "regarder en avant"
    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;

Informer les membres avec des totaux mobiles

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

Passons à la pratique !

Fonctions de fenêtre dans Snowflake