GROUP BY结果补全小时行缺口 按site_id分组无关联字段问题求助

实现思路 #

你需要先构造所有待统计站点 + 所有统计时段的全量维度笛卡尔积，再左联交易统计结果即可实现需求，每个站点的所有时段都会生成记录，无交易的时段统计值自动补0。

改造后SQL示例 #

SELECT
    s.site_id,
    gh,
    IFNULL(d.count, 0) AS `count`
-- 生成统计周期内的全量小时序列
FROM UNNEST(GENERATE_TIMESTAMP_ARRAY('2021-08-10 00:00:00', '2021-08-17 23:59:59', INTERVAL 1 HOUR)) as gh
-- 笛卡尔积关联所有需要统计的站点，得到每个站点对应的全量小时基准行
CROSS JOIN (
    -- 可替换为独立站点维度表的查询，避免漏掉统计周期内无交易的站点
    SELECT DISTINCT site_id FROM `transactions`
) s
-- 左关联按站点+小时聚合的交易统计结果
LEFT JOIN (
    SELECT
        site_id,
        TIMESTAMP_TRUNC(DATI, HOUR) hour,
        COUNT(*) `count`
    FROM `transactions`
    WHERE DATE(DATI) >= "2021-08-10"
    AND PMP IS NOT NULL
    AND (QTY IS NULL OR QTY = 0)
    GROUP BY site_id, hour
) d ON d.hour = gh AND d.site_id = s.site_id
ORDER BY s.site_id, gh

关键说明 #

• 站点列表如果有独立的站点维度表，建议替换SELECT DISTINCT site_id FROM transactions子查询，避免统计周期内完全没有交易的站点被遗漏
• 子查询的GROUP BY需要同时指定site_id和hour，保证统计结果是每个站点每小时的交易笔数
• 左关联时需要同时匹配小时和站点ID，才能正确将交易统计值对应到对应站点的对应时段

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