изучаем SQL. Она позволяет решать многошаговые задачи одним выражением
 Скачать 1.6 Mb.
|
|
SELECT CAST('1456328' AS SIGNED INTEGER); + + | CAST('1456328' AS SIGNED INTEGER) | + + | 1456328 | + + 1 row in set (0.01 sec) При преобразовании строки в число функция cast() попытается преоб разовать всю строку слева направо. Если в строке встретиться любой нечисловой символ, преобразование будет остановлено без формирова ния ошибки. Рассмотрим следующий пример: mysql> SELECT CAST('999ABC111' AS UNSIGNED INTEGER); + + | CAST('999ABC111' AS UNSIGNED INTEGER) | + + | 999 | + + 1 row in set (0.00 sec) 152 Глава 7. Создание, преобразование и работа с данными В данном случае преобразуются первые три цифры в строке, все ос тальные символы строки отбрасываются. В результате получаем зна чение 999. Для преобразования строки в значение типа date, time или datetime по надобится придерживаться форматов по умолчанию для каждого типа, поскольку передать строку формата в функцию cast() невозможно. Ес ли формат строки даты не соответствует применяемому по умолчанию (т. е. YYYY MM DD HH:MI:SS для типов datetime), придется прибег нуть к другой функции, например к функции MySQL str_to_date(), описанной ранее в этой главе. Упражнения Эти упражнения позволяют проверить понимание читателем некото рых встроенных функций, упомянутых в данной главе. Ответы приве дены в приложении С. 7.1 Напишите запрос, возвращающий с 17 го по 25 й символы строки 'Please find the substring in this string' (Пожалуйста, найдите под строку в этой строке). 7.2 Напишите запрос, возвращающий абсолютную величину и знак ( 1, 0 или 1) числа –25,768 23. Также возвратите число, округленное до сотых. 7.3 Напишите запрос, возвращающий только значение месяца текущей даты. Группировка и агрегаты Обычно данные хранятся с самым низким уровнем детализации, кото рый может понадобиться какому либо пользователю базы данных. Если Чаку (Chuck) для бухгалтерского учета требуется посмотреть операции одного клиента, в БД должна быть таблица, хранящая операции отдель ного клиента. Однако это не означает, что все пользователи должны ра ботать с данными в том виде, в каком они хранятся в БД. Основное вни мание уделено группировке и агрегированию данных, которые обеспе чивают пользователям возможность работать с данными на более вы соком уровне детализации, чем тот, с которым они хранятся в БД. Принципы группировки Иногда необходимо выявить в данных некоторые тенденции, что по требует от сервера некоторой подготовки данных, прежде чем можно будет получить искомые результаты. Например, вы отвечаете за опе рации в банке и хотели бы выяснить, сколько счетов открывает каж дый операционист банка. Можно было бы создать простой запрос для просмотра необработанных данных: mysql> SELECT open_emp_id > FROM account; + + | open_emp_id | + + | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | 154 Глава 8. Группировка и агрегаты | 10 | | 10 | | 10 | | 10 | | 10 | | 10 | | 10 | | 13 | | 13 | | 13 | | 16 | | 16 | | 16 | | 16 | | 16 | | 16 | + + 24 rows in set (0.01 sec) В таблице account всего 24 строки, поэтому относительно просто уви деть, что счета открывались четырьмя сотрудниками и что сотрудник с ID 16 открыл шесть счетов. Но для банка с десятками сотрудников и тысячами открываемых счетов этот подход оказался бы очень уто мительным и подверженным ошибкам. Вместо этого можно с помощью блока group by (группировать по) попро сить сервер БД сгруппировать данные. Вот тот же запрос, но с примене нием блока group by для группировки данных о счетах по ID сотрудника: mysql> SELECT open_emp_id > FROM account > GROUP BY open_emp_id; + + | open_emp_id | + + | 1 | | 10 | | 13 | | 16 | + + 4 rows in set (0.00 sec) Результирующий набор содержит по одной строке для каждого от дельного значения столбца open_emp_id, что в результате дает четыре, а не 24 строки. Этот результирующий набор получился меньшим, по тому что каждый из четырех сотрудников открыл больше одного сче та. Чтобы увидеть, сколько счетов открыл каждый сотрудник, в блоке 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_accounts FROM 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 | | CD | 10000.00 | 1500.00 | 4875.000000 | 19500.00 | 4 | | 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(), увидим следующие результаты: Агрегатные функции 159 mysql> 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. Функция count(*) теперь возвращает значение 4, что является правильным, поскольку в таблице number_tbl четыре строки, тогда как функция count(val) по прежнему возвращает значение 3. Разница в том, что функция count(*) считает строки и по этому не подвержена влиянию значений null, содержащихся в строке. А вот функция count(val) считает значения в столбце val, пропуская все встречающиеся значения null. Формирование групп Мало кого заинтересуют необработанные данные; тем, кто занимается анализом, потребуются обработанные данные, приведенные к виду, наиболее соответствующему их нуждам. Среди обычных примеров ма нипуляций с данными можно назвать: • Формирование общих показателей для географического региона, например общий объем продаж по Европе. • Выявление экстремумов, например лучший продавец 2005 года. • Определение повторяемости, например число новых счетов, откры тых в каждом отделении. Чтобы ответить на запросы подобных типов, вам потребуется попросить сервер БД сгруппировать строки по одному или более столбцам или вы ражениям. Как уже было показано в нескольких примерах, механиз мом группировки данных в рамках запроса является блок group by. В этом разделе рассматриваются группировка данных по одному или более столбцам, группировка данных с помощью выражений и форми рование обобщений в рамках группы. Группировка по одному столбцу Формирование группы по одному столбцу – самый простой и наиболее распространенный тип группировки. Например, если требуется найти общие остатки (total balance) для всех типов счетов, нужно всего лишь провести группировку по столбцу account.product_cd: mysql> SELECT product_cd, SUM(avail_balance) prod_balance > FROM account > GROUP BY product_cd; + + + | product_cd | prod_balance | + + + | BUS | 9345.55 | | CD | 19500.00 | 162 Глава 8. Группировка и агрегаты | CHK | 73008.01 | | MM | 17045.14 | | SAV | 1855.76 | | SBL | 50000.00 | + + + 6 rows in set (0.00 sec) Этот запрос формирует шесть групп, по одной для каждого типа сче тов, и затем суммирует доступные остатки по всем строкам в каждой группе. |