SQL实现按类型统计指定时间段内有效数据峰值之和

核心实现逻辑 #

你之前想到的判断每行是否存在后续更小数值的思路是完全可行的，下面给出兼容绝大多数主流数据库（MySQL 8+/PostgreSQL/Oracle/SQL Server）的实现方案，无需自定义复杂函数，性能适配中小数据量场景。
前提假设：存储数据的表名为peak_data，包含字段Type（数据类型）、Amount（峰值数值）、Date（时序字段，建议带时分秒保证时序准确性，若同时间有多条数据可增加自增ID辅助排序）。 #

具体实现步骤 #

• 第一步：过滤指定统计时间段内的所有数据，排除时间段外的干扰数据
• 第二步：按Type分组，每组内按时序升序排序，用窗口函数计算当前行之后所有同类型数据的最小数值
• 第三步：判定有效峰值：如果当前行Amount大于后续所有行的最小数值，说明时间段内存在下降趋势，属于有效峰值
• 第四步：按Type分组对有效峰值求和，得到最终统计结果

代码示例（以统计2021-07-11数据为例） #

WITH time_range_data AS (
    -- 过滤目标时间段数据
    SELECT 
        Type,
        Amount,
        Date
    FROM peak_data
    WHERE Date >= '2021-07-11' AND Date < '2021-07-12'
),
peak_check AS (
    -- 计算当前行之后同类型的最小数值
    SELECT 
        Type,
        Amount,
        MIN(Amount) OVER (
            PARTITION BY Type 
            ORDER BY Date ASC 
            ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
        ) AS next_min_amount
    FROM time_range_data
)
-- 统计各类型有效峰值总和
SELECT 
    Type,
    SUM(CASE WHEN Amount > next_min_amount THEN Amount ELSE 0 END) AS valid_peak_sum
FROM peak_check
GROUP BY Type;

方案说明 #

• 边界自动适配：每个类型的最后一条数据next_min_amount返回NULL，Amount > NULL判定为false，天然符合「无后续数据则不计入有效峰值」的规则，和示例中CCC类型的场景完全匹配
• 时序修正：如果同类型同一时间点有多条数据，可在窗口函数的ORDER BY后增加自增ID排序：ORDER BY Date ASC, id ASC即可保证时序正确
• 存储过程改造：只需要把统计的开始时间、结束时间改为存储过程入参即可复用

老版本MySQL（不支持窗口函数）替代方案 #

SELECT 
    t1.Type,
    SUM(CASE WHEN EXISTS (
        SELECT 1 FROM peak_data t2
        WHERE t2.Type = t1.Type 
        AND t2.Date > t1.Date 
        AND t2.Date < '2021-07-12'
        AND t2.Amount < t1.Amount
    ) THEN t1.Amount ELSE 0 END) AS valid_peak_sum
FROM peak_data t1
WHERE t1.Date >= '2021-07-11' AND t1.Date < '2021-07-12'
GROUP BY t1.Type;

该方案用EXISTS子查询实现和窗口函数版本完全一致的逻辑，缺点是大数据量下性能低于窗口函数版本。

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