Агрегации и GROUP BY

Агрегации sqlc начинаются с неприятного признания: пока трекер умел только читать и перечислять. ListOpenTodos отдаёт срез задач — а «сколько открытых?» отвечает len(items) в Go, «потрачено всего?» — цикл с аккумулятором, «кто завалил трекер?» — ручная сортировка. Каждый такой ответ гоняет из базы все строки, чтобы выбросить их после сложения: байт-хореография ради одного числа. В SQL для этого есть отдельный класс функций — агрегаты: они съедают набор строк и возвращают одну строку с итогом, и база считает всё сама, не вывозя данные наружу.

Тринадцатый урок переносит отчёты трекера из Go в SQL. Инструментов новых не появится — те же файлы запросов, те же аннотации :one и :many, те же правила Row-структур из двенадцатого урока. Меняется содержимое поля: вместо колонки схемы в Row-структуре оказывается результат функции — count, sum, avg, min или max, — и тип этого поля генератор вычисляет по своим правилам, которые сильно отличаются от правил колонок. Отсюда сюжет урока: сканирование агрегатов — не продолжение сканирования колонок, а отдельная дисциплина со своими минами. Добавьте сюда группировки, HAVING, подсчёты по условию и умножение строк поверх джойна — и получится полная карта темы.

Как проверять себя в этом уроке

Честно о формате, как и во всём курсе: sqlc-генератору нужны файлы схемы, поэтому в песочнице уроки курса не запускаются — runner-ов здесь нет. Понимание проверяют quiz, predict, fill, order и флешкарты: они гоняют то же, что вы повторите командой sqlc generate у себя. Каждый сгенерированный фрагмент урока получен запуском генератора версии 1.27 на канонической схеме трекера — не пересказ, а фактический вывод.

sqlc group by: считаем в базе, а не в Go#

Ключ «sqlc group by» — это два независимых навыка в одном запросе, и их стоит развести сразу. Агрегат — функция над набором строк, возвращающая одно значение: count считает строки, sum складывает, avg усредняет, min и max ищут крайности. GROUP BY — оператор, который разрезает набор строк на группы и запускает агрегаты отдельно в каждой. Один агрегат без группировки сжимает всю выборку в одну строку-итог; GROUP BY превращает одну выборку в столько строк-итогов, сколько групп. Формы возврата отсюда следуют автоматически: агрегат без GROUP BY — это :one, ровно одна строка; GROUP BY — это :many, по строке на группу. Аннотация становится не выбором стиля, а прямым следствием SQL — если ошибётесь, ошибка приедет в рантайме: :one на группировке прочтёт только первую группу и молча выбросит остальные.

Второй навык — чтение Row-структуры. Для генератора агрегации sqlc — просто ещё одно выражение в SELECT-списке: правило «больше одной колонки — структура из имени запроса плюс Row» из двенадцатого урока работает без поправок, имя поля берётся из AS, а тип выводится по каталогу функций. Именно здесь живут самые неожиданные решения генератора: сумма целых чисел получает тип, который врёт на пустой базе, среднее становится float64, а минимум и максимум — вообще interface{}. Почему так и как это чинить — сюжет следующих разделов. Файл для отчётов заведём по конвенции, которую двенадцатый урок утвердил для джойнов: одна тема выборок — один файл.

Файл aggregates.sql: три первых отчёта

-- db/queries/aggregates.sql: отчёты — новая тема выборок, новый файл

-- name: CountOpenTodos :one
SELECT count(*) FROM todos
WHERE done = false AND deleted_at IS NULL;

-- name: CountNotes :one
SELECT count(note) AS with_note, count(*) AS total
FROM todos
WHERE deleted_at IS NULL;

-- name: TotalSpent :one
SELECT sum(amount) FROM spendings;

Три формы разом. CountOpenTodos — счётчик с фильтром: бейдж «открытых задач» из седьмого урока, дисциплина мягкого удаления на месте. CountNotes — два агрегата в одной строке-итоге: сколько живых задач имеет заметку и сколько их всего; note — nullable-колонка из девятого урока, сейчас она станет учебным материалом. TotalSpent — сумма по журналу spendings, который появился у трекера вместе с транзакциями. Все три аннотированы :one: группировки нет, агрегат возвращает ровно одну строку — строку-итог.

