Window frames

Snowflake में Window Functions

Jake Roach

Field Data Engineer

Window frames

सभी रिकॉर्ड्स से बनी एक विंडो दिखाने वाला आउटपुट.

Window frames

`member_id` फ़ील्ड से रिकॉर्ड्स को पार्टिशन कर बनी विंडो का आउटपुट.

Dynamic window frames

"sliding" विंडो फ्रेम दिखाने वाला आउटपुट.

Running कैलकुलेशन ढूँढना

SELECT
    ...

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

        -- Window between the first
        -- and current row


        ROWS BETWEEN UNBOUNDED PRECEDING 
            AND CURRENT ROW

    )
...

ROWS BETWEEN हमें एक dynamic window frame बनाने देता है

UNBOUNDED PRECEEDING AND CURRENT ROW
क्रम की पहली पंक्ति से current row तक
"Running" कैलकुलेशन, rolling नहीं
CURRENT ROW AND UNBOUNDED FOLLOWING

<1>: जिस फ़ील्ड पर कैलकुलेशन होगा

<2>: क्रम निर्धारित करता है, window frame बनाता है

कैलोरीज़ का running total

SELECT
    member_id AS m_id, 
    calories_burned AS cb,

    -- Running total!
    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

        -- Window between current row and last

        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

कैलोरी-बर्निंग ट्रेंड्स

SELECT
    member_id,
    workout_date,
    calories_burned,

    AVG(calories_burned) OVER(   -- Running average of calories burned
        PARTITION BY member_id

        -- Crate a window by workout date, from the first workout to the current workout 
        ORDER BY workout_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

    ) AS avg_calories_burned

FROM FITNESS.workouts;

कैलोरी-बर्निंग ट्रेंड्स


            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

अभ्यास करते हैं!

Snowflake में Window Functions