Understanding SQL Server Window Functions
Also known as SQL Server analytic functions, helps in building complex aggregation queries simpler. We are going to look at its typical use cases, and how to use ORDER BY and PARTITION BY to set up frames for windows functions including RANGE and ROWS. Windows Functions can be used for many cases like the Computation of running totals and averages, Building Rankings, Best and Worst Performers monthly, quarterly, yearly, Sales Reports, Investigating Trends and their analysis etc.
Windows Functions are different from regular aggregate functions, as aggregate functions operate on an entire dataset with the help of the GROUP BY clause, while windows functions operate on a set of rows and return aggregated value for each row. It doesn’t combine or group rows into a single output row like in the GROUP BY clause, it just adds aggregated value to each row, maintaining separate identities of each row.
For example, with a window function, we computed revenue for the 3 days i.e. the sum of revenue amount of the current day, the previous day and the following day as in the image below.
Types of Window Functions - Aggregate Functions -> SUM(), MAX(), MIN(), COUNT(), AVG() Ranking Functions -> ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE() Valued Functions -> FIRST_VALUE(), LAST_VALUE(), LAG(), LEAD()
*Here, the term Windows refers to the set of rows in the database on which the function is going to operate. And we define these sets of rows (on which function operates) with the help of the OVER() clause. *In this part of the series, first we will discuss the OVER() clause.
Intro - As mentioned earlier, we will discuss the OVER() clause in this series. It is one of the basic components of the Window Function.
Syntax - window_function(expression | column_name) OVER( [PARTITION BY partition_list ] [ ORDER BY order_list ] )
***window_function: *** Here, we specify the name of the window function we want to use, link one mentioned above in types of window functions.
expression: Contains the name of the column or the expression for which we wanted aggregated value.
PARTITION BY partition_list: Here, we set the rows on which the window function operates. We provide column or column names separated by a comma(in case of multiple columns) for the partition_list. Note if partition by clause is omitted then the window function will set the whole table as a frame for operations.
ORDER BY order_list: This list helps us, in sorting the rows of each partition.
In the next series, we will look at the Computations, and Aggregation, using OVER() with the Where clause. For that, we will create dummy data for further discussion and examples to understand.