Sharp SQL2014: Based on the window offset calculation, sql2014 window offset

Sharp SQL2014: Based on the window offset calculation, sql2014 window offset
 

SQL Server 2012 introduces four offset functions: LAG, LEAD, FIRST_VALUE, and LAST_VALUE, which are used to return an element from an offset of the current row, or from the beginning or end of a window frame.

LAG and LEAD support window partitioning and window sorting clauses. FIRST_VALUE and LAST_VALUE support window partitioning and window sorting clauses, as well as window frame clauses.

9.5.1 LAG and LEAD functions

The LAG function is used to search before the current row, and the LEAD function is used later. The first parameter (required) of the function specifies the column to return values. The second parameter (optional) is the offset (if not specified, the default value is 1), and the third parameter (optional) it is the default value when the request offset does not return rows (if not specified, the default value is NULL ).

The following example uses the Orders table created in section 9.2 to query the sales of employees in the previous and next quarters. The LAG function returns the sales volume for the previous quarter, and the LEAD function returns the sales value for the next quarter. The query results are shown in Table 9-18.

SELECT EmpID, SalesYear, SalesQuarter,

LAG (SubTotal) OVER (PARTITION BYEmpID

Order by EmpID, SalesYear, SalesQuarter) AS PrevVal,

SubTotalAS CurVal,

LEAD (SubTotal) OVER (partition by EmpID

Order by EmpID, SalesYear, SalesQuarter) AS NextVal

FROM dbo. Orders;

 

Because no offset is specified, the function is assumed to be the default value 1. The third parameter is not specified. If there is no previous or next line, it is assumed that the default value is NULL. The following query is used to obtain the sales of the employee in the second quarter and the second quarter. The expression LAG (SubTotal, 2, 0) gets the value from the second row, if no row is found, 0 is returned. The query results are shown in Table 9-19.

SELECT EmpID, SalesYear, SalesQuarter,

LAG (SubTotal, 2, 0) OVER (partition by EmpID

Order by EmpID, SalesYear, SalesQuarter) AS PrevVal,

SubTotalAS CurVal,

LEAD (SubTotal, 2, 0) OVER (partition by EmpID

Order by EmpID, SalesYear, SalesQuarter) AS NextVal

FROM dbo. Orders;

Table 9-19 Sales of employees for the first and subsequent quarters

EmpID	SalesYear	SalesQuarter	PrevVal	CurVal	NextVal
1	2013	1	0	100	300
1	2013	2	0	200	400
1	2013	3	100	300	200
1	2013	4	200	400	200
1	2014	1	300	200	100
1	2014	2	400	200	100
1	2014	3	200	100	0
1	2014	4	200	100	0
2	2013	1	0	150	350
2	2013	2	0	250	450
2	2013	3	150	350	250
2	2013	4	250	450	250
2	2014	1	350	250	150
2	2014	2	450	250	150
2	2014	3	250	150	0
2	2014	4	250	150	0

9.5.2 FIRST_VALUE and LAST_VALUE Functions

The FIRST_VALUE and LAST_VALUE functions allow an element to be returned from the first and last rows of the window framework, respectively. Therefore, these functions support window partitioning, sorting, and frame clauses.

If you want the element to come from the first ROW of the window partition, use the FIRST_VALUE with the window frame range "rows between unbounded preceding and current row. This framework is default and can be omitted.

If you want the element to come from the last ROW of the window partition, use the LAST_VALUE with the window frame range "rows between current row and unbounded following. Note that this framework cannot be omitted, because the default framework "rows between unbounded preceding and current row" is used if this framework is not specified ", this will cause the last ROW to be the current row, so even if the LAST_VALUE function is used, the last ROW of the window will not be obtained.

The following example shows how to use these two functions. The following query uses the FIRST_VALUE function to return the employee's sales for the first quarter of the current year, and the LAST_VALUE function to return the sales for the last quarter of the current year. The query results are shown in Table 9-20.

SELECT EmpID, SalesYear, SalesQuarter,

FIRST_VALUE (SubTotal) OVER (partition by EmpID, SalesYear

Order by EmpID, SalesYear, SalesQuarter

ROWS BETWEEN UNBOUNDEDPRECEDING

And current row) AS FirstVal,

SubTotalAS CurVal,

LAST_VALUE (SubTotal) OVER (partition by EmpID, SalesYear

Order by EmpID, SalesYear, SalesQuarter

ROWS BETWEEN CURRENT ROW

And unbounded following) AS LastVal

FROM dbo. Orders

Order by EmpID, SalesYear, SalesQuarter;