Понимание предложения SQL GROUP BY и агрегатных функций
Обратите внимание: примеры в этой статье были созданы с помощью PostgreSQL, и другие разновидности SQL могут давать несколько иные результаты.
Введение
При первом знакомстве с предложением SQL GROUP BY оно кажется достаточно простым. Легко понять, что GROUP BY формирует группы строк на основе столбцов, которые имеют общее значение. Также легко понять, что эти группы можно использовать для уточнения наших запросов и получения более значимых результатов на основе этих групп. Однако, когда мы начнем использовать GROUP BY, мы можем обнаружить, что также очень легко создавать ошибки. При использовании GROUP BY существуют определенные требования, и, если они недостаточно поняты (или устранены), ошибки становятся вероятными. В этой статье я попытаюсь продемонстрировать, что это за требования, почему они необходимы и как можно с легкостью использовать GROUP BY для достижения впечатляющих результатов. Давайте начнем!
Что делает GROUP BY?
Проще говоря, предложение GROUP BY создает группы строк, в которых значения в указанных столбцах совпадают. Эти группы затем используются в окончательном выводе запроса.
В результатах строка может содержать значения из сгруппированных столбцов и несгруппированных столбцов, которые принадлежат одной группе. Важно отметить, что выходные данные могут содержать только одно значение для каждого столбца в группе. Значение сгруппированного столбца будет общим значением каждой строки в группе. Значение для несгруппированного столбца будет единственным значением, которое является результатом агрегирования всех значений в столбце внутри группы.
Если это уже начинает сбивать с толку, давайте посмотрим, как это работает в действии.
В оставшейся части этой статьи мы будем работать со следующим набором данных о ряде домашних животных, которые имеют разные черты и принадлежат к разным категориям. Прямо сейчас данные довольно беспорядочные, но мы увидим, как GROUP BY можно использовать для понимания наших данных разными способами.
Давайте начнем с очень простого примера и вернем список всех видов:
SELECT species FROM pets GROUP BY species; species ----------- chameleon dog cat parrot tortoise iguana owl (7 rows)
Мы знаем, что в нашем наборе данных 10 строк, а результаты нашего запроса вернули 7 строк. Это связано с тем, что в результате запроса было получено 7 групп, в которых значение в столбце видов одинаково. Мы можем увидеть это более четко с помощью следующей визуализации сгруппированных данных.
Обратите внимание, как значения в сгруппированном столбце сжимаются до одного значения на выходе, например Слово «собака» встречается дважды в столбце видов, но только один раз в выходных данных.
Давайте также посмотрим, что произойдет, если мы сгруппируем набор данных по классам:
SELECT class FROM pets GROUP BY class; class — — — — - mammal reptile bird
Все идет нормально. Попробуем вернуть имена всех питомцев, сгруппированных по классам:
SELECT class, name FROM pets GROUP BY class;
ERROR: column “pets.name” must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT class, name FROM pets GROUP BY class;
^
Здесь мы начинаем сталкиваться с проблемами. Наше сообщение об ошибке показывает, что столбец name необходимо включить в предложение GROUP BY или использовать в агрегатной функции. Причина этого в том, что каждый столбец в выходных данных должен содержать только одно значение для каждой группы. Поскольку существует несколько имен, мы получаем ошибку. Чтобы лучше понять это, давайте сравним ситуацию с примером из реальной жизни.
Простая аналогия
Допустим, учитель делит класс на группы учеников в зависимости от цвета их глаз.
Затем учитель говорит: «Хорошо, кареглазые дети, сколько вам лет?»
‘…’
Дети замирают. Некоторым детям 8, а некоторым 9. Они не знают, как ответить. Они не могут выбрать один возраст для всех детей в группе.
Однако один смелый и умный ученик отвечает: «Учитель, нам нужно больше информации. Вы имеете в виду средний возраст или все возрасты в списке? »
Вы, наверное, видите, к чему я клоню. SQL сталкивается с той же проблемой, что и студенты. Если данные не сгруппированы или не агрегированы, будет выдана ошибка, как и отважный и умный студент, просящий лучших инструкций. Когда в группе несколько значений, SQL не может выбрать одно значение для представления и должен вам точно скажут, каким образом значения должны быть объединены вместе, чтобы получить одно значение. Именно это и делают агрегатные функции.
Агрегатные функции
Агрегатная функция - это просто функция, которая принимает несколько значений, выполняет над ними некоторую операцию, а затем в результате выдает одно значение. Это именно то, что нужно, если мы хотим включить некоторую информацию из несгруппированного столбца в вывод набора данных, который был сгруппирован с помощью GROUP BY. Таким образом, везде, где есть группа строк, и некоторые строки имеют несколько разных значений в столбце, эти разные значения можно каким-либо образом объединить, чтобы сформировать одно значение для этой группы.
Существует множество различных агрегатных функций, которые можно изучить здесь, но несколько очень полезных агрегатных функций включают:
- count (column_name) - возвращает количество строк в группе.
- sum (имя_столбца) - суммирует числовые значения всех строк в указанном столбце в группе.
- string_agg (имя_столбца, разделитель) - объединит все строковые значения, разделенные разделителем всех строк в указанном столбце в группе.
Понимая агрегатные функции, давайте снова попробуем вернуть все имена домашних животных, сгруппированные по классам. Поскольку мы знаем, что должны объединить их как одно значение, давайте воспользуемся для этого функцией string_agg.
SELECT class, string_agg(name, ‘, ‘) FROM pets GROUP BY class;
class | string_agg
— — — — -+ — — — — — — — — — — — — — — — — -
mammal | coco, cinderella, django, donut
reptile | tim, charlie, ivan
bird | polly, olly, oswald
На этот раз мы добились успеха. Значения в столбце имени были объединены в одно значение (как показано выделенными блоками выше) и теперь могут быть включены в результаты. На приведенной выше диаграмме также показано, как сгруппированный столбец class также по существу агрегируется из множества идентичных значений в одно значение.
Давайте закрепим наше понимание еще одним быстрым примером. На этот раз мы хотим узнать общий вес каждого класса животных. На этот раз мы воспользуемся функцией sum.
SELECT class, sum(weight) FROM pets GROUP BY class;
class | sum
— — — — -+ — — -
mammal | 93
reptile | 258
bird | 12
Оговорка HAVING
Давайте погрузимся немного глубже и скажем, что мы хотим выяснить все виды животных, у которых есть более одного питомца. Для этого нам нужно отфильтровать все группы видов, у которых есть одно или меньше домашних животных. Предложение WHERE не работает, потому что нам нужен способ подсчета животных в каждой группе, а агрегатные функции недопустимы в предложении WHERE. К счастью, есть еще одно предложение, предназначенное именно для этой цели, предложение HAVING. Предложение HAVING фильтрует группы, сравнивая их с условным оператором. Если сравнение возвращает true, группа включается в результаты, в противном случае группа не включается. Давай попробуем.
SELECT species, count(id) FROM pets GROUP BY species HAVING count(id) > 1;
species | count
— — — — -+ — — —
dog | 2
cat | 2
owl | 2
На диаграмме выше мы видим, что строки сначала группируются по видам, а затем фильтруются с помощью предложения HAVING. предложение HAVING выбирает только группы, количество которых превышает 1 строку. Все остальные исключаются из результатов.
Функциональная зависимость
Прежде чем эта статья подходит к концу, необходимо изучить еще одну тему - концепцию функциональной зависимости. В документации PostgreSQL сказано следующее:
Когда присутствует GROUP BY или присутствуют какие-либо агрегатные функции, недопустимо, чтобы выражения списка SELECT ссылались на несгруппированные столбцы, за исключением агрегатных функций, или когда несгруппированный столбец функционально зависит от сгруппированных столбцов, поскольку в противном случае было бы больше более одного возможного значения, возвращаемого для несгруппированного столбца. Функциональная зависимость существует, если сгруппированные столбцы (или их подмножество) являются первичным ключом таблицы, содержащей несгруппированный столбец.
Мы уже исследовали первое условие в приведенном выше утверждении, а именно, что несгруппированные столбцы не могут быть включены в список SELECT, кроме как внутри агрегатных функций. Мы еще не рассмотрели второе допустимое условие для несгруппированного столбца: он может существовать в списке SELECT, если он функционально зависит от одного из сгруппированных столбцов. Вышеупомянутое утверждение заявляет, что «существует функциональная зависимость, если один из сгруппированных столбцов является первичным ключом таблицы, содержащей несгруппированный столбец». Это, вероятно, не так уж сложно понять, но давайте проясним это на небольшом примере. Выберем имена питомцев, сгруппированные по их идентификатору (первичному ключу таблицы).
SELECT name FROM pets GROUP BY id; name — — — — — — cinderella oswald charlie polly donut tim django ivan coco olly (10 rows)
Это сработало. Здесь важно отметить одну вещь: столбец name был включен в список SELECT без использования агрегатной функции или включения ее в предложение GROUP BY. Это возможно, потому что столбец name (и все другие столбцы в таблице) функционально зависит от столбца id, который был явно установлен как первичный ключ. Каждый первичный ключ требует, чтобы его значения были уникальными, а не нулевыми. Это означает, что если таблица сгруппирована по этим значениям, каждая группа будет состоять только из одной строки, поскольку по определению не может быть несколько строк в таблице с одним и тем же значением. Если несколько таблиц объединены вместе, может быть несколько строк с одним и тем же значением первичного ключа, но все значения в исходной таблице будут принадлежать только одному первичному ключу, что является еще одним способом сказать, что эти значения являются функционально зависит от первичного ключа.
Перед завершением рассмотрим еще один последний пример. На этот раз мы попытаемся выбрать значения id таблицы pets, сгруппированные по имени. Все имена разные, поэтому для каждого имени должно быть только одно значение id. Это сработает? Давай попробуем.
SELECT id FROM pets GROUP BY name;
ERROR: column “pets.id” must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT id FROM pets GROUP BY name;
^
Это не работает. Несмотря на то, что мы удовлетворяем условию, что в каждой группе может быть только одно значение, столбец id не функционально зависит от столбца name. Техническая причина этого заключается в том, что столбец с именем не является первичным ключом таблицы. Другая причина заключается в том, что столбец имени может иметь несколько одинаковых значений и затем образовывать группы из нескольких строк. Однако даже если для столбца имени были установлены ограничения UNIQUE и NOT NULL, он не был бы первичным ключом и технически не удовлетворял бы определению функциональной зависимости.
Вывод
В этой статье я попытался прояснить некоторую путаницу, которая может возникнуть при использовании предложения GROUP BY. Главное помнить, что GROUP BY формирует группы, и каждая группа может содержать только одно значение для каждого столбца результатов. Сгруппированные столбцы автоматически сжимаются в одно значение, тогда как несгруппированные столбцы должны быть либо агрегированы в одно значение, либо функционально зависеть от одного из сгруппированных столбцов.
Я надеюсь, что эта статья расширила ваше понимание оператора GROUP BY и поможет избежать некоторых из этих досадных ошибок.
Спасибо за чтение и удачного программирования!
