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

Question

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

select id, max(hittime)
from status
group by id

使用聚合窗口函数，如：

select id, max(hittime) over(partition by id order by hittime desc) from status

我该如何指定我只对分区中的第一个结果感兴趣？

编辑：我在思考是否有解决方案，使用[RANGE|ROWS] BETWEEN frame_start AND frame_end。如何获取不仅是max(hittime)而且还有第二个、第三个...

- JohnDoe

---

“first result”是什么意思？每个分区只有一个最大的命中时间。 - ypercubeᵀᴹ

这个窗口查询会返回整个表格，但我只对最大结果感兴趣。在这种情况下，像@amirreza-keshavarz建议的那样使用Distinct会有所帮助。 - JohnDoe

我不明白为什么你不想使用GROUP BY。而且你的第二个查询和Amirezza的查询都有一个多余的order by hittime desc。 - ypercubeᵀᴹ

我的问题可能不够具体。GROUP BY 没有问题，但是当我想要最大值和第二大的值时该怎么办？ - JohnDoe

Answer 1

我认为您需要的是一个排名函数，可以使用ROW_NUMBER或DENSE_RANK，具体取决于您如何处理并列情况。

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

- Noel

Answer 2

使用distinct语句。

select DISTINCT id, max(hittime) over(partition by id order by hittime desc) from status

- Amir Keshavarz