Агрегирование данных

Агрегатные функции позволяют определять свойства для заданных групп строк. С их помощью, например, можно выяснить, сколько всего строк присутствует в таблице или сколько строк в таблице имеют одно и то же свойство (скажем, одинаковое имя или дату рождения). Кроме того, агрегатные функции применяются для определения средних значений (к примеру, средней температуры в ноябре), а также для поиска максимального или минимального значения строк, соответствующих некоторому условию (допустим, определение самого холодного дня в августе).

Рассмотрим выражения GROUP BY и HAVING — команды SQL, чаще всего используемые для агрегации. Но вначале обсудим выражение DISTINCT, которое применяется для отчета об уникальных результатах для выходных данных запроса. Если в инструкции не указано ни выражение DISTINCT, ни GROUP BY, то возвращаемые данные все еще могут быть обработаны с помощью агрегатных функций, описываемых в этом разделе.

Для начала обсудим выражение DISTINCT На самом деле это не агрегатная функция, а скорее фильтр последующей обработки, позволяющий удалять дубликаты. Рассматривается это выражение, так как отбирает, подобно агрегатным функциям, результаты из выходных данных, а не обрабатывает отдельные строки.

Рассмотрим следующий запрос (рис. 7.8):

mysql> SELECT DISTINCT gruppirovka_name FROM

-> gruppirovka INNER JOIN prestupnik USING (gruppi rovka_id);

i gruppirovka_name !

+

i SoIncevskaya

i Potapovskaya

! Aleksandrovskaya !

! Matveevskaya

? Solopovskaya

i Severnaya

i Danilovskaya

+

7 rows in set (0.09 sec)

Рис. 7.8. Запрос путем объединения

Запрос выполняет поиск преступных группировок и входящих в них преступников путем объединения таблиц gruppi-rovka и prestupnik посредством выражения INNER JOIN, после чего возвращает значение для каждой преступной группировки. Как показал запрос, в базе данных имеется семь преступных группировок с преступниками. Если удалить выражение DISTINCT, получим по одной строке для каждого преступника (рис. 7.9):

mysql> SELECT gruppirovka_name FROM

  • -> gruppirovka INNER JOIN prestupnik USING
  • (gruppirovka_id); __

! gruppirouka_nane

! Solnceuskaya

I Solnceuskaya

! Solnceuskaya

! Solnceuskaya

! Potapouskaya

! Potapouskaya

! Aleksandrouskaya

? Aleksandrouskaya

! Aleksandrouskaya

I Matueeuskaya

! Matueeuskaya

! Solopouskaya

! Solopouskaya

! Seuernaya

! Seuernaya ! Danilouskaya

! Danilouskaya

BL? rows in set <0.00 sec)

Рис. 7.9. Краткая сводка

Таким образом, выражение DISTINCT позволяет получить краткую сводку.

Выражение DISTINCT применяется к выходным данным запроса и удаляет строки с идентичными значениями в столбцах, выбранных для выходных данных. Если переделать предыдущий пример так, чтобы получать оба значения: gruppirovka_name и prestupnik_name (не изменяя выражение JOIN и все еще применяя DISTINCT), то в выходных данных получим все 17 строк (рис.7.10):

mysql> SELECT DISTINCT gruppirovka_name, prestupnik_name

-> FROM gruppirovka INNER JOIN prestupnik

USING (gruppirovka_id);

gruppirovka_name ! prestupnik_name

Solncevskaya

! Andreev Sergey Ruslanovich

! Kravchenko Petr Ivanovich

I Ivashkin Ivan Kuzmich

! Petrikin Uasiliy Sergeevich

! Anisimov Uitaliy Abramovich

! Govorov Ivan Ivanovich

Solncevskaya

Solncevskaya

Solncevskaya

Potapovs kaya

Potapovs kaya

Aleksandrovskaya ! Getrov Ivan Gennadievich

Aleksandrovskaya ! Kabenov Gamshut Petrovich

! Makeev Gennadi*/ Petrovich

! Petrov Ivan Sidorovich

! Belov Aleksandr Petrovich

i Azizov Baden Pigorevich

! Kopica Ivan Petrovich

? Balashov Semen Pavlovich

! Petlyakov Maksim Ivanovich

! Pomoev Artur Uladimirovich

! Sidorov Gennadiy Ivanovich

Aleksandrovskaya

Matveevskaya

Matveevskaya

Solopovskaya

Solopovskaya

Severnaya

Severnaya

Danilovskaya

Danilovskaya

?.? rows in set <0.02 sec)

