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

Андрей Аксенов

Contents

Обзор
Примеры использования

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

Типовые агрегатные функции — это SUM(), COUNT(), AVG(), MIN() и MAX(). В сочетании с GROUP BY эти функции вычисляют агрегированные значения для каждой группы. Выражение HAVING позволяет фильтровать группы после агрегации, аналогично тому, как WHERE фильтрует отдельные строки до группировки.

Большинство агрегатных функций принимает один аргумент — столбец или выражение, которое подлежит агрегации:

<aggregate_name>(expression)

где:

aggregate_name — имя функции (например, SUM, AVG, COUNT), которая выполняет агрегацию.
expression — столбец или выражение, значения которых агрегируются.

Например, SUM(quantity) вычисляет общую сумму значений в столбце quantity. Функция COUNT(product) подсчитывает количество значений, не равных NULL, в столбце product.

Некоторые агрегатные функции принимают несколько аргументов:

<aggregate_name>(expression [, ...] )

Например, функция string_agg(<expression>, <delimiter>) объединяет строки, используя указанный разделитель.

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

<aggregate_name>( [ ALL | DISTINCT ] expression [, ...] ) [ FILTER ( WHERE <condition> ) ]

где:

ALL — (опционально, по умолчанию) агрегирует все входные значения.
DISTINCT — (опционально) агрегирует только уникальные входные значения, игнорируя дубликаты.
FILTER (WHERE <condition>) — (опционально) фильтрует строки, участвующие в агрегации. Если указан FILTER, в вычисление агрегата попадут только строки, удовлетворяющие <condition>.

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

Синтаксис агрегатного выражения имеет одну из следующих форм:

<aggregate_name>(expression [, …]) [ FILTER (WHERE <condition>) ] — агрегирует все значения, не равные NULL.
<aggregate_name>(ALL expression [, …]) [ FILTER (WHERE <condition>) ] — аналогичен предыдущей форме; ALL используется по умолчанию и агрегирует все значения, не равные NULL.
<aggregate_name>(DISTINCT expression [, …]) [ FILTER (WHERE <condition>) ] — агрегирует только уникальные значения, не равные NULL.
<aggregate_name>(*) [ FILTER (WHERE <condition>) ] — агрегирует все строки, включая строки со значениями NULL. Обычно используется с COUNT(*) для подсчета строк.
<aggregate_name>( [ expression [, … ] ] ) WITHIN GROUP ( <order_by_clause> ) [ FILTER ( WHERE <condition> ) ] — форма агрегирования с учетом порядка, в которой значения сортируются перед агрегированием.

Агрегатное выражение можно использовать только в списке результатов или в выражении HAVING команды SELECT. Использование в других частях запроса, таких как WHERE, запрещено, так как они вычисляются до расчета агрегатных функций. Это ограничение действует на уровне запроса, которому принадлежит агрегат.
Когда агрегатное выражение используется в подзапросе, оно обычно применяется к строкам, возвращаемым этим подзапросом. Однако если аргументы агрегатной функции ссылаются только на столбцы внешнего запроса, агрегат вычисляется на уровне этого внешнего запроса. В таком случае подзапрос рассматривает результат агрегата как постоянное значение.
Нельзя использовать агрегатную функцию в качестве аргумента другой агрегатной функции.
Нельзя передавать оконные функции в качестве аргумента в агрегатные функции.

Greengage DB позволяет использовать агрегатные функции, поддерживаемые PostgreSQL, например:

SUM() — возвращает сумму значений.
AVG() — возвращает среднее значение.
COUNT() — возвращает количество значений.
MAX() — возвращает максимальное значение.
MIN() — возвращает минимальное значение.

Чтобы увидеть полный список агрегатных функций, смотрите соответствующую статью в документации PostgreSQL: Aggregate Functions.

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

Функция Тип возвращаемого значения Описание

