After seeing SQL Window Function in the previous post, we will look at how to set the Window Frame for Window Functions. Mainly we are going to talk about Window Frames. Window Frame helps us to granularly manage which rows or sets of rows are to be taken into account when computing the result set. Note that, the frame definition is always relative to the current row. In simpler terms, it helps us to define the upper bound and lower bound of the query sets.
The simplest examples will be making frame access preceding 2 rows and following 2 rows based on the current row, and other variations with or without partitioning or grouping row sets. These types of variations open up many possibilities for complex use cases.
Basic Syntax is OVER (… ORDER BY order_list [ ROWS | RANGE ] )
We will see two kinds of Window frames namely ROWS, and RANGE. But before that, let's create a dummy daily_revenue table and seed it will data for our further demonstrations.
CREATE TABLE dbo.daily_revenue(
date date NOT NULL,
amount numeric(10, 2) NOT NULL
)
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2021-11-22' AS Date), CAST(6660.18 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2021-11-03' AS Date), CAST(8431.62 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2021-11-11' AS Date), CAST(1588.48 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2021-12-01' AS Date), CAST(7769.18 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2021-07-24' AS Date), CAST(9273.53 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2021-09-30' AS Date), CAST(2094.20 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2022-02-12' AS Date), CAST(7923.16 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2021-11-11' AS Date), CAST(45646.48 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2021-12-01' AS Date), CAST(466.18 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2021-07-24' AS Date), CAST(9546.53 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2021-06-17' AS Date), CAST(7026.66 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2021-12-27' AS Date), CAST(6766.42 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2021-11-15' AS Date), CAST(1172.48 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2021-05-16' AS Date), CAST(7693.74 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2021-12-02' AS Date), CAST(5484.18 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2021-05-13' AS Date), CAST(64568.26 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2021-12-02' AS Date), CAST(416.18 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2021-05-13' AS Date), CAST(2514.26 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2021-12-02' AS Date), CAST(5456.18 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2021-05-13' AS Date), CAST(48658.26 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2021-08-27' AS Date), CAST(3099.39 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2022-02-14' AS Date), CAST(4045.74 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2022-01-09' AS Date), CAST(4919.60 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2022-02-22' AS Date), CAST(5615.51 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2022-01-18' AS Date), CAST(2466.82 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2021-05-23' AS Date), CAST(9807.79 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2021-09-15' AS Date), CAST(2679.22 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2022-01-05' AS Date), CAST(3473.71 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2021-08-19' AS Date), CAST(3260.89 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2021-11-20' AS Date), CAST(4883.54 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2021-10-31' AS Date), CAST(2498.17 AS Numeric(10, 2)))
GO
INSERT dbo.daily_revenue (date, amount) VALUES (CAST(N'2021-11-14' AS Date), CAST(7787.83 AS Numeric(10, 2)))
GO
ROWS
ROWS is a part of the ORDER BY sub-clause of Window Functions which help in defining bounds for each row in the query result. The basic Syntax is as follows,
… ORDER BY order_list ROWS BETWEEN bounds
The bounds mentioned in the above syntax are the part where we mention the Lower bound and Upper bound for the query set. Suppose in the table created above, we wanted to find the running total of the revenue for the dates.
SELECT
date,
amount,
SUM(amount) OVER(ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as total_amount_1,
SUM(amount) OVER(ORDER BY date ASC ROWS UNBOUNDED PRECEDING) as total_amount_2
FROM daily_revenue;
In the above query, we made a running sum for the amount column with 2 examples variations naming them total_amount_1 and total_amount_2. For each row, we add the current_row and all the previous rows as we mentioned unbounded preceding the sum.
The bounds mentioned a few lines above can be of any five options
Unbounded Preceding - The First possible row.
n Preceding - The n-th row before the current row.
Current Row - The current row only.
n Following - The n-th row after the current row.
Unbounded Following - The Last possible row. Always mention the Lower Bounds part first, then mention the Upper Bound part. Another example is before winding up the ROWS part. For each row of daily_revenue, we have to return rows with date, revenue, and average revenue(calculated by taking previous date revenue, current date revenue, and following date revenue) as average_revenue_3_days.
SELECT
date,
amount,
AVG(amount) OVER(ORDER BY date ASC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as avg_revenue_3_days
FROM daily_revenue;
Since we wanted to take an average of the previous date revenue, current date revenue, and following date revenue, we used ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING for setting up Window Frame.
RANGE
This is the second type of Window Frame. The difference between ROWS and RANGE is that RANGE will take into account all rows that have the same value in the column by which we order by. The basic Syntax is as follows,
… ORDER BY order_list RANGE BETWEEN bounds
The bounds mentioned in the above syntax are the part where we mention the Lower bound and Upper bound for the query set. In our first demonstration above we calculated the running total of the revenue. But if you hadn’t noticed, there were multiple records of the same date a few times and now we are only interested in knowing the sum revenue at the end of the date. This can be implemented by changing from ROWS to RANGE.
SELECT
date,
amount,
SUM(amount) OVER(ORDER BY date ASC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as end_day_revenue
FROM daily_revenue;
Notice the colored part in the above image. Some rows share the same running total revenue because they are of the same date. The bound options for Range are as follows
RANGE UNBOUNDED PRECEDING
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
RANGE CURRENT ROW You can't use n PRECEDING and n FOLLOWING with RANGE though.
You must keep in mind the default window frame, if you don't specify an ORDER BY clause within OVER(...), the whole partition of rows will be the window frame. And if you do specify an ORDER BY clause within OVER(...), the database will assume RANGE UNBOUNDED PRECEDING as the window frame.
Summary
Window Frame can be defined within the OVER(…) clause. It can be either rows or ranges.
ROWS always treat rows individually like ROW_NUMBER() function.
RANGE always clubs or groups the rows that share the same value in the column we order by like the RANK() function, wherein as per partition criteria if the same value then it has the same ranks.
Bound Options are as below;
UNBOUNDED PRECEDING,
n PRECEDING (for ROWS only),
CURRENT ROW,
n FOLLOWING (for ROWS only),
UNBOUNDED FOLLOWING