Рис. 7.10. Выходные данные c DISTINCT

Поскольку ни одна из этих строк не является идентичной, дубликаты не будут удалены выражением DISTINCT. Вы можете проверить это, пропустив в инструкции выражение DISTINCT — получится тот же результат.

Для удаления дубликатов MySQL требуется отсортировать выходные данные. Если индексы выстраивают выходные данные в порядке, требуемом для сортировки, или же данные сами расположены в удобном порядке, то процесс будет лишь слегка затормаживаться. Однако в больших таблицах без возможности получения доступа к данным в правильном порядке, сортировка может выполняться очень медленно.

Выражение GROUP BY сортирует данные в группы для агрегации. Оно подобно выражению ORDER BY, но выполняется гораздо раньше, до обработки запроса. Выражение GROUP BY используется для организации данных перед выполнением таких выражений, как WHERE, ORDER BY и других применяемых функций. В отличие от них выражение ORDER BY используется последним после выполнения запроса для реорганизации выходных данных запроса.

Рассмотрим пример. Предположим, требуется выяснить, сколько преступников состоит в каждой преступной группировке. С помощью технологий, описанных выше, можно выполнить объединение INNER JOIN таблиц gruppirovka и prestupnik и использовать выражение ORDER BY gruppirovka_name для организации преступных группировок в порядке, удобном для подведения итогов. Стоит отметить, что при объединении таблиц, каждому преступнику ставится в соответствие преступная группировка, в которую он входит, что приводит к дублированию названий преступных группировок. Количество повторяющихся названий преступных группировок равно количеству преступников в них входящих (рис. 7.11). Применим такой запрос:

mysql> SELECT gruppirovka_name

  • -> FROM gruppirovka INNER JOIN prestupnik
  • -> USING (gruppirovka_id)
  • -> ORDER BY gruppirovka_name;

gruppirouka_nane i

-------------------+

Aleksandrouskaya ! Aleksandrovskaya ! Aleksandrovskaya ! Danilovskaya ! Danilovskaya ! Matueeuskaya ! Matueeuskaya ! Potapouskaya ! Potapouskaya ! Seuernaya !

Seuernaya !

Solnceuskaya ! Solnceuskaya ! Solnceuskaya ! Solnceuskaya ! Solopouskaya ! Solopouskaya !

  • -------------------+
  • 7 rows in set <0.00 sec)

Рис. 7.11. Подсчет количества преступников по группировкам

С помощью этого списка можно подсчитать, сколько преступников состоит в той или иной преступной группировке.

Выражение GROUP BY может автоматизировать этот процесс путем группирования преступников по группировкам. Затем применим функцию COUNTQ для подсчета количества преступников в каждой группе (рис. 7.12):

mysql> SELECT gruppirovka_name?

  • -> COUNT(gruppirovka_name) FROM
  • -> gruppirovka INNER 30IN prestupnik
  • -> USING (gruppirovka_id)
  • -> GROUP BY gruppirovka_name;_______

! gruppirovka_name ! COUNT !

! Aleksandrovskaya ! 3

! Danilovskaya ! 2

! Matveevskaya ! 2

! Potapovskaya ! 2

! Severnaya ! 2

! Solncevskaya ! 4

! Solopovskaya ! 2

rows in set <О.ШШ sec)

Рис.7. 12. Сортировка количества преступников по группировкам

Выходные данные gruppirovka_name,

COUNT (gruppirovka _пате) представляют именно то, что нужно. Обратите также внимание, что использовали GROUP BY gruppi-rovkajname для предварительной сортировки перед агрегацией вместо выражения ORDER BY gruppirovka_name для отображения.

Проанализируем этот запрос. Начнем с выражения GROUP BY. Оно указывает, каким образом объединять строки в группы. В данном примере MySQL было указано, что группировку строк следует выполнять по значению gruppirovka_name. В результате строки для преступной группировки с одним и тем же именем формируют кластер, то есть каждое уникальное имя становится группой. Сгруппированные строки интерпретируются в остальной части запроса как одна строка. Таким образом, если, к примеру, написать SELECT gruppirovka_name, то получим лишь по одной строке в каждой группе. То же самое делает выражение DISTINCT, которое выполняет группирование по имени столбца, а затем выбирает этот столбец для отображения. Функция COUNTC) указывает свойства группы, точнее, она указывает количество строк, которые формируют каждую группу. Можно подсчитать все столбцы в группе и получите тот же самый ответ. Таким образом, функции COUNT (gruppirovka_пате), COUNT(*) и COUNT (gruppirovka_id) выдают идентичный результат. Для столбца COUNT() можно использовать псевдоним.

