语法

开窗函数支持分区、排序和框架三种元素,其语法格式如下:

OVER (
[ <PARTITION BY clause> ]
[ <ORDER BY clause> ]
[ <ROW or RANGE clause> ]

)
<PARTITION BY clause> ::= PARTITION BY value_expression , ... [ n ]
<ORDER BY clause> ::= ORDER BY order_by_expression [ COLLATE collation_name ] [ ASC | DESC ] [ ,...n ]
<ROW or RANGE clause> ::= { ROWS | RANGE } <window frame extent>
<window frame extent> ::= { <window frame preceding> | <window frame between> }
<window frame between> ::= BETWEEN <window frame bound> AND <window frame bound>
<window frame bound> ::= { <window frame preceding> | <window frame following> }
<window frame preceding> ::= { UNBOUNDED PRECEDING | <unsigned_value_specification> PRECEDING | CURRENT ROW }
<window frame following> ::= { UNBOUNDED FOLLOWING | <unsigned_value_specification> FOLLOWING | CURRENT ROW }
<unsigned value specification> ::= { <unsigned integer literal> }

【窗口分区】:就是将窗口指定列具有相同值的那些行进行分区,分区与分组比较类似,但是分组指定后对于整个SELECT语句只能按照这个分组,不过分区可以在一条语句中指定不同的分区。
【窗口排序】:分区之后可以指定排序列,那么在窗口计算之前,各个窗口的行的逻辑顺序将确定。
【窗口框架】:框架是对窗口进行进一步的分区,框架有两种范围限定方式:一种是使用ROWS子句,通过指定当前行之前或之后的固定数目的行来限制分区中的行数;另一种是RANGE子句,按照排序列的当前值,根据相同值来确定分区中的行数。

  • 当使用框架时,必须要有ORDER BY子句,如果仅指定了ORDER BY子句而未指定框架,那么默认框架将采用 RANGE UNBOUNDED PRECEDING AND CURRENT ROW。
  • 如果窗口函数没有指定ORDER BY子句,也就不存在ROWS/RANGE窗口的计算。
  • 如果ROWS/RANGE子句采用 <window frame preceding>,那么CURRENT ROW 作为框架的默认结束行,例如:“ROWS 5 PRECEDING” 等价于 “ROWS BETWEEN 5 PRECEDING AND CURRENT ROW”。
  • PS:RANGE 只支持使用 UNBOUNDED 和 CURRENT ROW 窗口框架分隔符。

示例

1、未使用 partition by 指定分区示例

