SQL Window Functions

Window functions perform calculations on a set of rows (the window).

And, this set of rows is somehow related to the current row.

The result of the calculations is not returned as a single value.

Instead, they are added as a new column to the current row.

Example

#

This function lists all orders with a running total.

SELECT FirstName, LastName, OrderNumber, TotalAmount, 
       SUM(TotalAmount) OVER (ORDER BY OrderNumber) AS 'Running Total'
  FROM [Order] O
  JOIN Customer C ON C.Id = O.CustomerId
Result:  830 records
FirstName LastName OrderNumber TotalAmount Running Total
Paul Henriot 542378 440.00 440.00
Karin Josephs 542379 1863.40 2303.40
Mario Pontes 542380 1813.00 4116.40
Mary Saveley 542381 670.80 4787.20
Pascale Cartrain 542382 3730.00 8517.20
Mario Pontes 542383 1444.80 9962.00

Notice how the Running Total increments with each row.


Categories

Window Functions fall into three categories.
Each category lists their functions.

Category Functions
Aggregate Window Functions SUM, MAX, MIN, AVG, and COUNT
Ranking Window Functions RANK, DENSE_RANK, ROW_NUMBER, and NTILE
Value Window Functions LAG, LEAD, FIRST_VALUE, and LAST_VALUE

Syntax

Syntax for window functions.

WINDOW ( ALL expression ) 
  OVER ( PARTITION BY partition_list 
         ORDER BY order_list )

WINDOW -- the window function, such as, SUM, RANK, or LAG.

ALL -- an optional modifier to include all values, including duplicates. This is the default. DISTINCT is not supported by window functions.

expression -- the values used in the calculations; usually a column name.

OVER -- specifies the window (range) clauses and sort order.

PARTITION BY partition_list -- Defines the window (set of rows) where the window function will be applied. The partition_list is a comma-separated list of column names used in the partitioning. If this clause is not present, grouping will be done in the entire table and values will be aggregated accordingly..

ORDER BY order_list -- sorts rows within each partition. If not specified, it will order by the entire table.


More Examples

LAG

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
Problem: List orders with a previous order date and the # of days between these dates.
SELECT OrderNumber, 
       CAST(OrderDate AS DATE) AS OrderDate,
       CAST(LAG(OrderDate, 1) 
            OVER(ORDER BY OrderDate ASC) AS DATE) AS 'Prev OrderDate',
       DATEDIFF(day, LAG(OrderDate, 1) 
            OVER(ORDER BY OrderDate ASC), OrderDate) AS 'Days Between'
  FROM [Order]

LAG returns the previous value of the specified column. As expected, the first record returns NULL values.

Result:  830 records
OrderNumber OrderDate Prev OrderDate Days Between
542378 2012-07-04 NULL NULL
542379 2012-07-05 2012-07-04 1
542380 2012-07-08 2012-07-05 3
542381 2012-07-08 2012-07-08 0
542382 2012-07-09 2012-07-08 1
542383 2012-07-10 2012-07-09 1
542384 2012-07-11 2012-07-10 1
542385 2012-07-12 2012-07-11 1
542386 2012-07-15 2012-07-12 3

RANK

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: For all customers, rank their cities within country.
SELECT FirstName, LastName, City, Country, 
       RANK() OVER(PARTITION BY Country ORDER BY City) AS Rank
  FROM Customer

RANK returns the sequential number of a row within a partition of a result set. It is similar to ROW_NUMBER, except that RANK returns the same numeric value with ties.

Result:  91 records
FirstName LastName City Country Rank
Patricio Simpson Buenos Aires Argentina 1
Yvonne Moncada Buenos Aires Argentina 1
Sergio Gutiérrez Buenos Aires Argentina 1
Roland Mendel Graz Austria 1
Georg Pipps Salzburg Austria 2
Catherine Dewey Bruxelles Belgium 1
Pascale Cartrain Charleroi Belgium 2
André Fonseca Campinas Brazil 1
Paula Parente Campinas Resende 2

SUM. ROW_NUMBER.

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: List orders with total amount by city sorted from low to high. Add row numbers within each city.
SELECT FirstName, LastName, City, OrderNumber, TotalAmount,
       SUM(TotalAmount) OVER(PARTITION BY City) AS 'Total Spent',
       ROW_NUMBER() OVER(PARTITION BY City ORDER BY TotalAmount) AS 'Row Number'
  FROM [Order] O
  JOIN Customer C ON C.Id = O.CustomerId

ROW_NUMBER returns a sequential number of a row within a partition of a result set.

Result:  830 records
FirstName LastName City OrderNumber TotalAmount Total Spent Row Number
Palle Ibsen Århus 542521 86.40 3763.21 1
Palle Ibsen Århus 543197 86.85 3763.21 2
Palle Ibsen Århus 542927 420.00 3763.21 3
Palle Ibsen Århus 542493 447.20 3763.21 4
Palle Ibsen Århus 542955 1030.76 3763.21 5
Palle Ibsen Århus 543166 1692.00 3763.21 6
Paula Wilson Albuquerque 542392 624.80 52245.90 1
Paula Wilson Albuquerque 542891 624.80 52245.90 2
Paula Wilson Albuquerque 542699 977.50 52245.90 3
Paula Wilson Albuquerque 543130 1075.00 52245.90 4

You may also like



Guides


vsn 3.1