Рж. S.9. Отношение с вооброжоемым розделением но группы

Хотя запросы с GROUP BY в обшем случае могут содержать в предложении SELECT и группируемые атрибуты, и агрегаты, с технической точки зрения это совсем не обязательно. Например, можно написать;

SELECT StudioName

FROM Movie

GROUP BY StudioName;

Этот запрос группирует кортежи отношения Movie согласно названиям их стули11 и выдает название студии для каждой группы независимо от того, сколько кортежей содержит данное название студии. Значит, этот запрос дает такой же результат, как и запрос

SELECT DISNCT studioName FROM Movie

Предложение GROUP BY можно применять и в запросе, адресованном нескольким отношениям. Такой запрос интерпретируется согласно следующим шагам.

1. Вычисляется отношение R, которое получается из предложений FROM и WHERE, т.е. ft-это декартово произведение отношений, указанных в предложении FROM, к которому применен выбор, указанный в предложении WHERE.

2. Кортежи отношения R группируются согласно атрибутам из предложения GROUP BY.

3. Результат выдается в виде атрибутов и агрегатов пункта SELECT так, словно запрос касался отношения R.

Пример 3.25. Допустим, нужно вывести на печать таблицу, перечисляющую сумму продолжительности фильмов, выпущенных каждым продюсером. Нужна информация из двух отношений:

Movie(title, year, lengtli, inColor, studioName. producerC#) MovieExec(name, address. сегШ, netWorth)

поэтому мы начинаем с тэта-соединения, сравнивая входящие в них номера сертификатов. В результате получается отношение, в котором каждый кортеж из MovieExec спарен с кортежами из Movie, содержащими все фильмы конкретного продюсера. И наконец, подсчитывается сумма продолжительности фильмов в каждой группе. Такой запрос показан на рис. 5.10. О

5.5.3 Предложение HAVING

Предположим, что мы не хотим вк-пючать в таблицу примера 5.25 всех продюсеров фильмов. Тогда перед группированием можно ограничить кортежи так, чтобы 1!сжелательные группы оказапись пустыми. Например, если нужна общая продолжительность фильмов, выпущенных только продюсерами, чистьнЧ доход которых превышает 10 млн. дол., строку (3) рис. 5.10 можно заменить следующей строкой:

3) WHERE producerC# = certs AND networth >= ЮОООООО

Однако иногда нужно выбрать группы, основанные на некотором обшем свойстве самой данной группы. Тогда за предложением GROUP BY указывается предложение HAVING, состоящее из ключевого слова HAVING, за ним следует условие, касающееся группы.

Пример 5.26. Допустим, нужно определеть общую продо.чжнтельность фильмов только для продюсеров, выпустивших хотя бы один свой фильм до 1930 г. В коней рис. 5 10 добавляется предложение

HAVING MIN(year) < 1930

В результате получается запрос (рис. 5.11), удаляющий нз отношения такие группы кортежей, в которых каждый кортеж 1!меет в компоненте year значение 1930 н более. О

SELECT name. SUM(length) FROM MovieExec. Movie WHERE producerC# = cert* GROUP BY name HAVING M N(year) < 1930,

Рис. S.n. Вычисление гэодолжитепьности филытов, оыпущенных аорымк продюсероми

Порядок пушсгов в запросах SQL

Мы рассмотрели шесть предложений, которые могут входить в запрос SQL тгша selcct-froni wliere: SELECT, FROM, WHERE, GROUP BY, HAVING ii ORDER BY. Обязательны только первые три. Остальные предложения яачяются дополнительными; онн должны появляться в запросе в том порядке, в каком здесь перечислены.

1) SELECT name. SUM(length)

2) FROM MovieExec. Movie

3) WHERE p!oducerCfli = cert#

4) GROUP BY name;

Рис. 5.10. (Вычисление продолжительнопи фильмов, выпущенных каждулА продюсером

5-5.4 Упраяснсния к разделу 5.5

Упражнение 5.5.1. Запишите перечисленные ниже запросы, основанные на БД из упражнения 4.1.1;

Product(mal<er, mode!, type) PC(model, speed, ram, hd, cd, price) Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price)

и оцените их, используя данные этого упражнения. *а) Найдите среднюю скорость ПК.

b) Найдите среднюю скорость ПК-блокнотов, цена KOTojpbix превышает 2500 дол.

c) Найдите среднюю иену ПК, выпущенных производителем А.

!d) Найдите среднюю цену ПК и ПК-блокнотов, выпущенных производителем D.

е) Дли каждого значения скорости найдите среднюю стоимость ПК с тгкок же скоростью процессора.

*! О Для каждого производителя найдите средний размер экрана выпускаемых им ПК-олокнотов.

!g) Найд1гге лроизво.вдтелей, выпускающих по меньшей мере три различные Аюдели ПК.

! h) Найдите максимальную иену П К, выпускаемых каждым производителем.

! i) Для каждого значения скоросги ПК, превышающего 150 МГц, определите среднюю пену компьнзтера с такой же скоростью,

!! j) Найдите средний размер диска ПК тех производителей, которые выпускают и принтеры.

Упражнение 5.5.2. Запишите перечисленные ниже запросы, основанные на БД из упражнения 4.1.3:

Classe5(clsss, type, country, numGuns, bore, displacement) Ships(name, class, launched) 8attles(neme, date) Outcomes(shJp. battle, result)

и вычислите их. используя ланньве этого упражнения.

a) Определите число классов линейных кораблей.

b) Определтгге среднее число орудий для классов линейных кораблей.

!с) Определите среднее число орудии линейных кораблей. Учтите разницу между этим н предыдущим заданием. Определяется ли класс числом входящих в него кораблей?

id) Для каждого класса определите год. когда был спущен на воду первый корабль этого класса

! е) Для каждого класса определите число кораблей этого класса, потоплен ных Б сражении.