TSQL OVER子句: COUNT(*) OVER (由a命令)

Question

这是我的密码：

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;

在这一页BOL上，微软说：

如果未指定分区BY，则函数将查询结果集的所有行视为单个组。

因此，根据我的理解，最后一条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

但实际结果是：

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

有人能帮我解释原因吗？谢谢。

Answer 1

它给出了正在运行的总计(直到2012年版才在Server中实现此功能)。

当未指定时，ORDER BY定义要使用UNBOUNDED PRECEDING和CURRENT ROW聚合的窗口为默认窗口。Server默认为表现不佳 RANGE选项，而不是ROWS。

在领带的情况下，它们具有不同的语义，因为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;

返回

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

为了实现您期望得到的省略的结果，、PARTITION BY和ORDER BY都使用空OVER()子句(也如上面所示)。

Answer 2

如果未指定行/范围，但指定了ORDER，则前一行和当前行将用作窗口框架的默认值--这意味着什么，让我们关注“无界的前一行和当前行”。这提供了从起始行到当前行的运行总计。但是，如果您想要有一个总体计数，那么您也可以指定

“前面无界，后面无界”--这将考虑整个数据集，而Over()只是这一步的一个快捷方式。

    select a,b,
count(*) over(order by a) as [count],
COUNT(*) OVER (ORDER BY a 
                         RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Range],
COUNT(*) OVER (ORDER BY a 
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS  [Rows],
COUNT(*) OVER (ORDER BY a 
                         RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED Following) AS [Range_Unbounded_following],
COUNT(*) OVER (ORDER BY a 
                         ROWs BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED Following) AS [Row_Unbounded_following]
,COUNT(*) OVER () AS [Plain_over]
from t 
order by [count]

结果是

a        b        count       Range       Rows        Range_Unbounded_following Row_Unbounded_following Plain_over
-------- -------- ----------- ----------- ----------- ------------------------- ----------------------- -----------
NULL     NULL     4           4           1           12                        12                      12
NULL     NULL     4           4           2           12                        12                      12
NULL     NULL     4           4           3           12                        12                      12
NULL     NULL     4           4           4           12                        12                      12
a        b        7           7           5           12                        12                      12
a        b        7           7           6           12                        12                      12
a        b        7           7           7           12                        12                      12
c        d        11          11          8           12                        12                      12
c        d        11          11          9           12                        12                      12
c        d        11          11          10          12                        12                      12
c        d        11          11          11          12                        12                      12
e        NULL     12          12          12          12                        12                      12