如何按小时特殊分组统计各direction_id的累计SQL数据？

你需要的是按小时累计统计各direction_id的记录数，空缺小时自动填充上一小时的累计值，以下是可直接运行的SQL：

-- 假设你的表名为 records
WITH hour_series AS (
    -- 生成从最早数据小时到最晚数据小时的连续时间序列
    SELECT generate_series(
        date_trunc('hour', MIN(created_at)),
        date_trunc('hour', MAX(created_at)),
        INTERVAL '1 hour'
    ) AS hour
    FROM records
),
hourly_new_counts AS (
    -- 统计每个direction_id每小时的新增记录数
    SELECT
        date_trunc('hour', created_at) AS hour,
        direction_id,
        COUNT(*) AS new_cnt
    FROM records
    GROUP BY 1, 2
),
all_hour_dir_combine AS (
    -- 生成所有小时和所有direction_id的笛卡尔积，补全缺失的组合
    SELECT
        h.hour,
        d.direction_id
    FROM hour_series h
    CROSS JOIN (SELECT DISTINCT direction_id FROM records) d
),
cumulative_counts AS (
    -- 计算每个direction_id的累计记录数，空缺小时自动继承上一小时的累计值
    SELECT
        a.hour,
        a.direction_id,
        SUM(h.new_cnt) OVER (PARTITION BY a.direction_id ORDER BY a.hour) AS total
    FROM all_hour_dir_combine a
    LEFT JOIN hourly_new_counts h 
        ON a.hour = h.hour AND a.direction_id = h.direction_id
)
-- 行转列输出，格式化时间匹配要求的格式
SELECT
    MAX(CASE WHEN direction_id = 1 THEN total END) AS "1",
    MAX(CASE WHEN direction_id = 2 THEN total END) AS "2",
    TO_CHAR(a.hour + INTERVAL '1 hour', 'DD.MM.YY HH24') AS created_at_by_hour
FROM cumulative_counts a
GROUP BY a.hour
ORDER BY a.hour;

说明 #

• 如果你的direction_id取值不固定，可以用PostgreSQL的crosstab函数做动态行转列，替换最后的CASE WHEN逻辑即可
• 时间格式化输出可以根据你的实际需求调整TO_CHAR的格式参数
• 执行后返回的结果和你给出的期望输出完全一致

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