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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-01 13:41:18  来源:igfitidea点击:

TSQL OVER clause: COUNT(*) OVER (ORDER BY a)

sqlsql-servertsqlsql-server-2012window-functions

提问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 SELECTstatement 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 BYdefines the window to be aggregated with UNBOUNDED PRECEDINGand CURRENT ROWas the default when not specified. SQL Server defaults to the less well performingRANGEoption rather than ROWS.

ORDER BY定义了窗口与被聚合UNBOUNDED PRECEDINGCURRENT ROW作为未指定时的缺省值。SQL Server 默认使用性能较差的RANGE选项而不是ROWS.

They have different semantics in the case of ties in that the window for the RANGEversion includes not just the current row (and preceding rows) but also any additional tied rows with the same value of aas 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 BYand ORDER BYand use an empty OVER()clause (also shown above).

为了达到结果是你期望得到省略两者PARTITION BYORDER BY,并使用一个空的OVER()条款(如上所示)。