Исполняемые доки SQLite: Агрегатные функции

Q: В чём разница между SUM и TOTAL в SQLite?

Обе складывают числа, но SUM возвращает NULL, когда все входные значения - NULL, и по возможности использует целочисленную арифметику (которая может переполниться). TOTAL всегда возвращает число с плавающей точкой и 0.0, если строк нет вовсе. Берите TOTAL, когда нужен гарантированно числовой результат, и SUM, когда важно поведение по стандарту SQL.

Q: Как посчитать количество уникальных значений в SQLite?

Поставьте DISTINCT внутрь вызова: COUNT(DISTINCT customer_id). Это посчитает уникальные значения, не считая NULL. Просто COUNT(column) считает не-NULL значения, включая дубликаты, а COUNT(*) - все строки целиком, независимо от NULL.

Q: Игнорируют ли агрегаты SQLite значения NULL?

Да - каждый агрегат, кроме COUNT(), пропускает NULL на входе. AVG делит на количество не-NULL значений, а не на общее число строк. Исключение - COUNT(): он считает строки, а не значения, поэтому NULL-ы тоже попадают в счёт.

Что на самом деле делает агрегат

Большинство SQL-функций, с которыми вы уже сталкивались, работают построчно: UPPER(name) отрабатывает один раз на строку, ROUND(price, 2) - то же самое. Агрегатные функции устроены иначе. Они смотрят на набор строк целиком и сворачивают его в одно значение.

Заведём небольшую таблицу для опытов:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL,
    status TEXT
);

INSERT INTO orders (customer, amount, status) VALUES
    ('Ada',   42.00, 'paid'),
    ('Boris', 15.50, 'paid'),
    ('Ada',   88.00, 'paid'),
    ('Carl',  10.00, 'refunded'),
    ('Ada',   NULL,  'pending');

SELECT COUNT(*), SUM(amount), AVG(amount) FROM orders;

На входе пять строк, на выходе - одна. Вот и вся ментальная модель: агрегаты сжимают строки в сводку. Без GROUP BY сводка покрывает все строки результата.

COUNT: строки против значений

У COUNT есть три формы, и разница между ними заметна:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT,
    amount REAL
);

INSERT INTO orders (customer, amount) VALUES
    ('Ada',   42.00),
    ('Boris', 15.50),
    ('Ada',   88.00),
    ('Carl',  NULL),
    ('Ada',   NULL);

SELECT
    COUNT(*)                 AS rows_total,
    COUNT(amount)            AS rows_with_amount,
    COUNT(DISTINCT customer) AS unique_customers
FROM orders;

COUNT(*) считает строки. NULL-ы тоже. Всегда возвращает число.
COUNT(column) считает не-NULL значения в этом столбце.
COUNT(DISTINCT column) считает уникальные не-NULL значения.

Пять строк, у трёх есть amount, трое разных клиентов. Если COUNT(amount) оказался меньше COUNT(*) - вот почему: NULL-ы не считаются.

SUM, AVG, MIN, MAX

Арифметические агрегаты ведут себя ожидаемо, с одним негромким правилом - все они пропускают NULL:

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL);
INSERT INTO orders (amount) VALUES (10), (20), (30), (NULL);

SELECT
    SUM(amount)   AS total,
    AVG(amount)   AS mean,
    MIN(amount)   AS smallest,
    MAX(amount)   AS largest,
    COUNT(amount) AS counted
FROM orders;

AVG тут - это (10 + 20 + 30) / 3 = 20.0, а не 60 / 4 = 15.0. Знаменатель - количество не-NULL значений. Если хочется, чтобы пропуски считались как ноль, скажите об этом явно:

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL);
INSERT INTO orders (amount) VALUES (10), (20), (30), (NULL);

SELECT AVG(COALESCE(amount, 0)) FROM orders;

MIN и MAX работают и со строками, и с датами: для текста - лексикографическое сравнение, для дат в стандартном ISO-формате - как с обычными ISO-строками.

SUM против TOTAL

В SQLite есть второй «суммирующий» агрегат - TOTAL, - который снимает пару раздражающих особенностей SUM:

CREATE TABLE empty_table (amount REAL);

SELECT SUM(amount), TOTAL(amount) FROM empty_table;

SUM от нуля строк возвращает NULL. TOTAL - 0.0.
SUM от сплошных NULL-ов возвращает NULL. TOTAL - 0.0.
TOTAL всегда возвращает число с плавающей точкой и не страдает целочисленным переполнением.

Платой идёт нестандартность: TOTAL - это расширение SQLite, и результат всегда REAL, что может неожиданно прилететь, если вы рассчитывали на целое. Берите его, когда «нет строк - значит ноль» - это правильный ответ для вашего приложения, и оставайтесь с SUM, когда важно стандартное поведение.

DISTINCT внутри агрегатов

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

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada',   42.00),
    ('Ada',   42.00),
    ('Boris', 15.50),
    ('Carl',  10.00);

SELECT
    SUM(amount)              AS sum_all,
    SUM(DISTINCT amount)     AS sum_unique,
    COUNT(DISTINCT customer) AS unique_customers
FROM orders;

SUM(amount) складывает суммы всех строк. SUM(DISTINCT amount) берёт каждую уникальную сумму один раз - может быть полезно для чего-то вроде «общая сумма уникальных счетов», но в обычной работе нужно редко. По-настоящему распространённый вариант - COUNT(DISTINCT customer).

