You need to enable JavaScript to run this app. SQL合并生产与销售按年月关联表,为仅有销售的月份补生产值为0
优惠活动
大模型
产品
解决方案
定价
更多

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.09 13:06:04