изучаем SQL. Она позволяет решать многошаговые задачи одним выражением
 Скачать 1.6 Mb.
|
|
Группировка по нескольким столбцам В некоторых случаях может понадобиться сформировать группы, ох ватывающие более одного столбца. Развивая предыдущий пример, представим, что требуется найти общие остатки не только по каждому типу счетов, но и по отделениям (например: каков общий остаток для всех текущих счетов, открытых в отделении Woburn?). Следующий пример демонстрирует, как это может быть реализовано: mysql> SELECT product_cd, open_branch_id, > SUM(avail_balance) tot_balance > FROM account > GROUP BY product_cd, open_branch_id; + + + + | product_cd | open_branch_id | tot_balance | + + + + | BUS | 2 | 9345.55 | | BUS | 4 | 0.00 | | CD | 1 | 11500.00 | | CD | 2 | 8000.00 | | CHK | 1 | 782.16 | | CHK | 2 | 3315.77 | | CHK | 3 | 1057.75 | | CHK | 4 | 67852.33 | | MM | 1 | 14832.64 | | MM | 3 | 2212.50 | | SAV | 1 | 767.77 | | SAV | 2 | 700.00 | | SAV | 4 | 387.99 | | SBL | 3 | 50000.00 | + + + + 14 rows in set (0.00 sec) Этот вариант запроса формирует 14 групп, по одной для каждого обна руженного в таблице account сочетания типа счетов и отделения. Стол бец open_branch_id добавлен в блок select, а также введен в блок group by, поскольку он извлекается из таблицы, а не формируется агрегатной функцией. Формирование групп 163 Группировка посредством выражений Кроме столбцов группировку данных можно выполнить на основании значений, сгенерированных выражениями. Рассмотрим запрос, кото рый группирует сотрудников по году начала их работы в банке: mysql> SELECT EXTRACT(YEAR FROM start_date) year, > COUNT(*) how_many > FROM employee > GROUP BY EXTRACT(YEAR FROM start_date); + + + | year | how_many | + + + | 2000 | 3 | | 2001 | 2 | | 2002 | 8 | | 2003 | 3 | | 2004 | 2 | + + + 5 rows in set (0.00 sec) Этот запрос для группировки строк таблицы employee использует до вольно простое выражение, которое с помощью функции extract() из всей даты извлекает только значение года. Формирование обобщений Ранее в этой главе в разделе «Группировка по нескольким столбцам» был показан пример формирования общих остатков счетов по каждо му типу счетов и отделению. Однако допустим, что кроме общих остат ков для каждого сочетания тип счетов/отделение требуется получить и общие остатки по каждому отдельному типу счетов. Можно было бы выполнить дополнительный запрос и объединить результаты, или за грузить результаты запроса в электронную таблицу, или создать сце нарий на Perl или Java программу, или применить какой либо другой механизм для получения данных и проведения дополнительных вы числений. Но все таки лучше всего использовать вариант with rollup (с обобщением), заставив выполнить всю эту работу сервер БД. Вот из мененный запрос, использующий with rollup в блоке group by: mysql> SELECT product_cd, open_branch_id, > SUM(avail_balance) tot_balance > FROM account > GROUP BY product_cd, open_branch_id WITH ROLLUP; + + + + | product_cd | open_branch_id | tot_balance | + + + + | BUS | 2 | 9345.55 | | BUS | 4 | 0.00 | | BUS | NULL | 9345.55 | | CD | 1 | 11500.00 | 164 Глава 8. Группировка и агрегаты | CD | 2 | 8000.00 | | CD | NULL | 19500.00 | | CHK | 1 | 782.16 | | CHK | 2 | 3315.77 | | CHK | 3 | 1057.75 | | CHK | 4 | 67852.33 | | CHK | NULL | 73008.01 | | MM | 1 | 14832.64 | | MM | 3 | 2212.50 | | MM | NULL | 17045.14 | | SAV | 1 | 767.77 | | SAV | 2 | 700.00 | | SAV | 4 | 387.99 | | SAV | NULL | 1855.76 | | SBL | 3 | 50000.00 | | SBL | NULL | 50000.00 | | NULL | NULL | 170754.46 | + + + + 21 rows in set (0.02 sec) Теперь имеется семь дополнительных результатов, по одному для каж дого из шести разных типов счетов, и один – общая сумма (для всех ти пов счетов). Для шести обобщений по типам счетов столбец open_ branch_id содержит значение null, поскольку обобщение осуществляет ся по всем отделениям. Например, взглянув на строку #3 результата, можно заметить, что всего по счетам BUS во всех отделениях внесено 9 345,55 долларов. Строка итоговой суммы в обоих столбцах, prod uct_cd и open_branch_id, содержит значение null. Последняя строка вы ходных данных показывает общую сумму 170 754,46 долларов для всех типов счетов и всех отделений. При работе с Oracle Database для выполнения обобщения приме няется немного отличающийся синтаксис. Блок group by из пре дыдущего запроса при использовании в Oracle выглядел бы так: GROUP BY ROLLUP(product_cd, open_branch_id) Преимущество этого синтаксиса в том, что он позволяет выпол нять обобщения для подмножества столбцов в блоке group by. Например, если группировка осуществляется по столбцам a, b и c, можно было бы указать, что сервер должен проводить обоб щения только для b и c: GROUP BY a, ROLLUP(b, c) Если кроме суммы по типам счетов требуется подсчитать сумму по каж дому отделению, можно использовать вариант with cube, который фор мирует строки суммы для всех возможных сочетаний группирующих столбцов. К сожалению, with cube недоступен в MySQL версии 4.1, но есть в SQL Server и Oracle Database. Вот пример использования with cube (я убрал приглашение mysql>, чтобы показать, что этот запрос пока нельзя осуществить в MySQL): Условия групповой фильтрации 165 SELECT product_cd, open_branch_id, SUM(avail_balance) tot_balance FROM account GROUP BY product_cd, open_branch_id WITH CUBE; + + + + | product_cd | open_branch_id | tot_balance | + + + + | NULL | NULL | 170754.46 | | NULL | 1 | 27882.57 | | NULL | 2 | 21361.32 | | NULL | 3 | 53270.25 | | NULL | 4 | 68240.32 | | BUS | 2 | 9345.55 | | BUS | 4 | 0.00 | | BUS | NULL | 9345.55 | | CD | 1 | 11500.00 | | CD | 2 | 8000.00 | | CD | NULL | 19500.00 | | CHK | 1 | 782.16 | | CHK | 2 | 3315.77 | | CHK | 3 | 1057.75 | | CHK | 4 | 67852.33 | | CHK | NULL | 73008.01 | | MM | 1 | 14832.64 | | MM | 3 | 2212.50 | | MM | NULL | 17045.14 | | SAV | 1 | 767.77 | | SAV | 2 | 700.00 | | SAV | 4 | 387.99 | | SAV | NULL | 1855.76 | | SBL | 3 | 50000.00 | | SBL | NULL | 50000.00 | + + + + 25 rows in set (0.02 sec) Применение with cube дает на четыре строки больше, чем версия запро са с with rollup, по одной для каждого из четырех ID отделений. Как и в случае с with rollup, значения null в столбце product_cd обозначают то, что производится суммирование по отделениям. При работе с Oracle Database для указания на операцию cube также применяется немного отличающийся синтаксис. Блок group by из предыдущего запроса для Oracle выглядел бы так: GROUP BY CUBE(product_cd, open_branch_id) Условия групповой фильтрации В главе 4 были представлены различные типы условий фильтрации и показано, как их можно использовать в блоке where. При группировке данных тоже можно применять условия фильтрации к данным после 166 Глава 8. Группировка и агрегаты формирования групп. Этот тип условий фильтрации должен распола гаться в блоке having. Рассмотрим следующий пример: mysql> SELECT product_cd, SUM(avail_balance) prod_balance > FROM account > WHERE status = 'ACTIVE' > GROUP BY product_cd > HAVING SUM(avail_balance) >= 10000; + + + | product_cd | prod_balance | + + + | CD | 19500.00 | | CHK | 73008.01 | | MM | 17045.14 | | SBL | 50000.00 | + + + 4 rows in set (0.00 sec) В этом запросе два условия фильтрации: одно в блоке where (отсеивают ся неактивные счета), а второе в блоке having (отсеиваются счета всех типов с общим доступным остатком меньше 10 000 долларов). Таким образом, один из фильтров воздействует на данные до группировки, а другой – после создания групп. Если по ошибке оба фильтра помеще ны в блок where, возникает следующая ошибка: mysql> SELECT product_cd, SUM(avail_balance) prod_balance > FROM account > WHERE status = 'ACTIVE' > AND SUM(avail_balance) > 10000 > GROUP BY product_cd; ERROR 1111 (HY000): Invalid use of group function Данный запрос дал сбой, потому что агрегатную функцию нельзя включать в блок where. Причина в том, что фильтры блока обрабатыва ются до выполнения группировки, поэтому серверу еще не доступны какие либо действия над группами. При введении фильтров в запрос, включающий блок group by, не обходимо тщательно продумать, к чему применяется фильтр – к необработанным данным (тогда он относится к блоку where) или к сгруппированным данным (в этом случае он относится к блоку having). Однако в блок having можно включить агрегатные функции, не пере численные в блоке select, как показано ниже: mysql> SELECT product_cd, SUM(avail_balance) prod_balance > FROM account > WHERE status = 'ACTIVE' > GROUP BY product_cd > HAVING MIN(avail_balance) >= 1000 > AND MAX(avail_balance) <= 10000; Упражнения 167 + + + | product_cd | prod_balance | + + + | MM | 17045.14 | + + + 1 row in set (0.01 sec) Этот запрос формирует общие остатки для каждого типа счетов, но ус ловие фильтрации блока having исключает все группы, минимальный остаток которых меньше 1000 долларов или максимальный остаток которых больше 10 000 долларов. Упражнения Проработайте следующие упражнения, чтобы протестировать понима ние группировки и агрегатных функций SQL. Ответы приведены в при ложении С. 8.1 Создайте запрос для подсчета числа строк в таблице account. 8.2 Измените свой запрос из упражнения 8.1 для подсчета числа счетов, имеющихся у каждого клиента. Для каждого клиента выведите ID кли ента и количество счетов. 8.3 Измените запрос из упражнения 8.2 так, чтобы в результирующий на бор были включены только клиенты, имеющие не менее двух счетов. 8.4 (дополнительно) Найдите общий доступный остаток по типу счетов и отделению, где на каждый тип и отделение приходится более одного счета. Результа ты должны быть упорядочены по общему остатку (от наибольшего к наименьшему). Подзапросы Подзапросы – мощный инструмент, который можно использовать во всех четырех SQL выражениях для работы с данными. В этой главе по дробно рассматриваются многие варианты применения подзапроса. Что такое подзапрос? Подзапрос (subquery) – это запрос, содержащийся в другом SQL выра жении (далее я называю его содержащим выражением (containing sta tement )). Подзапрос всегда заключен в круглые скобки и обычно вы полняется до содержащего выражения. Как и любой другой запрос, подзапрос возвращает таблицу, которая может состоять из: • Одной строки с одним столбцом • Нескольких строк с одним столбцом • Нескольких строк и столбцов Тип возвращаемой подзапросом таблицы определяет, как можно ее ис пользовать и какие операторы можно применять в содержащем выра жении для взаимодействия с этой таблицей. По завершении выполне ния содержащего выражения таблицы, возвращенные любым подза просом, выгружаются из памяти. Таким образом, подзапрос действует как временная таблица, областью видимости которой является выра жение (т. е. после завершения выполнения выражения сервер высво бождает всю память, отведенную под результаты подзапроса). Предыдущие главы уже содержали несколько примеров подзапросов. Для начала приведем простой пример: mysql> SELECT account_id, product_cd, cust_id, avail_balance > FROM account > WHERE account_id = (SELECT MAX(account_id) FROM account); + + + + + Типы подзапросов 169 | account_id | product_cd | cust_id | avail_balance | + + + + + | 24 | SBL | 13 | 50000.00 | + + + + + 1 row in set (0.65 sec) В этом примере подзапрос возвращает максимальное значение столбца account_id таблицы account. Затем содержащее выражение возвращает данные по этому счету. Если возникают какие нибудь вопросы по по воду того, что делает подзапрос, можно выполнить его отдельно (без скобок) и посмотреть, что он возвращает. Вот подзапрос из предыду щего примера: mysql> SELECT MAX(account_id) FROM account; + + | MAX(account_id) | + + | 24 | + + 1 row in set (0.00 sec) Итак, подзапрос возвращает одну строку и один столбец. Это позволя ет использовать его как одно из выражений в условии равенства (если бы подзапрос возвращал две или более строк, он мог бы сравниваться с чем то, но не мог бы быть равным чему то; более подробно об этом позже). В этом случае можно взять значение, возвращаемое подзапро сом, и подставить его в правую часть условия фильтрации в основном запросе: mysql> SELECT account_id, product_cd, cust_id, avail_balance > FROM account a > WHERE account_id = 24; + + + + + | account_id | product_cd | cust_id | avail_balance | + + + + + | 24 | SBL | 13 | 50000.00 | + + + + + 1 row in set (0.02 sec) Здесь удобно использовать подзапрос, потому что он позволяет извле кать информацию о счете с наибольшим порядковым номером одним запросом. В противном случае пришлось бы с помощью одного запроса получать максимальный account_id и затем писать второй запрос для выбора необходимых данных из таблицы account. Как вы увидите, под запросы полезны и во многих других ситуациях и могут стать одним из самых мощных инструментов в вашем наборе SQL инструментов. Типы подзапросов Наряду с различиями, отмеченными ранее относительно типа возвра щаемой подзапросом таблицы (одна строка/один столбец, одна стро 170 Глава 9. Подзапросы ка/несколько столбцов или несколько столбцов), есть и другой показа тель, по которому можно дифференцировать подзапросы. Некоторые подзапросы полностью самодостаточны (называются несвязанными подзапросами (noncorrelated subqueries)), тогда как другие ссылаются на столбцы содержащего выражения (называются связанными подза просами (correlated subqueries)). В нескольких следующих разделах рассмотрены эти два типа подзапросов и приведены разные операто ры, позволяющие взаимодействовать с ними. Несвязанные подзапросы Приведенный ранее в этой главе пример является несвязанным подза просом. Он может выполняться самостоятельно и не использует ниче го из содержащего выражения. Большинство подзапросов являются несвязанными. Только выражения update или delete часто используют связанные подзапросы (более подробно об этом позже). Упомянутый пример не только является несвязанным, но и возвращает таблицу, со стоящую всего из одной строки и одного столбца. Такой тип подзапро са называется скалярным подзапросом (scalar subquery), и его можно помещать в любую часть условия, использующего обычные операторы (=, <>, <, >, <=, >=). Следующие примеры показывают применение ска лярного подзапроса в условии неравенства: mysql> SELECT account_id, product_cd, cust_id, avail_balance > FROM account > WHERE open_emp_id <> (SELECT e.emp_id > FROM employee e INNER JOIN branch b > ON e.assigned_branch_id = b.branch_id > WHERE e.title = 'Head Teller' AND b.city = 'Woburn'); + + + + + | account_id | product_cd | cust_id | avail_balance | + + + + + | 6 | CHK | 3 | 1057.75 | | 7 | MM | 3 | 2212.50 | | 8 | CHK | 4 | 534.12 | | 9 | SAV | 4 | 767.77 | | 10 | MM | 4 | 5487.09 | | 11 | CHK | 5 | 2237.97 | | 12 | CHK | 6 | 122.37 | | 13 | CD | 6 | 10000.00 | | 15 | CHK | 8 | 3487.19 | | 16 | SAV | 8 | 387.99 | | 17 | CHK | 9 | 125.67 | | 18 | MM | 9 | 9345.55 | | 19 | CD | 9 | 1500.00 | | 20 | CHK | 10 | 23575.12 | | 21 | BUS | 10 | 0.00 | | 23 | CHK | 12 | 38552.05 | | 24 | SBL | 13 | 50000.00 | Несвязанные подзапросы 171 + + + + + 17 rows in set (0.00 sec) Этот запрос возвращает данные по всем счетам, которые были откры ты операционистом отделения Woburn, который не является старшим (подзапрос написан в предположении, что в отделении только один старший операционист). Подзапрос в этом примере немного сложнее, чем в предыдущем, – он соединяет две таблицы и включает два усло вия фильтрации. Подзапросы могут быть простыми или сложными на столько, насколько требуется. Они могут использовать любые из всех доступных блоков запроса (select, from, where, group by, having, order by). Если при использовании в условии равенства подзапрос возвращает более одной строки, будет сформирована ошибка. Например, если пре дыдущий запрос изменить так, чтобы по подзапросу возвращались все операционисты отделения Woburn, а не только старший, будет полу чена следующая ошибка: mysql> SELECT account_id, product_cd, cust_id, avail_balance > FROM account > WHERE open_emp_id <> (SELECT e.emp_id > FROM employee e INNER JOIN branch b > ON e.assigned_branch_id = b.branch_id > WHERE e.title = 'Teller' AND b.city = 'Woburn'); ERROR 1242 (21000): Subquery returns more than 1 row Если выполнить только подзапрос, результаты будут такими: mysql> SELECT e.emp_id > FROM employee e INNER JOIN branch b > ON e.assigned_branch_id = b.branch_id > WHERE e.title = 'Teller' AND b.city = 'Woburn'; + + | emp_id | + + | 11 | | 12 | + + 2 rows in set (0.02 sec) Причина сбоя основного запроса в том, что выражение (open_emp_id) не может быть приравнено набору выражений (emp_id 11 и 12). Другими словами, единичный элемент не может приравниваться множеству. В следующем разделе вы увидите, как решить эту проблему с помо щью другого оператора. |