Что сгенерировано: int64 и одна Row-структура

// db/sqlc/aggregates.sql.go — сгенерировано (pgx/v5, v1.27)
const countOpenTodos = `-- name: CountOpenTodos :one
SELECT count(*) FROM todos
WHERE done = false AND deleted_at IS NULL
`

func (q *Queries) CountOpenTodos(ctx context.Context) (int64, error) {
        row := q.db.QueryRow(ctx, countOpenTodos)
        var count int64
        err := row.Scan(&count)
        return count, err
}

type CountNotesRow struct {
        WithNote int64 // count(note): только не-NULL заметки
        Total    int64 // count(*): все строки
}

func (q *Queries) CountNotes(ctx context.Context) (CountNotesRow, error) {
        row := q.db.QueryRow(ctx, countNotes)
        var i CountNotesRow
        err := row.Scan(&i.WithNote, &i.Total)
        return i, err
}

const totalSpent = `-- name: TotalSpent :one
SELECT sum(amount) FROM spendings
`

func (q *Queries) TotalSpent(ctx context.Context) (int64, error) {
        row := q.db.QueryRow(ctx, totalSpent)
        var sum int64 // колонка без алиаса везёт имя функции — даже тут
        err := row.Scan(&sum)
        return sum, err
}

CountOpenTodos вернул голый скаляр int64: одна колонка — структуры нет, правило седьмого урока. CountNotes с двумя колонками получил CountNotesRow — имя запроса плюс Row, поля из алиасов with_note и total; правило двенадцатого урока про Row-структуры переносится на агрегаты один в один. Тип везде int64: count в PostgreSQL имеет тип bigint, а bigint — это int64. И обратите внимание на переменную var sum int64 в TotalSpent: без алиаса колонка называется по функции, и имя sum протекло даже в сгенерированную переменную. Пока всё выглядит мирно — но у TotalSpent спрятана мина, и вскроется она на пустой базе.

COUNT: звёздочка против колонки#

count — единственный агрегат, у которого NULL не бывает вообще: даже на пустой таблице он вернёт одну строку с нулём — седьмой урок показывал это на CountOpenTodos. Зато внутри count прячется развилка, о которой седьмой урок честно сказал «вернёмся, когда дойдём до отчётов». Отчёты настали. count(*) считает строки целиком: для него не существует NULL-строк, любая строка — единица счёта. count(колонка) считает значения: из подсчёта выпадают строки, где колонка NULL. На NOT NULL-колонках развилка невидима — счёт совпадает байт в байт; на nullable — это два разных вопроса: «сколько задач живых» и «сколько живых задач имеют заметку». CountNotes из файла выше отвечает на оба разом: total — строками, with_note — значениями.

Правило чтения одно: спросите себя, что вы считаете — строки или значения, — и выбирайте форму осознанно. Ловушка обратного знака тоже существует: count(колонка) на колонке, которая nullable и часто пуста, вернёт неожиданно малое число, и код, ожидающий общий счёт строк, тихо сломается — без ошибок, просто неверный бейдж в интерфейсе. Третий вариант completes семейство: count(DISTINCT колонка) считает уникальные значения — нам он понадобится в конце урока как лекарство от умноженных строк. А вот обёрток count не требует никогда: значение есть всегда, int64 без доплат.

Проверка: count(note) на живых данных

В трекере 10 живых задач, у четырёх из них поле note пустое (NULL). Что вернёт запрос SELECT count(note) FROM todos WHERE deleted_at IS NULL?

SUM, AVG, MIN, MAX: NULL-агрегаты#

У остальных членов семьи — общая тайна, зафиксированная в стандарте SQL и в документации PostgreSQL: на пустом наборе строк sum, avg, min и max возвращают NULL. Не ноль, не ошибку — NULL. «Сумма пустого множества чисел» математически честно не определена, и база выражает это третьим состоянием. count из этой компании выбивается: у него есть естественный ответ «ноль строк — ноль», поэтому он и единственный, кто возвращает значение всегда. Вторая половина тайны: NULL-агрегаты прячутся и в группировках — если все значения внутри группы NULL, сумма группы тоже NULL; для NOT NULL-колонок, как amount в spendings, это невозможно, но помните о существовании варианта.

