SQL Server窗口函數(shù)：ROWS與RANGE

幾乎每次我展示SQL Server里的窗口時(shí)，人們都非常有興趣知道，當(dāng)你定義你的窗口（指定的一組行）時(shí)，ROWS與RANGE選項(xiàng)之間的區(qū)別。因此在今天的文章里我想給你展示下這些選項(xiàng)的區(qū)別，對(duì)于你的分析計(jì)算意味著什么。

ROWS與RANGE之間的區(qū)別

當(dāng)你用OVER()子句進(jìn)行你的分析計(jì)算來(lái)打開(kāi)你的窗口，你也可以在窗口里看到的，通過(guò)ROWS與RANGE選項(xiàng)來(lái)限制你的行數(shù)。來(lái)看下面的T-SQL語(yǔ)句：?

?1?SELECT
?2?????t.OrderYear,
?3?????t.OrderMonth,
?4?????t.TotalDue,
?5?????SUM(t.TotalDue)?OVER(ORDER?BY?t.OrderYear,?t.OrderMonth?ROWS?BETWEEN?UNBOUNDED?PRECEDING?AND?CURRENT?ROW)?AS?'RunningTotal'
?6?FROM
?7?(
?8?????SELECT
?9?????????YEAR(OrderDate)?AS?'OrderYear',
10?????????MONTH(OrderDate)?AS?'OrderMonth',
11?????????SalesPersonID,
12?????????TotalDue
13?????FROM?Sales.SalesOrderHeader?
14?)?AS?t
15?WHERE
16?????t.SalesPersonID?=?274
17?????AND?t.OrderYear?=?2005
18?GO

這個(gè)T-SQL語(yǔ)句用SUM()聚合函數(shù)進(jìn)行匯總計(jì)算。窗口本身從第1行（UNBOUNDED PRECEDING）上至當(dāng)前行（CURRENT ROW）。對(duì)于記錄級(jí)中的每1行，窗口變得越來(lái)越大，因此很容易進(jìn)行匯總運(yùn)算。下圖演示了這個(gè)概念。

從輸出你可以看到，結(jié)果是個(gè)自增長(zhǎng)的匯總——運(yùn)行合計(jì)匯總的結(jié)果。

現(xiàn)在假設(shè)你修改窗口為RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，會(huì)發(fā)生什么：

?1?SELECT
?2?????t.OrderYear,
?3?????t.OrderMonth,
?4?????t.TotalDue,
?5?????SUM(t.TotalDue)?OVER(ORDER?BY?t.OrderYear,?t.OrderMonth?RANGE?BETWEEN?UNBOUNDED?PRECEDING?AND?CURRENT?ROW)?AS?'RunningTotal'
?6?FROM
?7?(
?8?????SELECT
?9?????????YEAR(OrderDate)?AS?'OrderYear',
10?????????MONTH(OrderDate)?AS?'OrderMonth',
11?????????SalesPersonID,
12?????????TotalDue
13?????FROM?Sales.SalesOrderHeader?
14?)?AS?t
15?WHERE
16?????t.SalesPersonID?=?274
17?????AND?t.OrderYear?=?2005
18?GO

從下圖你可以看到，你得到了不同的結(jié)果，對(duì)于2005年11月的記錄顯示同樣的匯總。

我們來(lái)嘗試?yán)斫庀聻槭裁催@里RANGE選項(xiàng)比ROWS選項(xiàng)給你不同的結(jié)果。使用ROWS選項(xiàng)你定義當(dāng)前行的固定前后記錄。這里你看到的行取決于窗口的ORDER BY從句。你也可以說(shuō)你在物理級(jí)別定義你的窗口。

當(dāng)你使用RANGE選項(xiàng)事情就改變了。RANGE選項(xiàng)包含窗口里的所有行，和當(dāng)前行有相同ORDER BY值。從剛才的圖片你可以看到，對(duì)于2005年11月的2條記錄你拿到同個(gè)匯總，因?yàn)檫@2行有同樣的ORDER BY值（2005年11月）。使用RANGE選項(xiàng)你在邏輯級(jí)別定義你的窗口。如果更多的行有同個(gè)ORDER BY值，當(dāng)你使用ROWS選項(xiàng)你的窗口會(huì)包含更多的行。?

小結(jié)

在今天的文章里你看到了當(dāng)你為你的分析計(jì)算定義窗口時(shí)，ROWS和RANGE選項(xiàng)之間的區(qū)別。使用ROWS選項(xiàng)你在物理級(jí)別定義在你窗口里有多少行。使用RANGE選項(xiàng)取決于ORDER BY值在窗口里有多少行被包含。因此當(dāng)你使用RANGE選項(xiàng)時(shí)有性能上的巨大區(qū)別。在接下來(lái)的文章我會(huì)討論下這些副作用。