How can we access other rows in a set while accessing one particular row? In other words, can we access values in other rows other than current row while the current row is being processed? There were no built-in functions for supporting this functionality with previous versions of SQL Server but there were many ways of getting the required result generated. One common way was linking the same table to itself either using as a derived table or CTE. Microsoft SQL Server offers four offset functions for supporting this requirement. Here is note on it;
SQL Server 2012 Offset Functions: LAG, LEAD, FIRST_VALUE, LAST_VALUE
Offset functions allow to access values located in other rows while accessing the current row;
Function | Description |
LAG | LAG works on window partition and window order clauses. It allows to access a value of a row at a certain offset from the current row which appears before the current row based on the order specified. It accepts three parameters; value (or column) which needs to be returned, offset as optional (1 is default), and default value to be returned in case of no row at the specified offset (null is default). |
LEAD | LEAD works on same manner, just like LAG. Only different is, while LAG is looking for records before the current row, LEAD is looking for records after the current row. |
FIRST_VALUE | This allows to access values from the first row in the window frame. The first value of the first row is accessed with a window frame extent ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. |
LAST_VALUE | This allows to access values from the last row in the windows frame. The extent ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING should be used with LAST_VALUE. |
Here is an example for offset functions;
- USE AdventureWorks2012
- GO
- -- Create a view for forming a set that contains sales amount for years and months.
- CREATE VIEW dbo.SalesView
- AS
- SELECT YEAR(h.OrderDate) OrderYear, MONTH(OrderDate) OrderMonth
- , SUM(h.SubTotal) Total
- FROM Sales.SalesOrderHeader h
- GROUP BY YEAR(h.OrderDate), MONTH(OrderDate)
- GO
- SELECT
- OrderYear, OrderMonth, Total
- -- Getting previous month value for the year
- , LAG(Total, 1, 0) OVER (PARTITION BY OrderYear ORDER BY OrderMonth) PreviousMonthTotal
- -- Getting next month value for the year
- , LEAD(Total, 1, 0) OVER (PARTITION BY OrderYear ORDER BY OrderMonth) NextMonthTotal
- -- Getting first month value for the year
- , FIRST_VALUE(Total) OVER (PARTITION BY OrderYear ORDER BY OrderMonth ROWS BETWEEN UNBOUNDED PRECEDING
- AND CURRENT ROW) FirstMonthValue
- -- Getting last month value for the yea
- , LAST_VALUE(Total) OVER (PARTITION BY OrderYear ORDER BY OrderMonth ROWS BETWEEN CURRENT ROW
- AND UNBOUNDED FOLLOWING) LastMonthValue
- FROM dbo.SalesView
- ORDER BY 1, 2
No comments:
Post a Comment