Фразы GROUP BY и HAVING

 

В менеджменте часто требуется статистическая информация о каждой группе во множестве групп. Например, рассмотрим следующий запрос:

Запрос:Для каждого менеджера выяснить максимальную почасовую ставку среди его подчиненных.

Для того чтобы решить эту задачу, мы должны разделить работников на группы в соответствии с их менеджерами. Затем мы определим максималь­ную ставку внутри каждой группы. В SQL это делается таким образом:

 

SELECT SUPV_ID, MAX(HRLY_RATE)

FROM WORKER

GROUP BY SUPV_ID

Результат:

SUPV_IDMAX(HRLY RATE)

1311 15.50

1520 13.•75

2920 10.00

3231 17.40

При обработке этого запроса система сначала разбивает строки таблицы WORKER на группы по следующему правилу. Строки помещаются в одну группу тогда и только тогда, когда у них совпадает SUPV_ID. Затем фраза SELECT применяется к каждой группе. Поскольку в данной группе только одно значение SUPV_ID, то никакой неопределенности SUPV_ID в группе нет. Для каждой группы, фраза SELECT выводит SUPV_ID, a также вычисляет и выводит значение MAX(HRLY_RATE). Результат представлен выше.

В команде SELECT со встроенными функциями могут встречаться только те столбцы, которые входят во фразу GROUP BY. Обратите внимание, что SUPV_ID может использоваться в команде SELECT, поскольку он входит во фразу GROUP BY.

Фраза GROUP BY. Означает, что строки должны быть разбиты на группы с общими значениями указанного столбца (столбцов).

Фраза GROUP BY позволяет выполнять определенные сложные вычис­ления. Например, нам может понадобиться выяснить среднее значение этих максимальных ставок. Однако, вычисления со встроенными функциями ог­раничены в том смысле, что не разрешается использование встроенных функции внутри других встроенных функций. Таким образом, выражение типа

AVG(MAX(HRLY_RATE))

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

С командой GROUP BY можно использовать команду WHERE:

Запрос: Для каждого типа зданий выяснить средний уровень качества среди зданий статуса 1.

 

SELECT TYPE, AVG(QLTY_LEVEL)

FROM BUILDING

WHERE STATUS = 1

GROUP BY TYPE

Результат:

TYPEAVG(QLTY_LEVEL)

Магазин 1

Жилой дом 3

Офис 3.5

Фраза WHERE выполняется перед командой GROUP BY. Таким образом, ни одна группа не может содержать строку, в которой статус отличен от 1. Строки статуса 1 группируются по значению TYPE, а затем к каждой группе применяется фраза SELECT.

Фраза HAVING. Накладывает условия на группы.

 

Мы также можем применять условия и к группам, созданным фразой GROUP BY. Это делается при помощи фразы HAVING. Предположим, на­пример, что мы решили конкретизировать один из предыдущих запросов:

Запрос: Для каждого менеджера, у которого более одного подчиненного, выяснить максимальную почасовую ставку среди его подчиненных.

Мы можем отразить это условие соответствующей командой HAVING:

SELECT SUPV_ID, MAX(HRLY_RATE)

FROM WORKER GROUP BY SUPV_ID

HAVING COUNT(*) > 1

Результат:

SUPV_ID MAX(HRLY_RATE)

1311 15.50

1520 13.75

3231 17.40

Разница между фразами WHERE и HAVING состоит в том, что WHERE применяется к строкам, в то время как HAVING применяется к группам.

Запрос может содержать и команду WHERE, и команду HAVING. В этом случае первой выполняется фраза WHERE, поскольку она выполняется до разбиения на группы. Например, рассмотрим следующую модификацию приведенного ранее запроса:

Запрос: Для каждого типа зданий выяснить средний уровень качества среди зданий статуса 1. Рассматривать только те типы зданий, максимальный уровень качества которых не превышает 3.

 

SELECT TYPE, AVG (QLTY_JLEVEL)

FROM BUILDING

WHERE STATUS = 1

GROUP BY TYPE

HAVING MAX(QLTY_LEVEL) <= 3

Результат:

TYPE AVG(QLTY_LEVEL)

Магазин 1

Жилой дом 3

 

Обратите внимание, что начиная с фразы FROM фразы выполняются по порядку, а затем применяется фраза SELECT. Так, к таблице BUILDING применяется фраза WHERE, и все строки, в которых STATUS отличен от 1, удаляются. Оставшиеся строки группируются по TYPE; все строки с одина­ковым значением TYPE оказываются в одной группе. Таким образом, созда­ется нескрлько групп, по одной для каждого значения TYPE. Затем к каж­дой группе применяется фраза HAVING, и те группы, в которых макси­мальное значение уровня качества превышает 3, удаляются. Наконец, к ос­тавшимся группам применяется фраза SELECT.

