Сервер 2017.
Задачка такая, что для каждой записи с f=1 найти запись с f=2, предшествующую ей в порядке сортировки по полю n.
И в этом же наборе - наоборот тоже.
Для каждой записи с f=2 найти запись с f=1, предшествующую ей в порядке сортировки по полю n.
Ну и, допустим, получить сложить значение поля p текущей записи и найденной выше.

Понятно, что решается взаимопересечением, а также через cte.
Но что-то интересно про оконные))
По идее надо либо секцию задать такую, что в ней будет текущая запись с f=1, а предыдущие все с f=2 (и наоборот).
Либо задать порядок сортировки, где для записи с f=1 все предыдущие записи будут только с f=2
Нет же возможности в partition by или в order by задать выражение, которое строится с использованием значения поля текущей записи?

declare @a as table (
p int,
f int,
n int)

insert into @a values (1,1,10)
insert into @a values (1,1,11)
insert into @a values (1,2,12)
insert into @a values (1,3,13)
insert into @a values (2,4,14)
insert into @a values (2,5,15)
insert into @a values (2,1,16)
insert into @a values (2,1,17)

uaggster,

спасибо, но я тоже изменял сортировку, в итоге получал весь диапазон 2 после 1, например
и брал last_value, но так ряд с 2 не ограничивается n текущей записи

тут получилось бы, если отсортировать так, чтобы записи с 2 шли до текущей записи, а потом шли бы все с 1
к сожалению lag() over (order by case when f=1 then 1000000 else n end) тоже не помогает
ибо все с 1 собираются в кучку... и предыдущая запись берется из этой же кучки зачастую

но идея появилась)))
lag() over (order by case when f=1 then 1000000 else n end ,n desc )

мысли в слух

задача в том, чтобы получить значение p записи, которая найдется первая от исходной с f=2 (если исходная с f=1)
можно получить строку STRING_AGG, в которую при f=1 писать пустые значения
и после этого извлечь последнее непустое значение
но тут два преобразования, да потом строковая операция
долго, думаю, c cte быстрее

как бы не разносить ряды f=1 и f=2 - всё равно один не рубится по нужному n

то есть можно сконструировать
lag(n) over (order by case when f=1 then 1 else 0 end asc, case when f=2 then n else -n end asc )

по идее первым компонентом order by весь ряд c f=2 ставится впереди всего ряда c f=1
для него во втором компоненте order by задается обычный порядок сортировки, по n
так что последним в этом ряду будет запись с максимальным n, как и надо

как и надо, если бы можно было обрезать следующий ряд,который c f=1, по n
а не получается
между записью с максимальным n при f=2 и текущей записью с f=1 (значения для упорядочения: 1,-n) еще встают записи с f=1 и бОльшими n (то есть меньшими -n)

получается, что порядок ряда трогать нельзя, можно только удалить из него ненужное с f=1 через partition by
но текущую запись в этой секции надо оставить
что нибудь из серии over(partition by case when (f=2 or current row ) then 1 else 2 end = 1)

с cte нерекурсивно можно так:
;with cte1 (pp,ff,nn)
as (
select a.p,a.f,a.n
from @a a
where a.f=@f1 or a.f=@f2
),
cte2
as (
select aa.pp,aa.ff,aa.nn
,bb.pp ppp
,bb.ff fff
,bb.nn nnn
,row_number() over(partition by aa.nn order by bb.nn desc) rrr
from cte1 bb, cte1 aa
where bb.ff<>aa.ff and bb.nn<aa.nn
)
select * from cte2
where rrr=1

рекурсивно под вопросом
там вроде ограничение на количество рекурсий

в итоге для решения такой задачки без cte одним проходом - сервера немного не хватает

а именно надо иметь возможность использовать значение из текущей записи в order by хотя бы
тогда можно было бы разделить всю последовательность на две по полю f, а потом одну последовательность пихнуть вплотную к текущей записи, и получить предыдущее значение

из серии
log(p) over(order by n - case when f_current=f then 0 else n/n_current end)

тогда бы вся последовательность с f=2 умещалась бы между n_current и n_current-1
но при этом она умещалась бы только до n<n_current (<=, но в моем случае n уникальны)
записи с f=2 и n>n_current уже находились бы по другую сторону n_current, и не лезли бы в выборку

ну тоже вопрос эффективности, деление дорого
можно свести к чему-нибудь такому

log(p) over(partition by case when f=2 or n=n_current then 1 else 0 end order by n)

а как-нибудь так вообще прекрасно будет, если появится функция обращения к значениям текущей записи

log(p) over(partition by case when f=2 then 1 else 0 end order by n from current(n))

набрел на кладезь...

скорее всего получится слиянием просто
вообще columnstore занимает 1/10 от индекса
может быть просто в промежуточную память-оптимизированную таблицу с нужным индексом уйду

https://www.osp.ru/winitpro/2018/03/13054090/
и по этому автору там поиском много всего очень вкусного

NETClient,

да дать немного не в этом дело, наверное
тов.Бен наш Ган очень поспособствовал просветлению




дает и batch mode и параллельный план
в боевой задаче таблица секционирована по f
поэтому условия добавлены вроде бы лишние, но нет
на columnstore без доп.индекса отработка менее 1.5с (i7-6700/64/ssd intel 760p) на объеме 16000+13000 по двум f
всего объем 1106000 на восемь f, для двух самых тяжелых 523000+206000 время выполнения - 11с
в целом приемлемо, раз в минуту нужно дергать

да и нагрузка то ниже 50% по каждому ядру, не 100% как в предыдущем варианте
а смотрю нагрузка и на видеоадаптере появляется? ух ты))

дополнительный rs-индекс занимает половину объема, а время на 2с меньше всего
если уж ускоряться, то лучше в памяти, забрать из columnstore в память оптимизированную и в компилированном коде посчитать