Основная идея в принципе работы оконных функций. Обратите внимание - самые тяжелые операции вашего оптимального плана CTE +UNION ALL и варианта с оконными функциями технически одинаковые. Но возможно пока вы делали тесты ваш сервер решил кэшировать вашу таблицу. Потому на самом деле с вариантом холодного сервера время запросов практически будет одинаковое. Но главное в самих оконных функциях - они очень легко разносятся по разным процессорам. Не могу сказать как сделает PostgreSQL, но MSSQL однозначно раскидает каждое окно по разным процессорам. Ведь тут не нужно беспокоиться о блокировке или изменении данных.
По поводу дублирования счетчиков. Конечно это не относится к статье, но считаю нужно упомянуть. Это не дублирование. Это очень дешевое выполнение той работы которую позже нужно было бы сделать слою представления. Фактически это ничего не стоит, так как окна не считаются для каждой строки, если у вас есть sum(..) over () - она посчитается один раз и будет возвращать кэшированный результат для каждой строки. Но позже построителю предствления нужно будет совершить один единственный прямой обход запроса и на каждом шаге у него будут все необходимые данные для расчета цвета, итога строки или итога колонки.
Нет. Вам не нужно ничего кроме ваше первого запроса с группировкой!
Оконные функции прекрасно работают с агрегаторами группировок. Не очень красивый синтаксис, поэтому я предпочитаю сами группировки выносить в 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 [ИтогЧасов]
Не рассмотрен наиболее интересный вариант с помощью оконных функций. К тому же с группировками вы теряете сами строки, в которых как правило есть какие то важные данные. Ну и оконные функции позволяют все сделать за один проход, так как сами окна будут считаться в памяти, без обращений к физическим таблицам. На 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
Основная идея в принципе работы оконных функций. Обратите внимание - самые тяжелые операции вашего оптимального плана CTE +
UNION ALLи варианта с оконными функциями технически одинаковые. Но возможно пока вы делали тесты ваш сервер решил кэшировать вашу таблицу. Потому на самом деле с вариантом холодного сервера время запросов практически будет одинаковое. Но главное в самих оконных функциях - они очень легко разносятся по разным процессорам. Не могу сказать как сделает PostgreSQL, но MSSQL однозначно раскидает каждое окно по разным процессорам. Ведь тут не нужно беспокоиться о блокировке или изменении данных.По поводу дублирования счетчиков. Конечно это не относится к статье, но считаю нужно упомянуть. Это не дублирование. Это очень дешевое выполнение той работы которую позже нужно было бы сделать слою представления. Фактически это ничего не стоит, так как окна не считаются для каждой строки, если у вас есть sum(..) over () - она посчитается один раз и будет возвращать кэшированный результат для каждой строки. Но позже построителю предствления нужно будет совершить один единственный прямой обход запроса и на каждом шаге у него будут все необходимые данные для расчета цвета, итога строки или итога колонки.
Нет. Вам не нужно ничего кроме ваше первого запроса с группировкой!
Оконные функции прекрасно работают с агрегаторами группировок. Не очень красивый синтаксис, поэтому я предпочитаю сами группировки выносить в 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 ...
Не рассмотрен наиболее интересный вариант с помощью оконных функций. К тому же с группировками вы теряете сами строки, в которых как правило есть какие то важные данные. Ну и оконные функции позволяют все сделать за один проход, так как сами окна будут считаться в памяти, без обращений к физическим таблицам. На 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