Функция	Тип возвращаемого значения	Описание
MEDIAN(expr)	`timestamp`, `timestamptz`, `interval`, `float`	Вычисляет медианное значение. Поддерживает двумерные массивы и обрабатывает их как матрицы
SUM(array[])	`smallint[]`, `int[]`, `bigint[]`, `float[]`	Выполняет покомпонентное суммирование значений, являющихся двумерными числовыми массивами (матричное суммирование)
PIVOT_SUM(label[], label, expr)	`int[]`, `bigint[]`, `float[]`	Выполняет сводную агрегацию, суммируя значения по группам меток. Значения повторяющихся меток складываются
UNNEST(array[])	набор из `anyelement`	Разворачивает массив в набор строк, возвращая каждый элемент отдельно

MEDIAN(expr)

timestamp, timestamptz, interval, float

Вычисляет медианное значение. Поддерживает двумерные массивы и обрабатывает их как матрицы

SUM(array[])

smallint[], int[], bigint[], float[]

Выполняет покомпонентное суммирование значений, являющихся двумерными числовыми массивами (матричное суммирование)

PIVOT_SUM(label[], label, expr)

int[], bigint[], float[]

Выполняет сводную агрегацию, суммируя значения по группам меток. Значения повторяющихся меток складываются

UNNEST(array[])

набор из anyelement

Разворачивает массив в набор строк, возвращая каждый элемент отдельно

ПРИМЕЧАНИЕ

Вы можете создавать пользовательские агрегатные функции с помощью команды CREATE AGGREGATE.

Предварительные требования

Для выполнения команд, описанных в следующих разделах, подключитесь к мастер-хосту Greengage DB с помощью psql, как описано в статье Подключение к Greengage DB с использованием psql. Создайте новую базу данных и подключитесь к ней:

CREATE DATABASE marketplace;
\c marketplace

Затем создайте таблицу и заполните ее данными:

Создание таблицы

