Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Можно ли оконной функцией завязать две послед.записи с разными значениями нужного поля?  [new]
dklim.kzn
Member

Откуда: Казань
Сообщений: 123
Сервер 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)
31 мар 19, 22:49    [21848488]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли оконной функцией завязать две послед.записи с разными значениями нужного поля?  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3148
dklim.kzn,

Какой результат ожидается на приведенном примере данных?
1 апр 19, 09:04    [21848655]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли оконной функцией завязать две послед.записи с разными значениями нужного поля?  [new]
uaggster
Member

Откуда:
Сообщений: 770
dklim.kzn, либо я чего-то не понял, либо ответ - элементарен:

Select f + lag(f,1,0) over (order by f asc, n asc), f + lead(f,1,0) over (order by f asc, n asc) from @a

Select 
sum(f) over (order by f asc, n asc ROWS BETWEEN 1 preceding AND CURRENT ROW  )
,sum(f) over (order by f asc, n asc ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) from @a
1 апр 19, 09:09    [21848657]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли оконной функцией завязать две послед.записи с разными значениями нужного поля?  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20223
Ну как бы оно потому и окно, что для него едины как условия, так и сортировка... а Вы хотите, чтобы оно "плавало" от записи к записи, что приведёт к тому, что "типа окно" у каждой записи набора своё. Чтой-то более чем сомнительно.
1 апр 19, 09:09    [21848658]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли оконной функцией завязать две послед.записи с разными значениями нужного поля?  [new]
dklim.kzn
Member

Откуда: Казань
Сообщений: 123
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 )
1 апр 19, 11:12    [21848746]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли оконной функцией завязать две послед.записи с разными значениями нужного поля?  [new]
invm
Member

Откуда: Москва
Сообщений: 9128
dklim.kzn,

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

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

with t as
(
 select
  *,
  max(case when f = 1 then concat(str(n), str(p)) end) over (order by n rows between unbounded preceding and 1 preceding) as m1,
  max(case when f = 2 then concat(str(n), str(p)) end) over (order by n rows between unbounded preceding and 1 preceding) as m2
 from
  @a
)
select
 p, f, n,
 case
  when f = 1 then cast(substring(m2, 11, 1000) as int)
  when f = 2 then cast(substring(m1, 11, 1000) as int)
 end
from
 t
order by
 n;

select
 a.p, a.f, a.n,
 case
  when f = 1 then c.p
  when f = 2 then b.p
 end
from
 @a a outer apply
 (select top (1) p from @a where a.f = 2 and f = 1 and n < a.n order by n desc) b outer apply
 (select top (1) p from @a where a.f = 1 and f = 2 and n < a.n order by n desc) c;
1 апр 19, 12:21    [21848835]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли оконной функцией завязать две послед.записи с разными значениями нужного поля?  [new]
dklim.kzn
Member

Откуда: Казань
Сообщений: 123
мысли в слух

задача в том, чтобы получить значение 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)
1 апр 19, 22:30    [21849507]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли оконной функцией завязать две послед.записи с разными значениями нужного поля?  [new]
dklim.kzn
Member

Откуда: Казань
Сообщений: 123
с 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

рекурсивно под вопросом
там вроде ограничение на количество рекурсий
1 апр 19, 22:38    [21849513]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли оконной функцией завязать две послед.записи с разными значениями нужного поля?  [new]
dklim.kzn
Member

Откуда: Казань
Сообщений: 123
в итоге для решения такой задачки без 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))
2 апр 19, 08:17    [21849670]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли оконной функцией завязать две послед.записи с разными значениями нужного поля?  [new]
dklim.kzn
Member

Откуда: Казань
Сообщений: 123
набрел на кладезь...

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

https://www.osp.ru/winitpro/2018/03/13054090/
и по этому автору там поиском много всего очень вкусного
3 апр 19, 12:48    [21851181]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли оконной функцией завязать две послед.записи с разными значениями нужного поля?  [new]
NETClient
Member

Откуда:
Сообщений: 461
Мелкомягкие у оракла last_value украли, а ignore nulls забыли?
4 апр 19, 09:58    [21852106]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли оконной функцией завязать две послед.записи с разными значениями нужного поля?  [new]
dklim.kzn
Member

Откуда: Казань
Сообщений: 123
NETClient,

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

declare @f1  int, @f2 int
select @f1=1, @f2=2

;with cte as (
select n,f,p
, case when f=@f1 then max(case when f=@f2 then n else null end) over (order by n rows between unbounded preceding and 1 preceding)
 else max(case when f=@f1 then n else null end) over (order by n rows between unbounded preceding and 1 preceding) end nn
from @a 
where (f=@f1 or f=@f2)
)
select * from cte l 
inner join @a r 
on l.nn=r.n
where (r.f=@f1 or r.f=@f2)
order by l.n


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

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

К сообщению приложен файл. Размер - 105Kb
4 апр 19, 16:49    [21852993]     Ответить | Цитировать Сообщить модератору
 Re: Можно ли оконной функцией завязать две послед.записи с разными значениями нужного поля?  [new]
dklim.kzn
Member

Откуда: Казань
Сообщений: 123
дополнительный rs-индекс занимает половину объема, а время на 2с меньше всего
если уж ускоряться, то лучше в памяти, забрать из columnstore в память оптимизированную и в компилированном коде посчитать
4 апр 19, 17:08    [21853021]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить