理解OVER子句_ZenDei技術網路在線

簡介 Over子句在SQLServer 2005中回歸，並且在2012中得到了擴展。這個功能主要結合視窗函數來使用；也可以在序列函數“NEXT VALUE FOR”使用。OVER子句確定哪些來自查詢的列被應用到函數中，在函數中這些列被如何排序，並且何時重啟函數計算。由於篇幅限制，本篇僅僅就OVER子 ...

簡介

Over子句在SQLServer 2005中回歸，並且在2012中得到了擴展。這個功能主要結合視窗函數來使用；也可以在序列函數“NEXT VALUE FOR”使用。OVER子句確定哪些來自查詢的列被應用到函數中，在函數中這些列被如何排序，並且何時重啟函數計算。由於篇幅限制，本篇僅僅就OVER子句討論，不再深入各種函數了（提供幾個2014中新增的函數）。

語法:

<function> OVER (        [PARTITION BY clause]
                         [ORDER BY clause]
                         [ROWS or RANGE clause])

這個語法中，顯示所有的子句都是可選的，實際上，每個函數使用OVER子句的函數都能確定哪個子句被允許哪個被需要。下圖是展示那些函數是允許或者需要的：

R-需要, O-可選, X-不允許

PARTITION BY子句用來區分查詢結果集到數據子集中，或者分區。如果不使用PARTITION BY子句，整個來自查詢的結果集都將被使用。視窗函數被應用到每個獨立的分區數據，並且每個函數對於每個分區都是重新運算。通過定義一套確定分區的值來區分查詢到子集，這些值可以使列，標量函數，子查詢或者變數

舉例如下:

SELECT  COUNT(*)
FROM    [msdb].sys.indexes;

查詢結果如下：

這種情況下查詢僅僅返回一個數字，這就是msdb資料庫的索引的數量。現在讓我們加入OVER子句到這個查詢中：

SELECT  object_id, index_id, COUNT(*) OVER ()
FROM    [msdb].sys.indexes;

結果集如下:

這個查詢返回每個索引的對象ID和索引ID，並且還有結果集的索引總數。由於沒使用PARTITION BY子句，整個結果集都被當做一個分區。

現在我們加入PARTITION BY子句來看看結果如何改變:

SELECT  object_id, index_id, COUNT(*) OVER (PARTITION BY object_id)
FROM    [msdb].sys.indexes;

返回結果如下:

查詢返回每個索引的行數，但是現在查詢指定子句按照object_id 列來分區，因此count函數返回的是按object_id 分組的索引的數量。ORDER BY子句來控制排序。ROWS 或者 RANGE子句可以決定在分區內部的行數的子集。當使用ROWS 和 RANGE的時候，可以指定視窗函數的開始和結束點，如下圖所示:

有兩種語法指定視窗函數的範圍:

BETWEEN <beginning frame> AND <ending frame>
<beginning frame>

如果只有“開始點”，預設結束點為CURRENT ROW。

UNBOUNDED 關鍵字指定分區開端或者結束。CURRENT ROW 指定當前行是否是視窗的開始或者結束，這取決於視窗使用的位置。上圖中的“N”指定了之前當前列的或之後的行數。

下麵是有效規範的視窗函數:

-- 從分區中指定整個結果集
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 

-- 指定五行，並且在當前行的前四行
BETWEEN 4 PRECEDING AND CURRENT ROW

-- 指定當前行到分區結束的所有行
BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

-- 指定從分區開始到當前行的所有行

UNBOUNDED PRECEDING

為了展示以上說法，我們創建一些測試數據:兩個賬戶，每個賬戶四個日期，以及四個金額。然後執行查詢展示前面提到的語法的不同使用方式:

DECLARE @Test TABLE (
    Account     INTEGER,
    TranDate    DATE,
    TranAmount  NUMERIC(5,2));
INSERT INTO @Test (Account, TranDate, TranAmount)
VALUES  (1, '2015-01-01', 50.00),
        (1, '2015-01-15', 25.00),
        (1, '2015-02-01', 50.00),
        (1, '2015-02-15', 25.00),
        (2, '2015-01-01', 50.00),
        (2, '2015-01-15', 25.00),
        (2, '2015-02-01', 50.00),
        (2, '2015-02-15', 25.00);

