Раздел GROUP BY
В качестве основы примеров используется известная по книгам К. Дейта база данных "поставщики и детали":
S ( S#, SNAME, STATUS, CITY ) PRIMARY KEY ( S# )
P ( P#, PNAME, COLOR, WEIGHT, CITY ) PRIMARY KEY ( P# )
SP ( S#, P#, QTY ) PRIMARY KEY ( S#, P# ) FOREIGN KEY ( S#) REFERENCES S FOREIGN KEY ( P#) REFERENCES P
Вот запрос к этой базе данных, для которой люди "естественно" используют раздел GROUP BY:
Q1: Для каждой поставляемой детали выдать номер детали, максимальное и минимальное число поставок.
"Естественной" (с применением GROUP BY) формулировкой запрса является следующая:
SELECT SP.P#, MAX(SP.QTY) AS MXQ, MIN(SP.QTY) AS MNQ FROM SP GROUP BY SP.P# ;
Предположим, что база данных содержит следующие значения:
S SP
S# SNAME STATUS CITY S# P# QTY ------------------------------- -------------- S1 SMITH 20 LONDON S1 P1 300 S2 JONES 10 PARIS S1 P2 200 S3 BLAKE 30 PARIS S1 P3 400 S4 CLARK 20 LONDON S1 P4 200 S5 ADAMS 30 ATHENS S1 P5 100 S1 P6 100 P S2 P1 300 S2 P2 400 P# PNAME COLOR WEIGHT CITY S3 P2 200 ------------------------------------ S4 P2 200 P1 Nut Red 12 London S4 P4 300 P2 Bolt Green 17 Paris S4 P5 400 P3 Screw Blue 17 Rome P4 Screw Red 14 London P5 Cam Blue 12 Paris P6 Cog Red 19 Rome
Тогда результатом запроса будет следующая таблица:
P# MXQ MNQ --------------- P1 300 300 P2 400 200 P3 400 400 P4 300 200 P5 400 100 P6 100 100
Вот другая формулировка того же самого запроса без использования GROUP BY:
SELECT DISTINCT SP.P#, (SELECT MAX(SPX.QTY) FROM SP AS SPX WHERE SPX.P# = SP.P#) AS MXQ, (SELECT MIN(SPX.QTY) FROM SP AS SPX WHERE SPX.P# = SP.P#) AS MXQ FROM SP ;
Конечно, эта формулировка немного дленнее предыдущей, но логически они эквивалентны. Обобщая этот пример, можно вывести следующее заключение:
Пусть имеется таблица R { A, B, ... } и пусть agg - это агрегатная функция (например, SUM, MAX или MIN), применимая к столбцу R.B. Тогда выражение
SELECT R.A, agg(R.B) AS C FROM R GROUP BY R.A ;
может быть логически преобразовано в эквивалентное выражение
SELECT DISTINCT R.A (SELECT agg(RX.B) FROM R AS RX WHERE RX.A = R.A) AS C) FROM R) ;
Будем далее называть это преобразование преобразованием Типа 1.
Теперь рассмотрим, что произойдет, если в исходной формулировке с GROUP BY будет присутствовать раздел WHERE. Расширим запрос Q1:
Q2: Для каждой поставляемой детали выдать номер детали, максимальное и минимальное число поставок, но при этом не принимать во внимание поставки поставщика S1.
Вот формулировка с GROUP BY:
SELECT SP.P#, MAX(SP.QTY) AS MXQ, MIN(SP.QTY) AS MNQ FROM SP WHERE SP.S# <> 'S1' GROUP BY SP.P# ;
Эквивалентная формулировка запроса без GROUP BY (не единственная из числа возможных) не намного хитрее:
SELECT DISTINCT SP.P#, (SELECT MAX(SPX.QTY) FROM SP AS SPX WHERE SPX.P# = SP.P# AND SPX.S# <> 'S1') AS MXQ, (SELECT MIN(SPX.QTY) FROM SP AS SPX WHERE SPX.P# = SP.P# AND SPX.S# <> 'S1') AS MNQ, FROM SP WHERE SP.S# <> 'S1' ;
Как видно, раздел WHERE из исходной формулировки с GROUP BY размножился в двух вложенных выражениях раздела SELECT. В исходной формулировке раздел WHERE управляет как разделом SELECT, так и разделом GROUP BY. Последовательность записи разделов в языке SQL несколько нелогична. В общем случае выражение, включающее разделы SELECT-FROM-WHERE-GROUP BY вычисляется в последовательности FROM-WHERE-GROUP BY-SELECT, и имело бы смысл писать именно в таком порядке. Но язык SQL этого не позволяет.
Как видно из приведенного выше примера, преобразование Типа 1 нуждается лишь в незначительных расширениях, чтобы включать возможность использования раздела WHERE. Детали очевидны. Еще раз изменим наш пример:
Q3: Для каждой поставляемой детали выдать максимальное число поставок, но без номера детали.
Формулировка с GROUP BY:
SELECT MAX(SP.QTY) AS MXQ FROM SP GROUP BY SP.P# ;
С использованием преобразования Типа 1 мы получим следующую формулировку:
SELECT DISTINCT (SELECT MAX(SPX.QTY) FROM SP AS SPX WHERE SPX.P# = SP.P# AS MXQ FROM SP ;
Вот результаты выполнения этих двух запросов:
С GROUP BY Без GROUP BY
MXQ MXQ ----- ----- 300 300 400 400 400 100 300 400 100
Как видно, результаты разные, т.е. запросы не совсем эквивалентны, и преобразование Типа 1 не работает в этом частном случае. Но действительной причиной отсутствия эквивалентности является то, что результат выполнения запроса с GROUP BY не есть отношение, поскольку содержит строки-дубликаты. Более существенно то, что дубликаты осмысленны. Например, у двух строк "300" разный смысл: одна из них означает, что у некоторой детали максимальный объем поставок равен 300, а другая - что имеется некоторая другая деталь с тем же самым максимальным объемом поставок. Эти "осмысленные дубликаты" представляют собой очень существенный отход от базовых принципов реляционной модели данных. Возможность их наличия говорит о том, что SQL не является и никогда не был истинно реляционным языком.
Заметим, что при использовании SQL "осмысленные дубликаты" могут появляться в ряде других случаев. Например, даже такое простое выражение как
SELECT CITY FROM S ;
в общем случае производит результат с "осмысленными дубликатами".
Еще раз осмыслим запрос Q3. Что он на самом деле означает? Похоже, что нас интересовало множество максимальных поставок из SP. Формулировка без GROUP BY корректно производит эту информацию. Конечно, в результате не показывается, для каких конкретных деталей производились максимальные поставки, но требуемая информация предоставляется.
Формулировка с GROUP BY дает ту же самую информацию. Поэтому она допустима. Но некоторые люди (к числу которых не относится господин Дейт) могли бы сказать, что поскольку поставляются данные о шести деталях, то эта формулировка предпочтительнее той, которая без GROUP BY. Следует заметить, что эта информация представлена в нереляционной форме. Было бы корректнее получать ее по-другому, например, с использованием запроса Q1.
По мнению автора, запросы вида Q3, хотя и являются допустимыми, не слишком осмысленны.Такие запросы игнорируют существенную информацию. Обычно это связано с тем, что в раздел SELECT входят не все столбцы, используемые в разделе GROUP BY. Для подобных формулировок преобразование Типа 1 "работает некорректно". Но это преобразование работает правильно для всех "осмысленных" запросов.
