SQL如何按小时分组统计give、take两类操作的金额总和

架构老林

2026-8-27

解决方案

你可以直接用条件聚合单次扫描表完成统计，不需要拆分两次查询再合并，性能更优，写法更简洁：

SELECT
    SUM(CASE WHEN act_id = 1 THEN amt ELSE 0 END) AS sum_give,
    SUM(CASE WHEN act_id = 2 THEN amt ELSE 0 END) AS sum_take,
    CAST(date_trunc('hour', time_stamp) AS timestamp) AS time_stamp
FROM action
WHERE act_id IN (1, 2)
GROUP BY date_trunc('hour', time_stamp)
ORDER BY time_stamp;

逻辑说明

过滤条件只保留act_id为1、2的两类记录，减少不必要的数据扫描
聚合时通过CASE表达式判断记录类型：属于give类型就把amt计入sum_give，属于take类型就计入sum_take，不符合的记录累加0，不影响最终求和结果
直接按截断到小时维度的时间戳统一分组，一次分组即可同时得到两个维度的统计值
该写法天然兼容某一小时只有give、或只有take记录的场景，缺失类型的统计值会返回0，不会出现空值或时间戳缺失的问题

子查询合并写法（备选）

如果你后续有拆分统计逻辑的需求，也可以用CTE分别统计两类结果，再通过全外连接按小时字段合并：

WITH give_stat AS (
    SELECT
        SUM(amt) AS sum_give,
        CAST(date_trunc('hour', time_stamp) AS timestamp) AS hr
    FROM action
    WHERE act_id = 1
    GROUP BY date_trunc('hour', time_stamp)
),
take_stat AS (
    SELECT
        SUM(amt) AS sum_take,
        CAST(date_trunc('hour', time_stamp) AS timestamp) AS hr
    FROM action
    WHERE act_id = 2
    GROUP BY date_trunc('hour', time_stamp)
)
SELECT
    COALESCE(g.sum_give, 0) AS sum_give,
    COALESCE(t.sum_take, 0) AS sum_take,
    COALESCE(g.hr, t.hr) AS time_stamp
FROM give_stat g
FULL OUTER JOIN take_stat t ON g.hr = t.hr
ORDER BY time_stamp;