Рассмотрим еще один пример. Предположим, что необходимо выяснить, сколько преступлений совершил каждый преступник и в какую преступную группировку он входит (рис. 7.13):

mysql> SELECT gruppirovka_name_>

  • -> prestupnik_name, COUNT(*) FROM
  • -> gruppirovka INNER JOIN prestupnik
  • -> USING (gruppirovka_id)
  • -> INNER JOIN prestuplenie
  • -> USING (gruppirovka_id, prestupnik_id)
  • -> GROUP BY gruppirovka.gruppirovka_id,
  • -> prestupnik.prestupnik_id;___________________

-------------------ч

gruppirovka_name 1

  • 1------------------------------4
  • 1 prestupnik_name 1
  • 1-----------+
  • 1 COUNT*» !

Solnceuskaya 1

1 Andreeu Sergey Ruslanovich 1

i 2 :

Solnceuskaya 1

1 Kravchenko Petr Ivanovich 1

i i :

Solnceuskaya 1

1 Ivashkin Ivan Kuzmich 1

i i :

Solnceuskaya 1

1 Petrikin Uasiliy Sergeevich 1

i 4 :

Potapouskaya 1

1 Anisimov Uitaliy Abramovich 1

1 1 !

Potapouskaya 1

I Govorov Ivan Ivanovich 1

i 2 :

Aleksandrovskaya 1

1 Getrov Ivan Gennadievich 1

i 2 :

Aleksandrovskaya 1

1 Kabenov Gamshut Petrovich 1

i i :

Aleksandrovskaya 1

1 Makeev Gennadiy Petrovich 1

i i :

Matveevskaya 1

1 Petrov Ivan Sidorovich 1

i з :

Matveevskaya 1

1 Belov Aleksandr Petrovich 1

i 2 :

Solopovskaya 1

I Azizov Baden Pigorevich 1

i i :

Solopovskaya 1

I Kopica Ivan Petrovich 1

i 2 :

Severnaya 1

I Balashov Semen Pavlovich 1

i 2 :

Severnaya 1

I Petlyakov Maksim Ivanovich 1

i з :

Danilovskaya 1

1 Pomoeu Artur Uladimirovich 1

i 2 :

Danilovskaya 1

I Sidorov Gennadiy Ivanovich 1

1 1 !

.7 rows in set <0.05

! sec)

Рис. 7.13. Сортировка по каждому преступнику

Прежде всего обращаем внимание на ключевую фразу INNER JOIN между gruppirovka, prestupnik и prestuplenie, использующую столбцы первичного ключа (идентификатора). Забудем на некоторое время об агрегации — выходные данные этого запроса представляют по одной строке на каждое преступление.

Выражение GROUP BY помещает строки в кластеры. В этом запросе нам нужно сгруппировать преступления каждого преступника по преступным группировкам. Таким образом, выражение GROUP BY использует для этого столбцы gruppirovka_id и prestupnik_id. Можно использовать gruppirovka_id из трех таблиц.

Значения gruppirovka. gruppirovka_id, prestupnik. gruppi-rovka_id и prestuplenie.gruppirovka_id в данном случае равнозначны. Выбор не играет роли, поскольку выражение INNER JOIN в любом случае сделает их одинаковыми. То же самое относится к prestupnik_id.

Как и в предыдущем примере запроса, используем функцию COUNT(), чтобы знать, сколько строк присутствует в каждой группе. Например, функция COUNT(*) указывает, что преступник «Andreev Sergey Ruslanovich» входящий в Солнцевскую (Solncevskaya) преступную группировку совершил 2 преступления. Опять же, не играет роли, какой столбец или столбцы подсчитывается в запросе. Например, функция COUNT(*) выдает те же результаты, как и функция COUNT( gruppirovka. gruppirovka_id) или COUNT (gruppirovka_name).

В предыдущих главах рассматривались примеры использования функцию COUNTQ для указания количества строк в группе. Приведем еще несколько функций, которые используются для определения свойств агрегированных строк.

AVG() — возвращает среднее значение всех строк в группе для указанного столбца.

МАХ() — возвращает максимальное значение для строк в группе. С помощью данной функции можно, например, определить самый жаркий день месяца после группировки строк по месяцам.

MIN() — возвращает минимальное значение для строк в группе. Используя эту функцию, несложно найти самых юных студентов в классе, когда строки сгруппированы по классам.

