Немного отвлечемся от простых SELECT и посмотрим на реальной бизнес-задаче построения различных "тепловых карт" и "шахматок", как знание возможностей SQL может облегчить жизнь и разработчику, и его базе.
Обычно это начинается с "хотелок" бизнеса вроде "а вот тут мы нарисуем почасовую активность с динамикой по часам и суткам"...
![Активность по часам и дням Активность по часам и дням](https://habrastorage.org/getpro/habr/upload_files/a86/3fc/d1b/a863fcd1b37baf8deefb52b2bb4130c4.png)
... или "нам нужен отчет по статусам задач в разрезе сотрудников с общими итогами", ...
![Задачи по сотрудникам с общими итогами Задачи по сотрудникам с общими итогами](https://habrastorage.org/getpro/habr/upload_files/66e/94e/6c3/66e94e6c3911506a792e3c2c54df3b01.png)
... или даже "нам нужен список документов на выполнении с их общим количеством и детализацией по исполнителям и клиентам":
![Список документов со счетчиками по исполнителям и клиентам Список документов со счетчиками по исполнителям и клиентам](https://habrastorage.org/getpro/habr/upload_files/04f/b48/44d/04fb4844d578c234a229c974b99101cd.png)
Суть всех этих задач примерно одна и та же: у нас есть некоторый исходный набор фактов в БД, а в интерфейсе хочется получить одновременно агрегаты в нескольких разрезах.
Давайте попробуем на примере первой задачи с тепловой картой на временном интервале разобрать несколько вариантов возможной реализации на стороне БД, чтобы найти:
количество фактов в каждой "клетке" день/час
количество фактов в каждом дне
количество фактов в каждом часе
количество фактов на всем интервале
Но сначала сформируем таблицу из миллиона случайным образом распределенных исходных "фактов" по аналогии с использовавшейся в предыдущей статье "SQL HowTo: TOP-N на субинтервалах":
CREATE TABLE timefact AS
SELECT
'2023-01-01'::date
+ '1 sec'::interval * (random() * 365 * 86400)::integer ts -- время факта
FROM
generate_series(1, 1e6);
-- без индекса - никуда
CREATE INDEX ON timefact(ts);
Итак, попробуем посчитать искомые данные на интервале декабря.
Очевидно, сначала нам надо научиться получать данные для самой "матрицы" с координатами (день, час)
:
EXPLAIN (ANALYZE, BUFFERS)
SELECT
ts::date dt
, extract(hour FROM ts) hr
, count(*)
FROM
timefact
WHERE
ts BETWEEN '2023-12-01' AND '2023-12-31'
GROUP BY
1, 2
ORDER BY
1, 2;
Посмотрим на план этого запроса:
![66мс на чтение всех "фактов" с упорядоченной группировкой 66мс на чтение всех "фактов" с упорядоченной группировкой](https://habrastorage.org/getpro/habr/upload_files/150/858/fd4/150858fd498cdbf3d3878d4ed61cab3e.png)
Из 66мс почти треть заняла сортировка. В принципе, если мы можем позволить себе переупорядочивать данные на бизнес-логике, то от упорядочивания результата мы можем отказаться:
SELECT
ts::date dt
, extract(hour FROM ts) hr
, count(*)
FROM
timefact
WHERE
ts BETWEEN '2023-12-01' AND '2023-12-31'
GROUP BY
1, 2;
Это сэкономит нам примерно четверть времени:
![48мс на неупорядоченную группировку 48мс на неупорядоченную группировку](https://habrastorage.org/getpro/habr/upload_files/5dc/6de/5ec/5dc6de5ec6d91b1fdce8305a85e22a03.png)
А вот дальше - интереснее... Декабрь-то у нас не завершен, а вполне себе идет "прямо сейчас", поэтому просто выполнить последовательно 4 независимых запроса с нужной агрегацией по исходным данным не выйдет - цифры разбегутся.
Значит, нам необходимо как-то "зафиксировать" данные - и в PostgreSQL мы можем сделать это разными способами.
Временная таблица
Первый способ заключается в формировании временной таблицы с уже предагрегированными данными:
CREATE TEMPORARY TABLE preagg AS
SELECT
ts::date dt
, extract(hour FROM ts) hr
, count(*)
FROM
timefact
WHERE
ts BETWEEN '2023-12-01' AND '2023-12-31'
GROUP BY
1, 2;
Поскольку данные нам все-таки надо "записать", выполнение этого запроса будет примерно на 50% дольше. Зато дальше все просто и быстро - каждый запрос меньше 1мс:
-- перечитываем сформированные "клетки"
TABLE preagg;
-- по дням
SELECT
dt
, sum(count)
FROM
preagg
GROUP BY 1;
-- по часам
SELECT
hr
, sum(count)
FROM
preagg
GROUP BY 1;
-- "итого"
SELECT
sum(count)
FROM
preagg;
Правда, при активном использовании временных таблиц может "пухнуть" системный каталог (таблицы pg_class, pg_attribute, ...
), постепенно замедляя все запросы.
Несколько запросов в транзакции
В качестве альтернативы можно рассмотреть вариант транзакции в режиме REPEATABLE READ, где каждый из запросов будет "ходить" по исходным данным:
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- по "клеткам"
SELECT
ts::date dt
, extract(hour FROM ts) hr
, count(*)
FROM
timefact
WHERE
ts BETWEEN '2023-12-01' AND '2023-12-31'
GROUP BY
1, 2;
-- по дням
SELECT
ts::date dt
, count(*)
FROM
timefact
WHERE
ts BETWEEN '2023-12-01' AND '2023-12-31'
GROUP BY
1;
-- по часам
SELECT
extract(hour FROM ts) hr
, count(*)
FROM
timefact
WHERE
ts BETWEEN '2023-12-01' AND '2023-12-31'
GROUP BY
1;
-- "итого"
SELECT
count(*)
FROM
timefact
WHERE
ts BETWEEN '2023-12-01' AND '2023-12-31';
COMMIT;
Однако, мы перечитывали исходные данные и вычисляли ключи агрегации каждый раз, увеличив время выполнения запроса, примерно в те же 4 раза. Не говоря уж о том, что длительные транзакции (если этот отчет будет достаточно долгим) в PostgreSQL могут принести проблем.
CTE + UNION ALL
А почему бы нам не вычислить и вернуть сразу все данные за один запрос?..
Договоримся о формате ответа:
(dt IS NOT NULL, hr IS NOT NULL)
- "клетка"(dt IS NOT NULL, hr IS NULL)
- по дням(dt IS NULL, hr IS NOT NULL)
- по часам(dt IS NULL, hr IS NULL)
- "итого"
Вместо временной таблицы воспользуемся CTE, а результаты запросов "склеим" через UNION ALL:
WITH preagg AS (
SELECT
ts::date dt
, extract(hour FROM ts) hr
, count(*)
FROM
timefact
WHERE
ts BETWEEN '2023-12-01' AND '2023-12-31'
GROUP BY
1, 2
)
TABLE preagg
UNION ALL
SELECT
dt
, NULL hr
, sum(count) count
FROM
preagg
GROUP BY 1
UNION ALL
SELECT
NULL dt
, hr
, sum(count) count
FROM
preagg
GROUP BY 2
UNION ALL
SELECT
NULL dt
, NULL hr
, sum(count) count
FROM
preagg;
В принципе, на этом можно бы и остановиться, поскольку в плане уже все достаточно неплохо:
![47мс - несколько агрегаций по CTE 47мс - несколько агрегаций по CTE](https://habrastorage.org/getpro/habr/upload_files/c4f/adf/7c9/c4fadf7c9340b3d2182719684a0d3821.png)
GROUPING SETS
Но все-таки как-то "неаккуратненько" - слишком много повторяющегося кода нам пришлось написать. Но ведь этого можно и не делать, если воспользоваться функционалом наборов группирования.
Необходимый нам вариант группировок можно записать так:
GROUPING SETS (
(dt, hr)
, (dt )
, ( hr)
, ( )
)
-- или короче:
GROUPING SETS (
CUBE(dt, hr)
)
После чего наш запрос сокращается всего-то до вот такого:
SELECT
ts::date dt
, extract(hour FROM ts) hr
, count(*)
FROM
timefact
WHERE
ts BETWEEN '2023-12-01' AND '2023-12-31'
GROUP BY
GROUPING SETS (
CUBE(1, 2)
);
Такой план анализировать гораздо приятнее:
![68мс - GROUPING SETS 68мс - GROUPING SETS](https://habrastorage.org/getpro/habr/upload_files/d04/f77/59d/d04f7759d007c55c05f9bb39048bb095.png)
В большинстве случаев на этом и стоит остановиться, если значения ключей агрегации у вас не требуют дополнительных вычислений.
Однако, это не наш случай, поскольку мы вычисляли ключи для каждой исходной записи для каждого варианта агрегации независимо - от этого время выполнения нашего запроса существенно выросло!
CTE + GROUPING SETS
"Спрячем" вычисление ключей и предварительную агрегацию обратно "под CTE" и уберем повторные вычисления из GROUPING SETS
:
WITH preagg AS (
SELECT
ts::date dt
, extract(hour FROM ts) hr
, count(*)
FROM
timefact
WHERE
ts BETWEEN '2023-12-01' AND '2023-12-31'
GROUP BY
1, 2
)
TABLE preagg
UNION ALL
SELECT
dt
, hr
, sum(count) count
FROM
preagg
GROUP BY
GROUPING SETS (
1
, 2
, ()
);
Теперь наш запрос снова так же эффективен, как вариант с CTE + UNION ALL
, но написать нам пришлось существенно меньше кода:
![47мс - CTE + GROUPING SETS 47мс - CTE + GROUPING SETS](https://habrastorage.org/getpro/habr/upload_files/450/0bc/064/4500bc06483a12a9218dac8e0f1476e1.png)
Всем хочу напомнить, что для эффективной профилировки ваших запросов к PostgreSQL в виде таких красивых картинок вы можете совершенно свободно воспользоваться нашим сервисом explain.tensor.ru или приобрести его для корпоративных нужд.
![Профилирование SQL-запроса Профилирование SQL-запроса](https://habrastorage.org/getpro/habr/upload_files/c97/365/fbf/c97365fbf3ed5d2953874c52535feea1.png)