Если дискуссия ушла далеко от темы и вроде Вы не против ответов на вопросы о личных предпочтениях можно почти личный вопрос :)
Я писал конечно об этом и в поддержку и в том самом Доставили вопрос задавал. Почему нельзя дать возможность выключить подсказки о полосе в автонавигаторе. Да понимаю важная штука когда что-то ищешь в незнакомом месте. Наверняка это было очень непросто сделать, скорей всего кто-то заслуженно этим может гордиться. Но как всегда есть присловутое Но. К примеру я и мой круг знакомых процентов 90 времени пользуемся машиной по одним и тем же маршрутам. Работа - Дом - Дача - Что-то еще. Факт в том что дорога известна до каждой ямки. Ну может раз в месяц максимум нужно поехать в какое-то новое место. Все нужно это только сведения о пробках. И вот для таких пользователей эти черные баннеры на карте вылазят в виде этих черных ... ну вообщем у меня очень плохая ассоциация :) И ладно я уже почти смерился но тут раз и добавили они стали вылазить уже как две ... :)
Ну разве так сложно добавить маленький свитчер - не показывать ... на карте :)
Я не говорю хорошая это или плохая вещь. Это каждый решает сам для себя. Я лишь говорю о том что постоянный геотрекинг из фоновой службы это очень и очень подходит под определение "угрозы". И трекинг этот явно не обезличенный, как то ведь идентифицируется пользователи, тем более их можно выбрать прям по своим контактам, т.е. по номеру телефона.
Но 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
Если дискуссия ушла далеко от темы и вроде Вы не против ответов на вопросы о личных предпочтениях можно почти личный вопрос :)
Я писал конечно об этом и в поддержку и в том самом Доставили вопрос задавал. Почему нельзя дать возможность выключить подсказки о полосе в автонавигаторе. Да понимаю важная штука когда что-то ищешь в незнакомом месте. Наверняка это было очень непросто сделать, скорей всего кто-то заслуженно этим может гордиться. Но как всегда есть присловутое Но. К примеру я и мой круг знакомых процентов 90 времени пользуемся машиной по одним и тем же маршрутам. Работа - Дом - Дача - Что-то еще. Факт в том что дорога известна до каждой ямки. Ну может раз в месяц максимум нужно поехать в какое-то новое место. Все нужно это только сведения о пробках. И вот для таких пользователей эти черные баннеры на карте вылазят в виде этих черных ... ну вообщем у меня очень плохая ассоциация :) И ладно я уже почти смерился но тут раз и добавили они стали вылазить уже как две ... :)
Ну разве так сложно добавить маленький свитчер - не показывать ... на карте :)
Я не говорю хорошая это или плохая вещь. Это каждый решает сам для себя. Я лишь говорю о том что постоянный геотрекинг из фоновой службы это очень и очень подходит под определение "угрозы". И трекинг этот явно не обезличенный, как то ведь идентифицируется пользователи, тем более их можно выбрать прям по своим контактам, т.е. по номеру телефона.
Но 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