SQL多粒度双计数查询问题：事件月度总览与位置拆分统计

这是个很常见的多粒度统计需求——既要按「事件+位置」的细粒度统计位置维度的计数，又要同时输出「事件+月份」粗粒度的全局总计数，普通单轮GROUP BY确实没法直接实现这种跨粒度的聚合，不过我们可以通过窗口函数或者预聚合子查询两种方式来解决，下面给你具体的实现思路：

方案一：用窗口函数实现（推荐，简洁高效） #

窗口函数可以在分组统计的同时，基于指定的更大粒度维度计算聚合值，完美匹配你的需求。假设我们的两张表分别是events（事件表）和locations（位置关联表），先通过OUTER JOIN拼接数据，再用窗口函数计算全局计数：

-- 先通过CTE生成外连接后的数据集
WITH joined_dataset AS (
    SELECT
        e.event_id,
        e.event_name,
        l.location_id,
        l.location_name,
        -- 提取事件所属月份，作为粗粒度聚合的时间维度
        DATE_TRUNC('month', e.event_time) AS event_month
    FROM events e
    FULL OUTER JOIN locations l 
        ON e.id = l.event_id -- 按你的业务ID关联
)
SELECT
    event_id,
    event_name,
    event_month,
    location_id,
    location_name,
    -- 细粒度：当前事件+位置+月份的计数
    COUNT(*) AS location_count,
    -- 粗粒度：当前事件当月的总计数（窗口函数跨分组聚合）
    SUM(COUNT(*)) OVER(PARTITION BY event_id, event_month) AS event_total_count
FROM joined_dataset
-- 按细粒度维度分组
GROUP BY event_id, event_name, event_month, location_id, location_name
ORDER BY event_month, event_id, location_id;

逻辑说明： #

1. 先用CTE完成两张表的FULL OUTER JOIN，同时提取事件的月份维度；
2. 按「事件+月份+位置」分组，计算每个位置的事件计数location_count；
3. 通过SUM(COUNT(*)) OVER(PARTITION BY event_id, event_month)，在每个分组中计算该事件当月所有位置的计数总和，得到全局的event_total_count。

方案二：预聚合子查询实现（兼容旧版SQL方言） #

如果你的数据库不支持窗口函数（比如MySQL 5.x及以下），可以先预计算出事件当月的总计数，再和细粒度统计结果关联：

-- 预计算每个事件当月的总计数
WITH event_monthly_total AS (
    SELECT
        event_id,
        DATE_TRUNC('month', event_time) AS event_month,
        COUNT(*) AS event_total_count
    FROM events
    GROUP BY event_id, DATE_TRUNC('month', event_time)
),
-- 生成外连接后的细粒度数据集
joined_dataset AS (
    SELECT
        e.event_id,
        e.event_name,
        l.location_id,
        l.location_name,
        DATE_TRUNC('month', e.event_time) AS event_month
    FROM events e
    FULL OUTER JOIN locations l 
        ON e.id = l.event_id
)
SELECT
    jd.event_id,
    jd.event_name,
    jd.event_month,
    jd.location_id,
    jd.location_name,
    COUNT(*) AS location_count,
    emt.event_total_count
FROM joined_dataset jd
LEFT JOIN event_monthly_total emt
    ON jd.event_id = emt.event_id 
    AND jd.event_month = emt.event_month
GROUP BY jd.event_id, jd.event_name, jd.event_month, jd.location_id, jd.location_name, emt.event_total_count
ORDER BY jd.event_month, jd.event_id, jd.location_id;

逻辑说明： #

1. 先单独统计每个事件当月的总计数，存在event_monthly_total中；
2. 生成外连接后的细粒度数据集；
3. 将两个数据集按事件ID和月份关联，同时按细粒度维度分组统计位置计数，最终输出两种粒度的结果。

两种方案都能完美满足你的需求：location_count是事件按位置拆分的细粒度计数，event_total_count是该事件当月的全局总计数。

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