SQL合并生产与销售按年月关联表,为仅有销售的月份补生产值为0
问题根源
原SQL的写法本质是隐式内连接,等价于INNER JOIN逻辑,仅会保留生产表、销售表中年月完全匹配的行,因此会自动过滤掉仅存在销售数据的月份,也会丢失仅存在生产数据的月份;同时直接关联明细表的写法,还会在同月份存在多条生产/销售记录时生成笛卡尔积,导致统计数值错误。
优化方案
要实现全量时间覆盖、缺失指标补0的需求,需要先统一两张表的时间粒度,再通过全量时间锚点关联数据,同时用COALESCE函数处理缺失值:
- 先按年月聚合生产、销售明细数据,避免笛卡尔积问题
- 取两张表所有出现过的年月生成全量时间列表,作为关联基准
- 将聚合后的生产、销售数据分别关联到全量时间列表上,缺失的指标值补0
通用实现SQL
WITH monthly_prod AS ( SELECT DATE_TRUNC('month', p.Date) AS month_start, EXTRACT(YEAR FROM p.Date) AS Year, EXTRACT(MONTH FROM p.Date) AS Month, SUM(p.Quantity) AS Production FROM Production p -- 原SQL表名拼写为Prodcution,此处修正为正确拼写,如有误请调整 GROUP BY DATE_TRUNC('month', p.Date), EXTRACT(YEAR FROM p.Date), EXTRACT(MONTH FROM p.Date) ), monthly_sales AS ( SELECT DATE_TRUNC('month', s.Date) AS month_start, EXTRACT(YEAR FROM s.Date) AS Year, EXTRACT(MONTH FROM s.Date) AS Month, SUM(s.Sales) AS Sales FROM Sales s GROUP BY DATE_TRUNC('month', s.Date), EXTRACT(YEAR FROM s.Date), EXTRACT(MONTH FROM s.Date) ), all_months AS ( SELECT month_start, Year, Month FROM monthly_prod UNION SELECT month_start, Year, Month FROM monthly_sales ) SELECT am.Year, am.Month, COALESCE(mp.Production, 0) AS Production, COALESCE(ms.Sales, 0) AS Sales FROM all_months am LEFT JOIN monthly_prod mp ON am.month_start = mp.month_start LEFT JOIN monthly_sales ms ON am.month_start = ms.month_start ORDER BY am.Year, am.Month;
注意:如果使用的是MySQL数据库,可将上述SQL中的
DATE_TRUNC('month', 日期字段)替换为DATE_FORMAT(日期字段, '%Y-%m-01'),实现相同的月份粒度归一效果。
简化场景SQL
如果仅需要覆盖所有有销售数据的月份、不需要展示仅存在生产数据的月份,可直接以销售表为基准左连生产表,写法更简单:
SELECT EXTRACT(YEAR FROM s.Date) AS Year, EXTRACT(MONTH FROM s.Date) AS Month, COALESCE(p.Quantity, 0) AS Production, s.Sales FROM Sales s LEFT JOIN Production p ON EXTRACT(YEAR FROM s.Date) = EXTRACT(YEAR FROM p.Date) AND EXTRACT(MONTH FROM s.Date) = EXTRACT(MONTH FROM p.Date)
提示:该简化写法仅适用于同月份仅有1条生产、1条销售记录的场景,若同月份存在多条明细,仍需使用前述聚合方案避免统计错误。
内容的提问来源于stack exchange,提问作者ysd
相关产品推荐
相关产品推荐