Теперь позиция генератора — и она жёстче, чем вы ждёте. sqlc резолвит агрегат как обычную функцию по своему каталогу: sum по целым колонкам получает тип bigint и, главное, считается не-nullable — генератор уверен, что значение будет всегда. Обёртки, которые вы знаете из девятого урока, здесь не появятся: nullable-типы у sqlc вырастают из nullable-колонок и внешних соединений, а агрегат он считает гарантией. В результате сгенерированный int64 обещает то, чего база не обещала: на пустой таблице spendings сумма вернёт NULL, скан упадёт ошибкой, и произойдёт это в рантайме — компилятор и генерация пройдут беззвучно. avg ведёт себя иначе: генератор ставит float64 — тип с погрешностями, о котором четвёртый урок предупреждал на numeric. min и max и вовсе полиморфные: их тип результата зависит от аргумента, и генератор сдаётся, ставя interface{}. Три разные болезни — три лечения, и все три делаются руками в SQL.

Каст возвращает тип: три лечения

-- name: TotalSpentSafe :one
SELECT coalesce(sum(amount), 0)::bigint AS total FROM spendings;

-- name: AvgSpending :one
SELECT avg(amount) AS avg_amount FROM spendings;

-- name: AvgCostExact :one
SELECT avg(cost)::numeric AS avg_cost FROM todos
WHERE deleted_at IS NULL;

-- name: EarliestDeadline :one
SELECT max(deadline)::timestamptz AS latest FROM todos
WHERE deleted_at IS NULL;

// сгенерировано (pgx/v5, v1.27): сигнатуры методов
func (q *Queries) TotalSpentSafe(ctx context.Context) (int64, error)
func (q *Queries) AvgSpending(ctx context.Context) (float64, error)
func (q *Queries) AvgCostExact(ctx context.Context) (pgtype.Numeric, error)
func (q *Queries) EarliestDeadline(ctx context.Context) (pgtype.Timestamptz, error)

// те же запросы без кастов:
// coalesce(sum(amount), 0) AS total → interface{}
// max(deadline) AS latest           → interface{}

COALESCE заменяет NULL нулём — страховка от пустого набора, — но сам coalesce в каталоге функций sqlc не живёт, и генератор сдаётся: DataType any, поле interface{}. Каст ::bigint возвращает int64 — и заодно делает выражение честным: на пустой базе вернётся ноль, а не падение скана. AVG без каста даёт float64: pgx умеет сканировать numeric во float64, но копейки в плавающей точке живут плохо — каст ::numeric возвращает pgtype.Numeric по правилам четвёртого урока. MIN и MAX полиморфны, без каста — interface{}; каст к конкретному типу восстанавливает типизацию. Каст здесь — не грязный хак, а аннотация типа прямо в SQL, читаемая и генератором, и человеком.

Предскажите судьбу TotalSpent

Прочитайте код и выберите, что он выведет:

// журнал spendings только что создан миграцией — таблица пуста
total, err := queries.TotalSpent(ctx)
// что произойдёт дальше?

GROUP BY: Row-структура отчёта#

GROUP BY разрезает выборку на группы и отдаёт по строке-итогу на группу — значит, форма возврата меняется с :one на :many, и метод возвращает срез структур. Row-структура собирается по тем же правилам, что в двенадцатом уроке: имя запроса плюс Row, поля в порядке SELECT-списка, имя каждого поля — из алиаса AS. Алиасы в агрегатных запросах — не украшение, а контракт: todo_count станет TodoCount, user_name — UserName; без алиаса функция везёт своё имя, и рядом с Count, Sum и Avg жить можно, но подпись отчёта получается машинной. Каждую колонку, по которой группируете и которую выбираете, называйте словами предметной области — вы пишете API отчёта.

