TSQL OVER 子句: COUNT(*) OVER (ORDER BY a) [英] TSQL OVER clause: COUNT(*) OVER (ORDER BY a)
问题描述
这是我的代码:
USE [tempdb];走如果 OBJECT_ID(N'dbo.t') 不是 NULL开始删除表 dbo.t结尾走创建表 dbo.t(一个 NVARCHAR(8)，b NVARCHAR(8));走插入 t 值 ('a', 'b');插入 t 值 ('a', 'b');插入 t 值 ('a', 'b');插入 t 值 ('c', 'd');插入 t 值 ('c', 'd');插入 t 值 ('c', 'd');插入 t 值 ('c', 'd');插入 t 值 ('e', NULL);插入 t 值 (NULL, NULL);插入 t 值 (NULL, NULL);插入 t 值 (NULL, NULL);插入 t 值 (NULL, NULL);走选择 a, b,COUNT(*) OVER(按a排序)从T;在 BOL 的这个页面上，微软表示:><块引用>
如果未指定 PARTITION BY，则该函数将处理所有行查询结果集为单个组.
所以根据我的理解，最后一个 SELECT 语句会给我以下结果.由于所有记录都被视为一个组，对吗?
a b——————————————————空 空 12空 空 12空 空 12空 空 12121212cd 12cd 12cd 12cd 12空 12但实际结果是:
a b——————————————————空 空 4空 空 4空 空 4空 空 4一个 7一个 7一个 7cd 11cd 11cd 11cd 11空 12谁能帮忙解释一下原因?谢谢.
它给出了一个运行总数(这个功能直到 2012 版.)
ORDER BY 定义要聚合的窗口，UNBOUNDED PRECEDING 和 CURRENT ROW 在未指定时作为默认值.SQL Server 默认为 性能较差 RANGE 选项而不是 ROWS.
在绑定的情况下，它们具有不同的语义，因为 RANGE 版本的窗口不仅包括当前行(和前面的行)，还包括具有相同 <值的任何其他绑定行code>a 作为当前行.这可以从下面结果中每个计算的行数中看出.
选择一个，乙，COUNT(*) OVER (ORDER BY A无界前行和当前行之间的行) AS [行],COUNT(*) OVER (ORDER BY A无界前行和当前行之间的范围)作为[范围]，COUNT(*) OVER() AS [Over()]从T;退货
a b Rows Range Over()———————————————————————————————————————空 空 1 4 12空 空 2 4 12空 空 3 4 12空 空 4 4 125 7 126 7 127 7 12cd 8 11 12cd 9 11 12cd 10 11 12cd 11 11 12e NULL 12 12 12要达到您期望得到的结果，请省略 both PARTITION BY 和 ORDER BY 并使用空的 OVER() 子句(也如上所示).
This is my code:
USE [tempdb];
GO
IF OBJECT_ID(N'dbo.t') IS NOT NULL
BEGIN
DROP TABLE dbo.t
END
GO
CREATE TABLE dbo.t
(
a NVARCHAR(8),
b NVARCHAR(8)
);
GO
INSERT t VALUES ('a', 'b');
INSERT t VALUES ('a', 'b');
INSERT t VALUES ('a', 'b');
INSERT t VALUES ('c', 'd');
INSERT t VALUES ('c', 'd');
INSERT t VALUES ('c', 'd');
INSERT t VALUES ('c', 'd');
INSERT t VALUES ('e', NULL);
INSERT t VALUES (NULL, NULL);
INSERT t VALUES (NULL, NULL);
INSERT t VALUES (NULL, NULL);
INSERT t VALUES (NULL, NULL);
GO
SELECT a, b,
COUNT(*) OVER (ORDER BY a)
FROM t;
On this page of BOL, Microsoft says that:
If PARTITION BY is not specified, the function treats all rows of the query result set as a single group.
So based on my understanding, the last SELECT statement will give me the following result. Since all records are considered as in one single group, right?
a b
-------- -------- -----------
NULL NULL 12
NULL NULL 12
NULL NULL 12
NULL NULL 12
a b 12
a b 12
a b 12
c d 12
c d 12
c d 12
c d 12
e NULL 12
But the actual result is:
a b
-------- -------- -----------
NULL NULL 4
NULL NULL 4
NULL NULL 4
NULL NULL 4
a b 7
a b 7
a b 7
c d 11
c d 11
c d 11
c d 11
e NULL 12
Anyone can help to explain why? Thanks.
It gives a running total (this functionality was not implemented in SQL Server until version 2012.)
The ORDER BY defines the window to be aggregated with UNBOUNDED PRECEDING and CURRENT ROW as the default when not specified. SQL Server defaults to the less well performing RANGE option rather than ROWS.
They have different semantics in the case of ties in that the window for the RANGE version includes not just the current row (and preceding rows) but also any additional tied rows with the same value of a as the current row. This can be seen in the number of rows counted by each in the results below.
SELECT a,
b,
COUNT(*) OVER (ORDER BY a
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Rows],
COUNT(*) OVER (ORDER BY a
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Range],
COUNT(*) OVER() AS [Over()]
FROM t;
Returns
a b Rows Range Over()
-------- -------- ----------- ----------- -----------
NULL NULL 1 4 12
NULL NULL 2 4 12
NULL NULL 3 4 12
NULL NULL 4 4 12
a b 5 7 12
a b 6 7 12
a b 7 7 12
c d 8 11 12
c d 9 11 12
c d 10 11 12
c d 11 11 12
e NULL 12 12 12
To achieve the result that you were expecting to get omit both the PARTITION BY and ORDER BY and use an empty OVER() clause (also shown above).
这篇关于TSQL OVER 子句: COUNT(*) OVER (ORDER BY a)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
