Прежде чем перейти к статье, хочу вам представить, экономическую онлайн игру Brave Knights, в которой вы можете играть и зарабатывать. Регистируйтесь, играйте и зарабатывайте!
Наш СБИС, как и другие системы управления бизнесом, не обходится без формирования отчетов — каждый руководитель любит сводные цифры, особенно всякие суммы по разделам и красивые "Итого".
А чтобы эти итоги собрать, необходимо по исходным данным вычислить значение некоторой агрегатной функции: количество, сумма, среднее, минимум, максимум,… — и, как правило, не одной.
Сегодня мы рассмотрим некоторые способы, с помощью которых можно вычислить агрегаты в PostgreSQL или ускорить выполнение SQL-запроса.
Давайте сразу возьмем задачу чуть посложнее, чем тривиальное вычисление единственного агрегата. Попробуем вычислить по одной и той же выборке несколько агрегатов одновременно, и пусть это будут количество и сумма элементов в некотором входном наборе:
Это самый-самый простой случай — просто сразу одновременно в запросе пишем
И хоть агрегатных функций мы использовали две, в плане у нас все хорошо — узел
Проблемы начинаются, если мы хотим вычислить сразу несколько агрегатов, но вот у каждого из них — разные условия [не]включения одной и той же записи исходного набора. Например, попробуем вычислить для нашего набора количество чисел больше и меньше 10 — отдельными полями:
Понятно, что это можно сделать самым примитивным способом — вложенными запросами к CTE, каждый из которых сначала «выфильтровывает» себе из общей выборки подходящий под условия «кусочек», а потом считает на нем отдельный агрегат:
Какие из этого плана можно сделать выводы? Много бегаем и много фильтруем — дважды
А если выборка будет из 10K записей, а агрегатов захочется 3-4-5?.. Совсем нехорошо.
Этот вариант, наверное, самый простой и понятный:
Он идеален во всех отношениях, быстро и эффективно решает все наши задачи, но имеет и ложку дегтя — доступен только с версии 9.4 (мало ли у кого что «на бою» стоит).
Допустим, 9.4 еще не подвезли, а запрос все-таки хочется написать «в один проход». В этом случае можно воспользоваться знанием, что
Или можно чуть короче, если вспомнить о возможности скастовать
Вообще, подход вычисления агрегата от некоторого условия достаточно универсален, но имеет и свои подводные камни.
Допустим, мы хотим теперь получить не просто количество чисел, подходящих под то или иное условие, но массивы из них состоящие:
И тут нам тоже поможет агрегат — ведь он возвращает одно значение, но тип его может быть не обязательно скаляром, но записью таблицы или даже массивом — с помощью
Вариант с использованием
А вот если попытаться превратить его в агрегат от условия — придется разбираться с попаданием в набор
Но если вам хоть немного повезло, и стоит хотя бы версия 9.3, то можно воспользоваться функцией
Мы тут внезапно вынесли наш исходный набор в CTE — а почему? Потому что так банально быстрее. Давайте проверим на простом примере:
Почти на 40% быстрее! Пример, конечно, вырожденный, но эффект имеет место быть.
Еще один способ агрегации «за единственный проход» заключается в использовании уникализации выборки с помощью
Единственная проблема — такая группировка «небесплатна»:
Но предположим, что мы хотим что-то «этакое сложное», для чего нет подходящего агрегата:
В этом примере мы хотим вычислить произведение всех участвующих чисел, но такой агрегатной функции — нету. Понятно, что ее можно создать, но как-то это неспортивно — создавать по функции, если потребность возникает редко.
Соберем запрос, который решит нашу задачу:
Кучеряво! Попробуем упростить и ускорить, опираясь на тот факт, что входной аргумент заведомо является массивом — то есть может быть перебран поэлементно:
Намного лучше!
Применим
А чтобы эти итоги собрать, необходимо по исходным данным вычислить значение некоторой агрегатной функции: количество, сумма, среднее, минимум, максимум,… — и, как правило, не одной.
Сегодня мы рассмотрим некоторые способы, с помощью которых можно вычислить агрегаты в PostgreSQL или ускорить выполнение SQL-запроса.
Совместные агрегаты
Давайте сразу возьмем задачу чуть посложнее, чем тривиальное вычисление единственного агрегата. Попробуем вычислить по одной и той же выборке несколько агрегатов одновременно, и пусть это будут количество и сумма элементов в некотором входном наборе:
->
$1 = '{2,3,5,7,11,13,17,19}'
<-
count | sum
8 | 77
Это самый-самый простой случай — просто сразу одновременно в запросе пишем
count
и sum
:SELECT
count(*)
, sum(prime)
FROM
unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime;
И хоть агрегатных функций мы использовали две, в плане у нас все хорошо — узел
Aggregate
выполнялся всего лишь один раз:«Несовместимые» агрегаты
Проблемы начинаются, если мы хотим вычислить сразу несколько агрегатов, но вот у каждого из них — разные условия [не]включения одной и той же записи исходного набора. Например, попробуем вычислить для нашего набора количество чисел больше и меньше 10 — отдельными полями:
->
$1 = '{2,3,5,7,11,13,17,19}'
<-
countlt | countgt
4 | 4
Вложенные запросы
Понятно, что это можно сделать самым примитивным способом — вложенными запросами к CTE, каждый из которых сначала «выфильтровывает» себе из общей выборки подходящий под условия «кусочек», а потом считает на нем отдельный агрегат:
WITH src AS (
SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime
)
SELECT
(SELECT count(*) FROM src WHERE prime < 10) countlt
, (SELECT count(*) FROM src WHERE prime > 10) countgt;
Какие из этого плана можно сделать выводы? Много бегаем и много фильтруем — дважды
[CTE Scan + Rows Removed by Filter: 4]
.А если выборка будет из 10K записей, а агрегатов захочется 3-4-5?.. Совсем нехорошо.
FILTER-агрегаты
Этот вариант, наверное, самый простой и понятный:
SELECT
count(*) FILTER(WHERE prime < 10) countlt
, count(*) FILTER(WHERE prime > 10) countgt
FROM
unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime;
Он идеален во всех отношениях, быстро и эффективно решает все наши задачи, но имеет и ложку дегтя — доступен только с версии 9.4 (мало ли у кого что «на бою» стоит).
Агрегаты от условия
Допустим, 9.4 еще не подвезли, а запрос все-таки хочется написать «в один проход». В этом случае можно воспользоваться знанием, что
count(*) FILTER(WHERE cond)
эквивалентно sum(CASE cond)
:SELECT
sum(CASE WHEN prime < 10 THEN 1 ELSE 0 END) countlt
, sum(CASE WHEN prime > 10 THEN 1 ELSE 0 END) countgt
FROM
unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime;
Или можно чуть короче, если вспомнить о возможности скастовать
boolean
в integer
вместо CASE
с результатами 1/0:SELECT
sum((prime < 10)::integer) countlt
, sum((prime > 10)::integer) countgt
FROM
unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime;
Вообще, подход вычисления агрегата от некоторого условия достаточно универсален, но имеет и свои подводные камни.
Агрегация в массив
Допустим, мы хотим теперь получить не просто количество чисел, подходящих под то или иное условие, но массивы из них состоящие:
->
$1 = '{2,3,5,7,11,13,17,19}'
<-
primeslt | primesgt
{2,3,5,7} | {11,13,17,19}
И тут нам тоже поможет агрегат — ведь он возвращает одно значение, но тип его может быть не обязательно скаляром, но записью таблицы или даже массивом — с помощью
array_agg
.Вариант с использованием
FILTER
очевиден:WITH src AS (
SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime
)
SELECT
array_agg(prime) FILTER(WHERE prime < 10) primeslt
, array_agg(prime) FILTER(WHERE prime > 10) primesgt
FROM
src;
А вот если попытаться превратить его в агрегат от условия — придется разбираться с попаданием в набор
NULL
'ов, что уже совсем невесело:WITH src AS (
SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime
)
, tmp AS (
SELECT
array_agg(CASE WHEN prime < 10 THEN prime END) primeslt -- {2,3,5,7,NULL,NULL,NULL,NULL}
, array_agg(CASE WHEN prime > 10 THEN prime END) primesgt -- {NULL,NULL,NULL,NULL,11,13,17,19}
FROM
src
)
SELECT
ARRAY(SELECT * FROM unnest(primeslt) prime WHERE prime IS NOT NULL) primeslt
, ARRAY(SELECT * FROM unnest(primesgt) prime WHERE prime IS NOT NULL) primesgt
FROM
tmp;
Но если вам хоть немного повезло, и стоит хотя бы версия 9.3, то можно воспользоваться функцией
array_remove
для достижения того же эффекта:WITH src AS (
SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime
)
SELECT
array_remove(array_agg(CASE WHEN prime < 10 THEN prime END), NULL) primeslt
, array_remove(array_agg(CASE WHEN prime > 10 THEN prime END), NULL) primesgt
FROM
src;
Несколько агрегатов: Function Scan vs CTE
Мы тут внезапно вынесли наш исходный набор в CTE — а почему? Потому что так банально быстрее. Давайте проверим на простом примере:
SELECT
array_agg(i) FILTER(WHERE i % 2 = 0) even
, array_agg(i) FILTER(WHERE i % 2 = 1) odd
FROM
generate_series(1, 1000000) i;
WITH src AS (
SELECT generate_series(1, 1000000) i
)
SELECT
array_agg(i) FILTER(WHERE i % 2 = 0) even
, array_agg(i) FILTER(WHERE i % 2 = 1) odd
FROM
src;
Почти на 40% быстрее! Пример, конечно, вырожденный, но эффект имеет место быть.
DISTINCT + OVER
Еще один способ агрегации «за единственный проход» заключается в использовании уникализации выборки с помощью
DISTINCT
и применения «окна» к агрегатной функции:WITH src AS (
SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime
)
SELECT DISTINCT
array_agg(prime) FILTER(WHERE prime < 10) OVER() primeslt
, array_agg(prime) FILTER(WHERE prime > 10) OVER() primesgt
FROM
src;
Единственная проблема — такая группировка «небесплатна»:
Сложный агрегат
Но предположим, что мы хотим что-то «этакое сложное», для чего нет подходящего агрегата:
->
$1 = '{2,3,5,7,11,13,17,19}'
<-
exp | val
2 * 3 * 5 * 7 * 11 * 13 * 17 * 19 = | 9699690
В этом примере мы хотим вычислить произведение всех участвующих чисел, но такой агрегатной функции — нету. Понятно, что ее можно создать, но как-то это неспортивно — создавать по функции, если потребность возникает редко.
Соберем запрос, который решит нашу задачу:
WITH RECURSIVE src AS (
SELECT
*
FROM
unnest('{2,3,5,7,11,13,17,19}'::integer[])
WITH ORDINALITY T(prime, rn)
)
, T(rn, exp, val) AS (
SELECT
0::bigint
-- база агрегации
, '{}'::integer[]
, 1
UNION ALL
SELECT
src.rn
-- итеративное вычисление сразу всех агрегатов
, exp || src.prime
, val * src.prime
FROM
T
JOIN
src
ON src.rn = T.rn + 1 -- переход к следующей записи
)
SELECT
array_to_string(exp, ' * ') || ' = ' exp
, val
FROM
T
ORDER BY -- отбор финального результата
rn DESC
LIMIT 1;
Кучеряво! Попробуем упростить и ускорить, опираясь на тот факт, что входной аргумент заведомо является массивом — то есть может быть перебран поэлементно:
WITH RECURSIVE src AS (
SELECT '{2,3,5,7,11,13,17,19}'::integer[] arr
)
, T(i, exp, val) AS (
SELECT
1::bigint
-- база агрегации
, '{}'::integer[]
, 1
UNION ALL
SELECT
i + 1
-- итеративное вычисление сразу всех агрегатов
, exp || arr[i]
, val * arr[i]
FROM
T
, src
WHERE
i <= array_length(arr, 1)
)
SELECT
array_to_string(exp, ' * ') || ' = ' exp
, val
FROM
T
ORDER BY -- отбор финального результата
i DESC
LIMIT 1;
Намного лучше!
Math.bonus
Применим
string_agg
и немного математической магии:WITH src AS (
SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime
)
SELECT
string_agg(prime::text, ' * ') || ' = ' exp
, exp(sum(ln(prime)))::integer val -- для любителей математики
FROM
src;