如何使用SQL/BigQuery计算120分钟滑动窗口内的第二大温度值

问题原因 #

你之前两个NTH_VALUE写法不符合预期的核心问题如下：

• 第一种写法按event_ts_seconds排序，窗口内行按时间先后排列，NTH_VALUE(Temperature,2)取的是窗口内第二早产生的数据的温度值，和「第二大温度」的需求完全不匹配。
• 第二种写法将窗口排序键改为Temperature DESC后，RANGE BETWEEN 7200 PRECEDING AND CURRENT ROW的计算基准会变成排序键（也就是温度值），7200 PRECEDING指的是温度比当前行温度小7200的行，完全不是你需要的「过去120分钟」的时间范围逻辑，所以结果错误。

可行解决方案 #

下面给出两种兼容性不同的实现方案，你可以根据自己用的SQL引擎选择：

方案1：MAX嵌套排除法（全引擎兼容） #

逻辑最直观，所有支持窗口函数的SQL引擎都可以运行：先计算出每个时间窗口的最大值，再取窗口内所有小于最大值的温度的最大值，就是第二大温度。

WITH temp_with_max AS (
    SELECT 
        *,
        -- 先计算120分钟窗口的最大温度
        MAX(Temperature) OVER(
            PARTITION BY device_id 
            ORDER BY event_ts_seconds 
            RANGE BETWEEN 7200 PRECEDING AND CURRENT ROW
        ) AS Max_Temp_in_120frame
    FROM 你的表名
)
SELECT 
    *,
    -- 窗口内排除最大值后取最大，即为第二大温度
    MAX(CASE WHEN Temperature < Max_Temp_in_120frame THEN Temperature END) OVER(
        PARTITION BY device_id 
        ORDER BY event_ts_seconds 
        RANGE BETWEEN 7200 PRECEDING AND CURRENT ROW
    ) AS SecondMax_Temp_in_120frame
FROM temp_with_max

如果窗口内存在多个并列的最大值，你需要第二大值和最大值相同的话，将CASE WHEN里的<改为<=，再搭配排名逻辑处理即可。

方案2：数组聚合方法（适用于Spark SQL/BigQuery/Hive等支持数组函数的引擎） #

代码更简洁，不需要嵌套多层子查询：

SELECT
    *,
    -- 窗口内温度按降序聚合为数组、去重后取索引为1的元素（数组索引从0开始，0是最大值，1是第二大）
    ARRAY_DISTINCT(ARRAY_AGG(Temperature) OVER(
        PARTITION BY device_id 
        ORDER BY event_ts_seconds 
        RANGE BETWEEN 7200 PRECEDING AND CURRENT ROW
        ORDER BY Temperature DESC
    ))[SAFE_OFFSET(1)] AS SecondMax_Temp_in_120frame
FROM 你的表名

如果不需要排除重复温度值，去掉ARRAY_DISTINCT函数即可。

内容的提问来源于stack exchange，提问作者Vidyadhar Harmalkar