CREATE TABLE sales
(
    id           INT,
    product_name TEXT,
    category     TEXT,
    quantity     INT,
    price        NUMERIC(8, 2),
    sale_date    DATE,
    region       TEXT
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (product_name);

INSERT INTO sales
VALUES (1, 'Laptop', 'Electronics', 1, 1200.00, '2025-01-03', 'North'),
       (2, 'Headphones', 'Electronics', 2, 150.00, '2025-01-05', 'North'),
       (3, 'Coffee Maker', 'Home', 1, 85.00, '2025-01-07', 'South'),
       (4, 'T-Shirt', 'Clothing', 3, 25.00, '2025-01-10', 'East'),
       (5, 'Desk Chair', 'Home', 1, 200.00, '2025-01-15', 'South'),
       (6, 'Smartphone', 'Electronics', 2, 800.00, '2025-01-22', 'West'),
       (7, 'Headphones', 'Electronics', 1, 150.00, '2025-02-01', 'North'),
       (8, 'T-Shirt', 'Clothing', 2, 25.00, '2025-02-03', 'East'),
       (9, 'Laptop', 'Electronics', 1, 1200.00, '2025-02-07', 'North'),
       (10, 'Blender', 'Home', 1, 55.00, '2025-02-10', 'South'),
       (11, 'Jacket', 'Clothing', 2, 60.00, '2025-02-13', 'East'),
       (12, 'Smartphone', 'Electronics', 1, 800.00, '2025-02-18', 'West'),
       (13, 'Coffee Maker', 'Home', 1, 85.00, '2025-03-01', 'South'),
       (14, 'Desk Chair', 'Home', 2, 200.00, '2025-03-05', 'South'),
       (15, 'Jacket', 'Clothing', 1, 60.00, '2025-03-07', 'East'),
       (16, 'Headphones', 'Electronics', 3, 150.00, '2025-03-10', 'North'),
       (17, 'Laptop', 'Electronics', 1, 1200.00, '2025-03-15', 'North'),
       (18, 'T-Shirt', 'Clothing', 2, 25.00, '2025-03-20', 'East'),
       -- В следующих двух строках значения в столбце region равны NULL.
       (19, 'Tablet', 'Electronics', 1, 500.00, '2025-03-21', NULL),
       (20, 'Office Lamp', 'Home', 2, 45.00, '2025-03-22', NULL);

Этот SQL-запрос использует агрегатную функцию SUM() для вычисления общего количества всех продаж:

SELECT SUM(quantity) AS total_quantity
FROM sales;

Результат выглядит следующим образом:

 total_quantity
----------------
             31
(1 row)

Следующий SQL-запрос использует функцию SUM() для расчета общей суммы продаж, складывая результаты выражения price * quantity для всех строк в таблице:

SELECT SUM(price * quantity) AS total_sales
FROM sales;

Вывод выглядит следующим образом:

 total_sales
-------------
     8670.00
(1 row)

Этот запрос вычисляет количество проданных товаров в каждой категории с использованием GROUP BY:

SELECT category,
       SUM(quantity) AS total_quantity
FROM sales
GROUP BY category;

Результат выглядит следующим образом:

  category   | total_quantity
-------------+----------------
 Home        |              8
 Clothing    |             10
 Electronics |             13
(3 rows)

Следующий запрос фильтрует агрегированные результаты с помощью HAVING и возвращает категории, в которых общее количество проданных товаров больше или равно 10:

SELECT category,
       SUM(quantity) AS total_quantity
FROM sales
GROUP BY category
HAVING SUM(quantity) >= 10;

Результат должен быть следующим:

  category   | total_quantity
-------------+----------------
 Electronics |             13
 Clothing    |             10
(2 rows)

Этот SQL-запрос подсчитывает все строки в таблице sales, включая строки со значениями NULL в любом столбце:

SELECT COUNT(*) AS total_row_count
FROM sales;

Результат выглядит следующим образом:

 total_row_count
-----------------
              20
(1 row)

Следующий SQL-запрос подсчитывает количество строк, в которых столбец region не равен NULL, включая все неуникальные значения:

SELECT COUNT(region) AS non_null_region_count
FROM sales;

Результат следующий:

 non_null_region_count
-----------------------
                    18
(1 row)

Этот запрос выполняет ту же операцию, что и предыдущий, но явно использует ключевое слово ALL:

SELECT COUNT(ALL region) AS non_null_region_count
FROM sales;

Этот SQL-запрос подсчитывает количество уникальных значений, не равных NULL, в столбце region, показывая, сколько различных регионов представлено в данных о продажах:

SELECT COUNT(DISTINCT region) AS distinct_region_count
FROM sales;

Вывод должен выглядеть следующим образом:

 distinct_region_count
-----------------------
                     4
(1 row)

Этот запрос подсчитывает количество строк, в которых значение столбца region равно 'North', используя FILTER для учета только таких строк:

SELECT COUNT(*) FILTER (WHERE region = 'North') AS north_region_count
FROM sales;

Результат выглядит так:

 north_region_count
--------------------
                  6
(1 row)

Этот SQL-запрос вычисляет общее количество проданных товаров для каждой категории с помощью функции SUM():

SELECT category,
       SUM(quantity) AS total_quantity
FROM sales
GROUP BY category;

Вывод

  category   | total_quantity
-------------+----------------
 Home        |              8
 Electronics |             13
 Clothing    |             10
(3 rows)

Следующий запрос вычисляет среднюю цену товаров в каждой категории с помощью функции AVG():

SELECT category,
       AVG(price) AS avg_price
FROM sales
GROUP BY category;

Вывод

  category   |      avg_price
-------------+----------------------
 Electronics | 683.3333333333333333
 Clothing    |  39.0000000000000000
 Home        | 111.6666666666666667
(3 rows)

Используйте функцию ROUND(), чтобы округлять числовые значения до заданного количества знаков после запятой.

Этот SQL-запрос подсчитывает количество записей о продажах для каждой категории с помощью функции COUNT():

SELECT category,
       COUNT(*) AS sales_count
FROM sales
GROUP BY category;

Вывод

  category   | sales_count
-------------+-------------
 Home        |           6
 Clothing    |           5
 Electronics |           9
(3 rows)

Следующий запрос использует функцию MAX(), чтобы найти наивысшую цену для каждой категории товаров:

SELECT category,
       MAX(price) AS max_price
FROM sales
GROUP BY category;

Вывод

  category   | max_price
-------------+-----------
 Clothing    |     60.00
 Electronics |   1200.00
 Home        |    200.00
(3 rows)

Данный SQL-запрос возвращает минимальную цену в каждой категории товаров с помощью функции MIN():

SELECT category,
       MIN(price) AS min_price
FROM sales
GROUP BY category;

Вывод

  category   | min_price
-------------+-----------
 Electronics |    150.00
 Home        |     45.00
 Clothing    |     25.00
(3 rows)

Следующий SQL-запрос вычисляет медианную цену для каждой категории товаров:

SELECT category,
       MEDIAN(price) AS median_price
FROM sales
GROUP BY category;

Вывод

  category   | median_price
-------------+--------------
 Home        |           85
 Clothing    |           25
 Electronics |          800
(3 rows)

Следующий запрос находит медианную дату продажи для каждой категории:

SELECT category,
       MEDIAN(sale_date) AS median_sale_date
FROM sales
GROUP BY category;

Вывод

  category   |    median_sale_date
-------------+------------------------
 Clothing    | 2025-02-13 00:00:00+00
 Electronics | 2025-02-07 00:00:00+00
 Home        | 2025-02-19 12:00:00+00
(3 rows)

Этот SQL-запрос подсчитывает общее количество проданных товаров. Данные разбиваются по регионам с помощью функции PIVOT_SUM(), которая суммирует значения, сгруппированные по заданным меткам регионов:

SELECT product_name,
       PIVOT_SUM(ARRAY ['North', 'South', 'East', 'West'], region, quantity) AS quantity_per_region
FROM sales
WHERE region IS NOT NULL
GROUP BY product_name;

Вывод

 product_name | quantity_per_region
--------------+---------------------
 Smartphone   | {0,0,0,3}
 Laptop       | {3,0,0,0}
 Headphones   | {6,0,0,0}
 T-Shirt      | {0,0,7,0}
 Desk Chair   | {0,3,0,0}
 Blender      | {0,1,0,0}
 Jacket       | {0,0,3,0}
 Coffee Maker | {0,2,0,0}
(8 rows)

Создание таблицы

Чтобы выполнить примеры из этого раздела, создайте таблицу metrics_data:

CREATE TABLE metrics_data
(
    id     SERIAL,
    values FLOAT8[][]
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id);

Добавьте данные:

INSERT INTO metrics_data (values)
VALUES (ARRAY [[1.5, 2.0], [3.1, 4.0]]),
       (ARRAY [[0.5, 1.0], [1.9, 2.0]]);

Этот запрос поэлементно суммирует двумерные массивы из столбца values во всех строках таблицы metrics_data:

SELECT SUM(values) AS total_array_sum
FROM metrics_data;

Вывод

 total_array_sum
-----------------
 {{2,3},{5,6}}
(1 row)

Этот запрос показывает, как использовать функцию UNNEST(), чтобы развернуть столбец с двумерным массивом в отдельные скалярные значения:

SELECT UNNEST(values) AS value
FROM metrics_data;

Вывод

 value
-------
   1.5
     2
   3.1
     4
   0.5
     1
   1.9
     2
(8 rows)

Комбинирование запросов

Команда COPY

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

Обзор

Обзор синтаксиса

Агрегатные выражения

Ограничения агрегатных выражений

Встроенные агрегатные функции

Примеры использования

Базовые агрегатные запросы

Агрегатные выражения

Основные агрегатные функции

Расширенные аналитические функции

Расширенные аналитические функции: двумерные массивы