Двигаемся дальше. С агрегированием осталось немного. Нахождение экстремумов, а именно функции MIN(), MAX(). Не смотря на свою, с первого взгляда, незатейливость, подумаешь, найти минимум, да максимум. Но не все так просто. Эти функции работают как с числовыми столбцами, так и с датами и даже со строковыми переменными. Самое простое применение этих функций - это работа с числами.
Например, дадим запрос вида: Каковы наибольший и наименьший плановые объемы продаж?
SELECT MIN(QUOTA), MAX(QUOTA) FROM SALESREPS /
Получаем:
SQL> SELECT MIN(QUOTA), MAX(QUOTA)
2 FROM SALESREPS
3 /
MIN(QUOTA) MAX(QUOTA)
---------- ----------
200 400
Это столбцы, содержащие числа. Дадим еще один запрос: Когда был сделан самый первый из всех содержащихся в базе данных заказов?
SELECT MIN(ORDER_DATE) FROM ORDERS /
Получаем:
SQL> SELECT MIN(ORDER_DATE) 2 FROM ORDERS 3 / MIN(ORDER_DATE) --------------- 27.02.1989
Это уже даты, то есть промежутки времени и все зависит от длины промежутков. И еще простой запрос: Каков наибольший процент выполнения плана среди всех служащих?
SELECT MAX(100 * (SALES/QUOTA)) FROM SALESREPS /
Получаем:
SQL> SELECT MAX(100 * (SALES/QUOTA))
2 FROM SALESREPS
3 /
MAX(100*(SALES/QUOTA))
----------------------
135,442857142857
Это снова числа. Так же сами можете поработать со строками. Но учтите, что разные виды кодировок применяемые в серверах SQL, могут давать различные результаты. Например, прописные буквы в моей системе идут первыми, потом строчные, а например в системе EBCDIC фирмы IBM все наоборот. Так, что имейте это в виду. А как, например, просто подсчитать количество записей? Для этого есть функция COUNT(). Эта функция всегда возвращает целое число, не зависимо от типа передаваемых данных.
Например: Сколько клиентов у нашей компании?
SELECT COUNT(CUST_NUM) FROM CUSTOMERS /
Получаем:
SQL> SELECT COUNT(CUST_NUM)
2 FROM CUSTOMERS
3 /
COUNT(CUST_NUM)
---------------
22
Это целое число, так как клиентов не может быть 22.5, это если только вирусы поработали. :) Еще один запрос: Сколько служащих перевыполнили план?
SELECT COUNT(NAME) FROM SALESREPS WHERE SALES > QUOTA /
Получаем:
SQL> SELECT COUNT(NAME)
2 FROM SALESREPS
3 WHERE SALES > QUOTA
4 /
COUNT(NAME)
-----------
7
Как видим и условие нам не чуждо, пожалуйста задавайте любое! А вот еще один запрос на количество чего-либо: Сколько имеется заказов стоимостью более $250 ?
SELECT COUNT(AMOUNT) FROM ORDERS WHERE AMOUNT > 250 /
Получаем:
SQL> SELECT COUNT(AMOUNT)
2 FROM ORDERS
3 WHERE AMOUNT > 250
4 /
COUNT(AMOUNT)
-------------
7
Здесь то же самое, только условие немного другое. Вообще, если вдуматься здесь дословно звучит: "сколько стоимостей заказов?", по моему это глупо! Для этого существует функция COUNT(*), она просто подсчитывает строки, а не значения данных. Тогда было бы грамотнее записать что-то вроде:
SELECT COUNT(*) FROM ORDERS WHERE AMOUNT > 250 /
Получаем:
SQL> SELECT COUNT(*)
2 FROM ORDERS
3 WHERE AMOUNT > 250
4 /
COUNT(*)
-------------
7
Вот теперь, все как-то встало на свои места. Вообще COUNT(*), очень полезная функция и вы в этом еще не раз убедитесь, но не советую ею злоупотреблять, так как она относится к разряду ресурсоемких и там где возможно ее лучше избегать! Итак понять, что делает запрос с агрегатной функцией на первый взгляд не сложно. Но если список возвращаемых столбцов содержит несколько агрегатных функций, да еще и включающие в себя математические выражения. Такой запрос является достаточно сложным для понимания! Например, запрос вида: Найти среднюю стоимость заказов, общую стоимость заказов, среднюю стоимость заказов в процентах от лимитов кредита клиентов, а также среднюю стоимость заказов в процентах от плановых объемов продаж служащих.
SELECT AVG(AMOUNT), SUM(AMOUNT), (100 * AVG(AMOUNT/CREDIT_LIMIT)), (100 * AVG(AMOUNT/QUOTA)) FROM ORDERS, CUSTOMERS, SALESREPS WHERE CUST = CUST_NUM AND REP = EMPL_NUM /
Получаем:
SQL> SELECT AVG(AMOUNT), SUM(AMOUNT), (100 * AVG(AMOUNT/CREDIT_LIMIT)), (100 * AVG(AMOUNT/QUOTA)) 2 FROM ORDERS, CUSTOMERS, SALESREPS 3 WHERE CUST = CUST_NUM AND REP = EMPL_NUM 4 / AVG(AMOUNT) SUM(AMOUNT) (100*AVG(AMOUNT/CREDIT_LIMIT)) (100*AVG(AMOUNT/QUOTA)) ----------- ----------- ------------------------------ ----------------------- 170,0368333 5101,105 347,302701523109 50,8331082251082
Достаточно замудренный запрос в жизни Вы, как правило, будете делать чуть проще, но кому как нравится. Для того, чтобы с этим всем разобраться попробуйте разложить запрос на две части, сначала представьте как все будет работать без агрегатных функций, а затем агрегируйте и все станет на свои места. Примерно вот так:
SELECT AMOUNT, AMOUNT, AMOUNT/CREDIT_LIMIT, AMOUNT/QUOTA FROM ORDERS, CUSTOMERS, SALESREPS WHERE CUST = CUST_NUM AND REP = EMPL_NUM /
Вот теперь видно, что происходит, добавьте все агрегаты и все заработает снова! Но запомните, что одна агрегатная функция не может быть аргументом для другой. Это формируется правилом "нельзя вкладывать агрегатные функции"! Хотя если очень интересно, можете попробовать! :) Также в списке возвращаемых столбцов нельзя одновременно указывать агрегатные функции и простые имена столбцов. Поскольку такой запрос не имеет смысла! Например:
SELECT NAME, SUM(SALES) FROM SALESREPS
Так как первый столбец создает таблицу, а второй возвратит только одно значение, что вызовет ошибку! Но здесь, есть еще хитрости, которые мы рассмотрим позже. Пока можете закрепить то, что уже проработано.