SELECT  Account, TranDate, TranAmount,
        COUNT(*) OVER (PARTITION BY Account
                       ORDER BY TranDate
                       ROWS UNBOUNDED PRECEDING) AS RowNbr,
        COUNT(*) OVER (PARTITION BY TranDate) AS DateCount,
        COUNT(*) OVER (PARTITION BY Account
                       ORDER BY TranDate
                       ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS Last2Count
FROM    @Test
ORDER BY Account, TranDate;

查詢返回如下結果:

“RowNbr”列使用了count 函數返回分區後有多少行。這個分區是按照TranDate進行排序的，然後我們指定從分區的開始到當前行的視窗。對於第一行，‘2015-01-01’是第一行，座椅返回值就是1，然後第二行就是就是‘2015-01-15’，就是2，以此類推，其他這個賬戶的行往下排序。由於PARTITION BY 指定了Account 列，當Account 改變後這個函數被重置，於是可以看到Account 為2的時候RowNbr重新開始排序。

“DateCount”列根據“date”分組分區展示有多少個相同的date值。例子中每個交易的日期都有兩個所以該列值都是2。與group by 相似，不同點是總的返回行數。尤其當計算當前行所占的總行數的百分比的時候應用比較多。

“Last2Count” 列表示在分區內對於當前行和其前面一行的行數。有點拗口，具體點就是對於每個Account 最小date的數據就是第一行，那麼對於第一行距離第一行的計數就是1，其他行和都是計算它和它前面一行的數值都是2。比較常見的應用就是計算最近兩個月銷售的情況來計算獎金。

此時，我們已經展示了ROWS的子句。我們通過下麵的例子可以快速理解兩者的不同（註意4和5行以及12和13行是相同的值，此處產生不同）:

SELECT  FName,
        Salary,
        SumByRows  = SUM(Salary) OVER (ORDER BY Salary
                                        ROWS UNBOUNDED PRECEDING),
        SumByRange = SUM(Salary) OVER (ORDER BY Salary
                                       RANGE UNBOUNDED PRECEDING)
FROM    (VALUES (1, 'George',       800),
                (2, 'Sam',          950),
                (3, 'Diane',       1100),
                (4, 'Nicholas',    1250),
                (5, 'Samuel',      1250),
                (6, 'Patricia',    1300),
                (7, 'Brian',       1500),
                (8, 'Thomas',      1600),
                (9, 'Fran',        2450),
                (10,'Debbie',      2850),
                (11,'Mark',        2975),
                (12,'James',       3000),
                (13,'Cynthia',     3000),
                (14,'Christopher', 5000)
        ) dt(RowID, FName, Salary);

查詢結果如下:

後兩列的OVER子句除了ROWS/RANGE 的子句不同以外完全相同，註意，結束兩個的結束點都沒有指定，預設就是當前行。SumByRows 列通過計算第一行到當前行的所有行的值作為總數，而RANGE子句是計算到排序欄位（SALARY）的值相同的列的所有值得總和。所以當有重覆薪水值得時候就發現了兩者的不同，如上所示。

重要提示：ORDER BY在OVER子句中只控制在視窗函數中使用分區行的順序，而不控制最終結果集的順序。如果需要制定結果集順序，還要在查詢後加上ORDER BY 語句。

下麵介紹幾種2014加入的新的視窗函數，以便我們使用，個人覺得很有幫助，性能非常不錯。

1.LAG() and LEAD()

–向前或者向後N行

2.FIRST_VALUE() 與 LAST_VALUE()

–第一行或最後一行

3.PERCENT_RANK() 與 CUME_DIST()

–計算排序（統計分析常用）

4.PERCENTILE_DISC() 與 PERCENTILE_CONT()

–根據百分比取值（統計分析）

總結：

本篇主要介紹了OVER的用法，通過對比不同關鍵字的對比展示如何使用。在很多缺少排序和分區分組的條件下，能夠簡化t-sql語句提高語句效率。希望對大家的使用有幫助。