如何在零值分隔区段中计算指定id的MAX()、SUM()及成功率？

原始数据集 #

需求拆解 #

你需要以ID为A的零值记录作为区段分隔点，在每个有效区段内：

• 统计ID为B的记录的数值总和
• 统计ID为A的记录的数值最大值
• 通过「B的总和 / A的最大值」计算成功率，最终按区段输出结果

你的SQL问题分析 #

你当前尝试的SQL存在几个关键问题：

• 引用了不存在的字段：building、metric_value，和原始数据集不匹配
• 窗口函数逻辑没有实现「按零值分隔区段」的核心需求，无法正确划分独立区段
• 缺少针对ID的筛选聚合逻辑，无法分别计算A的最大值和B的总和

正确SQL实现（以PostgreSQL为例，其他数据库可微调语法） #

WITH sectioned_data AS (
    -- 第一步：基于A的零值生成区段编号
    SELECT
        metric_date,
        location,
        id,
        value,
        -- 累计A的零值数量，加1得到区段编号
        SUM(CASE WHEN id = 'A' AND value = 0 THEN 1 ELSE 0 END) OVER (ORDER BY metric_date) + 1 AS section
    FROM your_table_name
),
section_stats AS (
    -- 第二步：按区段聚合所需统计值
    SELECT
        location,
        section,
        MAX(CASE WHEN id = 'A' THEN value ELSE NULL END) AS max_a_value,
        SUM(CASE WHEN id = 'B' THEN value ELSE 0 END) AS sum_b_value
    FROM sectioned_data
    -- 排除末尾全零的无效区段
    WHERE section < (SELECT MAX(section) FROM sectioned_data)
    GROUP BY location, section
)
-- 第三步：计算并格式化最终结果
SELECT
    location,
    section,
    max_a_value AS max,
    sum_b_value AS sum,
    ROUND(sum_b_value::NUMERIC / max_a_value, 4) AS success_rate
FROM section_stats
ORDER BY location, section;

执行结果 #

这个结果完全匹配你的预期，核心逻辑是通过累计A的零值数量实现区段划分，再针对每个区段分别聚合A和B的统计值，最后计算成功率。

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