Cadres de fenêtre

Fonctions de fenêtre dans Snowflake

Jake Roach

Field Data Engineer

Cadres de fenêtre

Sortie montrant une fenêtre composée de tous les enregistrements.

Cadres de fenêtre

Sortie montrant des fenêtres construites en utilisant le champ member_id pour partitionner les enregistrements.

Cadres de fenêtre dynamiques

Sortie montrant un cadre de fenêtre "glissant".

Trouver un calcul cumulatif

SELECT
    ...

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

        -- Fenêtre entre la première
        -- et la ligne actuelle


        ROWS BETWEEN UNBOUNDED PRECEDING 
            AND CURRENT ROW

    )
...

ROWS BETWEEN nous permet de créer un cadre de fenêtre dynamique

UNBOUNDED PRECEEDING AND CURRENT ROW
Première ligne de la séquence jusqu'à la ligne actuelle
Calcul "cumulatif", pas roulant
CURRENT ROW AND UNBOUNDED FOLLOWING

<1>: champ pour le calcul

<2>: séquence les résultats, construit le cadre de fenêtre

Total cumulatif des calories brûlées

SELECT
    member_id AS m_id, 
    calories_burned AS cb,

    -- Total cumulatif !
    SUM(calories_burned) OVER(
        PARTITION BY member_id
        ORDER BY workout_date


        ROWS BETWEEN UNBOUNDED PRECEDING
            AND CURRENT ROW


    ) AS running_total

FROM fitness.workouts;


         m_id  |    cb   |  running_total
       ------- | ------- | --------------
        m_192  |   105   |      105 
        m_192  |   156   |      261 
        m_192  |    69   |      330 
        m_192  |   102   |      432  

        m_74   |   374   |      374  
        m_74   |   396   |      770  
        m_74   |   504   |     1274  

        m_233  |    51   |       51  
        m_233  |    81   |      132

LIGNE ACTUELLE ET SUIVANT SANS LIMITE

SELECT
    member_id AS m_id, 
    calories_burned AS cb,

    SUM(calories_burned) OVER(
        PARTITION BY member_id
        ORDER BY workout_date

        -- Fenêtre entre la ligne actuelle et la dernière

        ROWS BETWEEN CURRENT ROW
            AND UNBOUNDED FOLLOWING


    ) AS left_to_burn

FROM fitness.workouts;


         m_id  |    cb   |  left_to_burn
       ------- | ------- | --------------
        m_192  |   105   |      432 
        m_192  |   156   |      327 
        m_192  |    69   |      171 
        m_192  |   102   |      102  

        m_74   |   374   |     1274  
        m_74   |   396   |      900 
        m_74   |   504   |      504  

        m_233  |    51   |      132  
        m_233  |    81   |       81

Tendances de calories brûlées

SELECT
    member_id,
    workout_date,
    calories_burned,

    AVG(calories_burned) OVER(   -- Moyenne des calories brûlées
        PARTITION BY member_id

        -- Créer une fenêtre par date d'entraînement, du premier au courant
        ORDER BY workout_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

    ) AS avg_calories_burned

FROM FITNESS.workouts;

Tendances de calories brûlées


            member_id  |  workout_date  |  calories_burned  |  average_calories_burned
           ----------- | -------------- | ----------------- | -------------------------
              m_192    |   2025-01-01   |        105        |          105.0
              m_192    |   2025-01-03   |        156        |          130.5
              m_192    |   2025-01-04   |         69        |          110.0
              m_192    |   2025-01-10   |        102        |          108.0

               m_74    |   2025-02-10   |        374        |          374.0
               m_74    |   2025-02-13   |        396        |          385.0
               m_74    |   2025-02-14   |        504        |          426.7

              m_233    |   2025-03-05   |         51        |           51.0
              m_233    |   2025-03-12   |         81        |           66.0

Passons à la pratique !

Fonctions de fenêtre dans Snowflake