理解OVER子句

发布时间：2020-12-12 09:06:22 所属栏目：MsSql教程来源：网络整理

导读：??? 简介 ??? Over子句在SQLServer 2005中回归，并且在2012中得到了扩展。这个功能主要结合窗口函数来使用；也可以在序列函数“NEXT VALUE FOR”使用。OVER子句确定哪些来自查询的列被应用到函数中，在函数中这些列被如何排序，并且何时重启函数计算。由于篇

??? 简介

??? Over子句在SQLServer 2005中回归，并且在2012中得到了扩展。这个功能主要结合窗口函数来使用；也可以在序列函数“NEXT VALUE FOR”使用。OVER子句确定哪些来自查询的列被应用到函数中，在函数中这些列被如何排序，并且何时重启函数计算。由于篇幅限制，本篇仅仅就OVER子句讨论，不再深入各种函数了（提供几个2014中新增的函数）。

语法:

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

??? 这个语法中，显示所有的子句都是可选的，实际上，每个函数使用OVER子句的函数都能确定哪个子句被允许哪个被需要。下图是展示那些函数是允许或者需要的：

DB乐之者

R-需要,O-可选,X-不允许

PARTITION BY子句用来区分查询结果集到数据子集中，或者分区。如果不使用PARTITION BY子句，整个来自查询的结果集都将被使用。窗口函数被应用到每个独立的分区数据，并且每个函数对于每个分区都是重新运算。通过定义一套确定分区的值来区分查询到子集，这些值可以使列，标量函数，子查询或者变量

举例如下:

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

查询结果如下：

这种情况下查询仅仅返回一个数字，这就是msdb数据库的索引的数量。现在让我们加入OVER子句到这个查询中：

结果集如下:

这个查询返回每个索引的对象ID和索引ID，并且还有结果集的索引总数。由于没使用PARTITION BY子句，整个结果集都被当做一个分区。

现在我们加入PARTITION BY子句来看看结果如何改变:

SELECT object_id,COUNT(*

返回结果如下:

查询返回每个索引的行数，但是现在查询指定子句按照object_id 列来分区，因此count函数返回的是按object_id 分组的索引的数量。ORDER BY子句来控制排序。ROWS 或者 RANGE子句可以决定在分区内部的行数的子集。当使用ROWS 和 RANGE的时候，可以指定窗口函数的开始和结束点? ，如下图所示:

有两种语法指定窗口函数的范围:

BETWEEN  AND

如果只有“开始点”，默认结束点为CURRENT ROW。

UNBOUNDED 关键字指定分区开端或者结束。CURRENT ROW 指定当前行是否是窗口的开始或者结束，这取决于窗口使用的位置。上图中的“N”指定了之前当前列的或之后的行数。

下面是有效规范的窗口函数:

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

-- 指定当前行到分区结束的所有行
BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

-- 指定从分区开始到当前行的所有行

UNBOUNDED PRECEDING

为了展示以上说法，我们创建一些测试数据:两个账户，每个账户四个日期，以及四个金额。然后执行查询展示前面提到的语法的不同使用方式:

SELECT Account,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行是相同的值，此处产生不同）:

查询结果如下:

DB乐之者

??? 后两列的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语句提高语句效率。希望对大家的使用有帮助。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!