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

Есть выборка вида:
Id Value 1 5 2 5 3 6 4 3 5 5
Мне нужно сравнить значения Value из строк с Id равными N и N+1 (соседними строками). Т.е. для простоты считаем, что Id - непрерывная последовательность натуральных чисел.
Вопросы:
1) Как это лучше, а главное, оптимальней сделать?
2) Всё то же самое, но мне нужно сравнить значения N подряд идущих строк... Как это оптимальней сделать?

Ответ

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