А чтобы эти итоги собрать, необходимо по исходным данным вычислить значение некоторой агрегатной функции: количество, сумма, среднее, минимум, максимум, и, как правило, не одной.
Сегодня мы рассмотрим некоторые способы, с помощью которых можно вычислить агрегаты в 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;