FILTER: агрегация по подмножеству

Когда нужно агрегировать только часть строк, первое, что приходит в голову, - WHERE. Но WHERE фильтрует всё подряд: «посчитать оплаченные заказы» и «посчитать возвраты» в одном запросе так не сделать. Эту задачу решает FILTER:

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL, status TEXT);
INSERT INTO orders (amount, status) VALUES
    (42.00, 'paid'),
    (15.50, 'paid'),
    (88.00, 'paid'),
    (10.00, 'refunded'),
    (25.00, 'pending');

SELECT
    COUNT(*)    FILTER (WHERE status = 'paid')     AS paid_count,
    COUNT(*)    FILTER (WHERE status = 'refunded') AS refund_count,
    SUM(amount) FILTER (WHERE status = 'paid')     AS paid_revenue
FROM orders;

Каждое FILTER (WHERE ...) действует только на свой агрегат. Один проход по таблице - несколько срезов сразу. До появления FILTER ту же задачу решали через SUM(CASE WHEN status = 'paid' THEN amount END) - идея та же, но многословнее.

GROUP_CONCAT: склеить значения в строку

GROUP_CONCAT - белая ворона среди агрегатов. Вместо числа он возвращает значения, склеенные в одну строку:

CREATE TABLE tags (post_id INTEGER, tag TEXT);
INSERT INTO tags VALUES
    (1, 'sqlite'),
    (1, 'database'),
    (1, 'tutorial'),
    (2, 'python'),
    (2, 'sqlite');

SELECT
    post_id,
    GROUP_CONCAT(tag)        AS default_separator,
    GROUP_CONCAT(tag, ' | ') AS pipe_separated
FROM tags
GROUP BY post_id;

Разделитель по умолчанию - запятая. Передайте второй аргумент, чтобы поставить что-то другое. Порядок не гарантирован, если только вы не пишете GROUP_CONCAT(tag ORDER BY tag) - пригодится, когда вывод появляется в интерфейсе и должен быть стабильным.

Агрегация без GROUP BY

Каждый пример выше с агрегатами без GROUP BY возвращал ровно одну строку. Это и есть правило: SELECT с агрегатами и без GROUP BY - это сводка по всей таблице (после WHERE), укладывающаяся в одну строку.

Агрегаты в одном запросе можно смешивать:

CREATE TABLE orders (id INTEGER PRIMARY KEY, amount REAL, status TEXT);
INSERT INTO orders (amount, status) VALUES
    (42.00, 'paid'), (15.50, 'paid'), (88.00, 'paid'),
    (10.00, 'refunded'), (25.00, 'pending');

SELECT
    COUNT(*)    AS orders,
    SUM(amount) AS revenue,
    AVG(amount) AS average,
    MIN(amount) AS smallest,
    MAX(amount) AS largest
FROM orders
WHERE status = 'paid';

А вот чего делать не стоит - это смешивать неагрегированные столбцы с агрегатами и ждать осмысленного результата:

-- SQLite разрешит, но значение `customer` будет произвольным.
SELECT customer, SUM(amount) FROM orders;

SQLite ошибки тут не выдаст (другие СУБД - выдадут), но просто наугад покажет имя одного из клиентов рядом с общей суммой. Если нужна сумма по каждому клиенту - нужен GROUP BY, и про него - следующая страница.

Дальше: GROUP BY и HAVING

Агрегаты по всей таблице отвечают на «сколько в сумме». Агрегаты по группам - по клиенту, по месяцу, по статусу - отвечают на куда более интересные вопросы. GROUP BY - это про то, как разложить строки по корзинам перед агрегацией, а HAVING - как отфильтровать сами группы по результату агрегата. Об этом - дальше.

Часто задаваемые вопросы

Что такое агрегатные функции в SQLite?

Агрегатные функции принимают много строк и возвращают одно итоговое значение. Встроенные - это COUNT, SUM, AVG, MIN, MAX, TOTAL и GROUP_CONCAT. Без GROUP BY они сворачивают весь результирующий набор в одну строку.

В чём разница между SUM и TOTAL в SQLite?

Обе складывают числа, но SUM возвращает NULL, когда все входные значения - NULL, и по возможности использует целочисленную арифметику (которая может переполниться). TOTAL всегда возвращает число с плавающей точкой и 0.0, если строк нет вовсе. Берите TOTAL, когда нужен гарантированно числовой результат, и SUM, когда важно поведение по стандарту SQL.

Как посчитать количество уникальных значений в SQLite?

Поставьте DISTINCT внутрь вызова: COUNT(DISTINCT customer_id). Это посчитает уникальные значения, не считая NULL. Просто COUNT(column) считает не-NULL значения, включая дубликаты, а COUNT(*) - все строки целиком, независимо от NULL.

Игнорируют ли агрегаты SQLite значения NULL?

Да - каждый агрегат, кроме COUNT(*), пропускает NULL на входе. AVG делит на количество не-NULL значений, а не на общее число строк. Исключение - COUNT(*): он считает строки, а не значения, поэтому NULL-ы тоже попадают в счёт.

Связанные концепции

Агрегатные функции SQLite: COUNT, SUM, AVG, MIN, MAX