Перейти к содержанию

172 уроков, 13 библиотек и челлендж «Что выведет код?» — бесплатно, код прямо в браузере

Начать обучение
Питоника
Урок 6 из 10 Средний 40 мин 130 XP

Агрегаты и GROUP BY в SQL: COUNT, SUM, AVG и HAVING

Считаем, суммируем и усредняем по всей таблице и по группам: COUNT, SUM, AVG, GROUP BY и фильтр групп HAVING.

Редакция Питоники

До сих пор каждый запрос возвращал строки — по одной на книгу. Но на вопросы бизнеса отвечают другие запросы: «сколько всего книг?», «какая средняя цена?», «какой жанр самый многочисленный?». Один вопрос — один ответ. Это агрегатные функции: они принимают много строк и возвращают одно число. А вместе с GROUP BY они превращают SQL в язык отчётов — то, за что аналитики его и любят.

База та же: восемь книг в таблице books. У одной — «Русских народных сказок» — автор не указан: в author_id лежит NULL, и это не декорация, а важный участник сегодняшних опытов. Каждый блок самодостаточен: CREATE, INSERT и запрос — жми «Запустить».

COUNT: считаем строки

COUNT считает строки. Но у него два обличия, и разница между ними — классический вопрос на собеседовании. COUNT(*) считает все строки без разбора. COUNT(колонка) считает только строки, в которых эта колонка не NULL — пропуски игнорируются.

два счётчика: со звёздочкой и по колонке
import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute(
    "CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, "
    "author_id INTEGER, genre TEXT, price INTEGER, pages INTEGER)"
)
books = [
    (1, "Мастер и Маргарита", 1, "роман", 850, 480), (2, "Собачье сердце", 1, "повесть", 420, 160),
    (3, "Евгений Онегин", 2, "поэма", 500, 320), (4, "Руслан и Людмила", 2, "поэма", 380, 210),
    (5, "Война и мир", 3, "роман", 1200, 960), (6, "Анна Каренина", 3, "роман", 950, 864),
    (7, "Русские народные сказки", None, "сказки", 300, 240), (8, "Белая гвардия", 1, "роман", 700, 416),
]
cur.executemany("INSERT INTO books VALUES (?, ?, ?, ?, ?, ?)", books)

cur.execute("SELECT COUNT(*), COUNT(author_id) FROM books")
print(cur.fetchone())
Вывод
(8, 7)

Книг восемь, а непустых значений author_id — семь: сказки с их NULL счётчик не учёл. Так что формулировка имеет значение: «сколько книг в базе» — это COUNT(*), «у скольких книг указан автор» — COUNT(author_id). Найти саму книгу-сироту ты уже умеешь через IS NULL из урока про WHERE.

SUM, AVG, MIN и MAX

Остальные агрегаты работают по колонке: SUM складывает, AVG считает среднее арифметическое, MIN и MAX возвращают минимум и максимум. В одном SELECT их можно смешивать свободно.

сводка по ценам и страницам
import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute(
    "CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, "
    "author_id INTEGER, genre TEXT, price INTEGER, pages INTEGER)"
)
books = [
    (1, "Мастер и Маргарита", 1, "роман", 850, 480), (2, "Собачье сердце", 1, "повесть", 420, 160),
    (3, "Евгений Онегин", 2, "поэма", 500, 320), (4, "Руслан и Людмила", 2, "поэма", 380, 210),
    (5, "Война и мир", 3, "роман", 1200, 960), (6, "Анна Каренина", 3, "роман", 950, 864),
    (7, "Русские народные сказки", None, "сказки", 300, 240), (8, "Белая гвардия", 1, "роман", 700, 416),
]
cur.executemany("INSERT INTO books VALUES (?, ?, ?, ?, ?, ?)", books)

cur.execute("SELECT SUM(price), AVG(price), MIN(pages), MAX(pages) FROM books")
print(cur.fetchone())
Вывод
(5300, 662.5, 160, 960)

Разбор: суммарная цена всех книг — 5300 рублей, средняя — 662.5, от 160 до 960 страниц. Заметь: AVG всегда возвращает вещественное число, даже если исходные данные целые. А MIN и MAX работают не только с числами: MIN(title) честно вернёт «Анну Каренину» — первый заголовок по алфавиту.

Алиасы: называем вычисления

Колонки с агрегатами получают безликие имена вроде COUNT(*). Ключевое слово AS даёт вычислению внятный псевдоним — он появится в заголовках результата, и по нему же можно сортировать.

