MySQL如何按日按颜色统计并保留IN子句所有维度？

嘿，这个需求我之前做报表的时候经常碰到！核心思路就是先把你要保留的所有日期+颜色维度组合提前构建出来，再和你的业务数据表做左连接，这样哪怕某个维度组合没有任何记录，也能保留对应的行，完美解决你要始终展示指定维度的问题。

具体实现步骤 #

1. 构建基础维度集合：生成你需要统计的所有日期范围，加上你指定的所有颜色（就是你IN子句里的那些），通过笛卡尔积得到所有可能的日期+颜色组合。
2. 左连接业务数据：用这个完整的维度集合左连接你的业务数据表，这样没有匹配数据的维度组合也会被保留。
3. 聚合统计并补0：对连接后的结果做聚合，用COALESCE把NULL（无数据的情况）转换成0，让统计结果更直观。

示例代码（以MySQL为例） #

假设你的业务表叫daily_color_records，字段是record_date(日期)、color(颜色)、quantity(数量)：

WITH date_range AS (
    -- 生成需要统计的日期范围，这里示例是2024年1月1日到今天
    SELECT CURDATE() - INTERVAL (a.a + 10*b.a + 100*c.a) DAY AS record_date
    FROM (SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
    CROSS JOIN (SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    CROSS JOIN (SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
    WHERE CURDATE() - INTERVAL (a.a + 10*b.a + 100*c.a) DAY >= '2024-01-01'
),
target_colors AS (
    -- 这里放你需要始终展示的所有颜色，新增颜色直接加UNION ALL就行
    SELECT '红' AS color UNION ALL
    SELECT '黄' UNION ALL
    SELECT '蓝'
),
all_dimensions AS (
    -- 生成所有日期+颜色的组合
    SELECT dr.record_date, tc.color
    FROM date_range dr
    CROSS JOIN target_colors tc
)
-- 左连接业务表并统计数量
SELECT
    ad.record_date,
    ad.color,
    COALESCE(SUM(dcr.quantity), 0) AS total_quantity
FROM all_dimensions ad
LEFT JOIN daily_color_records dcr
    ON ad.record_date = dcr.record_date
    AND ad.color = dcr.color
GROUP BY ad.record_date, ad.color
ORDER BY ad.record_date, ad.color;

额外优化建议 #

• 如果你的颜色维度是固定且长期使用的，建议单独建一个color_dim维度表，每次查询直接关联这个表，新增颜色时只需要往维度表里插数据，查询语句不用改。
• 如果你用的是PostgreSQL，生成日期序列可以用更简洁的generate_series函数替换上面的日期生成逻辑：

WITH date_range AS (
    SELECT generate_series('2024-01-01'::date, CURRENT_DATE, '1 day'::interval)::date AS record_date
)

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