Время прочтения: 4 мин.
Зачастую в нашей работе возникает потребность получить набор данных за определенный период. Сделать это можно несколькими способами. В этой статье рассмотрим применение цикла WHILE для задачи поиска расходных операций за несколько месяцев по перечню счетов и сравним его с запросом, в котором весь период будет указан в блоке WHERE.
Для начала разберем синтаксис конструкции WHILE. Выглядит он следующим образом:
WHILE [логическое условие]
BEGIN
[инструкция]
END
В блоке Условие находится выражение, возвращающее значение TRUE или FALSE, в блоке Инструкций будет находиться наш запрос на выбор необходимого набора данных. Блок инструкций необходимо «ограничить» словами управления BEGIN и END.
Теперь рассмотрим на примере применение цикла WHILE и сравним его с простым запросом. Для начала создадим таблицы для перечня счетов (1000 счетов с движением средств), результатов простого запроса и результатов цикла WHILE.
CREATE TABLE [TB44_SANDBOX].[mis].[depohist_test_accounts]
(
[account_nbr] nvarchar(255)
) ON [PRIMARY] WITH (DATA_COMPRESSION = PAGE)
GO
CREATE TABLE [TB44_SANDBOX].[mis].[depohist_test_query]
(
[DEPOHIST_ID_MAJOR] bigint
,[DEPOHIST_ID_MINOR] bigint
,[DEPOHIST_ID_MEGA] bigint
,[DEPOSIT_ID_MAJOR] bigint
,[DEPOSIT_ID_MINOR] bigint
,[DEPOSIT_ID_MEGA] bigint
,[PERSON_ID_MAJOR] bigint
,[PERSON_ID_MINOR] bigint
,[PERSON_ID_MEGA] bigint
) ON [PRIMARY] WITH (DATA_COMPRESSION = PAGE)
GO
CREATE TABLE [TB44_SANDBOX].[mis].[depohist_test_while]
(
[DEPOHIST_ID_MAJOR] bigint
,[DEPOHIST_ID_MINOR] bigint
,[DEPOHIST_ID_MEGA] bigint
,[DEPOSIT_ID_MAJOR] bigint
,[DEPOSIT_ID_MINOR] bigint
,[DEPOSIT_ID_MEGA] bigint
,[PERSON_ID_MAJOR] bigint
,[PERSON_ID_MINOR] bigint
,[PERSON_ID_MEGA] bigint
) ON [PRIMARY] WITH (DATA_COMPRESSION = PAGE)
GO
Информация об операциях в нашем случае хранится в [BACKOFFICE_STG].[DEPOSIT].[VW_DEPOHIST], где созданы индексы на номер счета ([DEPOSIT_PRINTABLENO]) и дату операции ([DEPOHIST_OpDay]). Далее напишем запрос на выборку и вставку данных с указанием всего периода в блоке WHERE.
INSERT INTO [TB44_SANDBOX].[mis].[depohist_test_query]
SELECT
[dh].[DEPOHIST_ID_MAJOR]
,[dh].[DEPOHIST_ID_MINOR]
,[dh].[DEPOHIST_ID_MEGA]
,[dh].[DEPOSIT_ID_MAJOR]
,[dh].[DEPOSIT_ID_MINOR]
,[dh].[DEPOSIT_ID_MEGA]
,[dh].[PERSON_ID_MAJOR]
,[dh].[PERSON_ID_MINOR]
,[dh].[PERSON_ID_MEGA]
FROM
[BACKOFFICE_STG].[DEPOSIT].[VW_DEPOHIST] AS [dh] WITH(NOLOCK)
INNER JOIN [TB44_SANDBOX].[mis].[depohist_test_accounts] AS [t] WITH(NOLOCK)
ON [dh].[DEPOSIT_PRINTABLENO] = [t].[account_nbr]
WHERE
[dh].[DEPOHIST_OpCash] < 0
AND [dh].[DEPOHIST_OpDay] >= '2020-01-01'
AND [dh].[DEPOHIST_OpDay] < '2020-03-01'
Данный запрос выполнялся около полутора минут и вставил в таблицу 1606 строк.
Теперь напишем запрос с использованием цикла WHILE.
DECLARE @startdate date = '2020-01-01'
DECLARE @enddate date = '2020-03-01'
WHILE @startdate < @enddate
BEGIN
INSERT INTO [TB44_SANDBOX].[mis].[depohist_test_while]
SELECT
[dh].[DEPOHIST_ID_MAJOR]
,[dh].[DEPOHIST_ID_MINOR]
,[dh].[DEPOHIST_ID_MEGA]
,[dh].[DEPOSIT_ID_MAJOR]
,[dh].[DEPOSIT_ID_MINOR]
,[dh].[DEPOSIT_ID_MEGA]
,[dh].[PERSON_ID_MAJOR]
,[dh].[PERSON_ID_MINOR]
,[dh].[PERSON_ID_MEGA]
FROM
[BACKOFFICE_STG].[DEPOSIT].[VW_DEPOHIST] AS [dh] WITH(NOLOCK)
INNER JOIN [TB44_SANDBOX].[mis].[depohist_test_accounts] AS [t] WITH(NOLOCK)
ON [dh].[DEPOSIT_PRINTABLENO] = [t].[account_nbr]
WHERE
[dh].[DEPOHIST_OpCash] < 0
AND [dh].[DEPOHIST_OpDay] = @startdate
SET @startdate = DATEADD(DAY, 1, @startdate)
END
В начале определяем две переменные, в которых будет находится необходимый период. Далее логическое условие выполнения цикла – дата начала периода строго меньше даты окончания периода. В теле цикла между словами управления BEGIN и END наш запрос на выборку и вставку данных. Обратите внимание, что вместо периода для столбца [dh].[DEPOHIST_OpDay] мы указываем конкретное значение, которое содержит переменная @startdate. После запроса мы присваиваем нашей переменной новое значение в +1 день от текущего значения с помощью функции DATEADD(), чтобы перейти на следующий шаг выполнения цикла.
Таким образом наш цикл будет выполняться пока значение переменной @startdate не станет равным значению @enddate, и на каждом шаге цикла будет выполняться запрос на выборку данных за конкретную дату.
Теперь запустим этот запрос и сравним его результаты с предыдущим.
На вкладке «Сообщения» будет несколько строк, которые соответствуют выполненному запросу на каждом шаге цикла. Запрос с использованием цикла выполнялся 35 секунд и вставил в таблицу те же 1606 строк.
Данный способ позволяет быстрее получить необходимые данные, так как при каждом выполнении будет использоваться индекс на дату, и, если в результате выполнения запроса случится какая-либо ошибка, сохранить результат ранее отработанных шагов цикла.