Группировка устанавливает столбцы в агрегированных аргументах и замену NULL
В Интернете есть много примеров группирования, например, запрос Q1 в приведенном ниже примере. Но запрос Q2 отличается тем, что A2 является столбцом группировки и используется в качестве аргумента для SUM().
Какой из следующих пунктов подходит для Q2 в соответствии со стандартом SQL (любая версия с 2003 года который поддерживает группировку наборов)? Если (1) правильно, объясните почему со ссылкой на Стандарт.
A2 заменяется на NULL, если только он не является аргументом для агрегата. Эта интерпретация даст результаты R1 ниже. Это поведение Oracle (которое кажется более полезным).
A2 заменяется на NULL, включая то, где он используется в агрегате: это означает, что агрегат вернет NULL. Эта интерпретация даст результаты R2 ниже. Вот как я понял стандарт SQL (возможно, неправильно).
Пример кода:
-- Setup
create table A (A1 int, A2 int, A3 int);
insert into A values (1, 1, 100);
insert into A values (1, 2, 40);
insert into A values (2, 1, 70);
insert into A values (5, 1, 90);
-- Query Q1
-- Expected/Observed results:
--
-- A1 A2 SUM(A3)
-- ---------- ---------- ----------
-- 1 - 140
-- 2 - 70
-- 5 - 90
-- - 1 260
-- - 2 40
-- - - 300
select A1, A2, sum (A3)
from A
group by grouping sets ((A1), (A2), ())
order by 1, 2;
-- Query Q2
-- Results R1 (Oracle):
-- A1 A2 SUM(A2)
-- ---------- ---------- ----------
-- 1 - 3
-- 2 - 1
-- 5 - 1
-- - 1 3
-- - 2 2
-- - - 5
--
-- Results R2 (SQL Standard?):
-- A1 A2 SUM(A2)
-- ---------- ---------- ----------
-- 1 - -
-- 2 - -
-- 5 - -
-- - 1 3
-- - 2 2
-- - - - -- NULL row
select A1, A2, sum (A2)
from A
group by grouping sets ((A1), (A2), ())
order by 1, 2;
Я знаю об этом из SQL 2003 7.9 Синтаксис 17, который описывает, как столбцы заменяются на NULL. Тем не менее, я мог пропустить или неправильно понять правило в другом месте, которое исключает аргументы для агрегатов.
m) For each GS_i:
iii) Case:
1) If GS_i is an <ordinary grouping set>, then
A) Transform SL2 to obtain SL3, and transform HC to obtain
HC3, as follows:
II) Replace each <column reference> in SL2 and HC that
references PC_k by "CAST(NULL AS DTPCk)"
1 ответ
Как и в случае со многими сложными функциями SQL, он может помочь взглянуть на более ранние версии стандарта, где формулировка может быть проще. И оказывается, что групповые наборы были введены в SQL 1999, а затем были пересмотрены в SQL 2003.
SQL 1999
Синтаксическое правило 4 гласит:
Let SING be the <select list> constructed by removing from SL every <select
sublist> that is not a <derived column> that contains at least one <set
function specification>.
Тогда синтаксическое правило 11 определяет PC_k как ссылки на столбцы, содержащиеся в группе по списку. Создает производную таблицу, проецирующую объединение GSQQL_i, которые являются спецификациями запроса, проецирующими PC_k или NULL в зависимости от обстоятельств, PCBIT_i индикаторы функции группировки и SING,
Таким образом, любой, содержащий функцию set, не будет заменен своим аргументом, и его столбцы также не будут заменены. Так что ответ (1) правильный.
Однако в следующем запросе GSQQL_i соответствует <grand total> не группирует по C1, поэтому я думаю, что это даст ошибку, вместо того, чтобы заменить C1 на NULL для этого набора группировки.
select C1 + MAX(C2) from T group by grouping sets ((C1), ());
SQL 2003 - 2011
У меня до сих пор нет однозначного ответа на это. Это зависит от того, что они подразумевали (или забыли указать?) Под "ссылками" в правиле замены. Было бы более понятным, если бы он сказал одно из "немедленно содержащихся", "просто содержащихся" или "непосредственно содержащихся", как определено в ИСО 9075-1 (SQL Part 1: Framework).
В примечании (номер 134 в SQL 2003) в начале Общих правил говорится: "В результате синтаксических преобразований, указанных в правилах синтаксиса этого подпункта, остается рассмотреть только примитивные
Я нашел черновик Технического исправления 5 [ pdf], который представляет собой "разницу" по отношению к SQL 2003. Это включает соответствующие изменения на стр. 80–87. К сожалению, основная масса изменений имеет только краткое обоснование "Обеспечить правильное, унифицированное лечение CUBE и ROLLUP". Общее правило 3, приведенное выше, имеет обоснование "прояснить семантику ссылок на столбцы".