An extremely common use of SQL Server is grouping and aggregating data for reports. A simple example is to use the SUM() function with the GROUP BY clause to calculate a total for a particular group. For example, you can find the monthly sales for a product quite easily.
All examples are written for the AdventureWorks2014 database.
This is a great function, but SQL windowing functions are much more powerful than this. For starters, what if you wanted to see a running total of units sold for the year along with the current month’s value? You can use the SUM() function again. This time combine SUM() with the OVER() clause to calculate the value for each row.
Both examples show how we can use the SUM() function to create data for reports. What if we were looking for anomalies in the data? Let’s look for months where sales were outside the norm. For this task, we’ll use the LAG() function to compare our current row.
Row 11 (March of 2014) shows a 1400% increase over the previous month. This could raise red flags and for someone to further investigate the sales of February and March.
SQL Server introduced the OVER() function in SQL 2005, but the powerful windowing functions were not added until SQL 2012.
What about performance? If you use a windowing function to replace a CTE you can reduce I/O operation. Windowing functions are often less complex than CTEs , making it easier to understand the intent of the query.
Jeremiah Peschka wrote an article detailing how to implement a rolling average using a windowing function. He provides insight the I/O operation.
docs.Microsoft.com has details on the OVER clause. The page also has links to ranking, aggregate and analytic functions.