алиасы AS для агрегатов
import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute(
    "CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, "
    "author_id INTEGER, genre TEXT, price INTEGER, pages INTEGER)"
)
books = [
    (1, "Мастер и Маргарита", 1, "роман", 850, 480), (2, "Собачье сердце", 1, "повесть", 420, 160),
    (3, "Евгений Онегин", 2, "поэма", 500, 320), (4, "Руслан и Людмила", 2, "поэма", 380, 210),
    (5, "Война и мир", 3, "роман", 1200, 960), (6, "Анна Каренина", 3, "роман", 950, 864),
    (7, "Русские народные сказки", None, "сказки", 300, 240), (8, "Белая гвардия", 1, "роман", 700, 416),
]
cur.executemany("INSERT INTO books VALUES (?, ?, ?, ?, ?, ?)", books)

cur.execute(
    "SELECT COUNT(*) AS books_total, SUM(price) AS total, "
    "AVG(price) AS avg_price FROM books"
)
print(cur.fetchone())
Вывод
(8, 5300, 662.5)

На сами данные алиас не влияет — это ярлык для результата. Но без него отчёты быстро превращаются в шифровки, а сортировать по алиасу (чуть ниже увидим ORDER BY avg_price) — одно удовольствие.

GROUP BY: считаем по группам

Один COUNT на всю таблицу — это мало. Настоящие отчёты отвечают на вопросы вида «сколько книг в каждом жанре». Механика GROUP BY простая: разложи строки по стопкам — по одной на каждое значение колонки — и выполни агрегат в каждой стопке отдельно. В результате одна строка на одну группу.

книги по жанрам
import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute(
    "CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, "
    "author_id INTEGER, genre TEXT, price INTEGER, pages INTEGER)"
)
books = [
    (1, "Мастер и Маргарита", 1, "роман", 850, 480), (2, "Собачье сердце", 1, "повесть", 420, 160),
    (3, "Евгений Онегин", 2, "поэма", 500, 320), (4, "Руслан и Людмила", 2, "поэма", 380, 210),
    (5, "Война и мир", 3, "роман", 1200, 960), (6, "Анна Каренина", 3, "роман", 950, 864),
    (7, "Русские народные сказки", None, "сказки", 300, 240), (8, "Белая гвардия", 1, "роман", 700, 416),
]
cur.executemany("INSERT INTO books VALUES (?, ?, ?, ?, ?, ?)", books)

for row in cur.execute("SELECT genre, COUNT(*) FROM books GROUP BY genre"):
    print(row)
Вывод
('повесть', 1)
('поэма', 2)
('роман', 4)
('сказки', 1)

Четыре жанра — четыре стопки: романов четыре, поэм две, повесть и сказки по одной. Правило на все времена: колонка из SELECT без агрегата обязана стоять в GROUP BY — иначе непонятно, какое её значение показывать для целой группы. Кстати, GROUP BY author_id тоже сработает, но увидишь ты лишь безликие числа 1, 2, 3 — расшифровать их в имена авторов позволяет JOIN из следующего урока.

Агрегатов в групповом запросе может быть несколько, а сортировать результат удобнее по алиасу — как по обычной колонке.

средняя цена жанра, сортировка по алиасу
import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute(
    "CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, "
    "author_id INTEGER, genre TEXT, price INTEGER, pages INTEGER)"
)
books = [
    (1, "Мастер и Маргарита", 1, "роман", 850, 480), (2, "Собачье сердце", 1, "повесть", 420, 160),
    (3, "Евгений Онегин", 2, "поэма", 500, 320), (4, "Руслан и Людмила", 2, "поэма", 380, 210),
    (5, "Война и мир", 3, "роман", 1200, 960), (6, "Анна Каренина", 3, "роман", 950, 864),
    (7, "Русские народные сказки", None, "сказки", 300, 240), (8, "Белая гвардия", 1, "роман", 700, 416),
]
cur.executemany("INSERT INTO books VALUES (?, ?, ?, ?, ?, ?)", books)

for row in cur.execute(
    "SELECT genre, COUNT(*) AS cnt, AVG(price) AS avg_price "
    "FROM books GROUP BY genre ORDER BY avg_price DESC"
):
    print(row)
Вывод
('роман', 4, 925.0)
('поэма', 2, 440.0)
('повесть', 1, 420.0)
('сказки', 1, 300.0)

