Агрегатные функции в SQL на примерах

Агрегатные функции SQL принимают набор значений из многих строк и возвращают одно число: количество, сумму, среднее, минимум или максимум. Ниже разобраны пять стандартных функций, их работа с NULL и DISTINCT, подсчет итогов по группам и фильтрация групп, а в конце показано, как выполнить такие запросы из Python через sqlite3.

Учебная таблица для примеров

Все запросы ниже выполняются над одной таблицей заказов. В ней есть покупатель, город и сумма заказа, причем у одного заказа сумма не указана, чтобы было видно, как функции обходятся с пустыми значениями.

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

INSERT INTO orders (customer, city, amount) VALUES
    ('Анна',  'Москва', 1200),
    ('Борис', 'Казань',  800),
    ('Анна',  'Москва',  400),
    ('Вера',  'Казань', NULL),
    ('Глеб',  'Пермь',  2500);

Таблица создается и заполняется обычными командами, а выбирать из нее данные будем оператором SELECT, добавляя функции в список столбцов.

Пять стандартных функций

Стандарт SQL описывает пять агрегатных функций, и они есть в любой СУБД: SQLite, PostgreSQL, MySQL, SQL Server. Каждая проходит по строкам, отобранным запросом, и сворачивает их в одно значение.

Несколько функций можно вызвать в одном запросе. Каждому результату удобно дать имя через AS, иначе столбец будет называться текстом выражения.

SELECT COUNT(*)    AS orders_total,
       SUM(amount) AS revenue,
       AVG(amount) AS avg_check,
       MIN(amount) AS min_order,
       MAX(amount) AS max_order
FROM orders;
-- 5 | 4900.0 | 1225.0 | 400.0 | 2500.0

MIN и MAX работают и со строками: MIN(customer) вернет имя, которое идет первым по алфавиту, а для дат в формате ГГГГ-ММ-ДД дадут самую раннюю и самую позднюю дату.

Как функции обрабатывают NULL

В примере выше среднее получилось 1225, хотя заказов пять, а сумма 4900. Дело в том, что все агрегатные функции, кроме COUNT(*), пропускают NULL: AVG поделил сумму на четыре заполненных значения, а не на пять строк.

SELECT COUNT(*)      AS all_rows,     -- 5
       COUNT(amount) AS with_amount,  -- 4
       AVG(COALESCE(amount, 0)) AS avg_with_zero  -- 980.0
FROM orders;

Если пустую сумму нужно считать нулем, ее заменяют через COALESCE() прямо внутри функции. Еще одна ловушка: SUM по пустому набору строк возвращает NULL, а не 0. В отчетах это лечится той же конструкцией COALESCE(SUM(amount), 0).

Уникальные значения через DISTINCT

Ключевое слово DISTINCT внутри скобок заставляет функцию учитывать каждое значение один раз. Чаще всего его ставят в COUNT, чтобы узнать число разных покупателей или городов.

SELECT COUNT(customer)          AS orders_with_customer, -- 5
       COUNT(DISTINCT customer) AS customers,            -- 4
       COUNT(DISTINCT city)     AS cities                -- 3
FROM orders;

С SUM и AVG слово DISTINCT тоже работает, но применяется редко: два заказа на одинаковую сумму обычно оба должны попасть в итог.

Итоги по группам и фильтр HAVING

Без группировки функция считает один итог по всей выборке. Чтобы получить отдельные числа для каждого города или покупателя, добавляют оператор GROUP BY: строки делятся на группы с одинаковым значением столбца, и агрегат считается внутри каждой.

SELECT city, COUNT(*) AS cnt, SUM(amount) AS revenue
FROM orders
GROUP BY city
ORDER BY revenue DESC;
-- Пермь  | 1 | 2500.0
-- Москва | 2 | 1600.0
-- Казань | 2 | 800.0

Условие на строки задают в WHERE, но агрегатную функцию туда поставить нельзя: WHERE отрабатывает до группировки, когда итогов еще нет. Для отбора групп по результату агрегата служит HAVING.

SELECT customer, SUM(amount) AS total
FROM orders
WHERE city <> 'Пермь'
GROUP BY customer
HAVING SUM(amount) > 1000;
-- Анна | 1600.0

Порядок выполнения такой: FROM, затем WHERE, GROUP BY, HAVING, SELECT и ORDER BY. Отсюда правило: в SELECT при группировке можно выводить только столбцы из GROUP BY и агрегаты. SQLite такой запрос пропустит и вернет значение из случайной строки группы, а PostgreSQL выдаст ошибку.

Агрегатные запросы из Python

Модуль sqlite3 входит в стандартную библиотеку, поэтому проверить запросы можно без установки сервера. Результат агрегата без группировки удобно забирать методом fetchone(), он вернет кортеж из одной строки. Подробнее о подключении к базе рассказано в статье про sqlite в Python.

import sqlite3

with sqlite3.connect("shop.db") as conn:
    total, avg_check = conn.execute(
        "SELECT SUM(amount), AVG(amount) FROM orders WHERE city = ?",
        ("Москва",),
    ).fetchone()
    print(total, avg_check)   # 1600.0 800.0

    for city, cnt in conn.execute(
            "SELECT city, COUNT(*) FROM orders GROUP BY city"):
        print(city, cnt)

Значения в запрос передаются через знак вопроса, а не подстановкой строки: так драйвер сам экранирует данные и защищает от SQL-инъекций. Если в выборке нет ни одной строки, SUM и AVG вернут None, это стоит учитывать перед арифметикой в коде.

Дополнительные функции в разных СУБД

Кроме стандартной пятерки, у каждой СУБД есть свои агрегаты. Они удобны, но переносить запросы с ними между базами нужно с проверкой синтаксиса.

• SQLite: group_concat(col, ', ') склеивает значения группы в строку, total(col) работает как SUM, но для пустого набора возвращает 0.0 вместо NULL.
• PostgreSQL: string_agg(), array_agg(), статистические stddev() и variance().
• MySQL: GROUP_CONCAT(), STD(), битовые BIT_AND() и BIT_OR().
• SQL Server: STRING_AGG(), COUNT_BIG() для очень больших таблиц.

Большинство агрегатов можно использовать и как оконные функции с конструкцией OVER(): тогда итог считается для каждой строки без сворачивания выборки, например накопительная сумма по датам.

Частые вопросы

Ответы на вопросы, которые часто задают при изучении агрегатных функций.

Чем COUNT(*) отличается от COUNT(столбец)?

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

Можно ли использовать агрегатную функцию в WHERE?

Нет, WHERE фильтрует отдельные строки до подсчета итогов. Условие на сумму или количество пишут в HAVING после GROUP BY или выносят агрегат в подзапрос.

Почему AVG возвращает целое число?

В SQL Server и некоторых других СУБД среднее от целочисленного столбца тоже целое, дробная часть отбрасывается. Помогает приведение типа: AVG(CAST(score AS FLOAT)) или AVG(score * 1.0). SQLite и PostgreSQL возвращают дробный результат сразу.

Как вывести строку с максимальным значением целиком?

MAX вернет только само значение. Чтобы получить всю строку, используют подзапрос WHERE amount = (SELECT MAX(amount) FROM orders) или сортировку ORDER BY amount DESC LIMIT 1.