你的位置:首页 > 数据库

[数据库]SQL Server窗口函数:ROWS与RANGE


几乎每次我展示SQL Server里的窗口时,人们都非常有兴趣知道,当你定义你的窗口(指定的一组行)时,ROWSRANGE选项之间的区别。因此在今天的文章里我想给你展示下这些选项的区别,对于你的分析计算意味着什么。

ROWS与RANGE之间的区别

当你用OVER()子句进行你的分析计算来打开你的窗口,你也可以在窗口里看到的,通过ROWSRANGE选项来限制你的行数。来看下面的T-SQL语句: 

 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     TotalDue13   FROM Sales.SalesOrderHeader 14 ) AS t15 WHERE16   t.SalesPersonID = 27417   AND t.OrderYear = 200518 GO

这个T-SQL语句用SUM()聚合函数进行汇总计算。窗口本身从第1行(UNBOUNDED PRECEDING)上至当前行(CURRENT ROW)。对于记录级中的每1行,窗口变得越来越大,因此很容易进行汇总运算。下图演示了这个概念。

从输出你可以看到,结果是个自增长的汇总——运行合计汇总的结果。

现在假设你修改窗口为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,会发生什么:

 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     TotalDue13   FROM Sales.SalesOrderHeader 14 ) AS t15 WHERE16   t.SalesPersonID = 27417   AND t.OrderYear = 200518 GO

从下图你可以看到,你得到了不同的结果,对于2005年11月的记录显示同样的汇总。

我们来尝试理解下为什么这里RANGE选项比ROWS选项给你不同的结果。使用ROWS选项你定义当前行的固定前后记录。这里你看到的行取决于窗口的ORDER BY从句。你也可以说你在物理级别定义你的窗口。

当你使用RANGE选项事情就改变了。RANGE选项包含窗口里的所有行,和当前行有相同ORDER BY值。从刚才的图片你可以看到,对于2005年11月的2条记录你拿到同个汇总,因为这2行有同样的ORDER BY值(2005年11月)。使用RANGE选项你在逻辑级别定义你的窗口。如果更多的行有同个ORDER BY值,当你使用ROWS选项你的窗口会包含更多的行。 

小结

在今天的文章里你看到了当你为你的分析计算定义窗口时,ROWS和RANGE选项之间的区别。使用ROWS选项你在物理级别定义在你窗口里有多少行。使用RANGE选项取决于ORDER BY值在窗口里有多少行被包含。因此当你使用RANGE选项时有性能上的巨大区别。在接下来的文章我会讨论下这些副作用。

感谢关注!