Агрегации: count, sum и group_by
Отчёт «у кого сколько задач» одним запросом: база сама считает, группирует и фильтрует — Python получает готовые числа.
Редакция Питоники
До сих пор каждый запрос возвращал строки, а числа из них добирал Python: посчитать выполненные задачи — загрузить все и взять len(). Для десятка записей это норм, но вопрос «сколько» не требует вообще ни одной строки в Python: база умеет считать сама и возвращать одно число. Сегодня мы перестаём считать руками и начинаем задавать базе вопросы — она отвечает быстрее, чем мы успели бы загрузить данные.
Агрегация — это запрос, который возвращает не строки, а вычисленные по ним числа: база считает, Python получает готовый ответ. Инструментарий маленький: две функции из модуля sqlalchemy.func — count для количества и sum для суммы, — группировка group_by и фильтр по группам having. Из них собираются все отчёты мира: «у кого сколько задач», «сколько всего выполнено», «кто тянет больше всех».
Считаем: func.count и func.sum
Модуль func — это фабрика SQL-функций: func.count(), func.sum(...), func.max(...) превращаются в COUNT и SUM в генерируемом SQL. Самый короткий вопрос базе — «сколько всего строк»: func.count() без аргументов в паре с select_from.
from sqlalchemy import create_engine, ForeignKey, String, select, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session, relationship
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(40))
tasks = relationship("Task", back_populates="user")
class Task(Base):
__tablename__ = "tasks"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str] = mapped_column(String(80))
done: Mapped[int] = mapped_column(default=0)
points: Mapped[int] = mapped_column(default=1)
user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"))
user = relationship("User", back_populates="tasks")
engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
with Session(engine) as s:
anna = User(name="Анна")
boris = User(name="Борис")
s.add_all([anna, boris])
s.flush()
s.add_all([
Task(title="отчёт", done=1, points=5, user=anna),
Task(title="багрепорт", done=0, points=3, user=anna),
Task(title="презентация", done=1, points=4, user=anna),
Task(title="письмо", done=0, points=2, user=boris),
])
s.commit()
print("всего задач:", s.scalar(select(func.count()).select_from(Task)))
print("выполнено:", s.scalar(select(func.count()).select_from(Task).where(Task.done == 1)))
print("всего баллов:", s.scalar(select(func.sum(Task.points))))
всего задач: 4 выполнено: 2 всего баллов: 14
Три вопроса — три поездки в базу, каждая вернула одно число. Метод session.scalar() исполняет запрос и достаёт первое значение первой строки — ровно то, что нужно агрегату. Обрати внимание, куда перекочевали знакомые детали: where работает как всегда — сначала посчитали все задачи, потом только выполненные, — а func.sum(Task.points) сложил колонку баллов, вернув готовое число 14. Ни одна задача не загрузилась в Python — числа родились в базе.
Когда это важнее всего? На больших таблицах и в горячих местах приложения: боковая панель «вас ждут 5 задач» перерисовывается при каждом запросе страницы, и тянуть ради неё все задачи пользователя — расточительство в квадрате. Счётчик стоит базе микросекунды и не зависит от объёма данных: хоть пять задач, хоть пять миллионов — запрос вернёт одно и то же по форме число. Правило хорошего тона ORM: если из данных нужны только числа — это агрегация, а не select всех строк.
group_by: отчёт по группам
Одиночные числа — только половина дела. Настоящий отчёт звучит как «у кого сколько»: по каждой строке из users — число её задач. Это одна группировка: join собирает пары «автор — задача», group_by склеивает строки одного автора, func.count считает каждую группу.
from sqlalchemy import create_engine, ForeignKey, String, select, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session, relationship
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(40))
tasks = relationship("Task", back_populates="user")
class Task(Base):
__tablename__ = "tasks"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str] = mapped_column(String(80))
done: Mapped[int] = mapped_column(default=0)
points: Mapped[int] = mapped_column(default=1)
user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"))
user = relationship("User", back_populates="tasks")
engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
with Session(engine) as s:
anna = User(name="Анна")
boris = User(name="Борис")
s.add_all([anna, boris])
s.flush()
s.add_all([
Task(title="отчёт", done=1, points=5, user=anna),
Task(title="багрепорт", done=0, points=3, user=anna),
Task(title="презентация", done=1, points=4, user=anna),
Task(title="письмо", done=0, points=2, user=boris),
])
s.commit()
rows = s.execute(
select(User.name, func.count(Task.id))
.join(User.tasks)
.group_by(User.name)
.order_by(User.name)
).all()
for name, cnt in rows:
print(name, "-", cnt)
Анна - 3 Борис - 1
Один запрос — готовый отчёт. Механика внутри: join из прошлого урока собрал четыре пары «автор — задача», group_by(User.name) объявил, что строки с одинаковым именем — одна группа, и func.count(Task.id) посчитал строки в каждой. Цикл перебирает уже не задачи, а пары «группа — число»: отчёт мог бы иметь хоть миллион задач — в Python их по-прежнему две строки. Прошлые уроки сработали на этот запрос как детали конструктора: соединение из join-урока, условия, сортировка.
Так собирается большинство цифр, которые видит пользователь интерфейсов: счётчики в шапке раздела, строки сводки в письме, виджеты «активные проекты» на дашборде. Везде под красивой обёрткой — групповой запрос, который возвращает ровно столько строк, сколько групп, и не зависит от количества записей внутри групп. Заметь и формула отчёта: группирующая колонка в select, агрегат рядом, group_by — этот шаблон ты теперь пишешь быстрее, чем формулируешь вопрос.
Две детали, на которые стоит смотреть в каждом групповом запросе. Первая: order_by(User.name) — без него порядок групп не гарантирован, базы не обязаны возвращать группы в алфавитном порядке, и в отчёте хочется предсказуемости. Вторая: в результате нет Веры — она не завела ни одной задачи, пар в соединении для неё не нашлось, и внутренний join из прошлой недели её молча выбросил. Если отчёт обязан показывать и пустые строки — нужен внешний join.
from sqlalchemy import create_engine, ForeignKey, String, select, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session, relationship
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(40))
tasks = relationship("Task", back_populates="user")
class Task(Base):
__tablename__ = "tasks"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str] = mapped_column(String(80))
done: Mapped[int] = mapped_column(default=0)
points: Mapped[int] = mapped_column(default=1)
user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"))
user = relationship("User", back_populates="tasks")
engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
with Session(engine) as s:
anna = User(name="Анна")
boris = User(name="Борис")
vera = User(name="Вера")
s.add_all([anna, boris, vera])
s.flush()
s.add_all([
Task(title="отчёт", done=1, points=5, user=anna),
Task(title="багрепорт", done=0, points=3, user=anna),
Task(title="презентация", done=1, points=4, user=anna),
Task(title="письмо", done=0, points=2, user=boris),
])
s.commit()
rows = s.execute(
select(User.name, func.count(Task.id))
.outerjoin(User.tasks)
.group_by(User.name)
.order_by(User.name)
).all()
for name, cnt in rows:
print(name, "-", cnt)
Анна - 3 Борис - 1 Вера - 0
Вера на месте с честным нулём: внешнее соединение оставило её строку, а COUNT(Task.id) посчитал в ней ноль задач — ведь единственная присоединённая строка вся из NULL. Отсюда важная тонкость: именно count(Task.id) дал ноль, потому что считает непустые значения колонки. У него есть близнец func.count() без аргумента — это COUNT(*), подсчёт всех строк группы без разбора колонок, и в соединённой пустой группе он насчитал бы единицу.
from sqlalchemy import create_engine, ForeignKey, String, select, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session, relationship
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(40))
tasks = relationship("Task", back_populates="user")
class Task(Base):
__tablename__ = "tasks"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str] = mapped_column(String(80))
done: Mapped[int] = mapped_column(default=0)
points: Mapped[int] = mapped_column(default=1)
user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"))
user = relationship("User", back_populates="tasks")
engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
with Session(engine) as s:
anna = User(name="Анна")
boris = User(name="Борис")
vera = User(name="Вера")
s.add_all([anna, boris, vera])
s.flush()
s.add_all([
Task(title="отчёт", done=1, points=5, user=anna),
Task(title="багрепорт", done=0, points=3, user=anna),
Task(title="презентация", done=1, points=4, user=anna),
Task(title="письмо", done=0, points=2, user=boris),
])
s.commit()
rows = s.execute(
select(
User.name,
func.count(Task.id).label("tasks"),
func.count().label("rows"),
)
.outerjoin(User.tasks)
.group_by(User.name)
.order_by(User.name)
).all()
for r in rows:
print(r.name, "-", r.tasks, "и", r.rows)
Анна - 3 и 3 Борис - 1 и 1 Вера - 0 и 1
Разница вылезла ровно в одной строке — Верина: задач у неё ноль, но строка внешнего соединения существует, и COUNT(*) честно её сосчитал. Для «сколько у кого задач» правильный ответ даёт count(Task.id): он считает именно задачи. Метод .label("имя") дал колонкам результата внятные имена — без него они бы назывались как попало, а через label к ним удобно обращаться: r.tasks, r.rows.
Сумма по группам
func.sum устроен как count: внутри группы складывает значения колонки. Заменим счётчик задач на счётчик баллов — и отчёт покажет нагрузку, а не количество.
from sqlalchemy import create_engine, ForeignKey, String, select, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session, relationship
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(40))
tasks = relationship("Task", back_populates="user")
class Task(Base):
__tablename__ = "tasks"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str] = mapped_column(String(80))
done: Mapped[int] = mapped_column(default=0)
points: Mapped[int] = mapped_column(default=1)
user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"))
user = relationship("User", back_populates="tasks")
engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
with Session(engine) as s:
anna = User(name="Анна")
boris = User(name="Борис")
s.add_all([anna, boris])
s.flush()
s.add_all([
Task(title="отчёт", done=1, points=5, user=anna),
Task(title="багрепорт", done=0, points=3, user=anna),
Task(title="презентация", done=1, points=4, user=anna),
Task(title="письмо", done=0, points=2, user=boris),
])
s.commit()
rows = s.execute(
select(User.name, func.sum(Task.points).label("total"))
.join(User.tasks)
.group_by(User.name)
.order_by(User.name)
).all()
for r in rows:
print(r.name, "-", r.total)
Анна - 12 Борис - 2
Анна тащит двенадцать баллов из четырнадцати — цифра, которую не видно в списке задач, пока не сложишь руками. Любая другая функция модуля func подставляется так же: func.avg даст среднее, func.max и func.min — крайние значения. Принцип один: перечисли в select группирующую колонку и агрегат, добавь group_by — база вернёт отчёт.
Пара практических замечаний к суммам. Во-первых, sum по пустому набору возвращает не ноль, а NULL — в Python это None, и код вида r.total + 1 на пустой группе упадёт; лекарство — проверка на None или оборачивание в coalesce. Во-вторых, сумма обычно интереснее с сортировкой: добавив .order_by(func.sum(Task.points).desc()), получишь отчёт «с самых нагруженных» — агрегат, как и обычную колонку, можно класть в order_by.
having: фильтр по группам
К группам применимы свои фильтры: «оставь только тех, у кого две задачи и больше». Такое условие нельзя записать через where — на момент, где работает where, групп ещё нет. Для сформированных групп существует отдельный оператор having.
from sqlalchemy import create_engine, ForeignKey, String, select, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session, relationship
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(40))
tasks = relationship("Task", back_populates="user")
class Task(Base):
__tablename__ = "tasks"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str] = mapped_column(String(80))
done: Mapped[int] = mapped_column(default=0)
points: Mapped[int] = mapped_column(default=1)
user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"))
user = relationship("User", back_populates="tasks")
engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
with Session(engine) as s:
anna = User(name="Анна")
boris = User(name="Борис")
s.add_all([anna, boris])
s.flush()
s.add_all([
Task(title="отчёт", done=1, points=5, user=anna),
Task(title="багрепорт", done=0, points=3, user=anna),
Task(title="презентация", done=1, points=4, user=anna),
Task(title="письмо", done=0, points=2, user=boris),
])
s.commit()
rows = s.execute(
select(User.name, func.count(Task.id))
.join(User.tasks)
.group_by(User.name)
.having(func.count(Task.id) >= 2)
.order_by(User.name)
).all()
for name, cnt in rows:
print(name, "-", cnt)
Анна - 3
Борис отсеялся: его группа из одной задачи не прошла условие. Обрати внимание — having сравнивает не колонку, а агрегат: условие формулируется про count или sum, чего where не умеет в принципе. Запросы могут использовать оба фильтра сразу: where отберёт строки до группировки, having — группы после неё.
from sqlalchemy import create_engine, ForeignKey, String, select, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session, relationship
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(40))
tasks = relationship("Task", back_populates="user")
class Task(Base):
__tablename__ = "tasks"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str] = mapped_column(String(80))
done: Mapped[int] = mapped_column(default=0)
points: Mapped[int] = mapped_column(default=1)
user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"))
user = relationship("User", back_populates="tasks")
engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
with Session(engine) as s:
anna = User(name="Анна")
boris = User(name="Борис")
s.add_all([anna, boris])
s.flush()
s.add_all([
Task(title="отчёт", done=1, points=5, user=anna),
Task(title="багрепорт", done=0, points=3, user=anna),
Task(title="презентация", done=1, points=4, user=anna),
Task(title="письмо", done=0, points=2, user=boris),
])
s.commit()
rows = s.execute(
select(User.name, func.sum(Task.points).label("open_points"))
.join(User.tasks)
.where(Task.done == 0)
.group_by(User.name)
.having(func.sum(Task.points) > 2)
.order_by(User.name)
).all()
for r in rows:
print(r.name, "-", r.open_points)
Анна - 3
Запрос отвечает на вопрос: «у кого на невыполненных задачах больше двух баллов?» Where сначала выбросил выполненные строки — остались багрепорт Анны (3 балла) и письмо Бориса (2), — группы посчитали суммы, а having вычеркнул Борисов результат, не дотянувший до трёх. Порядок конвейера читается по цепочке: join собирает, where фильтрует строки, group_by режет на группы, having фильтрует группы, order_by выстраивает результат.
Такой конвейер — не обязательный ритуал: участвуют только нужные ступени. Без where — считаются все строки; без having — остаются все группы; без order_by — база сама решит, как отдать (и мы не позволим ей этого в отчётах). Единственная связка, которой не существует: having без group_by не имеет смысла — фильтровать группы можно лишь тогда, когда они образованы. В реальном коде отчётные запросы имеет смысл заворачивать в функции с говорящими именами вроде tasks_per_user(s) — отчеты редко пишутся один раз, а повторный запрос удобно переиспользовать.
SELECT "user".name, COUNT(task.id)
FROM "user"
INNER JOIN task ON task.user_id = "user".id
GROUP BY "user".name
HAVING COUNT(task.id) >= 2
ORDER BY "user".name;
Что дальше
Отчёты покорились: одиночные числа через scalar, таблица «кто — сколько» через group_by, нагрузка через sum, отбор сильных через having. Вместе с join из прошлого урока это полный набор аналитики среднего приложения — и всё ещё без единой строки SQL: каждый запрос собран из Python-выражений, проверяемых ещё до обращения к базе. Но база отвечает не только числами: порядок строк в ней не гарантирован, а отчётам и лентам нужен порядок и страницы — «топ-3 самых тяжёлых задач», «вторая страница по десять записей». Порядок, лимиты и смещения — в уроке 13.
Сколько, сколько у каждого, кто перегружен — три вопроса, три запроса, ноль загруженных строк: агрегации делают базу вычислителем, а не свалкой для len().
Сначала предскажи ответ в голове — это главный навык программиста.
from sqlalchemy import create_engine, String, select, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session
class Base(DeclarativeBase):
pass
class Task(Base):
__tablename__ = "tasks"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str] = mapped_column(String(80))
done: Mapped[int] = mapped_column(default=0)
engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
with Session(engine) as s:
s.add_all([
Task(title="а", done=1),
Task(title="б", done=0),
Task(title="в", done=1),
])
s.commit()
print(s.scalar(select(func.count()).select_from(Task).where(Task.done == 1)))
from sqlalchemy import create_engine, ForeignKey, String, select, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session, relationship
class Base(DeclarativeBase):
pass
class User(Base):
__tablename__ = "users"
id: Mapped[int] = mapped_column(primary_key=True)
name: Mapped[str] = mapped_column(String(40))
tasks = relationship("Task", back_populates="user")
class Task(Base):
__tablename__ = "tasks"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str] = mapped_column(String(80))
user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id"))
user = relationship("User", back_populates="tasks")
engine = create_engine("sqlite:///:memory:")
Base.metadata.create_all(engine)
with Session(engine) as s:
anna = User(name="Анна")
boris = User(name="Борис")
s.add_all([anna, boris])
s.flush()
s.add_all([
Task(title="а", user=anna),
Task(title="б", user=anna),
Task(title="в", user=boris),
])
s.commit()
rows = s.execute(
select(User.name, func.count(Task.id))
.join(User.tasks)
.group_by(User.name)
.having(func.count(Task.id) >= 2)
.order_by(User.name)
).all()
for name, cnt in rows:
print(name, cnt)
1. Как узнать количество строк в таблице, не загружая их?
2. Что делает group_by(User.name) в запросе с func.count()?
3. Чем having отличается от where?
4. Почему в отчёте с обычным join не оказалось пользователя без задач?
5. Что вернёт session.scalar(select(func.sum(Task.points))) на пустой таблице?
Собери отчёт о загрузке: найди авторов, у которых две задачи и больше, и выведи каждого с количеством задач. Авторы: Анна (задачи «отчёт», «багрепорт», «презентация»), Борис (задача «письмо»). Один запрос: join по связи, группировка по имени, агрегат count, условие по группам, сортировка по имени.
Как посчитать количество записей в таблице через SQLAlchemy?
Агрегатом: session.scalar(select(func.count()).select_from(Task)) вернёт число строк, не загружая их в Python. С условием — добавь where: подсчёт выполненных задач это тот же запрос с фильтром. Загружать все объекты ради len() стоит только когда записи всё равно нужны.
Как получить отчёт «сколько записей у каждой группы»?
Группировкой: select(User.name, func.count(Task.id)).join(User.tasks).group_by(User.name) — по строке на группу. Чтобы пользователи без записей попадали в отчёт с нулём, заменяй join на outerjoin и считай func.count(колонка), а не func.count() (COUNT(*) насчитает пустой строке единицу).
Почему условие по count не работает в where?
Where выполняется до группировки, когда агрегаты ещё не посчитаны — база отклонит запрос. Для условий про группы существует having: group_by(...).having(func.count(Task.id) >= 2). Правило выбора: про конкретные строки — where, про «сколько в группе» — having.
Чем func.count() отличается от func.count(колонка)?
func.count() — это COUNT(*): считает все строки группы. func.count(колонка) — COUNT(колонка): считает строки, где значение колонки не NULL. Разница проявляется во внешних соединениях: строка пользователя без задач получит COUNT(*) = 1, но COUNT(task.id) = 0 — именно поэтому в отчётах с нулями считают колонку.
Понравился урок? Сошлитесь на него
«Агрегация — это запрос, который возвращает не строки, а вычисленные по ним числа: база считает, Python получает готовый ответ.»
Скопируйте готовую ссылку в формате HTML, Markdown или чистый адрес и вставьте в статью на Habr, VC, Telegram-канал или свой блог — так о проекте узнают новые читатели.
Что читать дальше
SQLAlchemy · Урок 11
Join в ORM: запросы по связям
Навигация читает связь у готового объекта, join фильтрует по чужой таблице в одном запросе: все задачи автора по имени.
SQLAlchemy · Урок 13
Сортировка и лимиты: order_by, limit
Топ-3, страницы, «сначала свежие» — три метода на запрос: order_by задаёт порядок, limit режет размер, offset листает.
Похожие уроки по темам
Подобраны автоматически по пересечению тем и ключевых слов.
sqlite3 · Урок 6
Агрегаты и GROUP BY в SQL: COUNT, SUM, AVG и HAVING
Считаем, суммируем и усредняем по всей таблице и по группам: COUNT, SUM, AVG, GROUP BY и фильтр групп HAVING.
sql group by countsql having
sqlite3 · Урок 7
JOIN в SQL: соединяем таблицы INNER и LEFT JOIN
Данные живут в разных таблицах — JOIN соединяет их по ключу: INNER оставляет только пары, LEFT бережёт и одиночек.
join group by count
SQLAlchemy · Урок 6
select: первые запросы к таблице
select(Task) возвращает строки-объекты: перебор циклом, одна строка через first(), одно значение через scalar().
sqlalchemy select запросыsqlalchemy select