Первое послабление, которое стоит знать: группировать можно не всё, что выбираете. PostgreSQL умеет выводить функциональные зависимости: если в GROUP BY стоит первичный ключ таблицы, остальные колонки этой таблицы выбирать можно без группировки — они однозначно определяются ключом. GROUP BY u.id достаточно, чтобы выбрать u.name: у одного id одно имя. Классика «GROUP BY u.id, u.name» работает всегда и читается без знания этой фичи; группировка по ключу короче и не ломается, когда в SELECT добавят ещё колонку. Второе послабление честнее назвать подставой: sqlc вообще не проверяет правило группировки — запрос, где выбранная колонка не в GROUP BY, пройдёт генерацию и упадёт на живой базе с кодом 42803. Об этом — отдельный раздел ниже. И напоминание из двенадцатого урока: при LEFT JOIN фильтр deleted_at живёт в ON, иначе соединение выродится во внутреннее и группы «пустых» пользователей исчезнут.

CountTodosPerUser: отчёт по группировке

-- name: CountTodosPerUser :many
SELECT u.name AS user_name, count(t.id) AS todo_count
FROM users u
LEFT JOIN todos t ON t.user_id = u.id AND t.deleted_at IS NULL
GROUP BY u.id
ORDER BY todo_count DESC;

-- name: TodoStatsByUser :many
SELECT t.user_id, t.done, count(*) AS n
FROM todos t
WHERE t.deleted_at IS NULL
GROUP BY t.user_id, t.done;

// сгенерировано (pgx/v5, v1.27)
type CountTodosPerUserRow struct {
        UserName  string // из AS user_name
        TodoCount int64  // из AS todo_count: агрегат не бывает NULL
}

type TodoStatsByUserRow struct {
        UserID pgtype.Int8 // nullable-колонка — обёртка из девятого урока
        Done   bool
        N      int64
}

func (q *Queries) CountTodosPerUser(ctx context.Context) ([]CountTodosPerUserRow, error) {
        rows, err := q.db.Query(ctx, countTodosPerUser)
        if err != nil {
                return nil, err
        }
        defer rows.Close()
        var items []CountTodosPerUserRow
        for rows.Next() {
                var i CountTodosPerUserRow
                // сканирование агрегатов — то же, что и колонок: поле за полем
                if err := rows.Scan(&i.UserName, &i.TodoCount); err != nil {
                        return nil, err
                }
                items = append(items, i)
        }
        if err := rows.Err(); err != nil {
                return nil, err
        }
        return items, nil
}

GROUP BY u.id — группировка по первичному ключу: PostgreSQL выводит name из ключа, sqlc пропускает без вопросов. ORDER BY todo_count сортирует по алиасу SELECT — сортировка видит выходные имена, WHERE — нет (скоро объясним почему). Метод — обычный :many из седьмого урока: Query, rows.Next, Scan, rows.Err; сканирование агрегатов не отличается от сканирования колонок, отличия сидят в типах полей. TodoStatsByUser показывает группировку по паре колонок: строка на каждую комбинацию «исполнитель × статус», а сгруппированная nullable-колонка user_id остаётся pgtype.Int8 — правила девятого урока действуют и внутри GROUP BY. Пользователь без живых задач при LEFT JOIN не исчезнет: его группа пустая, а count отработает в ноль — это как раз то, зачем двенадцатый урок ставил фильтр в ON.

HAVING против WHERE: порядок выполнения#

Чтобы понять разницу, нужна карта того, как база исполняет SELECT — не в порядке написания, а в порядке вычисления: FROM, затем WHERE, затем GROUP BY, затем HAVING, затем SELECT с его алиасами, затем ORDER BY и LIMIT. WHERE работает до группировки: он режет исходные строки, и в его мире агрегатов ещё не существует — считать не по чему. HAVING работает после группировки: группы уже собраны, агрегаты уже посчитаны, и условие фильтрует именно группы — «оставь только тех, у кого count больше двух». Алиасы SELECT живут на шаге позже WHERE: там, где выполняется WHERE, имя todo_count ещё не родилось, поэтому фильтр по алиасу в WHERE невозможен — а в ORDER BY возможен, потому что сортировка идёт после SELECT. HAVING же видит агрегаты напрямую: HAVING count(t.id) > 0 или с параметром HAVING count(t.id) >= $1 — параметры работают и там.

