SQL Snow Day Module 3: Агрегирование данных

Добро пожаловать в часть 3 книги SQL Snow Day: праздничное приключение по основам Postgres с bit.io! В этом модуле вы изучите основы выбора и подмножества данных из вашей базы данных с помощью SQL. Прежде чем продолжить, убедитесь, что вы знакомы с материалом Модулей 1 и 2.

В этом модуле вы:

Узнайте, как агрегировать и обобщать данные. Это означает поиск сводной статистики, такой как суммы и средние значения по всем данным или по подмножествам.
Узнайте, как сохранить эти агрегированные результаты (или любые результаты запроса) в виде представления, которое можно запросить и использовать позже.
Примените вышеуказанное к своему набору данных, чтобы помочь Санте 🎅🏻

⏱30 минут

Зачем агрегировать данные?

Южный полюс находится в самом конце маршрута доставки Санты, и к тому времени он всегда устает. Поэтому вместо индивидуальной доставки он просто отправляет по одной партии подарков на каждый из островов пингвинов Палмера.

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

Список отдельных пингвинов и их размеры не помогут ему решить, кому что достанется. Но некоторые сводные данные по каждому из островов могут помочь. Он не хочет доставлять большие свитера на остров, где все пингвины маленькие! Вот где происходит агрегация.

Подсчет строк

COUNT — одна из самых основных функций агрегации. Вот как посчитать все строки в таблице пингвинов:

SELECT COUNT(*)
FROM penguins;

Что возвращает 344. Функция агрегирования появляется в предложении SELECT. Это легко комбинировать с методами фильтрации, которые мы изучили в предыдущем модуле. Например, чтобы подсчитать количество пингвинов Gentoo, вы можете использовать:

SELECT COUNT(*)
FROM penguins
WHERE species=’Gentoo’;

Что возвращает 124.

Упражнение. Попробуйте сами: сколько пингвинов в таблице пингвинов с острова Сон из 2009 года?

Агрегирование по категориям

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

SELECT species, COUNT(*)
FROM penguins
GROUP BY species;

Что возвращает:

Есть две ключевые части агрегирования данных с помощью SQL. Вы должны указать:

Столбец для группировки данных (в данном случае species)
как агрегировать оставшиеся выбранные столбцы (в данном случае count(*), который возвращает количество строк данных для каждой группы, указанной в строке GROUP BY)

Упражнение. Обратите внимание, что мы включили виды в предложение выбора. Попробуйте запустить запрос без него. Что происходит? Теперь попробуйте выполнить запрос без предложения GROUP BY. Что происходит? Отметьтесь в раздоре, если у вас есть вопросы и вам нужна помощь в понимании результатов!

🚧🚧🚧 Предупреждение 🚧🚧🚧

Включите агрегатную функцию при использовании предложения GROUP BY

Вообще говоря, когда ваш запрос включает предложение GROUP BY, любые столбцы, появляющиеся в команде SELECT, но не в предложении GROUP BY, должны быть включены в агрегатную функцию, такую как count() или avg(). В противном случае вы получите сообщение об ошибке:

column <column_name> must appear in the GROUP BY clause or be used in an aggregate function.

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

Мы знаем, сколько пингвинов каждого вида находится в таблице пингвинов. Мы также хотим знать, сколько пингвинов каждого типа обитает на каждом острове. Чтобы понять это, мы добавляем island к предложениям select и group by.

SELECT species, island, COUNT(*)
FROM penguins
GROUP BY species, island;

Что возвращает:

Существует множество различных функций агрегации. Обо всех них можно прочитать здесь. Они следуют той же схеме, что и COUNT. Некоторые очень полезные, о которых нужно знать:

avg: возвращает среднее значение выбранного столбца
min: возвращает минимальное значение выбранного столбца
max: возвращает максимальное значение выбранного столбца
sum: суммирует значения в выбранном столбце

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

SELECT species, avg(bill_length_mm)
FROM penguins
GROUP BY species;

Упражнения

Вот несколько упражнений на агрегирование, чтобы проверить ваше понимание.

Составьте запрос, чтобы найти количество самцов и самок папуасских пингвинов.
Какова средняя длина плавников всех пингвинов? Для каждого острова? Для каждого вида? (подсказка: см. раздел выше о других функциях агрегирования)
Используя то, что вы узнали о фильтрации данных в последнем модуле, узнайте максимальную глубину клюва каждого вида пингвинов в 2009 году. (Подсказка: вы можете либо использовать предложение WHERE, чтобы отфильтровать результаты до 2009 года, либо вам нужно сгруппировать по годам и видам, чтобы увидеть средние значения для каждого вида за каждый год)

Сохранение результатов запроса

Иногда вы хотите сохранить запрос — может быть, это запрос, который вы часто используете и не хотите вводить повторно; возможно, это агрегация, которую вы хотите иметь доступной, когда вам это нужно. Мы можем сохранять запросы с помощью Views. Представление присваивает имя запросу. Затем вы можете обратиться к запросу по имени, как если бы это была обычная таблица.

Давайте создадим представление, показывающее среднюю длину клюва, длину ласт и массу тела для каждого вида пингвинов на каждом острове. Для этого используем команду СОЗДАТЬ ВИД.

CREATE VIEW penguin_agg AS
SELECT species, island, avg(bill_length_mm) bill_length,
avg(flipper_length_mm) flipper_length,
avg(body_mass_g) body_mass
FROM penguins
GROUP BY species, island;

Обратите внимание, что при включении имен нескольких столбцов нам нужно было добавить псевдонимы. Это связано с тем, что по умолчанию столбец, сгенерированный агрегатной функцией, является именем этой агрегатной функции. Но столбцам нужны уникальные имена; у нас не может быть трех столбцов с именами avg. Строка SELECT avg(bill_length_mm) bill_length означает вычисление и выбор средней длины купюры и вызов полученного столбца bill_length.

Обязательно запустите приведенный выше код CREATE VIEW, так как мы будем использовать его в будущих модулях.

Упражнение. Помните, что Санта хочет раздать большие варежки, большие свитера и маленькие шарфы. На каждый остров может попасть только один тип предметов. Используя агрегатные функции (и, возможно, созданное вами представление), исходя из средних характеристик пингвинов на каждом острове, предложите Санте, какой тип предмета должен попасть на каждый остров. Дайте нам знать, что вы думаете о discord или Twitter!

Ресурсы

Документы Постгрес

Функции агрегации
"Взгляды"

Другие учебники

Теперь переходим к Модулю 4: Создание и обновление таблиц.