Vensterframes

Vensterfuncties in Snowflake

Jake Roach

Field Data Engineer

Vensterframes

Output met een venster dat uit alle records bestaat.

Vensterframes

Output met vensters die records partitioneren op het veld member_id.

Dynamische vensterframes

Output met een "schuivend" vensterframe.

Een lopende berekening vinden

SELECT
    ...

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

        -- Venster tussen de eerste
        -- en de huidige rij


        ROWS BETWEEN UNBOUNDED PRECEDING 
            AND CURRENT ROW

    )
...

ROWS BETWEEN laat ons een dynamisch vensterframe maken

UNBOUNDED PRECEEDING AND CURRENT ROW
Eerste rij in de reeks tot en met de huidige rij
"Lopende" berekening, geen rolling
CURRENT ROW AND UNBOUNDED FOLLOWING

<1>: veld om op te berekenen

<2>: bepaalt volgorde, bouwt vensterframe

Lopende som van verbrande calorieën

SELECT
    member_id AS m_id, 
    calories_burned AS cb,

    -- Lopende som!
    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

CURRENT ROW AND UNBOUNDED FOLLOWING

SELECT
    member_id AS m_id, 
    calories_burned AS cb,

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

        -- Venster tussen huidige rij en laatste

        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

Trends in calorieverbranding

SELECT
    member_id,
    workout_date,
    calories_burned,

    AVG(calories_burned) OVER(   -- Lopend gemiddelde verbrande calorieën
        PARTITION BY member_id

        -- Maak een venster per work-outdatum, van de eerste tot de huidige
        ORDER BY workout_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

    ) AS avg_calories_burned

FROM FITNESS.workouts;

Trends in calorieverbranding


            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

Laten we oefenen!

Vensterfuncties in Snowflake