Snowflake
Published in

Snowflake

Sliding Window function in Snowflake — Part 2

Sliding Window Definition:

Enables computing rolling values between any two rows (inclusive) in the window, relative to the current row.

Syntax:

slidingFrame ::=
{
ROWS BETWEEN <N> { PRECEDING | FOLLOWING } AND <N> { PRECEDING | FOLLOWING }
| ROWS BETWEEN UNBOUNDED PRECEDING AND <N> { PRECEDING | FOLLOWING }
| ROWS BETWEEN <N> { PRECEDING | FOLLOWING } AND UNBOUNDED FOLLOWING
}

ROWS BETWEEN is the windowing clause. It is used to specify what rows are considered while evaluating the analytic function.

Various description of windowing clause,

  • UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING : All preceding rows, current row, all following rows. [Click Here to know more about this topic]
  • 2 PRECEDING and 5 FOLLOWING : 2 Preceding rows, Current row and 5 Following rows.
  • 5 PRECEDING and CURRENT ROW : 5 Preceding rows and Current row.
  • CURRENT ROW and 1 FOLLOWING : Current row and 1 Following row.

For Example: {Preceding and Following}

For each film find an average rating of all films in its release year [2 Preceding Values and 1 Following values].

Source Table:

SQL:

SELECT ID, RELEASE_YEAR, RATING, 
MIN (RATING) OVER (PARTITION BY RELEASE_YEAR ORDER BY RATING ASC ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) AS MIN_RATING_2P_1F
FROM "COVID_DATA"."PUBLIC"."FLIM_RATING";

Output:

Explanation:

Complex part in above example is to drive the value (MIN_RATING_2F_1F) for the Year 2019. Please find the different iteration,

Year 2019 Rating:

Iteration:

Final Output for the Year 2019:

Additional Notes:

Some time's we will be dealing with the query rows between

(A) preceding and (B) preceding

(A) following and (B) following

Case 1: A=1 and B=1.

So here (A) behaves as we expect, that is it specifies the number of rows that are to be included along with the current row to perform the aggregate function that we have mentioned.

Now coming to (B), it specifies the number of rows (from below) that are to be excluded from included rows while performing aggregate function that we specify.

For better understanding,

Consider A=3 and B=1 and currently we are performing aggregate function on row number 7.

Then as A=3 we need to consider 4,5,6,7 rows to perform aggregate function.

Now B=1 says that 1 recent row should be excluded from selected rows i.e., 4,5,6,7 while performing mentioned aggregate function.

In our case(4,5,6,7) row number 7 should be excluded. And hence we’ll perform aggregate function on remaining rows i.e., 4,5,6 rows.

Snowflake screenshot attached:

Table (Numbers_1):

3 Preceding and 1 Preceding:

SELECT ID,YEAR,DIFF, 
SUM (DIFF) OVER (PARTITION BY ID ORDER BY DIFF ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS AGG_NUMBER
FROM NUMBERS_1;

Output:

Reference:

Welcome to Snowflake Documentation — Snowflake Documentation

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store