SQL TSQL OVER 子句: COUNT(*) OVER (ORDER BY a)
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/14860162/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
TSQL OVER clause: COUNT(*) OVER (ORDER BY a)
提问by Just a learner
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:
在BOL 的这个页面上,微软说:
If PARTITION BY is not specified, the function treats all rows of the query result set as a single group.
如果未指定 PARTITION BY,则该函数将查询结果集的所有行视为一个组。
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?
所以根据我的理解,最后一条SELECT
语句会给我以下结果。由于所有记录都被视为一个组,对吗?
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.
任何人都可以帮助解释为什么?谢谢。
回答by Martin Smith
It gives a running total (this functionality was not implemented in SQL Server until version 2012.)
它给出了一个运行总数(这个功能直到2012 版才在 SQL Server 中实现。)
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 performingRANGE
option rather than ROWS
.
在ORDER BY
定义了窗口与被聚合UNBOUNDED PRECEDING
和CURRENT ROW
作为未指定时的缺省值。SQL Server 默认使用性能较差的RANGE
选项而不是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.
在RANGE
绑定的情况下,它们具有不同的语义,因为版本的窗口不仅包括当前行(和前面的行),还包括与当前行具有相同值的任何其他绑定行a
。这可以从下面结果中每个人计算的行数中看出。
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 boththe PARTITION BY
and ORDER BY
and use an empty OVER()
clause (also shown above).
为了达到结果是你期望得到省略两者的PARTITION BY
和ORDER BY
,并使用一个空的OVER()
条款(如上所示)。