SQL SERVER LEAD和LAG使用

標籤：detail   weight   next   訪問   移動   pre   efault   正整數   擷取   

樣本：擷取在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        ) aWHERE   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是往前求值

SQL SERVER LEAD和LAG使用