Теперь честность о позиции генератора. sqlc проверяет имена: каждую колонку он сверяет со схемой, и опечатка уронит генерацию — двенадцатый урок показывал это на соединениях. Но правила выполнения — обязанность базы. Нарушите правило группировки (выбрали колонку, которой нет в GROUP BY и нет под агрегатом) — sqlc сгенерирует метод; напишете агрегат в WHERE — sqlc сгенерирует метод; а живой PostgreSQL ответит ошибкой 42803: column must appear in the GROUP BY clause или aggregate functions are not allowed in WHERE. Практический вывод: отчёты тестируйте на настоящей базе, а не только на генерации — генератор гарантирует типы, но не право запроса на жизнь.

Проверка: где проверяются правила

В выборку дописали условие WHERE deleted_at IS NULL AND count(*) > 0. Что произойдёт?

COUNT FILTER: WHERE внутри агрегата#

Отчёт «сколько всего и сколько из них закрытых» тянет два запроса, пока вы не узнаете про подсчёты по условию. Первая дорога — FILTER, возможность синтаксиса PostgreSQL: условие пишется прямо внутри агрегата — count() FILTER (WHERE t.done) — и сужает набор строк для одного этого агрегата, не трогая соседние. В одном SELECT спокойно живут count() AS total и count() FILTER (WHERE t.done) AS done_count: каждый со своей учётной политикой, одна поездка в базу. Параметры внутрь FILTER тоже попадают: count() FILTER (WHERE t.priority > $1) сгенерируется с обычным параметром — проверено на генераторе 1.27. Вторая дорога — CASE внутри агрегата: sum(CASE WHEN t.done THEN 1 ELSE 0 END) — тот же счётчик, собранный из единиц и нулей. CASE старше, многословнее и переносимее: он умеет не только флаговые подсчёты, но и условные значения — взвешенные суммы, раздельные суммы по веткам.

Выбирать стоит по вопросу, а не по вкусу: «сколько строк подходит под условие» — FILTER, читается как приговор; «сколько наберётся значений по правилу» — CASE. В сгенерированном коде разницы нет: оба дают int64, оба называют поле из AS, оба не бывают NULL. Единственная ловушка — забыть ELSE в CASE: без него ветка без условия вернёт NULL, и sum молча проигнорирует такие строки — в счётчике из единиц и нулей это спасает, а в сумме по суммам может тихо изменить итог.

FILTER и CASE: два счётчика закрытых

-- name: DoneCountFiltered :many
SELECT u.name,
       count(*) AS total,
       count(*) FILTER (WHERE t.done) AS done_count
FROM users u
LEFT JOIN todos t ON t.user_id = u.id AND t.deleted_at IS NULL
GROUP BY u.id, u.name;

-- name: DoneCountCase :many
SELECT u.name,
       sum(CASE WHEN t.done THEN 1 ELSE 0 END) AS done_sum
FROM users u
LEFT JOIN todos t ON t.user_id = u.id AND t.deleted_at IS NULL
GROUP BY u.id, u.name;

// сгенерировано (pgx/v5, v1.27)
type DoneCountFilteredRow struct {
        Name      string
        Total     int64
        DoneCount int64 // FILTER: условие живёт внутри агрегата
}

type DoneCountCaseRow struct {
        Name    string
        DoneSum int64 // sum по CASE: sum(integer) → bigint → int64
}

Два отчёта отвечают на один вопрос разными средствами. FILTER не участвует в группировке и не мешает соседнему total — строки, не подошедшие под условие, просто не считаются этим агрегатом. CASE возвращает integer (0 или 1), sum от integer даёт bigint, bigint — int64: арифметика типов та же, что у count. GROUP BY u.id, u.name здесь написан «в лоб» — для контраста с группировкой по ключу из предыдущего блока. Поля int64 без обёрток: агрегатные счётчики не бывают NULL.

Предскажите отчёт с числами

Прочитайте код и выберите, что он выведет:

