在SQL窗口函数中限制结果集

本文介绍了在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 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