Sharp SQL2014: Window-based offset calculation

SQL Server introduced four offset functions:LAG and lead, first_value and last_value, Returns an element for an offset from the current row, or a line at the beginning or end of a window frame.

LAG and lead Support window partitioning and window sort clauses,first_value and last_value Support window frame clauses on the basis of support for window partitioning and window sort clauses.

9.5.1 LAGand the Leadfunction

The LAG function is used to find before the current line, and the lead function looks after it. The first parameter of the function (required) specifies the column to return the value, the second parameter (optional) is the offset (if unspecified, the default is 1), and the third parameter (optional) is the default value if no row is returned by the requested offset (default is NULLif unspecified).

The following example uses the Orders table created in the 9.2 section to query the employee's sales for the last quarter and the next quarter. Where theLAG function returns sales for the previous quarter , 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) through (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 of 1. And does not specify a third parameter, which is assumed to be the default value NULLwhen there is no previous row or row. The following query is used to get the sales for the second quarter and the second quarter before the employee, and the expression LAG (SubTotal, 2, 0) gets the value from the second row above and returns 0if the row is not found. 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,

Leads (SubTotal, 2, 0) over (PARTITION by EmpID

ORDER by Empid,salesyear, Salesquarter) as Nextval

FROM dbo. Orders;

Table 9-19 Get the sales for the second quarter and the second quarter before the employee

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_valueand theLast_valuefunction

The first_value and last_value functions allow an element to be returned from the first and last line of the window frame, 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, you should use the first_valuewith the window frame range "ROWS between unbounded precedingand current ROW". Since the framework is default, it can also be omitted.

If you want the element to come from the last row of the window partition, you should use the last_valuewith the window frame scope "ROWS between current ROW and unbounded following". It is important to note that the framework cannot be omitted because the default framework "ROWS between unbounded precedingand current row" is used without specifying the framework, which results in the last line being the Current row, so even if you use the last_value function, you won't get the last line of the window.

Here is an example of how these two functions are used. The following query uses the first_value function to return the employee's sales for the first quarter of the year, using the last_value function to return sales for the last quarter of the 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;

Sharp SQL2014: Window-based offset calculation