MySQL多维度分组统计如何包含计数为0的结果行

你的问题核心在于多个RIGHT JOIN无法生成所有维度的笛卡尔积组合——每次RIGHT JOIN都是基于前一步的连接结果去关联，而不是直接生成所有可能的地区、分类、日期的组合。要得到全量的10×20×100行结果，我们需要先构建所有维度的完整组合，再左连接事实表统计数量。

正确的查询思路 #

1. 先通过CROSS JOIN将三个维度表（地区、分类、日期）进行交叉连接，生成所有可能的region_id+category_id+date_id组合。
2. 将这个全量组合作为左表，左连接fact_listing表，匹配对应的关联字段。
3. 分组后统计fact_listing_id的数量（左连接无匹配时该字段为NULL，COUNT()会自动计为0）。

完整SQL语句 #

SELECT
    COUNT(fl.fact_listing_id) AS count,
    dr.region_id,
    dc.category_id,
    dd.date_id
FROM
    dim_region dr
CROSS JOIN
    dim_category dc
CROSS JOIN
    dim_date dd
LEFT JOIN
    fact_listing fl ON dr.region_id = fl.region_id 
                   AND dc.category_id = fl.category_id 
                   AND dd.date_id = fl.created_at_date_id
GROUP BY
    dr.region_id, dc.category_id, dd.date_id
ORDER BY
    dr.region_id, dc.category_id, dd.date_id;

为什么原查询不行？ #

你原来的多RIGHT JOIN写法存在逻辑缺陷：

• 第一个RIGHT JOIN得到所有地区（包括无listing的），但此时只有地区维度是全量的；
• 第二个RIGHT JOIN关联分类时，是基于前一步的结果（地区+对应的listing记录）去匹配分类，若某个地区没有任何listing，那么这条地区记录无法关联到任何分类，就不会生成该地区和所有分类的组合；
• 同理，后续关联日期时也会丢失大量无数据的组合，最终无法得到全量的三维度交叉结果。

验证逻辑 #

这个查询会先生成dim_region×dim_category×dim_date的所有组合（10×20×100=20000行），然后左连接事实表匹配数据，不管该组合有没有对应的listing，都会返回一行，计数为0的行就是没有匹配到任何listing的组合。

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