Задание 5. Выполните следующие запросы к базе данных «Перевозки».

1. Каков максимальный вес груза, доставленного в каждый город?
2. Для каждого города с населением свыше 1 миллиона человек выяс­нить минимальный вес груза, отправленного в этот город.
3. Для каждого города, в который отправлено не менее трёх грузов, выяснить средний вес груза, отправленного в этот город.

 

7. Встроенные функции и подзапросы

 

Встроенные функции могут использоваться только во фразе SELECT или в команде HAVING. Однако фраза SELECT, содержащая встроенную функ­цию, может быть частью подзапроса. Рассмотрим пример такого подзапроса:

Запрос: У кого из работников почасовая ставка выше среднего?

SELECT WORKER_NAME

FROM WORKER

WHERE HRLY_RATE >

(SELECT AVG(HRLY_RATE)

FROM WORKER)

Результат:

NAME

К. Немо

П.Мэйсон

Х. Колумб

Обратите внимание, что подзапрос не коррелирует с главным запросом. Подзапрос выдает ровно одно значение - среднюю почасовую ставку. Глав­ный запрос выбирает работника только в том случае, если его ставка больше вычисленной средней.

В коррелированных запросах также могут использоваться встроенные функции:

Запрос: У кого из работников почасовая ставка выше средней почасовой ставки среди подчиненных того же менеджера?

В этом случае вместо вычисления одной средней почасовой ставки для всех работников мы должны вычислить среднюю ставку каждой группы ра­ботников, подчиняющейся одному и тому же менеджеру. Более того, наше вычисление должно производиться заново для каждого работника, рассмат­риваемого главным запросом:

 

SELECT A. WORKER_NAME

FROM WORKER A

WHERE A.HRLY_RATE >

(SELECT AVG(B.HRLY_RATE)

FROM WORKER В

WHERE B.SUPV_ID = A.SUPV_ID)

Результат:

A.WORKER NAME

К.Немо

П.Мэйсон

X.Колумб

Фраза WHERE подзапроса содержит ключевое условие корреляции. Это условие гарантирует, что среднее будет вычисляться только по тем рабочим, которые подчиняются тому же менеджеру, что и работник, рассматриваемый главным запросом.

9. Операции изменения данных

 

В SQL есть операции изменения данных INSERT, UPDATE и DELETE, позволяющие, соответственно, добавлять строки, изменять значения в стро­ках и удалять строки определенной таблицы в базе данных. Каждую из опе­раций мы обсудим отдельно.

INSERT. Операция INSERT (вставить) позволяет вводить в таблицу как отдельные строки путем указания значений каждого столбца, так и множе­ства строк путем формулировки запроса, определяющего вводимые строки.

INSERT INTO ASSIGNMENT (WORKER_ID, BLDG_ID, START DATE)

VALUES (1284, 485, 13.05)

Это выражение вводит в таблицу ASSIGNMENT одну строку. Имена столбцов, для которых задаются соответствующие значения, перечислены в скобках после имени обновляемой таблицы. Поскольку мы опустили NUM_DAYS, в этот столбец будет помещено пустое значение.

Предположим, что мы создали новую таблицу с именем BUILDING_2, состоящую из столбцов BLDG_ID, TYPE и QLTY_LEVEL (УРОВ_КАЧЕСТВА), и хотим заполнить эту таблицу строками из таблицы BUILDING, имеющими STATUS 2. Тогда мы воспользуемся второй формой команды INSERT:

INSERT INTO BUILDING_2

SELECT BLDG_ID, TYPE, QLTY_LEVEL

FROM BUILDING

WHERE STATUS = 2

INSERT. Операция, добавляющая строки к таблице.

UPDATE. Операция UPDATE (изменить) всегда применяется ко всем строкам, удовлетворяющим условию выражения WHERE. Если мы хотим повысить на 5 процентов ставку каждого подчиненного начальника 1520, то нам потребуется следующее выражение:

UPDATE WORKER

SET HRLY_RATE = 1.05 * HRLY_RATE

WHERE SUPV_ID = 1520 -

Если фраза WHERE отсутствует, то операция применяется к каждой строке таблицы. Например, если бы мы хотели повысить на 5 процентов ставку каждого работника, то мы должны были бы просто опустить команду WHERE в выражении UPDATE.

UPDATE. Операция, изменяющая значения столбцов в строке.

DELETE. Операция DELETE (удалить) также применяется ко всем стро­кам, удовлетворяющим условию WHERE. Если фраза WHERE отсутствует. то удаляются все строки таблицы. Допустим, что все рабочие, чей начальник имеет индекс 1520, были уволены, и мы хотим удалить соответствующие строки из базы данных. Это сделает следующее выражение:

DELETE FROM WORKER

WHERE SUPV ID = 1520

DELETE.Операция, удаляющая строки из таблицы.