Finestre di frame

Window Functions in Snowflake

Jake Roach

Field Data Engineer

Finestre di frame

Output che mostra una finestra composta da tutti i record.

Finestre di frame

Output che mostra finestre costruite usando il campo member_id per partizionare i record.

Finestre dinamiche

Output che mostra una finestra "scorrevole".

Trovare un calcolo cumulativo

SELECT
    ...

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

        -- Finestra tra la prima
        -- e la riga corrente


        ROWS BETWEEN UNBOUNDED PRECEDING 
            AND CURRENT ROW

    )
...

ROWS BETWEEN ci permette di creare una finestra dinamica

UNBOUNDED PRECEEDING AND CURRENT ROW
Prima riga in sequenza fino alla riga corrente
Calcolo "cumulativo", non mobile
CURRENT ROW AND UNBOUNDED FOLLOWING

<1>: campo per il calcolo

<2>: sequenza risultati, costruisce finestra

Totale cumulativo delle calorie bruciate

SELECT
    member_id AS m_id, 
    calories_burned AS cb,

    -- Totale cumulativo!
    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

RIGA CORRENTE E UNBOUNDED FOLLOWING

SELECT
    member_id AS m_id, 
    calories_burned AS cb,

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

        -- Finestra tra riga corrente e ultima

        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

Tendenze di calorie bruciate

SELECT
    member_id,
    workout_date,
    calories_burned,

    AVG(calories_burned) OVER(   -- Media mobile delle calorie bruciate
        PARTITION BY member_id

        -- Crea una finestra per data allenamento, dal primo all'ultimo
        ORDER BY workout_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

    ) AS avg_calories_burned

FROM FITNESS.workouts;

Tendenze di calorie bruciate


            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

Ayo berlatih!

Window Functions in Snowflake