Лайфхаки в IT

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

Время прочтения: 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 строк.

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

Советуем почитать