Самый дорогой жанр — роман: в среднем 925 рублей за книгу. Обрати внимание на ORDER BY avg_price: алиас, объявленный в SELECT, спокойно живёт в ORDER BY. С WHERE такой номер не всегда проходит — а почему, станет ясно прямо сейчас.

Чем HAVING отличается от WHERE?

Оба фильтруют, но на разных этапах конвейера: WHERE отсеивает строки до группировки, HAVING фильтрует готовые группы после неё — оба фильтра нужны, но работают на разных этапах запроса. WHERE работает до группировки: он отсеивает отдельные строки, и в агрегатах ещё никто не участвовал. HAVING работает после группировки: перед ним уже стоят готовые стопки, и фильтровать он может по значению агрегата. Поэтому условие COUNT(*) >= 2 невозможно записать в WHERE — на этапе его выполнения агрегатов ещё не существует.

WHERE отбирает строки, HAVING - группы
import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute(
    "CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, "
    "author_id INTEGER, genre TEXT, price INTEGER, pages INTEGER)"
)
books = [
    (1, "Мастер и Маргарита", 1, "роман", 850, 480), (2, "Собачье сердце", 1, "повесть", 420, 160),
    (3, "Евгений Онегин", 2, "поэма", 500, 320), (4, "Руслан и Людмила", 2, "поэма", 380, 210),
    (5, "Война и мир", 3, "роман", 1200, 960), (6, "Анна Каренина", 3, "роман", 950, 864),
    (7, "Русские народные сказки", None, "сказки", 300, 240), (8, "Белая гвардия", 1, "роман", 700, 416),
]
cur.executemany("INSERT INTO books VALUES (?, ?, ?, ?, ?, ?)", books)

for row in cur.execute(
    "SELECT genre, COUNT(*) AS cnt FROM books "
    "WHERE price < 600 GROUP BY genre HAVING COUNT(*) >= 2"
):
    print(row)
Вывод
('поэма', 2)

Проследим конвейер. WHERE оставил четыре книги дешевле 600 рублей: «Собачье сердце», оба Онегина с «Русланом» и сказки. GROUP BY разложил их на три стопки: повесть — 1, поэма — 2, сказки — 1. HAVING выбросил стопки с одной книгой. Осталась поэма. Романы в выборку не попали вовсе — их цена выше 600, WHERE убрал их ещё до группировки.

ВопросWHEREHAVING
Когда работаетдо группировкипосле группировки
Что фильтруетотдельные строкицелые группы
Агрегаты в условиинельзяможно и нужно
ПримерWHERE price < 600HAVING COUNT(*) >= 2
ошибка: агрегат в WHERE
cur.execute("""
    SELECT genre, COUNT(*)
    FROM books
    WHERE COUNT(*) > 1
    GROUP BY genre
""")
Вывод
sqlite3.OperationalError: misuse of aggregate: COUNT()
Блок не запускается намеренно: он вызывает ошибку. В реальном выводе перед этой строкой будет ещё traceback, но суть всегда в последней строке.
законный агрегат вместо голой колонки
import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute(
    "CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, "
    "author_id INTEGER, genre TEXT, price INTEGER, pages INTEGER)"
)
books = [
    (1, "Мастер и Маргарита", 1, "роман", 850, 480), (2, "Собачье сердце", 1, "повесть", 420, 160),
    (3, "Евгений Онегин", 2, "поэма", 500, 320), (4, "Руслан и Людмила", 2, "поэма", 380, 210),
    (5, "Война и мир", 3, "роман", 1200, 960), (6, "Анна Каренина", 3, "роман", 950, 864),
    (7, "Русские народные сказки", None, "сказки", 300, 240), (8, "Белая гвардия", 1, "роман", 700, 416),
]
cur.executemany("INSERT INTO books VALUES (?, ?, ?, ?, ?, ?)", books)

for row in cur.execute(
    "SELECT genre, MIN(title) AS first_title, COUNT(*) AS cnt "
    "FROM books GROUP BY genre"
):
    print(row)
Вывод
('повесть', 'Собачье сердце', 1)
('поэма', 'Евгений Онегин', 2)
('роман', 'Анна Каренина', 4)
('сказки', 'Русские народные сказки', 1)

Что вернут агрегаты на пустой таблице?

Вопрос-ловушка, который проверяют на собеседованиях. Таблица есть, но строк в ней ноль. Сколько книг? Ноль — логично. А какая у них средняя цена? Ответ SQL выдаст неожиданный для новичка.

агрегаты на пустой таблице
import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE empty_books (title TEXT, price INTEGER)")

