alan_beaulieu-learning_sql-ru (865932), страница 28
Текст из файла (страница 28)
Этот результирующий набор получился меньшим, по тому что каждый из четырех сотрудников открыл больше одного сче та. Чтобы увидеть, сколько счетов открыл каждый сотрудник, в блокеselect можно подсчитать количество строк в каждой группе с помо щью агрегатной функции (aggregate function):mysql> SELECT open_emp_id, COUNT(*) how_many> FROM accountПринципы группировки155> GROUP BY open_emp_id;+++| open_emp_id | how_many |+++|1 |8 ||10 |7 ||13 |3 ||16 |6 |+++4 rows in set (0.00 sec)Агрегатная функция count() подсчитывает количество строк в каждойгруппе, а звездочка предписывает серверу сосчитать все строки в груп пе.
Сочетание блока group by и функции обобщения count() позволяетформировать именно те данные, которые требуются для ответа на при кладной вопрос, без необходимости просматривать необработанныеданные.При группировке может понадобиться отфильтровать из результирую щего набора ненужные данные, опираясь на информацию групп дан ных, а не необработанных данных. Блок group by выполняется послевычисления блока where, поэтому условия фильтрации нельзя добав лять в блок where. Вот, например, попытка отфильтровать всех сотруд ников, открывших меньше пяти счетов:mysql> SELECT open_emp_id, COUNT(*) how_many> FROM account> WHERE COUNT(*) > 4> GROUP BY open_emp_id, product_cd;ERROR 1111 (HY000): Invalid use of group functionАгрегатную функцию count(*) нельзя использовать в блоке where, по тому что на момент вычисления блока where группы еще не сформиро ваны.
Вместо этого можно поместить условия фильтрации группыв блок having. Вот пример того же запроса с блоком having:mysql> SELECT open_emp_id, COUNT(*) how_many> FROM account> GROUP BY open_emp_id> HAVING COUNT(*) > 4;+++| open_emp_id | how_many |+++|1 |8 ||10 |7 ||16 |6 |+++3 rows in set (0.00 sec)Группы, содержащие меньше пяти элементов, были отфильтрованыс помощью блока having, и теперь результирующий набор включаеттолько сотрудников, открывших пять или более счетов.156Глава 8. Группировка и агрегатыАгрегатные функцииАгрегатные функции осуществляют определенную операцию над все ми строками группы.
Хотя у всех серверов БД есть собственные набо ры специализированных агрегатных функций, большинством из нихреализованы следующие общие агрегатные функции:Max()Возвращает максимальное значение из набора.Min()Возвращает минимальное значение из набора.Avg()Возвращает среднее значение набора.Sum()Возвращает сумму значений из набора.Count()Возвращает количество значений в наборе.Вот запрос, использующий все обычные агрегатные функции для ана лиза доступных остатков (available balance) всех текущих счетов:mysql> SELECT MAX(avail_balance) max_balance,> MIN(avail_balance) min_balance,> AVG(avail_balance) avg_balance,> SUM(avail_balance) tot_balance,> COUNT(*) num_accounts> FROM account> WHERE product_cd = 'CHK';++++++| max_balance | min_balance | avg_balance | tot_balance | num_accounts |++++++| 385527.05 |122.37 | 7300.800985 |73008.01 |10 |++++++1 row in set (0.09 sec)Результаты этого запроса сообщают о том, что из десяти текущих сче тов таблицы account максимальный остаток составляет 38 552,05 дол ларов, минимальный остаток – 122,37 долларов, средний остаток –7 300,80 долларов, а общий остаток (баланс) по всем десяти счетам –73 008,01 долларов.
Надеюсь, теперь роль данных агрегатных функ ций вам ясна; возможности применения этих функций подробно рас смотрены в следующих разделах.Сравнение неявных и явных группВ предыдущем примере все значения, возвращаемые по запросу, фор мируются агрегатной функцией, а сами агрегатные функции применя ются к группе строк, определенной условием фильтрации product_cd =Агрегатные функции157'CHK'. Поскольку блок group by отсутствует, имеется единственная неявная группа (все возвращенные запросом строки).Однако в большинстве случаев потребуется извлекать и другие столбцы,а не только сформированные агрегатными функциями. Что если, к при меру, заставить предыдущий запрос выполнить эти же пять агрегатныхфункций для каждого типа счетов, а не только для текущих счетов? Длятакого запроса пришлось бы извлекать столбец product_cd в дополнениек столбцам, сформированным пятью агрегатными функциями:SELECT product_cd,MAX(avail_balance) max_balance,MIN(avail_balance) min_balance,AVG(avail_balance) avg_balance,SUM(avail_balance) tot_balance,COUNT(*) num_accountsFROM account;Однако если попытаться выполнить этот запрос, будет получена сле дующая ошибка:ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT( ),...)with no GROUP columns is illegal if there is no GROUP BY clauseХотя для разработчика очевидно, что он хочет применить агрегатныефункции к множеству счетов каждого типа, выявленного в таблицеaccount, этот запрос дает сбой, потому что не был явно задан способ груп пировки данных.
Следовательно, необходимо добавить блок group byи определить в нем группу строк, к которой следует применять агре гатные функции:mysql> SELECT product_cd,> MAX(avail_balance) max_balance,> MIN(avail_balance) min_balance,> AVG(avail_balance) avg_balance,> SUM(avail_balance) tot_balance,> COUNT(*) num_accts> FROM account> GROUP BY product_cd;+++++++| product_cd | max_balance | min_balance | avg_balance | tot_balance | num_accts |+++++++| BUS|9345.55 |0.00 | 4672.774902 |9345.55 |2||10000.00 |1500.00 | 4875.000000 |19500.00 |4|| CD| CHK|38552.05 |122.37 | 7300.800985 |73008.01 |10 || MM|9345.55 |2212.50 | 5681.713216 |17045.14 |3|| SAV|767.77 |200.00 | 463.940002 |1855.76 |4|| SBL|50000.00 |50000.00 | 50000.000000 |50000.00 |1|+++++++6 rows in set (0.00 sec)158Глава 8.
Группировка и агрегатыЕсли есть блок group by, сервер знает, что сначала надо сгруппироватьстроки с одинаковым значением в столбце product_cd, а затем приме нить пять агрегатных функций к каждой из шести групп.Подсчет уникальных значенийПри использовании функции count() для определения числа членовв каждой группе существует выбор: или пересчитать все члены груп пы, или посчитать только уникальные (distinct) значения столбца извсех членов группы. Рассмотрим, например, следующие данные, кото рыми представлены сотрудники, ответственные за открытие каждогосчета:mysql> SELECT account_id, open_emp_id> FROM account> ORDER BY open_emp_id;+++| account_id | open_emp_id |+++|8 |1 ||9 |1 ||10 |1 ||12 |1 ||13 |1 ||17 |1 ||18 |1 ||19 |1 ||1 |10 ||2 |10 ||3 |10 ||4 |10 ||5 |10 ||14 |10 ||22 |10 ||6 |13 ||7 |13 ||24 |13 ||11 |16 ||15 |16 ||16 |16 ||20 |16 ||21 |16 ||23 |16 |+++24 rows in set (0.00 sec)Как видите, все множество счетов было открыто четырьмя разнымисотрудниками (с ID = 1, 10, 13 и 16).
Допустим, хочется подсчитатьчисло открывших счета сотрудников – не вручную, а с помощью за проса. Если к столбцу open_emp_id применить функцию count(), увидимследующие результаты:Агрегатные функции159mysql> SELECT COUNT(open_emp_id)> FROM account;++| COUNT(open_emp_id) |++|24 |++1 row in set (0.00 sec)В этом случае столбец open_emp_id задан как столбец, который долженбыть пересчитан. При этом полученный результат ничем не отличает ся от результата выполнения функции count(*). Если требуется под считать уникальные значения в группе, а не просто пересчитать числострок в ней, нужно указать ключевое слово distinct:mysql> SELECT COUNT(DISTINCT open_emp_id)> FROM account;++| COUNT(DISTINCT open_emp_id) |++|4 |++1 row in set (0.00 sec)Следовательно, если задано ключевое слово distinct, функция count()проверит значение столбца для каждого члена группы, а не простоподсчитает число значений в ней.Использование выраженийВ качестве аргументов агрегатных функций вы можете использовать нетолько столбцы, но и созданные вами выражения.
Например, требует ся найти максимальное значение отложенных вкладов по всем счетам,которое вычисляется путем вычитания доступного остатка из отложен ного остатка. Сделать это можно посредством следующего запроса:mysql> SELECT MAX(pending_balance avail_balance) max_uncleared> FROM account;++| max_uncleared |++|660.00 |++1 row in set (0.00 sec)В данном примере используется довольно простое выражение, но при меняемые в качестве аргументов агрегатных функций выражения мо гут быть настолько сложными, насколько это нужно, и возвращатьчисло, строку или дату. В главе 11 будет показано, как с помощью вы ражения case и агрегатных функций можно управлять попаданием илинепопаданием конкретной строки под действие агрегатной функции.160Глава 8.
Группировка и агрегатыОбработка значений NullПри агрегировании, да и при вычислении любого численного выраже ния, всегда следует учитывать влияние значения null на результат вы числения. Для иллюстрации создадим простую таблицу для хранениячисловых данных и заполним ее набором {1, 3, 5}:mysql> CREATE TABLE number_tbl> (val SMALLINT);Query OK, 0 rows affected (0.01 sec)mysql> INSERT INTO number_tbl VALUES (1);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO number_tbl VALUES (3);Query OK, 1 row affected (0.00 sec)mysql> INSERT INTO number_tbl VALUES (5);Query OK, 1 row affected (0.00 sec)Рассмотрим следующий запрос, применяющий пять агрегатных функ ций к этому набору чисел:mysql> SELECT COUNT(*) num_rows,> COUNT(val) num_vals,> SUM(val) total,> MAX(val) max_val,> AVG(val) avg_val> FROM number_tbl;++++++| num_rows | num_vals | total | max_val | avg_val |++++++|3 |3 |9 |5 |3 |++++++1 row in set (0.00 sec)Как и следовало ожидать, результаты таковы: и count(*), и count(val)возвращают значение 3, sum(val) – значение 9, max(val) – 5, а avg(val) – 3.Теперь добавим в таблицу number_tbl значение null и выполним запросеще раз:mysql> INSERT INTO number_tbl VALUES (NULL);Query OK, 1 row affected (0.01 sec)mysql> SELECT COUNT(*) num_rows,> COUNT(val) num_vals,> SUM(val) total,> MAX(val) max_val,> AVG(val) avg_val> FROM number_tbl;++++++| num_rows | num_vals | total | max_val | avg_val |++++++|4 |3 |9 |5 |3 |Формирование групп161++++++1 row in set (0.00 sec)Даже при добавлении в таблицу значения null функции sum(), max()и avg() возвращают те же значения; это означает, что они игнорируютвсе встречающиеся значения null.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
