锋利的SQL2014：基于窗口的偏移计算

  摘自作者新书《锋利的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	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和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;