本文介绍了在SQL窗口函数中限制结果集的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
假设我想重写以下聚合查询
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 idusing an aggregate windowing function like
select id, max(hittime) over(partition by id order by hittime desc) from statusHow 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窗口函数中限制结果集
发布评论