-- users: Аня, Боря
-- todos: у Ани три живые задачи (две done) и одна удалённая;
--        у Бори одна живая задача (не done)
SELECT u.name,
       count(*) AS total,
       count(*) FILTER (WHERE t.done) AS done_count
FROM users u
LEFT JOIN todos t ON t.user_id = u.id AND t.deleted_at IS NULL
GROUP BY u.id, u.name
ORDER BY total DESC;

Умножение строк: двойной подсчёт#

Обещание двенадцатого урока наступило: почему группировка поверх джойна так любит умножать строки. Механика проста и безжалостна. JOIN один-ко-многим порождает строку на каждую пару «родитель × ребёнок»: задача с двумя платежами — две строки, с тремя — три. GROUP BY собирает эти строки обратно в группы, но работает уже по парам, и любой агрегат видит размноженный мир. Родительские колонки в каждой паре повторяются, детские — нет: это асимметрия и есть источник лжи. Числовой пример: задача «Купить домен» стоит 100.00, по ней два платежа по 50. Запрос с соединением spendings даст две строки; count(*) вернёт 2, хотя задача одна; sum(t.cost) вернёт 200 — стоимость задвоена, потому что parent-колонка продублировалась в каждой паре. При этом sum(s.amount) вернёт честные 100: детская колонка в каждой паре своя. Счётчик по детям врёт вверх, сумма по родителю врёт кратно числу детей — и никаких ошибок, только неверные цифры в продакшене.

Три лечения, ровно те, что двенадцатый урок перечислил списком. Первое: агрегировать детей до соединения — подзапрос с GROUP BY todo_id и суммой, соединённый с задачами как с обычной таблицей; каждый агрегат считает свой собственный мир, умножать нечего. Второе: count(DISTINCT t.id) — счётчик родителя, разворачивающий дубликаты; для сумм он не спасает, DISTINCT от суммы не существует. Третье: EXISTS — полусоединение: «есть ли у задачи хоть один платёж» превращается в булево условие без умножения строк вовсе. Выбор по вопросу: сумма по детям — подзапрос; счёт родителя — DISTINCT; факт существования — EXISTS.

Проверка: двойной подсчёт в цифрах

Задача «Купить домен»: cost 100.00, два платежа по 50.00 в spendings. Что вернёт запрос SELECT t.title, sum(t.cost) AS total FROM todos t JOIN spendings s ON s.todo_id = t.id WHERE t.deleted_at IS NULL GROUP BY t.id?

Шпаргалка: агрегат в SELECT и тип поля#

Выражение в SELECT	Поле Row-структуры	Что важно
count(*)	N int64	строка-итог всегда, ноль на пустом наборе
count(note)	WithNote int64	считает не-NULL значения, не строки
count(*) FILTER (WHERE t.done) AS done_count	DoneCount int64	условие внутри агрегата; параметры работают
sum(amount)	Spent int64	пустой набор вернёт NULL — падение скана: sqlc верит NOT NULL
coalesce(sum(amount), 0)::bigint	Total int64	без каста coalesce даёт interface{}; с кастом — ноль и тип
avg(amount) / avg(cost)	Avg float64	pgx сканирует numeric; для денег берите ::numeric
min(id), max(deadline)	Lo interface{}	полиморфные функции не выводятся — каст обязателен
max(deadline)::timestamptz	Latest pgtype.Timestamptz	каст возвращает типизацию; t.user_id в GROUP BY — pgtype.Int8

Пять правил агрегаций#

Аннотации не меняются: агрегат без GROUP BY — :one, одна строка-итог; с GROUP BY — :many, строка на группу. Row-структура — имя запроса плюс Row, имя поля — из AS; сканирование агрегатов повторяет сканирование колонок.
count — единственный агрегат без NULL: ноль на пустом наборе, int64 всегда. count(*) считает строки, count(колонка) — не-NULL значения, count(DISTINCT) — уникальные значения.
sum, avg, min и max на пустом наборе возвращают NULL, а sqlc ставит обычные типы и обёрток не даёт: страховка пишется руками — coalesce(sum(...), 0)::bigint даёт ноль и честный int64.
Каст возвращает тип: coalesce и min/max без каста — interface{}, avg — float64; ::bigint, ::numeric и ::timestamptz возвращают int64, pgtype.Numeric и pgtype.Timestamptz.
Порядок выполнения: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. WHERE не видит агрегатов и алиасов, HAVING фильтрует группы, ORDER BY видит алиасы; правила выполнения sqlc не проверяет — 42803 приедет с живой базы.

