在Oracle上为每个值获取第二高的最大值

Question

我想使用PL/SQL(Oracle(获得第二高的最大值。

我有一张这样的桌子：

CLIENT | ORDER_DATE
1      | 14/09/2018
1      | 01/02/2019
2      | 13/12/2019
2      | 01/01/2020
2      | 15/12/2019

我想为每个客户端获得一个最大值(ORDER_DATE(和第二高最大值(ORDER_DATE(的表：

CLIENT | MAX(ORDER_DATE) | 2nd highest max(ORDER_DATE)
1      | 01/02/2019      | 14/09/2018
2      | 01/01/2020      | 15/12/2019

我尝试过使用排名，但只得到一行(一个随机客户端(：

select *
from (select CLIENT,
max(ORDER_DATE),
row_number() over (order by max(ORDER_DATE) desc) as rk
from order_table
group by CLIENT) t
where rk = 2

Answer 1

应用ROW_NUMBER():等分析函数后需要条件聚合

WITH t2 AS
(
SELECT client,order_date,
ROW_NUMBER() OVER (PARTITION BY client ORDER BY order_date DESC) as rk
FROM order_table
)
SELECT client, 
MAX(CASE WHEN rk=1 THEN order_date END) AS "max order date",
MAX(CASE WHEN rk=2 THEN order_date END) AS "2nd highest max ord.date"
FROM t2
GROUP BY client

演示

Answer 2

您可以尝试以下操作-在over()子句中使用partition by client

select client,max_date,order as seconf_highest_date from
(
select *,max(ORDER_DATE) over(partition by client order by ORDER_DATE desc) as max_date,
row_number() over (partition by client order by ORDER_DATE desc) as rk
from order_table
)A where rk=2

Answer 3

您也可以使用分析函数来实现这一点：

select distinct client, 
max(date) over (partition by client),
nth_value(date, 2) over (partition by client order by date desc)
from order_date;

令人高兴的是，Oracle支持nth_value()作为一个分析函数。遗憾的是，Oracle没有提供与更简单的聚合功能相同的功能。