Агрегаты

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

А чтобы эти итоги собрать, необходимо по исходным данным вычислить значение некоторой агрегатной функции: количество, сумма, среднее, минимум, максимум, и, как правило, не одной.


Сегодня мы рассмотрим некоторые способы, с помощью которых можно вычислить агрегаты в 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) countgtFROM  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) countgtFROM  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) countgtFROM  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) primesgtFROM  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) primesgtFROM  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) primesgtFROM  src;

Несколько агрегатов: Function Scan vs CTE

Мы тут внезапно вынесли наш исходный набор в CTE а почему? Потому что так банально быстрее. Давайте проверим на простом примере:

SELECT  array_agg(i) FILTER(WHERE i % 2 = 0) even, array_agg(i) FILTER(WHERE i % 2 = 1) oddFROM  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) oddFROM  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() primesgtFROM  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[]  , 1UNION 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, valFROM  TORDER BY -- отбор финального результата  rn DESCLIMIT 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[]  , 1UNION 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, valFROM  TORDER BY -- отбор финального результата  i DESCLIMIT 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;