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

BI SQL FIRST_VALUE

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

BI SQL LAST_VALUE

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

BI SQL LAG

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

BI SQL LAG YOY

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

BI SQL LEAD