不使用游标、循环、lead/lag函数求时序数据三元组峰值的SQL方案问询

核心思路 #

由于X列是从1开始连续递增的整数，直接通过对X做数学运算生成分组标识即可实现每3个数据为一组，之后按分组聚合取每组Y的最大值，完全不需要用到游标、循环或者lead/lag类窗口偏移函数。

实现SQL #

SELECT 
    GROUP_ID,
    MAX(Y) AS PEAK_VALUE
FROM (
    SELECT
        X,
        Y,
        -- 每3个连续X划分为同一组，分组ID从1开始递增
        FLOOR((X - 1) / 3) + 1 AS GROUP_ID
    FROM Z_AXIS
) T
GROUP BY GROUP_ID
ORDER BY GROUP_ID;

如果需要自动舍弃最后不足3个数据的分组，加上HAVING过滤即可：

SELECT 
    GROUP_ID,
    MAX(Y) AS PEAK_VALUE
FROM (
    SELECT
        X,
        Y,
        FLOOR((X - 1) / 3) + 1 AS GROUP_ID
    FROM Z_AXIS
) T
GROUP BY GROUP_ID
HAVING COUNT(*) = 3
ORDER BY GROUP_ID;

对应你的测试数据输出结果 #

• 第1组（X=1~3，Y值10、5、8）：峰值为10
• 第2组（X=4~6，Y值20、30、15）：峰值为30
• 第3组（X=7~9，Y值20、2、14）：峰值为20
• 第4组（X=10~12，Y值6、50、20）：峰值为50
• 第5组（X=13~15，Y值30、60、25）：峰值为60
• 第6组（X=16~18，Y值15、0、4）：峰值为15
• 第7组（X=19~21，Y值2、8、10）：峰值为10
• 第8组（X=22~24，Y值15、12、40）：峰值为40
• 第9组（X=25，Y值20）：峰值为20（开启不足3个舍弃过滤时此组会被排除）

内容的提问来源于stack exchange，提问作者Just an intern