Using SQL Analytic Functions
These functions can return values at specific places in a window frame.
FIRST_VALUE
Returns the first value in the current window frame. The following query returns the list of products, their unit price and the least expensive product in the same category. Note that the least expensive product is fixed for each partition because the frame always starts at the first row (when omitted, it defaults to UNBOUNDED PRECEDING).
Example:
SELECT
ProductName,
UnitPrice,
FIRST_VALUE(ProductName) OVER (PARTITION BY CategoryID ORDER BY UnitPrice) AS LeastExpensive
FROM
Products
LAST_VALUE
Returns the last value in the current window frame. The following query returns the list of products, their unit price and the most expensive product in the same category. Note that the most expensive product is fixed for each partition because the frame ends at UNBOUNDED FOLLOWING.
Example:
SELECT
ProductName,
UnitPrice,
LAST_VALUE(ProductName) OVER (PARTITION BY CategoryID ORDER BY UnitPrice ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS MostExpensive
FROM
Products
ORDER BY
CategoryID, UnitPrice
LAG
Returns the value from a previous row in the same dataset, which allows to compare the current row with a previous row. By default, LAG returns the value of the row immediately preceding the current row, however a custom offset can be specified as the second, optional, parameter of the function. A third optional parameter can be used to specify a default value to return when the value being retrieved by LAG falls outside of the window frame: if this parameter is not specified then NULL is used as a default value. Both offset and default value can be a constant, a column reference or an expression.
The following query calculates yearly sales using a subquery and then uses LAG on the result in order to compute the year over year change.
Example:
SELECT
[Year],
toformat(Sales, 'n') AS Sales,
toformat((Sales / (LAG(Sales) OVER (ORDER BY [Year] ASC)) - 1) * 100, 'n') + '%' AS YoY
FROM
(
SELECT
year(OrderDate) AS [Year],
SUM(Quantity * UnitPrice * (1 - Discount)) AS [Sales]
FROM
Orders AS o INNER JOIN OrderDetails AS od ON od.OrderID = o.OrderID
GROUP BY
year(OrderDate)
) AS t
The following query calculates monthly sales using a subquery and then uses LAG on the result in order to compute a month over the same month of the previous year change using 12 as an offset for the LAG function. The example also uses the value of Sales as the default value so that the change results in 0 when a month to calculate the comparison does not exist.
Example:
SELECT
[Year],
[Month],
toformat(Sales, 'n') AS Sales,
toformat((Sales / (LAG(Sales, 12, Sales) OVER (ORDER BY [Year] ASC)) - 1) * 100, 'n') + '%' AS [Vs Prev. Year]
FROM
(
SELECT
year(OrderDate) AS [Year],
month(OrderDate) AS [Month],
SUM(Quantity * UnitPrice * (1 - Discount)) AS [Sales]
FROM
Orders AS o INNER JOIN OrderDetails AS od ON od.OrderID = o.OrderID
GROUP BY
year(OrderDate),
month(OrderDate)
) AS t
LEAD
Returns the value from a following row in the same dataset. By default, LEAD returns the value of the row immediately following the current row, however a custom offset can be specified as the second, optional, parameter of the function. A third optional parameter can be used to specify a default value to return when the value being retrieved by LEAD falls outside of the window frame: if this parameter is not specified then NULL is used as a default value. Both offset and default value can be a constant, a column reference or an expression.
The following query calculates monthly sales by employee for the year 1997 and then uses the LEAD function partitioned by Employee and sorted by month to show the next month sales per employee.
Example:
SELECT
[Employee],
[Month],
toformat(Sales, 'n') AS Sales,
toformat(LEAD(Sales, 1, 0) OVER (PARTITION BY Employee ORDER BY [Month] ASC), 'n') AS [Next Month Sales]
FROM
(
SELECT
e.FirstName + ' ' + e.LastName AS Employee,
month(OrderDate) AS [Month],
SUM(Quantity * od.UnitPrice * (1 - Discount)) AS [Sales]
FROM
Orders AS o INNER JOIN OrderDetails AS od ON od.OrderID = o.OrderID
INNER JOIN
Employees AS e ON e.EmployeeID = o.EmployeeID
WHERE
year(OrderDate) = 1997
GROUP BY
e.FirstName + ' ' + e.LastName,
month(OrderDate)
) AS t
