Разработка
NTA

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

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

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

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

При решении этой задачи, были применены традиционные конструкции языка 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

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

{ "author_name": "NTA", "author_type": "editor", "tags": [], "comments": 0, "likes": 1, "favorites": 2, "is_advertisement": false, "subsite_label": "dev", "id": 218782, "is_wide": true, "is_ugc": false, "date": "Thu, 11 Mar 2021 07:48:53 +0300", "is_special": false }
0
0 комментариев
Популярные
По порядку

Комментарии

null