sql - 如何使用 Postgres 动态框架的窗口函数中的列值？

Question

我不确定 Postgres 是否可行，但我希望每一行都计算其窗口函数“frame”。

那么我如何在 Postgres 的窗口函数中使用当前行的列作为 ROWS 的参数？

例如..

create table items (
  id serial primary key, name varchar, price integer, precede_by integer
);

insert into items (name, price, precede_by) 
  values 
  ('A', 1, 2), ('A', 1, 3), 
  ('B', 1, 1),('B',1, 2), ('B', 1, 1), ('B', 2, 2), 
  ('C', 1, 2), ('C', 1,2), ('C', 1, 2), ('C', 1, 5);

我想使用 precede_by 列中设置的整数值。例如，类似这样的东西。

select name, sum(price) over 
  (partition by name rows between precede_by preceding and current row) 
from items;

但这行不通。我收到 ERROR: ROWS 的参数不能包含变量


Answer 1

不行，想怎么写窗函数都不行。我相信 CROSS JOIN LATERAL 可以解决问题

with oitems as
(
  select *, row_number() over (partition by name order by id) rn from items
)
select * 
from oitems i1
cross join lateral 
(
  select sum(price) 
  from  oitems i2
  where i1.name = i2.name and i2.rn >= i1.rn - i1.precede_by and i2.rn <= i1.rn
) t

另一个(可能更优雅)解决方案是使用自连接 GROUP BY

with oitems as
(
  select *, row_number() over (partition by name order by id) rn from items
)
select i1.id, i1.name, i1.price, sum(i2.price) 
from oitems i1
join  oitems i2 on i1.name = i2.name and i2.rn >= i1.rn - i1.precede_by and i2.rn <= i1.rn
group by i1.id, i1.name, i1.price

dbfiddle demo