Три ловушки агрегаций

Первая: TotalSpent на свежей базе падает в Scan — пустой набор вернул NULL в int64. Любой отчёт проверяйте на пустых данных, а страховку coalesce с кастом пишите сразу. Вторая: sqlc не проверяет ни правило группировки, ни запрет агрегатов в WHERE — метод сгенерируется и скомпилируется, а упадёт при первом вызове на живом PostgreSQL с кодом 42803; генерация гарантирует типы, но не право запроса на жизнь. Третья: умножение строк поверх джойна один-ко-многим — count(*) считает детские строки, sum по родительской колонке растёт кратно числу детей; сверяйте цифры отчёта с ручным счётом на данных, где у записи двое детей.

Восстановите отчёт без умножения строк

Четыре пропуска: агрегат внутри подзапроса, ключ группировки детей, ключ соединения, дисциплина мягкого удаления.

-- name: CostPerTodo :many SELECT t.title, sq.spent FROM todos t JOIN ( SELECT todo_id, (amount) AS spent FROM spendings GROUP BY ) sq ON sq.todo_id = t. WHERE t. IS NULL;

Соберите отчёт по строкам

Отчёт «у каждого пользователя: всего задач и сколько закрытых»: аннотация, два агрегата с алиасами, соединение с фильтром в ON, группировка.

Строки

Ваша программа

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

Карточки: агрегации

Термин

Агрегаты и аннотации

нажмите, чтобы перевернуть

Определение

Без GROUP BY агрегат возвращает одну строку-итог → :one; с GROUP BY — строку на группу → :many. Row-структура собирается по правилам двенадцатого урока: имя запроса плюс Row, поле из AS. Скан агрегатов не отличается от скана колонок — отличаются типы полей.

1 / 6

Что дальше#

Агрегации встали на место: count как единственный без-NULL-агрегат и его развилка «строки против значений», семейство sum/avg/min/max с NULL на пустом наборе и типами от генератора, касты как способ вернуть типизацию, GROUP BY по ключу и по парам, HAVING после WHERE с честной картой порядка выполнения, FILTER и CASE для подсчётов по условию и умножение строк поверх джойна с тремя лекарствами. Трекер умеет спрашивать «сколько» — и знает, почему цифру из отчёта стоит сверить с ручным счётом.

Отчёты посчитаны — теперь их надо листать: список на тысячу задач нельзя отдавать целиком. Четырнадцатый урок «Пагинация: LIMIT/OFFSET и keyset» сравнит два подхода — OFFSET, который пересчитывает пропущенное, и курсорную пагинацию, которая листает по ключу, — и научит трекер отдавать данные страницами. Если понадобится освежить формы :one и :many — они в седьмом уроке; nullable-обёртки и указатели — в девятом; Row-структуры джойнов — в двенадцатом; план остальных тем — на странице курса sqlc.

Песочница урока

Редактируйте код и запускайте его прямо здесь — интерпретатор работает в браузере, ничего устанавливать не нужно. За каждый запуск — +2 XP.

Вывод

Запустите код, чтобы увидеть вывод.

Коротко: что запомнить

