Window Functions in SQL Server: Part One: The OVER() Clause

Window functions were first introduced in standard SQL 2003 and then extended in SQL 2008. Since SQL Server 2005, Microsoft has been steadily improving window functions in the database engine. These functions perform an aggregate operation against a user-defined range of rows (the window) and return a detail-level value for each row.

Well-known aggregate functions include SUM, AVG, MIN, MAX, and many others. Recent versions of SQL Server have introduced ranking and analytic functions such as LAG, LEAD, RANK, CUME_DIST, and many others.

We'll explore SQL Server window functions as we go through this series. However, the clause that allows the application of these functions should be detailed first – OVER(). And that's what we'll do in this article.

Getting OVER() Window Functions

OVER() defines the user-specified range on which an aggregate function is applied, returning the results in addition to the existing dataset. The key difference between GROUP BY and OVER() is that GROUP BY applies aggregations across the entire query, rolling up the returned dataset by the specified non-aggregate fields. This will generally reduce the number of rows returned. With OVER() the same number of rows will be returned as the base query. Any aggregations will be returned on each row, based on the range identified in the OVER() clause.

The following table and data will be used in subsequent examples. The PhysicalSkill and MentalSkill columns are measurements of the person represented in FullName. House is an organization of several individuals for grouping purposes.

CREATE TABLE WindowTable (
WindowID INT IDENTITY,
House Varchar(32),
FullName Varchar(64),
PhysicalSkill Decimal(4, 2),
MentalSkill Decimal (4, 2)
)
GO

INSERT INTO WindowTable
(House, FullName, PhysicalSkill, MentalSkill)
VALUES 
('Stark', 'Robb Stark', 6, 6),
('Stark', 'Ned Stark', 8, 7),
('Stark', 'Bran Stark', 2, 9),
('Stark', 'Arya Stark', 4, 6),
('Lannister', 'Jamie Lannister', 7, 5),
('Lannister', 'Tyrion Lannister', 3, 10),
('Lannister', 'Tywin Lannister', 3, 8)
GO

A simple SELECT of the data returns the following:

SELECT 
  House,
  FullName,
  PhysicalSkill,
  MentalSkill
FROM WindowTable

With GROUP BY, summing up the skill levels for each house will get rid of some details that may be useful in future statements:

SELECT 
  House,
  SUM(PhysicalSkill) AS TotalPhysicalSkillByHouse,
  SUM(MentalSkill) AS TotalMentalSkillByHouse
FROM WindowTable
GROUP BY House

Full names and individual skill levels can't be included without making them part of the aggregation, which isn't ideal here. However, OVER() can maintain detail levels while still returning the summed values:

SELECT
  House,
  FullName,
  PhysicalSkill,
  SUM(PhysicalSkill) OVER (PARTITION BY House) AS TotalPhysicalSkillByHouse,
  MentalSkill,
  SUM(MentalSkill) OVER (PARTITION BY House) AS TotalMentalSkillByHouse
FROM WindowTable

Each window (pink border) contains the summed values for the requested partition only (By House, which is shown with a blue border) at the base query detail level.

It's possible to continue narrowing down the window, using ROWS or RANGE. This example uses ROWS:

SELECT 
  House,
  FullName,
  PhysicalSkill,
  SUM(PhysicalSkill)
    OVER (PARTITION BY House) AS TotalPhysicalSkillByHouse,
  MentalSkill,
  SUM(MentalSkill)
    OVER (PARTITION BY House ORDER BY House ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS MentalSkillThisRowAndOneMore,
  SUM(MentalSkill)
    OVER (PARTITION BY House) AS TotalMentalSkillByHouse
FROM WindowTable
WHERE House = 'Stark'

For clarity, this example is limited to the Stark house. The additional clause ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING is best translated as "Only aggregate this row and the one right after it". The 6.00 and 7.00 on rows one and two add to 13.00, and so forth.

The last row contains only its own value, as there is no FOLLOWING row. PRECEDING is also a valid keyword in this context. UNBOUNDED PRECEDING can be used to produce a running total, which can be highly useful in reporting:

SELECT
  House,
  FullName, 
  PhysicalSkill,
  SUM(PhysicalSkill)
    OVER (PARTITION BY House) AS TotalPhysicalSkillByHouse,
  MentalSkill,
  SUM(MentalSkill)
    OVER (PARTITION BY House ORDER BY House ROWS UNBOUNDED PRECEDING) AS MentalSkillRunningTotal,
  SUM(MentalSkill)
    OVER (PARTITION BY House) AS TotalMentalSkillByHouse
FROM WindowTable
WHERE House = 'Stark'

This example illustrates the difference between ROWS and RANGE:

SELECT
  House,
  FullName,
  PhysicalSkill,
  SUM(PhysicalSkill)
    OVER (PARTITION BY House) AS TotalPhysicalSkillByHouse,
  MentalSkill,
  SUM(MentalSkill)
    OVER (PARTITION BY House ORDER BY House ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS MentalSkillRows,
  SUM(MentalSkill)
    OVER (PARTITION BY House ORDER BY House RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS MentalSkillRange,
  SUM(MentalSkill)
    OVER (PARTITION BY House) AS TotalMentalSkillByHouse
FROM WindowTable

The same clause is used for both ROWS and RANGE, but the results are different. There are two partitions, one for each house. The window function is computed for each partition separately. ROWS operates on an individual row level; in the above example, it produces a running total using the current row and all previous rows in the window. The value changes on each row.

RANGE operates at the logical level of the window. Instead of a running total on each row, the summation is done at the window level. Windows are defined at the House level, so RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW computes the total mental skill for each house.

Select 
  House,
  FullName,
  PhysicalSkill,
  SUM(PhysicalSkill)
    OVER (PARTITION BY House) AS TotalPhysicalSkillByHouse,
  MentalSkill,
  SUM(MentalSkill)
    OVER (ORDER BY House ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS MentalSkillRows,
  SUM(MentalSkill)
    OVER (ORDER BY House RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS MentalSkillRange,
  SUM(MentalSkill)
    OVER (PARTITION BY House) AS TotalMentalSkillByHouse
FROM WindowTable

In this example, there is one partition. The window is defined at the House level. RANGE adds up the values at the window level. For rows in the Lannister window, it sums the mental skills of rows within this window and all preceding windows. Since Lannister is the first window, it only sums mental skills for all Lannister rows. For the Stark window, RANGE sums the mental skills within the current window (Stark) and all preceding windows (there is only one, which happens to be Lannister). Since there are only two windows, two distinct values (23 and 51) are returned. ROWS continue to operate at the row level.

In the next article, we'll dig into some of the functions that, used in conjunction with OVER(), can produce detailed analyses with minimal effort.