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

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

• AVG: находит среднее значение. Входной параметр должен представлять один из следующих типов: smallint, int, bigint, real, double precision, numeric, interval. Для целочисленнных параметров результатом будет значение типа numeric, для параметров, которые представляют число с плавающей точкой, - значение типа double precision.

• BIT_AND: выполняет операцию побитового умножения (операции логического И) для чисел следующих типов: smallint, int, bigint, bit. Если параметр содержит значение NULL, то возвращается NULL.

• BIT_OR: выполняет операцию побитового сложения (операции логического ИЛИ) для чисел следующих типов: smallint, int, bigint, bit. Если параметр содержит значение NULL, то возвращается NULL.

• BOOL_AND: выполняет операцию логического умножения для значений типа bool. Если входные все значения равны true, то возвращается true, иначе возвращается false.

• BOOL_OR: выполняет операцию логического сложения для значений типа bool. Если входные хотя бы одно из значений равно true, то возвращается true, иначе возвращается false.

• COUNT(*): находит количество строк в запросе

• COUNT(expression): находит количество строк в запросе, для которых expression не содержит значение NULL.

• SUM: находит сумму значений

• MIN: находит наименьшее значение

• MAX: находит наибольшее значение

• STRING_AGG(expression, delimiter): соединяет с помощью delimiter все текстовые значения из expression в одну строку.

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

Если в наборе нет строк, то все агрегатные функции за исключением COUNT(*) возвращают значение NULL.

Avg

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

Пусть в базе данных у нас есть таблица товаров Products, которая описывается следующими выражениями:

CREATE TABLE Products
(
    Id SERIAL PRIMARY KEY,
    ProductName VARCHAR(30) NOT NULL,
    Company VARCHAR(20) NOT NULL,
    ProductCount INT DEFAULT 0,
    Price NUMERIC NOT NULL,
	IsDiscounted BOOL
);
  
INSERT INTO Products (ProductName, Company, ProductCount, Price, IsDiscounted) 
VALUES
('iPhone X', 'Apple', 3, 76000, false),
('iPhone 8', 'Apple', 2, 71000, true),
('iPhone 7', 'Apple', 5, 42000, true),
('Galaxy S9', 'Samsung', 2, 46000, false),
('Galaxy S8 Plus', 'Samsung', 1, 56000, true),
('Desire 12', 'HTC', 5, 28000, true),
('Nokia 9', 'HMD Global', 6, 38000, true);

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

SELECT AVG(Price) AS Average_Price FROM Products;

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

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

SELECT AVG(Price) FROM Products
WHERE Company='Apple';

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

SELECT AVG(Price * ProductCount) FROM Products

Count

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

SELECT COUNT(*) FROM Products;

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

SELECT COUNT(DISTINCT Company) FROM Products;

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

Min и Max

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

SELECT MIN(Price) FROM Products;

Поиск максимальной цены:

SELECT MAX(Price) FROM Products;

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

Sum

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

SELECT SUM(ProductCount) FROM Products;

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

SELECT SUM(ProductCount * Price) FROM Products;

BOOL_AND и BOOL_OR

Допустим, нам надо узнать, есть ли в таблице товары, которые подлежать скидке, то есть у которых IsDiscounted = true. В этом случае можно выполнить функцию BOOL_OR, которая возвращает true, если хотя бы одно значение в наборе равно true:

SELECT BOOL_OR(IsDiscounted) FROM Products;

Если нам надо узнать, все ли товары подлежат скидке, то можно применить функцию BOOL_AND, которая возвращает true, если все значения в наборе равны true:

SELECT BOOL_AND(IsDiscounted) FROM Products;

STRING_AGG

Функция STRING_AGG() объединяет набор строковых значений или значений bytea. Например, выберем названия всех товаров:

SELECT STRING_AGG(ProductName, ', ') FROM Products;

Или выберем всех производителей:

SELECT STRING_AGG(DISTINCT Company, ', ') FROM Products;

-- результат операции
-- Apple, HMD Global, HTC, Samsung 

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

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

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

SELECT COUNT(*) AS ProdCount,
	   SUM(ProductCount) AS TotalCount,
       MIN(Price) AS MinPrice,
       MAX(Price) AS MaxPrice,
       AVG(Price) AS AvgPrice
FROM Products;