基于动物数据集中的范围的列- 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           

在partition by的value和category列中，应该创建输出列，如下所示：

当name不是null时，output列将占用name的值，并在+2和减去两个num列的范围内填充相同的值。

例如，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<>fiddle。

编辑：

对于Postgres中的range窗口框架来说，对“前面”和“跟随”的支持是相对较新的。在旧版本中，横向连接可能是最简单的方法：

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<>fiddle。

Answer 2

尝试在大小写中使用窗口函数：

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