Шаг 36 - SELECT - "Группировка" и отбор HAVING

Итак, наконец, мы почти приблизились к завершению оператора SELECT! Еще немного и можно будет сказать, что мы все обсудили! Но, а пока рассмотрим, как выбрать данные при группировке, то есть выводить не все данные, а только те, которые нас интересуют. Ранее для отбора строк по условию мы пользовались выражением WHERE. Для отбора групп по условию существует оператор HAVING. Его синтаксис аналогичен выражению WHERE и мало того их можно использовать вместе! Давайте рассмотрим следующий запрос:

Какова средняя стоимость заказа для каждого служащего из числа тех, у которых общая стоимость заказов превышает $300?

SELECT REP, AVG(AMOUNT)
FROM ORDERS
GROUP BY REP
HAVING SUM(AMOUNT) > 300
/

Вот и результат:

SQL> SELECT REP, AVG(AMOUNT)
  2  FROM ORDERS
  3  GROUP BY REP
  4  HAVING SUM(AMOUNT) > 300
  5  /

                                    REP AVG(AMOUNT)
--------------------------------------- -----------
                                    103      301,22
                                    105     148,125
                                    107 228,5933333
                                    108 311,2064285
                                    110      327,25

5 rows selected

Как видно выражение HAVING SUM(AMOUNT) > 300 сработало как условие при группировке строк! А теперь посмотрим на рисунок.

36_1.gif (12357 b)

Здесь видно, если условие SUM(AMOUNT) > 300 ложно, то эта группа из результирующего набора отбрасывается. Если истинно, то группа попадает в результирующий набор! Ничего сложного!

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

SELECT CITY, SUM(QUOTA), SUM(SALESREPS.SALES)
FROM OFFICES, SALESREPS
WHERE OFFICE = REP_OFFICE
GROUP BY CITY
HAVING COUNT(*) >= 2
/

Получаем:

SQL> SELECT CITY, SUM(QUOTA), SUM(SALESREPS.SALES)
  2  FROM OFFICES, SALESREPS
  3  WHERE OFFICE = REP_OFFICE
  4  GROUP BY CITY
  5  HAVING COUNT(*) >= 2
  6  /

CITY                           SUM(QUOTA) SUM(SALESREPS.SALES)
------------------------------ ---------- --------------------
Контрогайка                           700              835,915
Красный Мотоцикл                      575              692,637
Чугуевск                             1175             1121,084

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

  1. Объединяются таблицы OFFICES и SALESREPS для того, чтобы найти город, в котором работает служащий.
  2. Группируются строки объединенной таблицы по офисам.
  3. Исключаются группы, содержащие две или менее строки - это те строки, которые не удовлетворяют критерию предложения HAVING.
  4. Вычисляются общие плановые и фактические объемы продаж для каждой группы.

Вот так строится этот запрос, если вы все это поняли. Замечательно. Тогда посмотрите кое что посложнее:

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

SELECT DESCRIPTION, PRICE, QTY_ON_HAND, SUM(QTY)
FROM PRODUCTS, ORDERS
WHERE MFR = MFR_ID
GROUP BY MFR_ID, PRODUCT_ID, DESCRIPTION, PRICE, QTY_ON_HAND
HAVING SUM(QTY) > (0.75 * QTY_ON_HAND)
ORDER BY QTY_ON_HAND DESC
/

Получаем следующий набор:

SQL> SELECT DESCRIPTION, PRICE, QTY_ON_HAND, SUM(QTY)
  2  FROM PRODUCTS, ORDERS
  3  WHERE MFR = MFR_ID
  4  GROUP BY MFR_ID, PRODUCT_ID, DESCRIPTION, PRICE, QTY_ON_HAND
  5  HAVING SUM(QTY) > (0.75 * QTY_ON_HAND)
  6  ORDER BY QTY_ON_HAND DESC
  7  /

DESCRIPTION             PRICE    QTY_ON_HAND   SUM(QTY)
------------------ ---------- -------------- ----------
Лампа настольная           55            277        223
Рейка деревянная          107            207        223
Носки черные               76            167        223
Рейка пластмассовая       117            139        223
Труба алюминиевая         355             38         32
Карандаш простой           25             37        223
Подушка ватная            177             37         32
Нож специальный           475             32         30
Монитор LG               2500             28        223
Наушники SONY             975             28         30
Коробка картонная        2,75             25        223
Рубероид рулоны           250             24         30
Электродвигатель          243             15         26
Бочка пластмассовая       350             14         60
Доска профильная         4500             12         60
Телевизор SAMSUNG        4500             12         60
Профиль специальный      1875              9         30
Осветитель ртутный       1425              5         30
Тарелка фарфоровая        180              0          2

19 rows selected

А вот здесь, ничего разжевывать не буду, это вам задание на дом! Напишите план запроса и пришлите на мой ящик, а я посмотрю кто как понимает данный материал! :) Ограничения, накладываемые на предложение HAVING те же, что и ограничения для запросов с группировкой, которые мы рассматривали в предыдушем шаге, но есть ряд дополнений. Предложение HAVING должно содержать как минимум одну агрегатную функцию, если это не так, то лучше применять предложение WHERE. Так как предложение WHERE применимо к отдельным строкам, а предложение HAVING к группам строк и об этом не стоит забывать! А, остальное все как обычно! :) Да, что касается NULL в условии поиска HAVING, они точно такие же, как и для предложения WHERE, т.е. "Если условие поиска имеет значение NULL, группа строк исключается и строка в результатах запроса для нее не генерируется". К слову предложение HAVING, в принципе можно применять и без GROUP BY, но тогда результат запроса рассматривается как одна группа состоящая из всех строк. На практике это применяется довольно редко. Вот так работает HAVING и все, что к нему прилагается!


Предыдущий Шаг | Следующий Шаг | Оглавление
Автор Летучий Сергей.