多粒度下按buying_id聚合sales并保留个体信息的实现需求

原始交易表 #

字段说明 #

• id与name一一对应，每个用户拥有唯一id；
• buying_id关联type：前两行代表个人单独采购；id为2、3、4的用户同属一家公司，共享相同buying_id；
• sales与buying_id关联：如第3、4行表示C采购了12美元苹果，D采购了13美元苹果。

需求 #

按buying_id对sales求和，保留所有个体信息，同一buying_id下仅第一条记录显示求和结果，其余记录显示0。

预期输出 #

实现方案 #

使用SQL窗口函数即可实现该需求，以MySQL为例：

SELECT 
    id,
    buying_id,
    name,
    firm,
    type,
    CASE 
        WHEN ROW_NUMBER() OVER (PARTITION BY buying_id ORDER BY id) = 1 
        THEN SUM(sales) OVER (PARTITION BY buying_id) 
        ELSE 0 
    END AS `sum(sales)`
FROM 
    your_transaction_table;

• SUM(sales) OVER (PARTITION BY buying_id)：计算每个buying_id对应的sales总和；
• ROW_NUMBER() OVER (PARTITION BY buying_id ORDER BY id)：给每个buying_id分组内的记录按id排序并生成行号；
• 通过CASE判断，组内第一条记录显示总和，其余显示0。

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