Как сравнить значения из соседних строк в отсортированном наборе?

#sql #sql_server #оптимизация

Есть выборка вида:

Id Value
1 5
2 5
3 6
4 3
5 5


Мне нужно сравнить значения Value из строк с Id равными N и N+1 (соседними строками).
Т.е. для простоты считаем, что Id - непрерывная последовательность натуральных чисел.

Вопросы:

1) Как это лучше, а главное, оптимальней сделать?

2) Всё то же самое, но мне нужно сравнить значения N подряд идущих строк... Как это
оптимальней сделать?
    

Ответы

Ответ 1

Ну тут, собственно, какие варианты могут быть.

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

Если вычислять на лету - то либо windowed функции, либо join-ы на себя:

select
    d.id, d.value,
    prev = lag(d.value) over (order by d.id)
from data d

select
    d.id, d.value,
    prev = d_lag.value
from data d
    left join data d_lag on d_lag.id = d.id - 1


Если используются оконные функции, то для оптимальной производительности желательно
придерживаться т.н. POC (Partitioning, Ordering, Coverage) индексации. Т.е. в данном
случае должен быть кластерный индекс по Id, либо некластерный покрывающий index (Id)
include (Value). При использовании join-ов эти индексы также пригодятся.

Не совсем понятно, что значит "сравнить значения N подряд идущих строк". Вам нужно
N предыдущих строк разложить по столбцам?

select
    id, value,
    prev = lag(value) over (order by id),
    prev2 = lag(value, 2) over (order by id),
    prev3 = lag(value, 3) over (order by id),
    prev4 = lag(value, 4) over (order by id)
from data

select
    d.id, d.value,
    prev = d_lag.value,
    prev2 = d_lag2.value,
    prev3 = d_lag3.value,
    prev4 = d_lag4.value
from data d
    left join data d_lag on d_lag.id = d.id - 1
    left join data d_lag2 on d_lag2.id = d.id - 2
    left join data d_lag3 on d_lag3.id = d.id - 3
    left join data d_lag4 on d_lag4.id = d.id - 4


Или нужно скользящее среднее по N предыдущим строкам?

select
    d.id,
    [ma20(-1)] = avg(d.value) over (order by d.id rows between 20 preceding and 1
preceding)
    [ma20(0)] = avg(d.value) over (order by d.id rows between 19 preceding and current
row),
from data d

select
    d.id,
    [ma20(-1)] = [s-1].ma20,
    [ma20(0)] = s0.ma20 
from data d
    cross apply (
        select ma20 = avg(d2.value)
        from data d2
        where d2.id between d.id - 19 and d.id
    ) s0
    cross apply (
        select ma20 = avg(d2.value)
        from data d2
        where d2.id between d.id - 20 and d.id - 1
    ) [s-1]


Можете попробовать эти запросы на своих данных. На тестовых данных в 100 тыс. строк
время исполнения запросов для двух методов (windowed и join) у меня получалось сравнимо,
только последний запрос выигрывал по времени за счёт параллелелизма, но IO всюду в
пользу windowed версий (в случае со скользящим средним - значительно). Вообще по IO
все эти windowed запросы сравнимы с простым select id, value from data.

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