Window Aggregate Functions in SQL

What is window function: Imagine you have a dataset with many rows, and you want to perform some calculations on each row based on a group of rows related to it. This group of rows is called a "window" or "frame."

In the context of databases and data analysis, a window function, also known as an analytic function or windowed function, is a type of function that performs a calculation across a set of rows related to the current row within a partition of a result set. This set of rows is called a "window" or "frame."

The basic syntax for using window aggregate functions is as follows:

<Window Function> (<expression>) OVER (
    &#091;PARTITION BY <partition_column(s)>]
    ORDER BY <order_column(s)>
    &#091;ROWS <frame_specification>]
)

Calculate a Running Total

SELECT 
	 custid, 
	 orderid, 
	 val,
	 SUM(val) OVER(PARTITION BY custid) AS custtotal,
	 SUM(val) OVER() AS grandtotal
FROM Sales.OrderValues

suppose that you wanted to query the Sales.OrderValues view and compute the running total values from the beginning of the current customer’s activity until the current order. You need to use the SUM aggregate.

SELECT custid, orderid, orderdate, val,
	SUM(val) OVER(PARTITION BY custid
	ORDER BY orderdate, orderid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT     ROW) AS runningtotal
FROM Sales.OrderValues

--OR----

SELECT custid, orderid, orderdate, val,
	SUM(val) OVER(PARTITION BY custid ORDER BY orderdate, orderid) AS runningtotal
FROM Sales.OrderValues

As another example for a window frame extent, if you wanted the frame to include only the last three rows, you would use the form ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.

SQL Copy code

SELECT custid, orderid, orderdate, val,
	SUM(val) OVER(PARTITION BY custid
	ORDER BY orderdate, orderid ROWS BETWEEN 2 PRECEDING AND CURRENT     ROW) AS runningtotal
FROM Sales.OrderValues

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW: This part specifies the window frame for the running total calculation. It instructs the SQL engine to consider the current row and the two preceding rows in the ordering specified (orderdate, orderid) within each partition. So, for each row in the result set, the running total will include the values of the current row and the two rows that come before it according to the specified ordering.

Window Ranking Functions

WITH CTE AS (
	SELECT TOP 10 custid, orderid, val,
		 ROW_NUMBER() OVER(ORDER BY val) AS rownum, 
		 RANK() OVER(ORDER BY val) AS rnk, 
		 DENSE_RANK() OVER(ORDER BY val) AS densernk
		 
	FROM Sales.OrderValues
	)
SELECT *,
	NTILE(5) OVER(ORDER BY val) AS ntile5, 
--arrange the 10 rows within the 5 partition 10/5 = 2 (2 rows in each partition)
	NTILE(6) OVER(ORDER BY val) AS ntile6 
--arrange the 10 rows within the 6 partition 10/6 = 1 reminder = 4  (2 rows in 4 section and 5 and 6 section have 1 row only. )
FROM CTE

• rownum: It assigns a unique row number to each row based on the ascending order of the val column.
• rnk: It calculates the rank of each row based on the ascending order of the val column. Rows with the same value will have the same rank, and the next rank will be skipped.
• densernk: It calculates the dense rank of each row based on the ascending order of the val column. Rows with the same value will have the same rank, and the next rank will not be skipped.

The comments provided in the code explain the result for ntile5 and ntile6 more explicitly. For ntile5, it divides the 10 rows into 5 partitions with 2 rows in each partition. For ntile6, it divides the 10 rows into 6 partitions with 2 rows in 4 of the partitions and 1 row each in the remaining 2 partitions (due to the remainder).