摘自作者新书《锋利的SQL》（第2版），网购京东：http://item.jd.com/11692900.html
SQL Server 2012引入了四个偏移函数：LAG和LEAD、FIRST_VALUE和LAST_VALUE，用于从当前行的某个偏移量、或是一个窗口框架的开头或结尾的行返回一个元素。
LAG和LEAD支持窗口分区和窗口排序子句，FIRST_VALUE和LAST_VALUE在支持窗口分区和窗口排序子句的基础上，还支持窗口框架子句。
9.5.1 LAG和LEAD函数
LAG函数用于在当前行之前查找，LEAD函数在之后查找。函数的第一个参数（必选）指定要返回值的列，第二个参数（可选）是偏移量（如果未指定，默认为1），第三个参数（可选）是在请求的偏移量没有行返回的情况下的默认值（如果未指定，默认为NULL）。
下面的示例使用在9.2节创建的Orders表，用于查询雇员上一季度和下一季度的销售额。其中，LAG函数返回上一季度销售额，LEAD函数返回下一季度销售值。查询结果如表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;
由于没有指定偏移量，函数便假定为默认值1。并且没有指定第三个参数，当没有前一行或下一行时，便假定为默认值NULL。下面的查询用于获取雇员前面第二个季度和后面第二个季度的销售额，表达式LAG(SubTotal, 2, 0)从前面第二行中获取值，并且如果找不到行，将返回0。查询结果如表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;
表9-19 获取雇员前面第二个季度和后面第二个季度的销售额
| EmpID | SalesYear | SalesQuarter | PrevVal | CurVal | NextVal |
| 1 | 2013 | 1 | 100 | 300 | |
| 1 | 2013 | 2 | 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 | |
| 1 | 2014 | 4 | 200 | 100 | |
| 2 | 2013 | 1 | 150 | 350 | |
| 2 | 2013 | 2 | 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 | |
| 2 | 2014 | 4 | 250 | 150 |
9.5.2 FIRST_VALUE和LAST_VALUE函数
FIRST_VALUE和LAST_VALUE函数分别允许从窗口框架的第一行和最后一行返回一个元素。因此，这些函数支持窗口分区、排序和框架子句。
如果希望元素来自窗口分区的第一行，应使用带有窗口框架范围“ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”的FIRST_VALUE。由于该框架是默认的，故而也可以省略。
如果希望元素来自窗口分区的最后一行，应使用带有窗口框架范围“ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING”的LAST_VALUE。需要注意的是，该框架不能省略，因为在未指定该框架的情况下会使用默认框架“ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”，这样会导致最后一行就是当前行，即CURRENT ROW，所以即使使用了LAST_VALUE函数，也不会得到窗口的最后一行。
下面通过一个示例来说明这两个函数的使用方法。下面的查询使用FIRST_VALUE函数返回雇员当年第一个季度的销售额，使用LAST_VALUE函数返回当年最后一个季度的销售额。查询结果如表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;
