Шаг 32 - Снова SELECT - Агрегатные функции далее...

Двигаемся дальше. С агрегированием осталось немного. Нахождение экстремумов, а именно функции 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

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


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