Изучите науку о данных при социальном дистанцировании
Как группировать и агрегировать данные с помощью SQL
Использование примера Spice Girls о том, как группировать и агрегировать данные с помощью ключевых слов SQL GROUP BY, MIN, MAX, COUNT, AVG и SUM
Вступление
Мне было так приятно общаться с друзьями, чтобы поделиться своей любовью к науке о данных. Мы активно изучаем SQL, и у нас уже есть три урока. Если вы хотите начать с начала, вот ссылка на первый урок. Все уроки также можно найти здесь. В противном случае изучите этот урок по группировке и агрегированию данных с помощью SQL.
Предыдущий урок
На прошлой неделе мы рассмотрели фильтрацию данных с помощью SQL. Используя одно из моих любимых шоу, Charmed, например данные, мы использовали предложения WHERE в запросах. Кроме того, мы исследовали использование ключевых слов IN, AND, OR, LIKE, BETWEEN и NOT.
Этот урок
Теперь, когда мы знаем, как фильтровать данные, перейдем к агрегированию. Мы узнаем, как использовать ключевые слова MIN и MAX, чтобы найти минимум и максимум наших данных соответственно. Мы также попрактикуемся в использовании ключевых слов COUNT, AVG и SUM аналогичным образом.
Это также будет первый урок, в котором мы столкнулись со значениями NULL. Итак, мы узнаем, как бороться с ними в наших наборах данных.
Так уж получилось, что все друзья, которых я преподаю науке о данных, пока я изолирую себя, - женщины. Так что лучший пример для использования в этом уроке, чем Spice Girls, чтобы отпраздновать немного женской силы. Мы будем использовать данные о первом альбоме Spice Girls под названием Spice. Это тот, который был очень популярным в моем доме, когда я рос.
Ключевые выводы:
- используйте ключевое слово MIN, чтобы найти минимальное значение в столбце
- используйте ключевое слово MAX, чтобы найти максимальное значение в столбце
- используйте ключевое слово COUNT для подсчета количества строк в столбце или таблице
- используйте ключевое слово AVG, чтобы найти среднее значение числового столбца
- используйте ключевое слово SUM, чтобы найти итог числового столбца, когда все значения складываются вместе
- используйте ключевое слово GROUP BY для группировки по столбцу в таблице
- знать, как значения NULL будут обрабатываться в каждом из вышеперечисленных методов
- понять, как работают псевдонимы и как использовать ключевое слово AS для их создания
Эта проблема
Я считаю, что первый альбом Spice Girls - лучший. На всякий случай, если люди мне не верят, я хочу найти данные для их резервного копирования. Используя данные австралийских чартов для синглов, выпущенных с каждого альбома Spice Girls, я думаю, что смогу доказать первое правило!
Данные
Этот набор данных содержит информацию обо всех треках в каждом альбоме, выпущенном Spice Girls. В таблице указана длина каждого трека, когда был выпущен каждый сингл, какое место песня заняла в австралийских чартах и сколько недель песня находилась в австралийских чартах. Я не включил альбом лучших хитов, потому что думал, что это запутает дело.
Я получил данные для этой таблицы со страницы Википедии Дискография Spice Girls, страницы Википедии Spice (альбом), страницы Википедии Spice World (альбом), Forever (Альбом Spice Girls) и Австралийской сайт графиков .
Синтаксис для агрегирования данных
В этом уроке я научу вас, как использовать агрегирующие ключевые слова MIN, MAX, COUNT, SUM и AVG в операторе SELECT. Эти агрегаторы также могут использоваться в других местах запросов. Например, их можно использовать в предложении HAVING, но это выходит за рамки этого урока. Я написал еще один рассказ Сравнение WHERE и HAVING в SQL на случай, если вы захотите узнать больше.
Ключевое слово MAX можно использовать для поиска максимального значения в столбце. Его можно использовать для множества различных типов данных, включая целые числа, числа с плавающей запятой, строки и даты.
Как вы можете видеть ниже, синтаксис для использования MAX включает размещение ключевого слова MAX внутри оператора select. Ключевое слово MAX имеет набор скобок после него, в которые вы помещаете имя столбца, для которого хотите найти максимальное значение.
SELECT
MAX(name_column_one) AS 'largest_col_one'
FROM
name_of_table
;
Еще одно полезное ключевое слово, которое можно использовать вместе с агрегаторами, - это AS. AS используется для создания псевдонима или временного имени столбца, созданного агрегатором в возвращаемой таблице. Поскольку агрегатор используется в столбце 1, полезно дать результату новое имя, чтобы его можно было легко идентифицировать в итоговой таблице. Ключевое слово AS также может использоваться для присвоения псевдонима таблице, но мы рассмотрим это в нашем уроке по объединению таблиц.
Ключевое слово MIN используется почти так же, как ключевое слово MAX. Как и следовало ожидать, он используется для возврата минимального значения выбранного столбца.
SELECT
MIN(name_column_two) AS 'smallest_col_two'
FROM
name_of_table
;
COUNT - ключевое слово, используемое для подсчета количества строк в выбранном столбце. Таким образом, в приведенном ниже примере будет подсчитано общее количество строк в таблице.
SELECT
COUNT(name_column_three) AS 'col_three_counts'
FROM
name_of_table
;
Однако COUNT более полезно, когда к таблице применен какой-либо фильтр или группировка. В этом случае вы будете подсчитывать количество строк в таблице, удовлетворяющих условиям фильтра или группировки.
SELECT
COUNT(name_column_three) AS 'col_three_counts'
FROM
name_of_table
WHERE
name_column_one = value
;
В отличие от MIN, MAX и COUNT, ключевые слова SUM и AVG можно использовать только в столбцах, которые содержат числовые типы данных, такие как целые числа или числа с плавающей запятой. AVG используется для возврата среднего значения столбца.
SELECT
AVG(name_column_four) AS 'mean_col_four'
FROM
name_of_table
;
СУММ складывает значения в числовой столбец. Синтаксис использования SUM такой же, как и у других агрегаторов.
SELECT
SUM(name_column_five) AS 'summed_col_five'
FROM
name_of_table
;
Синтаксис для группировки данных
Группировка очень полезна для суммирования данных с повторяющимися значениями. Часто используется вместе с агрегаторами MIN, MAX, COUNT, SUM и AVG. GROUP BY собирает вместе идентичные значения в столбце, к которому применяется. Затем агрегатор можно использовать для другого столбца на основе этих группировок. Предложение GROUP BY часто используется в столбце, содержащем значения категорий.
Например, если вы используете агрегатор AVG с GROUP BY, вы можете найти среднее значение для одного столбца для каждой группы значений в другом столбце. Синтаксис этого примера показан ниже. В этом случае возвращенная таблица будет содержать среднее значение из первого столбца для каждого значения во втором столбце.
SELECT
AVG(name_column_one) AS mean_col_one, name_column_two
FROM
name_of_table
GROUP BY
name_column_two
;
Что происходит со значениями NULL?
Значение NULL - это то, что происходит, когда в поле не введено значение. Таким образом, это похоже на заполнитель, который сообщает вам, что в таблице есть пустое место. Он отличается от значения 0 или пустой строки ‘’, поскольку они все еще являются значениями. NULL имеет свои собственные ключевые слова.
Вы можете проверить значения NULL в столбце с помощью ключевого слова IS NULL. Это делается в операторе WHERE и возвращает только те записи, которые содержат значения NULL в столбце, указанном в операторе WHERE.
SELECT
*
FROM
name_of_table
WHERE
name_column_one IS NULL
;
Однако более распространенным сценарием будет исключение всех записей, содержащих значения NULL. В этом случае вы можете добавить в свой запрос ключевое слово NOT. В приведенном ниже примере в возвращаемую таблицу будут включены только записи, не имеющие значений NULL во втором столбце.
SELECT
*
FROM
name_of_table
WHERE
name_column_two IS NOT NULL
;
Значения NULL игнорируются совокупными ключевыми словами MIN, MAX, AVG, SUM и COUNT. Для MIN и MAX это довольно просто, вы получите минимальное или максимальное значение в столбце, игнорируя все значения NULL. Аналогично для SUM все значения в столбце, которые не равны NULL, будут добавлены вместе. Для AVG вы получаете среднее значение столбца, когда все значения NULL удалены.
COUNT немного сложнее: будут подсчитаны все строки в выбранном столбце, за исключением строк, содержащих значения NULL. Единственное исключение из этого правила показано ниже. В этом случае будут подсчитаны все строки таблицы, независимо от того, есть ли в них значения NULL.
SELECT
COUNT(*)
FROM
name_of_table
;
В отличие от агрегаторов, GROUP BY не игнорирует значения NULL. Вместо этого они сгруппированы в одну группирующую категорию.
А теперь займемся решением нашей проблемы
- перейдите по адресу http://sqlfiddle.com/ (или вы можете использовать https://www.db-fiddle.com/, поскольку я обнаружил, что скрипт SQL в последнее время имел некоторое время простоя)
- В поле слева поместите запросы CREATE TABLE и INSERT INTO ниже.
CREATE TABLE spice_girls(
album varchar(255),
title varchar(255),
length TIME(0),
track_number int,
single_released date,
peak_chart_position_au int,
chart_weeks_au int);
INSERT INTO spice_girls(
album,
title,
length,
track_number,
single_released,
peak_chart_position_au,
chart_weeks_au
)
VALUES
("Spice", "Wannabe", '000:02:53', 1, '1996-07-08', 1, 29),
("Spice", "Say You'll Be There", '000:03:55', 2, '1996-10-10', 12, 22),
("Spice", "2 Become 1", '000:04:01', 3, '1996-12-16', 2, 18),
("Spice", "Love Thing", '000:03:38', 4, NULL, NULL, NULL),
("Spice", "Last Time Lover", '000:04:11', 5, NULL, NULL, NULL),
("Spice", "Mama", '000:05:04', 6, '1997-02-27', 13, 14),
("Spice", "Who Do You Think You Are", '000:04:00', 7, '1997-02-27', 13, 14),
("Spice", "Something Kinda Funny", '000:04:05', 8, NULL, NULL, NULL),
("Spice", "Naked", '000:04:25', 9, NULL, NULL, NULL),
("Spice", "If U Can't Dance", '000:03:48', 10, NULL, NULL, NULL),
("Spice World", "Spice Up Your Life", '000:02:53', 1, '1997-10-06', 8, 20),
("Spice World", "Stop", '000:03:24', 2, '1997-01-01', 5, 21),
("Spice World", "Too Much", '000:04:31', 3, '1997-12-08', 9, 15),
("Spice World", "Saturday Night Divas", '000:04:25', 4, NULL, NULL, NULL),
("Spice World", "Never Give Up on the Good Times", '000:04:30', 5, NULL, NULL, NULL),
("Spice World", "Move Over", '000:02:46', 6, NULL, NULL, NULL),
("Spice World", "Do It", '000:04:04', 7, NULL, NULL, NULL),
("Spice World", "Denying", '000:03:46', 8, NULL, NULL, NULL),
("Spice World", "Viva Forever", '000:05:09', 9, '1998-07-20', 2, 21),
("Spice World", "The Lady Is a Vamp", '000:03:09', 10, NULL, NULL, NULL),
("Forever", "Holler", '000:04:15', 1, '2000-10-23', 2, 15),
("Forever", "Tell Me Why", '000:04:13', 2, NULL, NULL, NULL),
("Forever", "Let Love Lead the Way", '000:04:57', 3, '2000-10-23', 2, NULL),
("Forever", "Right Back at Ya", '000:04:09', 4, NULL, NULL, NULL),
("Forever", "Get Down with Me", '000:03:45', 5, NULL, NULL, NULL),
("Forever", "Wasting My Time", '000:04:13', 6, NULL, NULL, NULL),
("Forever", "Weekend Love", '000:04:04', 7, NULL, NULL, NULL),
("Forever", "Time Goes By", '000:04:51', 8, NULL, NULL, NULL),
("Forever", "If You Wanna Have Some Fun", '000:05:25', 9, NULL, NULL, NULL),
("Forever", "Oxygen", '000:04:55', 10, NULL, NULL, NULL),
("Forever", "Goodbye", '000:04:35', 11, '1998-12-07', 3, 16)
;
Примечание о специальных символах. При создании этой таблицы мы сталкиваемся с новым синтаксическим нюансом, который мы не видели в предыдущих уроках. Некоторые названия песен содержат апострофы. Поэтому, если мы заключим строки названия песни в одинарные кавычки «», как обычно, возникнет ошибка. Это потому, что программа может принять апостроф за заключительную цитату. Итак, чтобы включить в нашу таблицу строку, содержащую апостроф, нам нужно заключить строки в двойные кавычки.
Обратное также можно сделать, если вам нужна строка, содержащая двойные кавычки. Если вы хотите узнать больше о специальных символах, таких как цитаты, это отличный ресурс.
Примечание о типе данных TIME: в этой таблице также есть тип данных, который мы не видели ни в одном из предыдущих уроков. Это тип данных TIME. Время имеет формат «часы: минуты: секунды».
3. Нажмите кнопку схемы сборки.
4. В правом поле введите свои запросы.
5. Выполните приведенный ниже запрос и посмотрите, вернет ли он то, что вы ожидаете:
SELECT
SUM(length) AS 'total_length'
FROM
spice_girls
;
6. Выполните приведенный ниже запрос и посмотрите, вернет ли он то, что вы ожидаете:
SELECT
SUM(length) AS 'album_length',
album
FROM
spice_girls
GROUP BY
album
;
7. Выполните приведенный ниже запрос и посмотрите, вернет ли он то, что вы ожидаете:
SELECT
album,
SUM(length) AS 'album_length',
AVG(length) AS 'average_song_length',
COUNT(length) AS 'number_of_songs',
MIN(length) AS 'shortest_song_length',
MAX(length) AS 'longest_song_length'
FROM
spice_girls
GROUP BY
album
ORDER BY
album
;
Упражнение 1. Напишите запрос, чтобы показать, какой альбом является лучшим, если мы определим лучший альбом как альбом с наивысшим пиковым положением в чарте одного из его синглов.
Упражнение 2. Напишите запрос, чтобы показать, какой альбом является лучшим, если мы определим лучший альбом как альбом с наивысшей средней пиковой позицией в чарте всех синглов в альбоме.
Упражнение 3. Напишите запрос, чтобы показать, какой альбом лучший, если мы определим лучший альбом как альбом с наибольшим количеством синглов в австралийских чартах в течение как минимум 5 недель.
Обзор обучения
После завершения этого урока вы должны знать:
- как использовать ключевое слово MIN, чтобы найти минимальное значение в столбце
- как использовать ключевое слово MAX, чтобы найти максимальное значение в столбце
- как использовать ключевое слово COUNT для подсчета количества строк в столбце или таблице
- как использовать ключевое слово AVG, чтобы найти среднее значение числового столбца
- как использовать ключевое слово SUM, чтобы найти итог числового столбца, когда все значения складываются вместе
- как использовать ключевое слово GROUP BY для группировки по столбцу в таблице
- понять, как обрабатываются значения NULL
- иметь возможность писать свои собственные запросы, используя один или несколько из вышеперечисленных методов
- иметь возможность использовать ключевое слово AS для создания псевдонима для агрегированного столбца
Следующий урок
На следующей неделе будет наш последний урок SQL. Мы научимся соединять столы вместе. Есть несколько различных способов объединения наборов данных с помощью ключевых слов UNION, UNION ALL и JOIN. Если вы хотите изучить соединения до следующей недели, я ранее писал статью о различии между внутренними и внешними соединениями в SQL.
Помимо данных, еще одна моя страсть - это живопись. Вы можете найти мое искусство дикой природы на www.katemarielewis.com