cur.execute("SELECT COUNT(*), SUM(price), AVG(price) FROM empty_books")
print(cur.fetchone())
Вывод
(0, None, None)

COUNT честно вернул 0 — «строк не было». А SUM и AVG вернули NULL: в Python это None. Смысл такой: «суммировать было нечего, данных нет» — и это не то же самое, что 0. «Средняя цена продаж — 0» и «продаж не было вовсе» — разные заявления, и SQL их различает. Учти это в отчётах: NULL из агрегата на пустой группе умеет доехать до интерфейса и там напугать пользователя.

Собираем отчёт целиком

Финальный аккорд — отчёт, который смешивает всё из урока: несколько агрегатов разом, фильтр групп через HAVING и сортировку по алиасу. Задача: по жанрам, где больше одной книги, показать количество, суммарную и среднюю цену — от самого дорогого жанра.

сводка по жанрам
import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute(
    "CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, "
    "author_id INTEGER, genre TEXT, price INTEGER, pages INTEGER)"
)
books = [
    (1, "Мастер и Маргарита", 1, "роман", 850, 480), (2, "Собачье сердце", 1, "повесть", 420, 160),
    (3, "Евгений Онегин", 2, "поэма", 500, 320), (4, "Руслан и Людмила", 2, "поэма", 380, 210),
    (5, "Война и мир", 3, "роман", 1200, 960), (6, "Анна Каренина", 3, "роман", 950, 864),
    (7, "Русские народные сказки", None, "сказки", 300, 240), (8, "Белая гвардия", 1, "роман", 700, 416),
]
cur.executemany("INSERT INTO books VALUES (?, ?, ?, ?, ?, ?)", books)

for row in cur.execute(
    "SELECT genre, COUNT(*) AS cnt, SUM(price) AS total, AVG(price) AS avg_price "
    "FROM books GROUP BY genre "
    "HAVING COUNT(*) >= 2 ORDER BY total DESC"
):
    print(row)
Вывод
('роман', 4, 3700, 925.0)
('поэма', 2, 880, 440.0)

Шесть служебных слов в одном запросе, и каждое на своём этапе: FROM взял таблицу, GROUP BY собрал четыре стопки, HAVING выбросил одиночек, SELECT посчитал агрегаты, ORDER BY отсортировал. Допиши LIMIT 1 — и получишь жанр-лидер из готового отчёта, без отдельных запросов. По тем же кирпичикам собирается и группировка в Pandas: groupby с agg — это прямой родственник GROUP BY.

Что дальше

Ты научился задавать базе вопросы, которые сворачивают тысячи строк в пару цифр. Но авторы в нашей базе всё ещё спрятаны за безликими author_id — группировка по такой колонке малоинформативна. В следующем уроке мы разберём JOIN и наконец соединим таблицы книг и авторов — от простого INNER до LEFT с NULL-строками. Сортировку и лимиты из урока 5 при этом можно навешивать на любые сгруппированные результаты.

Агрегат сворачивает стопку строк в одну цифру, а GROUP BY раскладывает таблицу на такие стопки.

Что выведет код?

Сначала предскажи ответ в голове — это главный навык программиста.

import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute(
    "CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author_id INTEGER)"
)
books = [
    (1, "Мастер и Маргарита", 1),
    (2, "Собачье сердце", 1),
    (3, "Евгений Онегин", 2),
    (4, "Русские народные сказки", None),
]
cur.executemany("INSERT INTO books VALUES (?, ?, ?)", books)

cur.execute("SELECT COUNT(*), COUNT(author_id) FROM books")
print(cur.fetchone())

import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, genre TEXT)")
books = [
    (1, "Война и мир", "роман"),
    (2, "Анна Каренина", "роман"),
    (3, "Белая гвардия", "роман"),
    (4, "Евгений Онегин", "поэма"),
    (5, "Руслан и Людмила", "поэма"),
]
cur.executemany("INSERT INTO books VALUES (?, ?, ?)", books)

for row in cur.execute(
    "SELECT genre, COUNT(*) AS cnt FROM books "
    "GROUP BY genre ORDER BY cnt DESC LIMIT 1"
):
    print(row)

import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, price INTEGER)")
books = [
    (1, "Война и мир", 1200),
    (2, "Анна Каренина", 950),
    (3, "Мастер и Маргарита", 850),
    (4, "Собачье сердце", 420),
]
cur.executemany("INSERT INTO books VALUES (?, ?, ?)", books)

