Группировка

Для группировки данных в SQLite применяются операторы GROUP BY и HAVING. Они употребляются в рамках команды SELECT:

SELECT столбцы
FROM таблица
[WHERE условие_фильтрации_строк]
[GROUP BY столбцы_для_группировки]
[HAVING условие_фильтрации_групп]
[ORDER BY столбцы_для_сортировки]

Для рассмотрения группировки возьмем следующую таблицу products, которая хранит набор товаров:

DROP TABLE IF EXISTS products;
CREATE TABLE products
(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    company TEXT NOT NULL,
    product_count INTEGER DEFAULT 0,
    price INTEGER
);
  
INSERT INTO products (name, company, product_count, price)
VALUES
('iPhone 13', 'Apple', 3, 76000),
('iPhone 12', 'Apple', 3, 51000),
('iPhone 11', 'Apple', 3, 43000),
('Galaxy S21', 'Samsung', 2, 56000),
('Galaxy S20', 'Samsung', 1, 51000),
('P40 Pro', 'Huawei', 5, 36000),
('Nokia XR20', 'HMD Global', 2, 45000),
('T11 Pro', 'Xiaomi', 1, 54000);

GROUP BY

Оператор GROUP BY определяет, как строки будут группироваться. Этот оператор возвращает одну строку для каждой группы. И для каждой полученной группы можно выполнить агрегатные функции MIN, MAX, SUM, COUNT и AVG.

Например, сгруппируем товары по производителю

SELECT company, COUNT(*) AS models_count
FROM products
GROUP BY company;

Первый столбец в выражении SELECT - company представляет название группы, а второй столбец - models_count представляет результат функции COUNT(), которая вычисляет количество строк в группе.

И если в выражении SELECT производится выборка по одному или нескольким столбцам и также используются агрегатные функции, то необходимо использовать выражение GROUP BY. Так, следующий пример не будет работать корректно, так как он не содержит выражение группировки:

SELECT company, COUNT(*) AS models_count
FROM products;

Оператор GROUP BY может выполнять группировку по множеству столбцов. Так, добавим группировку по количеству товаров:

SELECT company, product_count, COUNT(*) AS models_count
FROM products
GROUP BY company, product_count;

Следует учитывать, что выражение GROUP BY должно идти после выражения WHERE, но до выражения ORDER BY:

SELECT company, COUNT(*) AS models_count
FROM products
WHERE price > 40000
GROUP BY company
ORDER BY models_count DESC;

Подобным образом можно использовать и другие агрегатные функции. Например, получим общую информацию по товарам по каждому производителю:

SELECT company, COUNT(*) AS total_count,
MIN(price) AS min_price,
MAX(price) AS max_price,
AVG(price) AS avg_price,
SUM(price*product_count) AS total_sum
FROM products
GROUP BY company
ORDER BY total_sum DESC;

Фильтрация групп. HAVING

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

Использование HAVING во многом аналогично применению WHERE. Только есть WHERE применяется для фильтрации строк, то HAVING - для фильтрации групп.

Например, найдем все группы товаров по производителям, для которых определено более 1 модели:

SELECT company, COUNT(*) AS models_count
FROM products
GROUP BY company
HAVING COUNT(*) > 1;

В одной команде также можно сочетать выражения WHERE и HAVING:

SELECT company, COUNT(*) AS models_count
FROM products
WHERE price * product_count > 80000
GROUP BY company
HAVING COUNT(*) > 1;

То есть в данном случае сначала фильтруются строки: выбираются те товары, общая стоимость которых больше 80000. Затем выбранные товары группируются по производителям. И далее фильтруются сами группы - выбираются те группы, которые содержат больше 1 модели.

Если при этом необходимо провести сортировку, то выражение ORDER BY идет после выражения HAVING:

SELECT company, COUNT(*) AS models, SUM(product_count) AS units
FROM products
WHERE price * product_count > 80000
GROUP BY company
HAVING SUM(product_count) > 2
ORDER BY units DESC;

Здесь группировка идет по производителям, и также выбирается количество моделей для каждого производителя (models) и общее количество всех товаров по всем этим моделям (units). В конце группы сортируются по количеству товаров по убыванию.