如何基于Batch_ID对Time列去重后计算SQL聚合值？

原始数据 #

需求 #

计算Items的总和，同时对同一Batch_ID的Time值去重后求和。期望结果：

• Items总和：150
• Time总和：1.40

问题分析 #

你当前使用的SQL：

SELECT user_id, SUM(items), SUM(DISTINCT time) FROM items_p GROUP BY dayweek,user_id

得到Time总和为0.70，原因是SUM(DISTINCT time)是对全表所有唯一的Time值求和，而非按Batch_ID去重后求和——这里所有记录的Time都是0.70，去重后只剩一个值，求和自然是0.70，不符合需求。

正确实现方案 #

方案1：子查询先聚合批次数据 #

先按Batch_ID和User分组，得到每个批次的Items总和以及唯一的Time值（同一批次Time相同，用MAX/MIN/AVG均可），再对User进行最终聚合：

SELECT 
    User,
    SUM(total_batch_items) AS total_items,
    SUM(batch_time) AS total_time
FROM (
    SELECT 
        Batch_ID,
        User,
        SUM(Items) AS total_batch_items,
        MAX(Time) AS batch_time
    FROM items_p
    GROUP BY Batch_ID, User
) AS batch_agg
GROUP BY User;

方案2：窗口函数标记批次首行 #

用窗口函数给每个批次的记录标记行号，只对每个批次的首行Time进行求和，同时直接计算所有Items的总和：

SELECT 
    User,
    SUM(Items) AS total_items,
    SUM(CASE WHEN batch_row = 1 THEN Time ELSE 0 END) AS total_time
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY Batch_ID ORDER BY Items) AS batch_row
    FROM items_p
) AS ranked_data
GROUP BY User;

两种方案都能得到你想要的结果：Items总和150，Time总和1.40。

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