Страницы

Поиск по вопросам

среда, 25 декабря 2019 г.

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

#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 следует, вероятно, предпочесть то, что работает быстрее на конкретных данных.

Комментариев нет:

Отправить комментарий