5.4.2 Дуб-пикаты в объединениях, пересечениях и разностях

в отличие от оператора SELECT, сохраняюшего дубликаты мо умолчанию и уяа-ляютего их топько при иагунчни ключевого слона DISTINCT, операции объединения, пересечения и разности, введенные в разделе 5.2.5, обычно уяапяют дубликаты кортежей. Для предотвращения удаления дубликатов за операторами UNION, INTERSECT или EXCEPT ставится ключевое слово ALL. В результате применяется семантика мультимножеств, описанная к разделе 4.6.2.

Пркпг.ер 5.21. Добавим к выражению с объединением из примера 5.16 ключевое слово ALL.

(SELECT tiae. year FROM Movie) UN ON ALL

(SELECT movieTitle AS title, movieYear AS year FROM Starsln);

Теперь название i год выпуска фильма появятся в результате столько раз, сколько их ахожденнй содержится в отношениях Movie и Starsln, вместе взятых. Например, если фильм входит в отношение Movie один раз, а в отношении Starsln перечислены три юшозвезды, участвующие в этом фильме (фильм входит в три различных кортежа из Starsln), его название и год выпуска войдут в результат объединения четыре раза. □

Операторы INTERSECT ALL н EXCEPT ALL обозначают пересечение и разность

мультимножеств. Если R н 5-отношения, результатом выражения

Л INTERSECT ALL S

будет отношение, в котором число вхождений кортежа i равно минимальному числу его вхо>1саенип в А* и в S.

Число в.кожден11й кортежа i в результат выражения

REXCEPT ALL S

равно разности между числом вхождений г в Л и числом его вхоадений в S при условии, что эта разность положительна. Именно такие определения рассматривались для мультимножеств в разделе 4.6.2.

5.4.3 Упражнения к разделу 5.4

Япренкнение 5.4.1, Запишите в SQL все запросы упражнения 4.1.1, обеспечив устранение дубликатов

Упрожнение 5.4.2. Запишите в SQL все запросы упражнения 4.1.3, обеспечив

устранен! е дубликатов

! Упрожнение 5.4.3. Для кажаого ответа из упражнения 5.3.1 определите, может ли результат запроса содержать дубликаты кортежей. Еапи да, то перепишите запрос так, чтобы дубликаты были исключены. Если нет, santmiHTC запрос без помощи подзапросов, что в итоге тоже дает свободный от дубликатов ответ.

! Упрожнение 5.4.4. Повторите упражнение 5.4.3 дпя ваших ответов к упражнению 5.3.2.

5.5 Агрегация

другим классом операций нал отношениями в целом являются операции агрегации значении в стагбце. Агрегация - эю формирование единственного значения из списка значений, содержаищхся в стапбие. Примерами агрегации являются сумма значеииП и срсансе значение в столбце. SQL позволяет не только агрегировать столбцы, но и группировать кортежи отношения по какому-либо критерию, например по конкретному значению из другого столбца, а затем проводить агрегацию в полученных группах.

5.5.1 Операторы агрегации

в SQL есть операторы, которые применяются к столбцу отношения и порождают на нем некоторый итог, или агрегацию.

L SUM - сумма значений в столбце.

2. AVG - среднее из всех значений в столбце.

3. MIN - минимальное из всех значений в столбце.

4. МАХ - максимальное из всех значений в столбце.

5. COUNT -число значений (включая дубликаты, если только они явным образом не удаляются с помощью DISTINCT).

Эти операторы применяются к выражению со скалярным значением, обычно к имени столбца, в пункте SELECT.

Пример 5.22. Следующий запрос находт среднюю величину чистого дохода всех продюсеров фильмов:

SELECT AVG(nelWofth) FROM MovieExec

Заметим, что в этом запросе вообще нет пункта WHERE. Запрос проверяет столбец отношения

MovieExec(name. address. cert#, netWorlh)

суммирует найденные в нем значения, извлекая по одному значению из каждого кортежа (включая дубликаты), и делит пмучепную сумму на число кортежей. Если дубликатов нет, запрос дает ожадаемую среднюю величину чистого дохода. При наличш дубликатов чистый доход продюсера фильмов, чей кортеж входит в отношение п раз. используется при поасчете средней величины тоже п раз. □

Пример 5.23. Запрос

SELECT COUNT(*) FROM MovieExec:

псдсчшывает число кортежей в отношении MovieExec. При условии, что name - ключ для MovieExec и в этом отношении нет дубликатов кортежей, результат совпадет с числом продюсеров, внесенных в БД.

Применение оператора афегании со знаком *, т.е. к кортежу в целом, имеет смысл только дня COUNT. Бесполезно применять любой другой оператор агрегации более чем к олгюму столбцу.

Для папучения такой таблицы применяется предложение GROUP BY, стоящее в запросе непосредственно за предложением WHERE. За ключевыми словами GROUP BY указывается список группируемых атрибутов. В простейшей ситуации в предложен[щ FROM указано тапько одно отношение и его кортежи i-руппируются согласно нх значениям в группируемых атрибутах. Любой оператор агрегации, используемый в пункте SELECT, применяется только внутри групп.

Пример 5.24. Задача нахождения суммы продолжительности всех фильмов каждой студии в отношении

Movie(title, year, lengtfi, InColor, studioName, producerCS)

решается с помошью запроса

SELECT StudioName. SUM(length)

FROM Movie

GROUP BY StudioName;

Можно допустить, что кортежи отношения Movie распознаются и группируются так. как показано на рис. 5.9. Затем подсчитывается сумма компонентов продолжительности в каждой группе и дпя каждой группы название студни печатается вместе с такой суммой. □

Заметим, что предложение SELECT в примере 5.24 содержит термы двух типов.

1. Агрегаты, п которых оператор агрегации применяется к атрибут) или к выражению, содержащему атрибут. Как уже было сказано, эти термы оиениваются отдельно в каждой группе.

2. Атрибуты шпа studюName п данном примере, входящие в пункт GROUP BY. В предложеини SELECT, содержащем агрегаты, неагрегнрованными .могут быть только атрибуты, упомянутые в предложении GROUP BY.

Для получения гарантии, что дублирующиеся кортежи учитываются лишь один раз, можно вычислять только атрибут name и использовать в запросе ключевое слово DISTINCT:

SELECT COUNT(DISTINCT name) FROM MovieExec;

Если даже name не является ключом (т.е. существуют различные кортежи, относящиеся к одному и тому же продюсеру фильмов или продюсеры с одним и тем же именем), этот запрос учитывает каждое имя только один раз. □

5-5-2 Группирование

Иногда необходимо рассмазрнвать кортежи отношения в виде групп, соответствующих значению одного из столбцов. Допустим, нужно вычислить общее количество вре.мени (в минутах), в течение которого длятся фильмы, снятые на каждой студии. Тогда кортежи отношения Movie нужно сгруппировать в соответствии со студиями и вычнсл1П-ь сумму в столбце length каждой nonjnjeHHoft группы. Результат может потребоваться в виде таблицы, связывающей студии с суммами продолжительности их фильмов, например: