如何使用SQL实现两个Load类型交易间的数据按类型聚合

实现思路 #

• 首先对所有交易按交易日期升序排序，用窗口函数生成分组标识：每遇到一次Load类型的交易，分组编号就+1，这样同组内的交易就属于同一个前序Load到下一个Load之间的区间
• 提取每个分组对应的Load日期，即每组中第一条Load记录的日期
• 过滤掉各组内的Load类型记录，剩余记录按Type分组聚合金额即可

参考SQL代码（兼容MySQL 8+、PostgreSQL、Hive等支持窗口函数的SQL引擎） #

WITH transaction_with_group AS (
    -- 第一步：给交易分配分组号，每遇到一次Load就新增一个分组
    SELECT 
        Date,
        Type,
        Amount,
        SUM(CASE WHEN Type = 'Load' THEN 1 ELSE 0 END) OVER (ORDER BY STR_TO_DATE(Date, '%m/%d/%y') ASC) AS group_id
    FROM transactions
),
group_load_date AS (
    -- 第二步：获取每个分组对应的Load日期
    SELECT 
        group_id,
        MIN(Date) AS load_date
    FROM transaction_with_group
    WHERE Type = 'Load'
    GROUP BY group_id
)
-- 第三步：过滤非Load交易，按Type聚合金额
SELECT 
    g.load_date AS 'Load Date',
    t.Type,
    SUM(t.Amount) AS Amount
FROM transaction_with_group t
JOIN group_load_date g ON t.group_id = g.group_id
WHERE t.Type != 'Load'
GROUP BY g.load_date, t.Type
ORDER BY g.load_date, t.Type;

注意事项 #

• 如果你的数据库不支持STR_TO_DATE函数，替换为对应数据库的日期转换函数即可，比如PostgreSQL用TO_DATE(Date, 'MM/DD/YY')
• 如果同个日期下有Load和其他类型交易，建议排序时增加交易流水号等唯一标识作为排序第二字段，保证Load排在同日期其他交易前，避免分组逻辑错误

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