Агрегат без GROUP BY — :one (одна строка-итог), с GROUP BY — :many (строка на группу); Row-структура собирается по правилам двенадцатого урока, имя поля — из AS.
count — единственный агрегат без NULL: ноль на пустом наборе и int64 всегда; count(*) считает строки, count(колонка) — не-NULL значения, count(DISTINCT) — уникальные.
sum, avg, min, max на пустом наборе возвращают NULL; sqlc ставит обычные типы и обёрток не даёт — NULL-агрегаты стреляют ошибкой скана в рантайме; страховка — coalesce(..., 0)::bigint.
Каст возвращает тип: coalesce и min/max без каста — interface{}, avg — float64; ::bigint, ::numeric и ::timestamptz дают int64, pgtype.Numeric и pgtype.Timestamptz.
GROUP BY по первичному ключу разрешает выбирать зависимые колонки без группировки; по nullable-колонке — оставляет pgtype.Int8; sqlc правило группировки не проверяет.
Порядок выполнения: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT; WHERE не видит агрегатов и алиасов, HAVING фильтрует группы — ошибки 42803 приедут с живой базы.
JOIN один-ко-многим умножает строки до агрегации: count(*) считает детей, sum по родительской колонке задваивается; лечения — подзапрос-агрегат, count(DISTINCT) и EXISTS.

Домашнее задание

Проведите трекер через агрегации. Первое: соберите db/queries/aggregates.sql с CountOpenTodos и CountNotes, прогоните sqlc generate и сверьте CountNotesRow поле за полем; объясните себе, почему у одного метода скаляр, а у другого структура. Второе: напишите TotalSpent и TotalSpentSafe с coalesce и ::bigint; на пустой таблице spendings вызовите оба и опишите разницу между нулём и ошибкой скана — своими словами, с текстом ошибки. Третье: соберите MinMaxID без кастов и найдите interface{} в Row-структуре; почините кастом ::bigint и перегенерируйте; рядом соберите EarliestDeadline с ::timestamptz и сравните типы. Четвёртое: напишите CountTodosPerUser дважды — с GROUP BY u.id и с GROUP BY u.id, u.name — и сравните Row-структуры; затем уберите deleted_at из ON и объясните, куда делись пользователи без задач. Пятое: на живой базе выполните выборку с колонкой вне GROUP BY и прочитайте ошибку 42803; убедитесь, что sqlc generate при этом молчит — прививка от иллюзии «сгенерировалось значит работает». Шестое: соберите DoneCountFiltered, рядом CASE-версию и сверьте числа на тестовых данных; добавьте третий столбец count(*) FILTER (WHERE t.deadline IS NOT NULL) AS with_deadline. Седьмое: напишите отчёт «задача и суммарные платежи» через подзапрос-агрегат; рядом соберите наивный джойн с sum(t.cost) и найдите расхождение на данных, где у задачи два платежа.

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

Почему метод sum сгенерировался с int64, если на пустом наборе приходит NULL?

sqlc резолвит агрегат по каталогу функций и считает результат не-nullable: обёртки из девятого урока вырастают из nullable-колонок и внешних соединений, а агрегат он гарантией не считает. PostgreSQL на пустом наборе возвращает строку-итог со значением NULL, и Scan в int64 падает ошибкой — в рантайме, на первом вызове. Страховка — coalesce(sum(...), 0)::bigint: ноль вместо NULL и честный int64 в сигнатуре.

Почему у min и max в Row-структуре поле interface{}?

min и max полиморфны: их тип результата зависит от типа аргумента, и каталог функций sqlc не даёт фиксированного ответа — генератор ставит any, в Go это interface{}. Каст к конкретному типу возвращает типизацию: max(deadline)::timestamptz даёт pgtype.Timestamptz, max(id)::bigint — int64. Писать интерфейс-проверки типов ради одного отчёта редко выгодно — проще каст.

FILTER или CASE — что выбрать для подсчётов по условию?

FILTER читается как «считай только такие строки» и не мешает соседним агрегатам в том же SELECT — для счётчиков это самый короткий синтаксис PostgreSQL. CASE универсальнее: умеет условные значения, а не только 0/1, и живёт за пределами PostgreSQL. В сгенерированном коде разницы нет — оба дают int64, оба берут имя поля из AS. Только не забывайте ELSE в CASE: без него непокрытые ветки вернут NULL и выпадут из суммы.

Термины урока

Ключевые понятия из этого урока — короткие определения в глоссарии. Наведите на термин, чтобы вспомнить, откройте карточку для подробностей и ссылки на практику.

Сравните с альтернативами

Практический вопрос после первых уроков: а почему именно эта библиотека? Мы разобрали конкурентов по одним критериям, с кодом одной задачи рядом и персональным подбором под ваши приоритеты.