cur.execute("SELECT AVG(price) FROM books WHERE price > 800")
print(cur.fetchone())
Проверь себя
0 / 5

1. В таблице 8 книг, у одной из них author_id = NULL. Что вернёт COUNT(author_id)?

2. Что вернёт SUM(price) на таблице без единой строки?

3. Чем HAVING отличается от WHERE?

4. Запрос: SELECT genre, COUNT(*) AS cnt FROM books GROUP BY genre. Сколько строк он вернёт, если в базе 4 разных жанра и 8 книг?

5. Что не так с запросом SELECT title, COUNT(*) FROM books GROUP BY genre?

Карточки терминов
Запомнено: 0 / 6
Практика

В редакторе готова база books с восемью книгами. Посчитай, сколько книг в каждом жанре, оставь только жанры, где две или больше книг, и отсортируй результат по количеству — от большего к меньшему. Формат строки: жанр и количество.

practice.py
Вопросы и ответы по уроку

Чем HAVING отличается от WHERE простыми словами?

WHERE отбирает отдельные строки до того, как из них соберутся группы, поэтому агрегаты в нём недоступны. HAVING смотрит на уже собранные группы и умеет фильтровать по агрегатам: HAVING COUNT(*) >= 2. Типовая связка: WHERE price < 600 режет строки, GROUP BY собирает группы, HAVING оставляет группы нужного размера.

Почему COUNT(*) и COUNT(колонка) дают разные числа?

COUNT(*) считает строки целиком — их ровно столько, сколько вернул запрос. COUNT(колонка) считает только непустые значения: строки с NULL в этой колонке пропускаются. В базе из урока COUNT(*) дал 8, а COUNT(author_id) — 7, потому что у одной книги автор не указан. Для подсчёта уникальных значений пригодится связка COUNT(DISTINCT genre).

Что вернёт SUM на пустой таблице?

NULL — не ноль. Смысл ответа: «суммировать было нечего». COUNT на пустом наборе возвращает 0, а SUM и AVG — NULL (в Python это None). Для отчётов это важно: «средний чек — 0 рублей» и «чеков не было» — разные утверждения, и SQL различает их честно.

Можно ли сортировать по алиасу агрегата?

Да: COUNT(*) AS cnt честно живёт в ORDER BY cnt DESC — так делают отчёты «топ категорий». С WHERE ситуация спорная: SQLite ссылку на алиас там прощает, но PostgreSQL — нет, поэтому в WHERE опирайся на настоящие колонки. И помнить про обратный порядок этапов: WHERE выполняется раньше SELECT, где алиас только объявляется.

Понравился урок? Сошлитесь на него

«WHERE отсеивает строки до группировки, HAVING фильтрует готовые группы после неё — оба фильтра нужны, но работают на разных этапах запроса.»

Скопируйте готовую ссылку в формате HTML, Markdown или чистый адрес и вставьте в статью на Habr, VC, Telegram-канал или свой блог — так о проекте узнают новые читатели.

TelegramVK

Что читать дальше

sqlite3 · Урок 5

ORDER BY, LIMIT и DISTINCT: управляем выдачей SELECT

Три ключевых слова, которые превращают сырую выдачу SELECT в готовый ответ: отсортировать, отрезать страницу, убрать дубли.

Pandas · Урок 6

Сортировка, топы и подсчёты: sort_values, value_counts, rank

sort_values по нескольким столбцам, nlargest вместо сортировки всей таблицы, частоты value_counts и рейтинги rank — собираем отчёты «топ-10 товаров» и «доли городов».

Похожие уроки по темам

Подобраны автоматически по пересечению тем и ключевых слов.

NumPy · Урок 6

Агрегирующие функции NumPy: sum, mean, min, max, std на реальных данных

Считаем статистику на данных сети кофеен: sum, mean, median, std и percentile, загадка axis=0 против axis=1 и nan-функции для пропусков.

агрегирующие функции numpynumpy среднее

NumPy · Урок 1

Что такое NumPy и как установить через pip: первый массив ndarray

Первый массив ndarray: создаём, сравниваем со списком, разбираем dtype и shape — и ускоряем сумму миллиона чисел примерно в сто раз.

чем ndarray отличается от списка pythonnumpy для анализа данных

sqlite3 · Урок 4

WHERE и фильтрация в SQL: условия, LIKE, BETWEEN и IN

Главный фильтр SQL: сравнения и логика с AND, OR, NOT, шаблоны LIKE с % и _, диапазоны BETWEEN, списки IN — и классическая ловушка = NULL.

sql wherewhere sql