Русский
Русский
English
Статистика
Реклама

SQL HowTo 1000 и один способ агрегации

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

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


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

Источник: habr.com
К списку статей
Опубликовано: 19.06.2020 12:20:11
0

Сейчас читают

Комментариев (0)
Имя
Электронная почта

Блог компании тензор

Postgresql

Sql

Высокая производительность

Программирование

Базы данных

Explain

Explain.tensor.ru

Sql tips and tricks

Array

Unnest

Рекурсия

Агрегаты

Ненормальное программирование

Категории

Последние комментарии

© 2006-2020, personeltest.ru