Обзор статьи "A discussion of some redundances in SQL"

Уважаемые читатели!

Журнал Database Programming and Design уже второй месяц предоставляет возможность электронного доступа к колонке Кристофера Дейта "According to Date". В майском выпуске господин Дейт начал публиковать интересную заметку по поводу избыточности средств языка SQL. Мне показалось полезным предложить вашему вниманию первую часть этой заметки. Прошу обратить внимание, что в примерах используется SQL-92. Думаю, что многие из вас не обращали внимание на соответствующие возможности этого языка. Если вас интересуют другие работы К.Дейта, обращайтесь в электронный книжный магазин www.mistral.ru.

С уважением, Сергей Кузнецов

DataBase Programming & Design OnLine, May 1998

www.dbpd.com

A discussion of some redundances in SQL

Greevous Bodily Harm (Part 1 of 2)

C.J. Date
оригинал статьи можно найти по адресу
http://www.dbpd.com/9805date.htm

Кристофер Дейт является независимым автором, лектором, исследователем и консультантом, специализирующимся в области систем реляционных баз данных. Корресподенцию ему можно послать по почте по адресу: Database Programming & Design, 411 Borel Ave., Ste. 100, San Mateo, CA 94402.

Известно ли вам, что разделы GROUP BY и HAVING (для их совместного названия далее используется аббревиатура GBH) в языке SQL избыточны? Другими словами, любой осмысленный запрос, который можен быть выражен с использованием одного из этих разделов или их обоих может быть выражен и без их применения. (Ниже поясняется значение слова "осмысленный".)

Раздел 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 "работает некорректно". Но это преобразование работает правильно для всех "осмысленных" запросов.

Раздел HAVING

Вот запрос, для формулировки которого большинство людей использовало бы раздел HAVING:

	Q4: Для каждой детали, поставляемой более чем одним поставщиком,
	выдать номер детали.

Возможной формулировкой с использованием GHB могла бы быть следующая:

	SELECT SP.P#
	 FROM SP
	 GROUP BY SP.P#
	 HAVING COUNT(*) > 1 ;

Результатом такого запроса является таблица

	    P#
	  -----
	    P1
	    P2
	    P4
	    P5

Вот формулировка без использования разделов GROUP BY и HAVING:

	SELECT DISTINCT SP.P#
	 FROM SP
	 WHERE (SELECT COUNT(*)
	         FROM SP AS SPX
	         WHERE SPX.P# = SP.P#) > 1 ;

Снова эта формулировка длиннее варианта с GBH, но логически они эквивалентны. И опять легко обобщить пример. Если использовать обозначения из предыдущего раздела, то выражение

	SELECT R.A
	 FROM R
	 GROUP BY R.A
	 HAVING agg(R.B) comp scalar ;

(где comp является некоторой операцией скалярного сравнения, а scalar - некоторое скалярное выражение) может быть логически преобразовано к эквивалентному выражению

	SELECT DISTINCT R.A
	 FROM R
	 WHERE (SELECT agg(R.B)
	         FROM R AS RX
	         WHERE RX.A = R.A) comp scalar ;

Будем называть такого типа преобразования преобразованиями Типа 2. Посмотрим, что произойдет, если исходная формулировка будет включать раздел WHERE.

	Q5: Для каждой детали, поставляемой более чем одним поставщиком
	(кроме поставщика S1), выдать номер детали.

Формулировка с GBH:

	SELECT SP.P#
	 FROM SP
	 WHERE SP.P# <> 'S1'
	 GROUP BY SP.P#
	 HAVING COUNT(*) > 1 ;

Формулировка без GBH лишь немного хитрее:

	SELECT DISTINCT SP.P#
	 FROM SP
	 WHERE (SELECT COUNT(*)
	         FROM SP AS SPX
	         WHERE SPX.P# = SP.P#
	         AND SPX.S# <> 'S1') > 1 ;

Пример показывает, что преобразование Типа 2 может быть легко обобщено для случая наличия раздела WHERE. Вот несколько более сложный пример:

	Q6: Для каждой детали, поставляемой более чем одним поставщиком,
	выдать номер детали и общее число поставок этой детали.

Формулировка запроса с использованием GBH:

	SELECT SP.P#, SUM(SP.QTY) AS TQY
	 FROM SP
	 GROUP BY SP.P#
	 HAVING COUNT(*) > 1 ;

Применяя правила преобразований Типа 1 и 2, получим следующее:

	SELECT DISTINCT SP.P#,
	             (SELECT SUM(SPX.QTY)
	               FROM SP AS SPX
	               WHERE SPX.P# = SP.P#) AS TQY
	 FROM SP
	 WHERE (SELECT COUNT(*)
	         FROM SP AS SPX
	         WHERE SPX.P# = SP.P#) > 1 ;

И еще один пример:

	Q7: Для каждой детали, поставляемой более чем одним поставщиком,
	выдать общее число поставок этой детали, но без номера детали.

Вот формулировка с применением GBH:

	SELECT SUM(SP.QTY) AS TQY
	 FROM SP
	 GROUP BY SP.P#
	 HAVING COUNT(*) > 1 ;

Преобразованный вариант:

	SELECT (SELECT SUM(SPX.QTY)
	               FROM SP AS SPX
	               WHERE SPX.P# = SP.P#) AS TQY
	 FROM SP
	 WHERE (SELECT COUNT(*)
	         FROM SP AS SPX
	         WHERE SPX.P# = SP.P#) > 1 ;

В результате выполнения этих запросов были бы получены следующие результаты:

	  С GROUP BY               Без GROUP BY
	  и HAVING                 и HAVING
	 
	     TQY                       TQY
	    -----                     -----
	     600                       600
	    1000                      1000
	     500                       500
	     500

Снова запросы производят разные результаты и потому не эквивалентны. Но эта ситуация аналогична той, которая обсуждалась по поводу примера Q3, и не требует дополнительных комментариев.