postgreColumn基于动物数据集sql中的一个范围

Question

表：

num       value      category      name
503       8978       bird          woodpecker
502       7812       animal        
502       7812       animal        
501       7812       animal        
500       7812       animal        panther
499       7812       animal        
498       7812       animal     
467       7812       animal        elephant           

在value和category列的partition by中，应如下创建输出列：
当name不为空时，output列将占用name的值，并在num列的加2和减2范围内填充相同的值
例如，500具有name not null，500-2=498和500+2=502，在498到502的范围内，输出填充panther

输出：

num       value      category      name             output
503       8978       bird          woodpecker       
502       7812       animal                         panther
502       7812       animal                         panther
501       7812       animal                         panther
500       7812       animal        panther          panther
499       7812       animal                         panther
498       7812       animal                         panther
467       7812       animal        elephant         elephant 

Answer 1

您可以使用range窗口框架：

select t.*,
coalesce(name,
max(name) over (partition by category
order by num
range between 2 preceding and 2 following
)
) as imputed_name
from t;

这里有一个db<gt；不停摆弄

编辑：

对"；前面的"；以及"；下面的"；对于Postgres中的CCD_ 10窗口帧来说是相对较新的。在旧版本中，横向连接可能是最简单的方法：

select t.*,
coalesce(t.name, t2.name) as imputed_name
from t left join lateral
(select t2.name
from t t2
where t2.category = t.category and
t2.name is not null and
t2.num between t.num - 2 and t.num + 2
limit 1
) t2
on 1=1
order by num desc;

这里有一个db<gt；这个版本的小提琴。

Answer 2

尝试将窗口函数与case一起使用：

select num,value,category,name,output from
(
--if num is in range [match_number-2, match_number+2] then get animal's name
select *, CASE when num>=(match_number-2) and num<=(match_number+2) then max_nam else NULL end as output from
(
--find num matching name
select *,max( case when name=max_nam then num else 0 end )over (partition by value,category) match_number from
(
--find name (not null) partition by value,category 
select *,max(name)over(partition by value,category)max_nam from Table
)X
)Y
)Z