SQL, Анализ данных

Вычисление диапазонов с применением оконных функций

Время прочтения: 3 мин.

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

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

Name Type
id Int  (primary key)
pay_date date
payer nvarchar
amount money
currency Nvarchar  

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

select 
	pay_date,
	(
	select min(pay_date)
		from payments t2
		where t2.pay_date >= t1.pay_date
			and not exists 
				(
				select * from payments t3
				where t3.pay_date = dateadd(day, 1, t2.pay_date)
				)
	)  group_date
from payments t1

Дальше находим минимальное и максимальное значение даты, сгруппировав их по идентификатору, поместив наш запрос в предложение from другого запроса:

select
	 min(pay_date) as start_date,
	 max(pay_date) as end_date
from 
	(
	select 
		pay_date,
		 (
		select min(pay_date)
			from payments t2
			where t2.pay_date >= t1.pay_date
				and not exists 
					 (
					select * from payments t3
					where t3.pay_date = dateadd(day, 1, t2.pay_date)
					)
		) group_date
	from payments t1
	) as t4
group by group_date

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

Существенно ускорить и упростить выполнение задачи нам помогло оконные функции, а именно функции ранжирования ROW_NUMBER, которая последовательно нумерует все строки таблицы с сортировкой по дате. Идентификатор группы можно задать, отнимая от текущего значения даты, дни, в количестве, равном номеру строки: с увеличением даты на один день, номер строки так же увеличивается на единицу. До тех пор, пока даты идут последовательно, идентификатор не будет меняться.

select
	pay_date,
	dateadd(day, -ROW_NUMBER() OVER (ORDER BY pay_date), pay_date) as group_date
from payments

Остается только, аналогично предыдущему запросу, минимальное и максимальное значение даты, проведя группировку:

select  
	min(pay_date) as start_date,
	max(pay_date) as end_date
from
	(
	select
		pay_date,
		dateadd(day, -ROW_NUMBER() OVER (ORDER BY pay_date), pay_date) as group_date
	from payments
	) as t1
group by group_date

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

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