STD() или STDDEV() — возвращает стандартное отклонение значений для строк в группе. Можно применять данную функцию, к примеру, когда нужно определить разброс между контрольными отсчетами, если строки сгруппированы, например, по курсам университета.

SUM() — возвращает сумму значений для строк в группе.

Вместе с выражением GROUP BY можно использовать и другие редко используемые функции.

Выражение GROUP BY позволяет сортировать и создавать кластеры данных и определять количество средних, минимальных и максимальных значений. Теперь опишем выражение HAVING, используемое для добавления дополнительного контроля агрегации строк в операции GROUP BY.

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

mysql> SELECT prestupnik_name, COUNT(*) FROM

  • -> prestupnik INNER JOIN prestuplenie
  • -> USING (gruppirovka_id, prestupnik_id)
  • -> GROUP BY prestupnik_name
  • -> HAVING COUNT(*) > 1;_______________________

<------------------------------

! pre s tupn ik_n aroe

-+----------+

! COUNT<*> !

! Andreev Sergey Ruslanovich

: 2 :

! Balashov Semen Pavlovich

: 2 :

! Belov Aleksandr Petrovich

: 2 :

! Getrov Ivan Gennadievich

i 2 i

! Govorov Ivan Ivanovich

! 2 !

! Kopica Ivan Petrovich

: 2 :

! Petlyakov Maksim Ivanovich

: з :

! Petrikin Uasiliy Sergeevich

: 4 :

! Petrov Ivan Sidorovich

: з :

! Pomoev Artur Uladimirovich

! 2 !

LO rows in set <0.00 sec)

Рис.7.14. Определение наибольшего количества преступлений

Выражение НА VING должно содержать функцию или столбец из инструкции SELECT. В данном примере используем HAVING COUNT(*)>1, где функция COUNT(*) является частью инструкции SELECT. Обычно выражение HAVING использует такую агрегатную функцию, как COUNT(), SUM() или МАХ(). Если вы попытаетесь написать выражение HAVING с использованием столбца или функции, которых нет в инструкции SELECT, то, скорее всего, придется заменить его выражением WHERE. Выражение HAVING предназначено лишь для формирования каждой группы или кластера, а не для выбора строк в выходных данных. Далее приведем пример, демонстрирующий, в каком случае не следует использовать выражение НА VING.

Допустим, что необходимо выяснить, сколько преступников входит в Солнцевскую (Solncevskaya) преступную группировку. Следующий запрос не рекомендуется использовать — это как раз и есть тот случай, когда не следует использовать выражение HAVING (рис. 7.15);

mysql> SELECT gruppirovka_name,

  • -> COUNT(gruppirovka_name) FROM
  • -> gruppirovka INNER JOIN prestupnik
  • -> USING (gruppirovka_id)
  • -> GROUP BY gruppirovka_name
  • -> HAVING gruppirovka_name=',SolncevskayaJ’;_____

gruppirouka_name ! COUNT i ------------------+-------------------------+ Solnceuskaya ! 4 i ------------------+-------------------------+ row in set <0.00 sec>

Рис. 7.15. Использование выражения HAVING

Запрос выдает ответ, хотя и правильный, но выполняется очень медленно. Так не следует писать запросы, поскольку выражение HAVING используется для формирования групп из строк, а для фильтрации отображаемых результатов применяется некорректно. В этом запросе лучше использовать выражение WHERE (рис. 7.16);

mysql> SELECT gruppirovka_name,

  • -> COUNT(gruppirovka_name) FROM
  • -> gruppirovka INNER 3OIN prestupnik
  • -> USING (gruppirovka_id)
  • -> WHERE gruppirovka_name=-’Solncevskaya-’ -> GROUP BY gruppirovka_name;

! gruppirouka_name ! COUNT !

! Solnceuskaya

N. row in set <0.00 sec)

Рис. 7.16. Использование выражения WHERE

Это корректный запрос. Он формирует группы и на основе выражения WHERE определяет, какие группы следует отобразить.

Контрольные вопросы

  • 1. Назначение псевдонимов.
  • 2. К каким структурам применяются псевдонимы?
  • 3. Как дать псевдоним столбцу?
  • 4. Назначение группировки строк.
  • 5. С помощью какого выражения языка SQL производится группировка строк?
  • 6. Назначение функции COUNT.
  • 7. С помощью какого выражения языка SQL производится сортировка сгруппированных данных?
  • 8. Какова очередность выполнения операций WHERE, GROUP BY и HAVING?
 
Посмотреть оригинал
< Пред   СОДЕРЖАНИЕ ОРИГИНАЛ   След >