如何在Esper中获取第二大值？嵌套子查询失效问题求解

你的原查询失效的原因在于Esper对聚合上下文里嵌套子查询的处理逻辑：外层的SELECT max(averageTemperature) FROM JFK是对整个流做聚合操作，返回单条结果，内层子查询的作用域和外层聚合冲突，导致无法正确遍历所有数据计算第二大值。

下面提供两种可行的实现方式：

方式一：使用窗口函数（推荐，Esper 8.x及以上版本支持） #

利用DENSE_RANK()窗口函数对温度值降序排名，再通过条件聚合提取排名第1和第2的温度值：

SELECT 
  MAX(CASE WHEN rank_val = 1 THEN averageTemperature END) AS Temp1,
  MAX(CASE WHEN rank_val = 2 THEN averageTemperature END) AS Temp2
FROM (
  SELECT 
    averageTemperature,
    DENSE_RANK() OVER (ORDER BY averageTemperature DESC) AS rank_val
  FROM JFK
) ranked_data

• DENSE_RANK()会给相同温度值分配相同排名，避免因重复最大值导致第二值丢失
• 外层通过CASE和MAX聚合，直接提取对应排名的温度值

方式二：嵌套子查询调整写法 #

通过两次子查询分别获取最大值和小于最大值的最大值，同时添加LIMIT 1避免返回多行结果：

SELECT 
  (SELECT MAX(averageTemperature) FROM JFK) AS Temp1,
  (SELECT MAX(averageTemperature) FROM JFK WHERE averageTemperature < (SELECT MAX(averageTemperature) FROM JFK)) AS Temp2
FROM JFK
LIMIT 1

• 外层FROM JFK会遍历所有事件，LIMIT 1确保只返回一条聚合结果
• 内层子查询独立计算最大值，再筛选出小于该值的最大温度

内容的提问来源于stack exchange，提问作者Mainul Hasan