Группировка данных по элементам диапазона в MSSQL

Категория: Код

– Автор: Игорь (Администратор)

Основной проблемой группировки данных по элементам диапазона является отсутствие строк в используемой выборке для одного или нескольких элементов. Представьте себе, что вы ведете ежедневный учет расходов. Все свои траты вы раскладываете по позициям (продукты, интернет и т.д.). В один прекрасный день, вы решили использовать графики, чтобы понять в какие дни месяца вы тратите основную часть своих средств. И вроде бы все хорошо. Однако, при первом же использовании вы быстро натолкнулись на одну простую проблему. Вы тратите деньги не каждый деньги. Поэтому, вместо красивого и информативного графика, перед вами возникло нечто непонятное и не связное, состоящее только из дней с расходами. 

Глобально решать данную проблему можно разными способами:

• Создание нулевых или пустых записей
• Добавление отсутствующих строк до момента передачи данных (обработка данных не силами БД)
• Использование существующих таблиц, содержащих необходимый набор элементов
• Создание отдельных таблиц с нужными наборами
• Создание временных таблиц
• Использование CTE

Примечание: Конечно, существуют и другие способы. В данном случае были взяты только основные, так как их можно применить практически к любой базе данных.

Создание нулевых записей в таблице - это не самая лучшая идея, так как у такого решения много минусов. Во-первых, вы уверены, что вам не понадобится считать само количество записей?. Во-вторых, каким последним элементов вы решите ограничиться для бесконечных данных (например, дата)? В-третьих, как вы думаете, как быстро база данных будет разрастаться? В-четвертых, как часто и каким образом вы будете проверять наличие нужных записей?

Из плюсов наверно только то, что сами запросы будут оставаться простыми - как будто бы проблемы и не было. Как итог, это решение может подойти только для небольших фиксированных наборов. 

Примечание: У дат в MSSQL есть предельное значение. Однако, это не меняет сути. Для перечисления всех возможных дней потребуется огромное количество записей.

Добавление отсутствующих строк до момента передачи данных. На самом деле, это самое оптимальное решение, так как не только сами запросы остаются простыми, но и снимается лишняя нагрузка с базы данных. Как, например, в случае с web, всю нагрузку легко можно перенести на javascript. Составить небольшой цикл для добавления пустых строчек - не так уж сложно.

Из минусов стоит выделить сложность и неудобство при масштабном применении. Если одни и те же данные будут часто использоваться в разных местах, то это может привести к путанице и ошибкам. Особенно, если одних случаях данные будут выводится как есть, а в других предварительно обрабатываться различными способами.

Использование существующих таблиц с необходимым набором элементов - это, практически всегда, отложенная проблема. Тут достаточно много вопросов, в которых необходимо быть уверенным. Данные в таблицах не изменятся? Структура таблицы не поменяется? Как быстро вы обнаружите, что каких-то элементов не хватает? А точно ли не будет такого диапазона, для которого не найдутся нужные элементы? И так далее. Из плюсов, как и с нулевыми записями, простота запроса.

Создание отдельных таблиц с нужными наборами. По сути, это то же создание справочников. Такой способ можно применять только для фиксированных и небольших наборов. Создание таблицы для 2 млн элементов - достаточно расточительное дело. Особенно, если необходимо получать данные для диапазонов из ста элементов. К минусам так же стоит отнести тот факт, что с каждым добавленным справочником "для запросов" ваша схема базы данных будет разрастаться.

Созданием временных таблиц. Конечно, это универсальный способ, который подходит не только для MSSQL. Но, времени отнимает массу. Достаточно взглянуть на пример, чтобы увидеть его недостатки:

-- Создаем временную таблицу
create table ##temptable (dayNum date);
-- Заполняем диапазон
-- Как аналог можно использовать while - не суть важно
insert into ##temptable (dayNum) values ('2014-11-01');
insert into ##temptable (dayNum) values ('2014-11-02');
insert into ##temptable (dayNum) values ('2014-11-03');
--.....
-- Собственно, сам запрос
select *
from ##temptable
--..... join и т.д.
-- Удаляем временную таблицу
drop table ##temptable;

Вам нужно не только создавать и наполнять таблицу, но и следить за ее использованием и удалением. Неаккуратное использование одной и той же таблицы в разных процедурах легко может привести к ошибкам. Особенно, если вы будете использовать не сессионные временные таблицы, а глобальные.

Использование CTE. Начиная с MSSQL 2005 появилась возможность использовать рекурсивные запросы, с помощью технологии под названием Common Tree Expressions (CTE). И хоть ее основное назначение это облегчение обработки древовидных данных и избавление базы данных от нагромождения временных таблиц (на момент MSSQL 2000, временные таблицы были единственной возможностью получить дерево в виде списка), CTE так же можно использовать и для решения ряда других задач. В данном случае, для составления списка элементов диапазона. Выглядеть это будет примерно так:

-- Переменные для задания границ
-- Использовать переменные не обязательно, 
-- можно и напрямую вставлять границы в запрос
declare @start date;
declare @end date;
 
-- Задаем границы
select @start = '2014-11-01', @end = '2015-11-05';
 
-- Определяем CTE
with period as
(
    -- Задаем нижнюю грань
    select @start as [Date]
    -- Объединяем результаты
    union all
    -- Определяем следующий элемент
    select dateadd(day, 1, period.[Date]) as [Date]
    -- Используем саму таблицу
    from period
    -- Указываем точку остановки.
    where period.[Date] < @end
)
 
-- Выводим наш запрос
select *
from period
--.... join и т.д.
-- Данный параметр задается после order, т.е. в самом конце
-- Параметр задает максимальное количество рекурсивных вызовов. 
-- По умолчанию он равен 100
option (maxrecursion 0)
;

Запрос с CTE будет выполняться медленнее, чем при использовании справочников, и требует дополнительных ресурсов. Но, его вполне можно использовать для небольших и динамических диапазонов. Основной плюс использования CTE - вам не нужно ничего наполнять или создавать.

Так же к плюсам можно отнести тот факт, что каждый вызов будет создавать отдельный запрос, так что вам не придется следить за блокировками записей и тому подобными вещами. Вспомните о временных таблицах или справочниках. Как пример, если справочник часто обновляется, включая существующие элементы, то большое количество запросов, использующих данный справочник, могут привести к серьезным задержкам по времени. 

Примечание: Помните, что по умолчанию у CTE есть ограничение на количество количество рекурсивных запросов - 100.

Теперь вы знаете об основных способах группировки данных по элементам диапазонов, а так же об их минусах и плюсах.