Агрегатные функции

Агрегатные функции вычисляют некоторые скалярные значения на основе набора строк. В SQLite определены следующие агрегатные функции:

• AVG: вычисляет среднее значение

• SUM: вычисляет сумму значений

• MIN: вычисляет наименьшее значение

• MAX: вычисляет наибольшее значение

• COUNT: вычисляет количество строк в запросе

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

Для рассмотрения агрегатных функций возьмем следующую таблицу 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);

Avg

Функция Avg возвращает среднее значение на диапазоне значений столбца таблицы. При этом строки со значением NULL игнорируются.

Например, найдем среднюю цену товаров из базы данных:

SELECT AVG(price) AS average_price FROM products;

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

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

SELECT AVG(price) FROM products
WHERE company='Apple';

Также можно находить среднее значение для более сложных выражений. Например, найдем среднюю сумму всех товаров, учитывая их количество:

SELECT AVG(price * product_count) FROM products;

Дополнительно функция AVG может принимать два оператора:

AVG([ALL | DISTINCT] выражение);

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

SELECT AVG(DISTINCT price) FROM products;

Count

Функция Count вычисляет количество строк в выборке. Есть две формы этой функции. Первая форма COUNT(*) подсчитывает число строк в выборке:

SELECT COUNT(*) FROM products;

Вторая форма функции вычисляет количество строк по определенному столбцу, при этом строки со значениями NULL игнорируются:

SELECT COUNT(company) FROM products;

Однако выражение выше подсчитывает количество всех строк, где в столбце company нет значения NULL, то есть количество в том числе повторяющихся значений. Чтобы одсчитать количество только уникальных значений, необходимо использовать оператор DISTINCT:

SELECT COUNT(DISTINCT company) FROM products;

Min и Max

Функции Min и Max вычисляют минимальное и максимальное значение по столбцу соответственно. Например, найдем минимальную цену среди товаров:

SELECT MIN(price), MAX(price) FROM products;

Данные функции также игнорируют значения NULL и не учитывают их при подсчете.

Sum

Функция Sum вычисляет сумму значений столбца. Если столбец имеет значение NULL, то оно игнорируется. Например, подсчитаем общее количество товаров:

SELECT SUM(product_count) FROM products;

Также вместо имени столбца может передаваться вычисляемое выражение. Например, найдем общую стоимость всех имеющихся товаров:

SELECT SUM(product_count * price) FROM products;

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

SELECT SUM(DISTINCT price) FROM products;

Комбинирование функций

Объединим применение нескольких функций:

SELECT COUNT(DISTINCT company) AS companies_count, 
SUM(product_count) AS total_count,
MIN(price) AS min_price,
MAX(price) AS max_price,
AVG(price) AS avg_price
FROM products;