SQL SERVER LEAD和LAG使用

玥殇

　　示例：获取在48小时之内重复的记录
　　

SELECT  *　　
FROM    ( SELECT    b.* ,
　　LAG(b.OperatorTime, 1, b.OperatorTime) OVER ( PARTITION BY b.No ORDER BY b.OperatorTime ) AS BeforTime ,
　　LEAD(b.OperatorTime, 1, b.OperatorTime) OVER ( PARTITION BY b.No ORDER BY b.OperatorTime ) AS NextTime
　　FROM      Test b
　　) a
　　
WHERE   DATEDIFF(HH, a.BeforTime, a.OperatorTime) < 24
　　AND DATEDIFF(HH, a.OperatorTime, a.NextTime) < 24
　　AND a.No IN ( SELECT   c.No
　　FROM     dbo.Test c
　　GROUP BY c.No
　　HAVING   COUNT(c.No) > 1 )
　　

　　LAG函数：
　　作用：访问相同结果集中先前行的数据，而用不使用 SQL Server 2016 中的自联接。 LAG 以当前行之前的给定物理偏移量来提供对行的访问。 在 SELECT 语句中使用此分析函数可将当前行中的值与先前行中的值进行比较。
　　语法：
　　

LAG (scalar_expression [,offset] [,default])　　OVER ( [ partition_by_clause ] order_by_clause )  
　　

　　参数：
　　scalar_expression
　　要根据指定偏移量返回的值。 这是一个返回单个（标量）值的任何类型的表达式。 scalar_expression不能为分析的函数。
　　偏移量
　　当前行（从中获得取值）后的行数。 如果未指定，则默认值为 1。 偏移量可以是列、 子查询或计算结果为正整数其他表达式或可以隐式转换为bigint。 偏移量不能为负值或分析函数。
　　默认值
　　要返回时的值scalar_expression在偏移量为 NULL。 如果未指定默认值，则返回 NULL。 默认可以是列、 子查询或其他表达式，但不是能为分析的函数。 默认必须是类型兼容与scalar_expression。
　　通过( [ partition_by_clause ] order_by_clause)
　　partition_by_clause将划分为分区函数应用到的 FROM 子句生成的结果集。 如果未指定，则此函数将查询结果集的所有行视为单个组。 order_by_clause应用函数之前确定数据的顺序。 如果partition_by_clause指定，它确定分区中的数据的顺序。 Order_by_clause是必需的。
　　SELECT-OVER 子句:
　　作用：在应用关联的开窗函数前确定行集的分区和排序。 也就是说，OVER 子句定义查询结果集内的窗口或用户指定的行集。 然后，开窗函数将计算窗口中每一行的值。 您可以将 OVER 子句与函数一起使用，以便计算各种聚合值，例如移动平均值、累积聚合、运行总计或每组结果的前 N 个结果。
　　例子：
　　

SELECT SalesOrderID, ProductID, OrderQty　　,
SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS Total　　,
AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Avg"　　,
COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Count"　　,
MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Min"　　,
MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Max"　　

FROM Sales.SalesOrderDetail　　

WHERE SalesOrderID IN(43659,43664);　　

　　
SalesOrderID ProductID   OrderQty Total      
Avg         Count       Min    Max　　

　　
43659        776         1        26          2           12          1      6  
　　
43659        777         3        26          2           12          1      6  
　　
43659        778         1        26          2           12          1      6  
　　
43659        771         1        26          2           12          1      6  
　　
43659        772         1        26          2           12          1      6  
　　
43659        773         2        26          2           12          1      6  
　　
43659        774         1        26          2           12          1      6  
　　
43659        714         3        26          2           12          1      6  
　　
43659        716         1        26          2           12          1      6  
　　
43659        709         6        26          2           12          1      6  
　　
43659        712         2        26          2           12          1      6  
　　
43659        711         4        26          2           12          1      6  
　　
43664        772         1        14          1           8           1      4  
　　
43664        775         4        14          1           8           1      4  
　　
43664        714         1        14          1           8           1      4  
　　
43664        716         1        14          1           8           1      4  
　　
43664        777         2        14          1           8           1      4  
　　
43664        771         3        14          1           8           1      4  
　　
43664        773         1        14          1           8           1      4  
　　
43664        778         1        14          1           8           1      4  
　　

　　LEAD:
　　访问相同结果集的后续行中的数据，而不使用 SQL Server 2012 中的自联接。 LEAD 以当前行之后的给定物理偏移量来提供对行的访问。 在 SELECT 语句中使用此分析函数可将当前行中的值与后续行中的值进行比较。
　　语法：LEAD ( scalar_expression [ ,offset ] , [ default ] )     OVER ( [ partition_by_clause ] order_by_clause )
　　scalar_expression，要返回的值基于指定的偏移量。 这是一个返回单个（标量）值的任何类型的表达式。scalar_expression 不能为分析函数
　　offset默认值为1， offset 可以是列、子查询或其他求值为正整数的表达式，或者可隐式转换为bigint。offset 不能是负数值或分析函数。
　　default默认值为NULL， offset 可以是列、子查询或其他求值为正整数的表达式，或者可隐式转换为bigint。offset不能是负数值或分析函数。
　　LEAD和LAG非常相似，只是LAG是往前求值