在SQL窗口函数中限制结果集 [英] Limit result set in sql window function

问题描述

假设我想重写以下聚合查询

  select id，状态为max（hittime）
 
组ID为
  

使用汇总窗口函数，如

 从状态
   
 
 
如何指定我只对分区中的第一个结果感兴趣？
 
 
 
编辑：我当时以为[RANGE | [行]在frame_start和frame_end之间。不仅可以获得max（hittime），还可以获得第二，第三... ... 
解决方案
我认为您需要的是
  select id，hittime 
 from（ 
选择id，命中时间，
 density_rank（）超过（按命中时间先后按ID顺序对ID进行分区），将状态
中的
排序为x 
，其中排名= 1； -获得最大点击时间
-排名< = 2; --max和第二大
  
 
Assume I would like to rewrite the following aggregate query
select id, max(hittime)
from status
group by id
using an aggregate windowing function like
select id, max(hittime) over(partition by id order by hittime desc) from status
How can I specify, that I am only interested in the first result within the partition?

EDIT: I was thinking that there might be a solution with [ RANGE | ROWS ] BETWEEN frame_start AND frame_end. What to get not only max(hittime) but also the second, third ...
解决方案
I think what you need is a ranking function, either ROW_NUMBER or DENSE_RANK depending on how you want to handle ties.
select id, hittime
from (
     select id, hittime,
            dense_rank() over(partition by id order by hittime desc) as ranking
     from status
     ) as x
where ranking = 1;  --to get max hittime
--where ranking <=2;  --max and second largest


                        
这篇关于在SQL窗口函数中限制结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！