alan_beaulieu-learning_sql-ru (865932), страница 29

Просмтор этого файла доступен только зарегистрированным пользователям. Но у нас супер быстрая регистрация: достаточно только электронной почты!

Текст из файла (страница 29)

Функция 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)Этот запрос формирует шесть групп, по одной для каждого типа счетов, и затем суммирует доступные остатки по всем строкам в каждойгруппе.Группировка по нескольким столбцамВ некоторых случаях может понадобиться сформировать группы, охватывающие более одного столбца. Развивая предыдущий пример,представим, что требуется найти общие остатки не только по каждомутипу счетов, но и по отделениям (например: каков общий остаток длявсех текущих счетов, открытых в отделении 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 долларов. Строка итоговой суммы в обоих столбцах, product_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. Вот пример использования withcube (я убрал приглашение mysql>, чтобы показать, что этот запрос поканельзя осуществить в MySQL):Условия групповой фильтрации165SELECT product_cd, open_branch_id,SUM(avail_balance) tot_balanceFROM accountGROUP 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_balanceFROM accountWHERE status = 'ACTIVE'GROUP BY product_cdHAVING MIN(avail_balance) >= 1000AND 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 statement)).

Подзапрос всегда заключен в круглые скобки и обычно выполняется до содержащего выражения. Как и любой другой запрос,подзапрос возвращает таблицу, которая может состоять из:• Одной строки с одним столбцом• Нескольких строк с одним столбцом• Нескольких строк и столбцовТип возвращаемой подзапросом таблицы определяет, как можно ее использовать и какие операторы можно применять в содержащем выражении для взаимодействия с этой таблицей. По завершении выполнения содержащего выражения таблицы, возвращенные любым подзапросом, выгружаются из памяти. Таким образом, подзапрос действуеткак временная таблица, областью видимости которой является выражение (т.

Характеристики

Список файлов ответов (шпаргалок)