Предложение TSQL OVER: COUNT (*) OVER (ORDER BY a)

Это мой код:

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 Microsoft заявляет, что:

Если PARTITION 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

Любой может помочь объяснить, почему? Спасибо.

Ответы

Ответ 1

Он дает общее количество (эта функциональность не была реализована в SQL Server до версия 2012.)

ORDER BY определяет агрегированное окно с UNBOUNDED PRECEDING и CURRENT ROW как значение по умолчанию, если оно не указано. SQL 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() (также показано выше).