Время прочтения: 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
Такое решение оказалось значительно производительнее и доступнее для понимания. В дальнейшем его можно адаптировать не только для поиска дат, но и для других типов данных, поддерживающих полное упорядочение.