Разработка
NTA

Цикл WHILE для выбора данных за период в T-SQL

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

Зачастую в нашей работе возникает потребность получить набор данных за определенный период. Сделать это можно несколькими способами. В этой статье рассмотрим применение цикла 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 строк.

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

4 показа
9K открытий
0
3 комментария
Viktor Gladkih

@NTA , почему бы просто не использовать в WHERE  [dh].[DEPOHIST_OpDay] between  @startdate and @enddate? 

Ответить
Развернуть ветку
Andrey Vladimirsky

Может, чтобы транзакт лог не раздуть, или не создавать слишком долгую транзакцию?

Ответить
Развернуть ветку
Viktor Gladkih

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

Ответить
Развернуть ветку
Написать комментарий...
0 комментариев
Раскрывать всегда