Комментарии 14
Не рассмотрен наиболее интересный вариант с помощью оконных функций. К тому же с группировками вы теряете сами строки, в которых как правило есть какие то важные данные. Ну и оконные функции позволяют все сделать за один проход, так как сами окна будут считаться в памяти, без обращений к физическим таблицам. На t-sql примерно так будут выглядеть все примерно так:
with data as (
SELECT
convert(date, ts) dt
, DATEPART (hour, ts) hr
FROM
timefact
WHERE
ts BETWEEN '2023-12-01' AND '2023-12-31'
)
SELECT
data.dt
, data.hr
, count() over () as [ОбщиеИтоги] , count() over (partition by data.dt) as [ИтогиДня]
, count() over (partition by data.dt, data.hr) as [ИтогиЧасаДня] , count() over (partition by data.hr) as [ИтогиЧасаВсехДней]
FROM
data
Чтобы получить "агрегаты" - то есть уменьшить количество исходных строк, в PostgreSQL (не в курсе про T-SQL) пришлось бы использовать конструкт вида:
SELECT DISTINCT ON(dt) dt, count(*) OVER (PARTITION BY dt)
И так для каждого способа группировки, что будет идентично CTE + UNION ALL за исключением множественных пересортировок в плане. Очевидно, что исходные строки при этом потеряются.
А без DISTINCT дополнительные значения будут расчитаны для каждой исходной записи, много-много раз, и не совсем понятно, как это соотносится с задачей.
Вот "склеить" выборку исходных данных (плюс пустые поля для ключей агрегации) и к ней итоги - это можно.
Нет. Вам не нужно ничего кроме ваше первого запроса с группировкой!
Оконные функции прекрасно работают с агрегаторами группировок. Не очень красивый синтаксис, поэтому я предпочитаю сами группировки выносить в CTE, но оптимизаторы достаточно умные чтобы развернуть CTE в один запрос. В ваш первый запрос с группировкой просто нужно добавить колонки, опять же в диалекте T-SQL:
...
, sum(count(*)) over () as [ОбщийИтог]
, sum(count(*)) over (partition by dt) as [ИтогДня]
, sum(count(*)) over (partition by dt, hr) as [ИтогЧаса]
, sum(count(*)) over (partition by hr) as [ИтогЧасов]
from ...
Наверное, я не до конца понимаю идею:
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
)
SELECT
dt
, hr
, count cnt_cell
, sum(count) OVER (PARTITION BY dt) cnt_day
, sum(count) OVER (PARTITION BY hr) cnt_hour
, sum(count) OVER () cnt_total
FROM
preagg;Такой запрос плодит дубли значений счетчиков:
А кроме этого имеет план с множественными пересортировками, о которых я писал выше:
Основная идея в принципе работы оконных функций. Обратите внимание - самые тяжелые операции вашего оптимального плана CTE +UNION ALL и варианта с оконными функциями технически одинаковые. Но возможно пока вы делали тесты ваш сервер решил кэшировать вашу таблицу. Потому на самом деле с вариантом холодного сервера время запросов практически будет одинаковое. Но главное в самих оконных функциях - они очень легко разносятся по разным процессорам. Не могу сказать как сделает PostgreSQL, но MSSQL однозначно раскидает каждое окно по разным процессорам. Ведь тут не нужно беспокоиться о блокировке или изменении данных.
По поводу дублирования счетчиков. Конечно это не относится к статье, но считаю нужно упомянуть. Это не дублирование. Это очень дешевое выполнение той работы которую позже нужно было бы сделать слою представления. Фактически это ничего не стоит, так как окна не считаются для каждой строки, если у вас есть sum(..) over () - она посчитается один раз и будет возвращать кэшированный результат для каждой строки. Но позже построителю предствления нужно будет совершить один единственный прямой обход запроса и на каждом шаге у него будут все необходимые данные для расчета цвета, итога строки или итога колонки.
Это не дублирование. Это очень дешевое выполнение той работы которую позже нужно было бы сделать слою представления.
Я по-прежнему не понимаю цели передавать одно и то же значение, например, итого-счетчика в каждой строке, если его можно передать один раз, в одной записи.
Потому что нет никакого смысла счетчик не передавать. Вы получаете некий resultSet в котором так же строго типизированные колонки и на счетчик отведено ровно столько байт сколько нужно для хранения типа. И даже если в колонке null представление этого null-а уложится в то-же самое поле, а остаток дополнится нулями. Но позже в слое представления не нужно делать лишнее действие в виде чтения первой строки и извлечения из него значения счетчитка в локальную переменную. Достаточно просто идти вперед и счетчик всегда под рукой. Но самый большой профит в том что даже не меняя запрос слой предствления может взять ваш результат и без проблем пересортировать его к примеру по время - дата. И опять спокойно пройти один раз имея все данные для отражения в перевернутом виде.
Достаточно просто идти вперед и счетчик всегда под рукой.
Это может быть полезно только для достаточно узкого класса задач вроде вычисления доли строки относительно итога.
Но позже в слое представления не нужно делать лишнее действие в виде чтения первой строки и извлечения из него значения счетчитка в локальную переменную.
Гораздо проще сделать fetchOne в переменную как раз. То есть подобные "преимущества" никак не оправдывают ни лишней работы СУБД, ни раздувания объема resultset, ни избыточных затрат на его разбор на стороне БЛ.
Это может быть полезно только для достаточно узкого класса задач вроде вычисления доли строки относительно итога.
Долю очень просто вычислить прямо на сервере:
, sum(count(*)) over () / (100 * sum(count(*)) over (partition by dt)) as [ПроцентДняОтПериода]
, sum(count(*)) over () / (100 * sum(count(*)) over (partition by dt, dh)) as [ПроцентЧасаОтПериода]
, sum(count(*)) over (partition by dе) / (100 * sum(count(*)) over (partition by dt, dh)) as [ПроцентЧасаОтДня]
И это будет очень дешево. Потому что каждая из оконных функций кэширует результат от параметров. Т.е. сколько бы вы не написали расчетов sum(count(*)) over () - расчет будет один раз, а sum(count(*)) over (partition by dt) выполнится по одному разу для каждого дня.
То есть подобные "преимущества" никак не оправдывают ни лишней работы СУБД, ни раздувания объема resultset, ни избыточных затрат на его разбор на стороне БЛ.
Технически для задач из статьи вообще не нужна общая сумма, ее всегда можно получить в конце обхода результат суммируя к один из агрегатов.
Но и в добавлении колонки я не вижу страшного. С одной стороны мы получили новую колонку в 4 байта (тип int) => 720 строк * 4 = 2.8 Кб. С другой стороны исчезли строки группировки (50 штук). Обходов стало поменьше.
Если честно, совершенно не понял, для чего рассматривались варианты без GROUPING SET. Ладно бы это было расширение, а то ведь стандарт SQL.
Причин три:
некоторые все еще живут на 9.4 или на форках от еще более ранних версий, где их нет
некоторые ORM "не разумеют"
GROUPING SETS, а задача-то никуда не деласьпоказать разницу для тех, кто "я так привык, мне и так удобно", и, например, вместо
HAVINGпри группировке используют подзапрос +WHERE
Не думаю, что пытаться поддерживать версии, не поддерживаемые mainstream - хорошая идея. Ваш выбор, конечно. Просто лично я его не приветствую по вполне объективным причинам.
ORM - вообще отдельная история. Опять таки, если он не формирует оптимальный запрос и это критично, то проблема решается представлениями и функциями, а вовсе не временными таблицами, использование которых в PostgreSQL - не лучшая идея.
Последний аргумент понятен. Но так как Вы пишете туториал, то логичней было бы с самого начала описать самый эффективный вариант, а уже потом уже доказывать, что остальные варианты менее эффективны. Психологически, многие дойдя до первого рабочего варианта берут в работу его. Не всегда дочитав туториал до конца. Я даже на работе с таким регулярно сталкиваюсь.
Ну и последний штрих. Раз уж пишете туториал, то стоило бы рассмотреть все возможности GROUPING SETS, включая и [DISTINCT] ROLLUP.
Психологически, многие дойдя до первого рабочего варианта берут в работу его. Не всегда дочитав туториал до конца. Я даже на работе с таким регулярно сталкиваюсь.
Есть и обратная сторона, тоже сталкиваюсь: "Фу, как это для меня сложно, даже читать дальше не буду!"
Раз уж пишете туториал, то стоило бы рассмотреть все возможности GROUPING SETS, включая и [DISTINCT] ROLLUP.
Я не ставил себе тут цели разбирать все возможности SQL, только показать возможные решения конкретной прикладной задачи.
Есть и обратная сторона, тоже сталкиваюсь: "Фу, как это для меня сложно, даже читать дальше не буду!"
Если бы Вы, например, уходили в дебри рекурсивных запросов для skip index scan или даже LATERAL с UNION ALL вместо OR - я бы это понял. Но тут все в рамках стандарта SQL, с которым интересующийся данным вопросом по любому должен ознакомиться.
Я не ставил себе тут цели разбирать все возможности SQL, только показать возможные решения конкретной прикладной задачи.
Ваша статья - Вам и решать. Я лишь высказал свое субъективное мнение. А прислушиваться к нему или нет - Ваше дело.
SQL HowTo: итоги по строкам и столбцам «в одно действие»