Но 2ГИС будет ими пользоваться. Они ведь случайно в карты встроили постоянный фоновый геотрекер и назвали это Друзья на карте. И это не скрывается они подробно описывают в своем блоге "Доставили" подробности что этот трекер работает всегда как фоновая служба.
Заметьте ругается только на вашу последнюю предновогоднюю версию. Если удалить и поставить версию помладше претензий нет. А что у вас появилось перед новым годом - друзья на карте. Со всеми другими правами сберовские приложения живут спокойно, sms получают (бизнис клиент), обновления ставят.
И знаете... Именно из-за вашей этой новики, которую никак нельзя отключить, только якобы "сказать кому можно меня видеть", лично я согласился с гуглом и таки удалил приложения. Хотя честно скажу оно мне нравилось больше конкурентов.
Это может быть полезно только для достаточно узкого класса задач вроде вычисления доли строки относительно итога.
Долю очень просто вычислить прямо на сервере:
, 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 штук). Обходов стало поменьше.
Потому что нет никакого смысла счетчик не передавать. Вы получаете некий resultSet в котором так же строго типизированные колонки и на счетчик отведено ровно столько байт сколько нужно для хранения типа. И даже если в колонке null представление этого null-а уложится в то-же самое поле, а остаток дополнится нулями. Но позже в слое представления не нужно делать лишнее действие в виде чтения первой строки и извлечения из него значения счетчитка в локальную переменную. Достаточно просто идти вперед и счетчик всегда под рукой. Но самый большой профит в том что даже не меняя запрос слой предствления может взять ваш результат и без проблем пересортировать его к примеру по время - дата. И опять спокойно пройти один раз имея все данные для отражения в перевернутом виде.
Основная идея в принципе работы оконных функций. Обратите внимание - самые тяжелые операции вашего оптимального плана 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
Но 2ГИС будет ими пользоваться. Они ведь случайно в карты встроили постоянный фоновый геотрекер и назвали это Друзья на карте. И это не скрывается они подробно описывают в своем блоге "Доставили" подробности что этот трекер работает всегда как фоновая служба.
Заметьте ругается только на вашу последнюю предновогоднюю версию. Если удалить и поставить версию помладше претензий нет. А что у вас появилось перед новым годом - друзья на карте. Со всеми другими правами сберовские приложения живут спокойно, sms получают (бизнис клиент), обновления ставят.
И знаете... Именно из-за вашей этой новики, которую никак нельзя отключить, только якобы "сказать кому можно меня видеть", лично я согласился с гуглом и таки удалил приложения. Хотя честно скажу оно мне нравилось больше конкурентов.
Долю очень просто вычислить прямо на сервере:
, 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) выполнится по одному разу для каждого дня.
Технически для задач из статьи вообще не нужна общая сумма, ее всегда можно получить в конце обхода результат суммируя к один из агрегатов.
Но и в добавлении колонки я не вижу страшного. С одной стороны мы получили новую колонку в 4 байта (тип int) => 720 строк * 4 = 2.8 Кб. С другой стороны исчезли строки группировки (50 штук). Обходов стало поменьше.
Потому что нет никакого смысла счетчик не передавать. Вы получаете некий resultSet в котором так же строго типизированные колонки и на счетчик отведено ровно столько байт сколько нужно для хранения типа. И даже если в колонке null представление этого null-а уложится в то-же самое поле, а остаток дополнится нулями. Но позже в слое представления не нужно делать лишнее действие в виде чтения первой строки и извлечения из него значения счетчитка в локальную переменную. Достаточно просто идти вперед и счетчик всегда под рукой. Но самый большой профит в том что даже не меняя запрос слой предствления может взять ваш результат и без проблем пересортировать его к примеру по время - дата. И опять спокойно пройти один раз имея все данные для отражения в перевернутом виде.
Основная идея в принципе работы оконных функций. Обратите внимание - самые тяжелые операции вашего оптимального плана 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