select *,
sum(U_Id) over(order by U_Id) 列1,
sum(U_Id) over(order by U_Id RANGE BETWEEN unbounded preceding AND CURRENT ROW) 列2,
sum(U_Id) over(order by U_Id rows BETWEEN unbounded preceding AND CURRENT ROW) 列3,
sum(U_Id) over(order by U_Id rows BETWEEN 1 preceding AND 2 following) 列4,
sum(U_Id) over(order by U_Id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 列5
from UserInfo

结果分析:

RANGE 是逻辑窗口,是指定当前行对应值的范围取值,列数不固定,只要行值在范围内,对应列都包含在内。
ROWS 是物理窗口,即根据order by 子句排序后,取的前N行及后N行的数据计算。(与当前行的值无关,只与排序后的行号相关)

  • 【列1】未指定窗口,所以默认为 RANGE UNBOUNDED PRECEDING AND CURRENT ROW,故此与【列2】值相同。
  • 【列2】RANGE BETWEEN unbounded preceding AND CURRENT ROW 表示指定取值范围为 当前行与当前行前面的所有行的值。
    即第一行的值为:1  第二行的值为:3+1  第三行的值为:4+3+1
  • 【列3】rows BETWEEN unbounded preceding AND CURRENT ROW 表示指定取值范围为 当前行与当前行前面的所有行的值。
    即第一行的值为:1  第二行的值为:3+1  第三行的值为:4+3+1
  • 【列4】rows BETWEEN 1 preceding AND 2 following 表示指定取值范围为 当前行与前一行和后两行的值。
    即第一行的值为:1+3+4  第二行的值为:1+3+4+5  第三行的值为:3+4+5+6
  • 【列5】ROWS BETWEEN 1 PRECEDING AND CURRENT ROW 表示指定取值范围为 当前行与前一行的值。
    即第一行的值为:1  第二行的值为:1+3  第三行的值为:3+4

2、使用 partition by 指定分区示例

select *,
sum(U_Id) over(partition by U_Pwd order by U_Id) 列1,
sum(U_Id) over(partition by U_Pwd order by U_Id RANGE BETWEEN unbounded preceding AND CURRENT ROW) 列2,
sum(U_Id) over(partition by U_Pwd order by U_Id rows BETWEEN unbounded preceding AND CURRENT ROW) 列3,
sum(U_Id) over(partition by U_Pwd order by U_Id rows BETWEEN 1 preceding AND 2 following) 列4,
sum(U_Id) over(partition by U_Pwd order by U_Id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) 列5
from UserInfo

以上根据 列(U_Pwd)被分为3个区,使用 partition by 指定分区就是先进行分区,然后再根据指定窗口和指定窗口取值范围进行计算。


ROWS与RANGE之间的区别

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

SELECT
t.OrderYear, t.OrderMonth, t.TotalDue,
SUM(t.TotalDue) OVER(ORDER BY t.OrderYear, t.OrderMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 'RunningTotal'
FROM
(
SELECT
YEAR(OrderDate) AS 'OrderYear', MONTH(OrderDate) AS 'OrderMonth', SalesPersonID, TotalDue
FROM Sales.SalesOrderHeader
) AS t
WHERE
t.SalesPersonID = 274 AND t.OrderYear = 2005
GO

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

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

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

SELECT
t.OrderYear, t.OrderMonth, t.TotalDue,
SUM(t.TotalDue) OVER(ORDER BY t.OrderYear, t.OrderMonth RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 'RunningTotal'
FROM
(
SELECT
YEAR(OrderDate) AS 'OrderYear', MONTH(OrderDate) AS 'OrderMonth', SalesPersonID, TotalDue
FROM Sales.SalesOrderHeader
) AS t
WHERE
t.SalesPersonID = 274 AND t.OrderYear = 2005
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选项取决于ORDER BY值在窗口里有多少行被包含。因此当你使用RANGE选项时有性能上的巨大区别。

最新文章

  1. Linux 下 Oracle 内核参数优化
  2. linux中非root用户使用wireshark进行抓包
  3. unique函数的作用
  4. android 学习随笔九(网络:简单新闻客户端实现)
  5. css基本知识
  6. 循环列表的Java实现,解决约瑟夫环问题
  7. linux read和write函数
  8. CorAnimation7-高效绘图、图像IO以及图层性能
  9. StopWatch的用法
  10. HTML5的本地存储功能,值得研究
  11. Swift -&gt; RunTime(动态性) 问题 浅析
  12. 使用MQ来保证分布式事务的最终一致性
  13. C++入门程序作业1
  14. dispatchTouchEvent
  15. .net 高并发 多消费者模式处理订单
  16. [linux] 查看网卡UUID
  17. onTouch
  18. Jquery动画操作的stop()函数
  19. AutoFac文档12(转载)
  20. CSS总结摘要

热门文章

  1. M2项目测试
  2. linux 第七周 总结及实验
  3. Sprint 冲刺第三阶段第3-5天
  4. Linux (centos7) 防火墙命令
  5. [2017BUAA软工]个人项目
  6. Selenium的自我总结1
  7. Javascript中实现继承的方式
  8. Oracle 数据库启动过程
  9. 好文章之——PHP系列(一)
  10. PHP5.5特性