如何关联月度实际销售统计与销售目标表实现数据对比？

问题背景 #

我正在用Kaggle的电商数据集制作入门级作品集，当前遇到SQL关联问题：已写出按年、月统计月度销售总额和总利润的查询语句，想要在结果中添加对应月份的销售目标以对比实际与目标差异，但尝试用CTE或临时表关联时，因日期格式拆分问题未能成功。

当前已实现的SQL查询 #

Select Year(list.OrderDateFixed) as Year, Month(list.OrderDateFixed) as Month, Sum(od.Amount) as TotalAmount, sum(od.profit) as TotalProfit
from [List of Orders] list
    join [Order Details] od
    on list.[Order ID] = od.[Order ID]
Group by  YEAR(list.OrderDateFixed), MONTH(list.OrderDateFixed)
Order by  YEAR(list.OrderDateFixed), MONTH(list.OrderDateFixed)

查询结果 #

Year    Month   TotalAmount TotalProfit
2018    4   32726.00    -3960.00
2018    5   28545.00    -3584.00
2018    6   23658.00    -4970.00
2018    7   12966.00    -2138.00
2018    8   30899.00    -2180.00
2018    9   26628.00    -4963.00
2018    10  31615.00    3093.00
2018    11  48086.00    11619.00
2018    12  37579.00    5284.00
2019    1   61439.00    9760.00
2019    2   38424.00    5917.00
2019    3   58937.00    10077.00

销售目标表结构及数据示例 #

MonthOrderDateFixed Category    Target
2018-04-01  Furniture   10400.00
2018-05-01  Furniture   10500.00
2018-06-01  Furniture   10600.00
2018-07-01  Furniture   10800.00
2018-08-01  Furniture   10900.00
2018-09-01  Furniture   11000.00
2018-10-01  Furniture   11100.00
2018-11-01  Furniture   11300.00
................................

解决方案 #

核心思路是将两张表的日期统一到年+月维度匹配，以下两种方法均可实现：

方法1：在关联条件中直接提取年、月匹配 #

将原查询封装为CTE，再与目标表通过提取的年、月字段关联：

WITH MonthlySales AS (
    SELECT 
        YEAR(list.OrderDateFixed) AS Year,
        MONTH(list.OrderDateFixed) AS Month,
        SUM(od.Amount) AS TotalAmount,
        SUM(od.profit) AS TotalProfit
    FROM [List of Orders] list
    JOIN [Order Details] od ON list.[Order ID] = od.[Order ID]
    GROUP BY YEAR(list.OrderDateFixed), MONTH(list.OrderDateFixed)
)
SELECT 
    ms.Year,
    ms.Month,
    ms.TotalAmount,
    ms.TotalProfit,
    t.Target,
    -- 新增实际与目标差异计算列
    ms.TotalAmount - t.Target AS SalesVsTargetDiff
FROM MonthlySales ms
JOIN [销售目标表名] t 
    ON ms.Year = YEAR(t.MonthOrderDateFixed)
    AND ms.Month = MONTH(t.MonthOrderDateFixed)
ORDER BY ms.Year, ms.Month;

注意：将[销售目标表名]替换为数据集里实际的目标表名称

方法2：转换为当月第一天直接匹配 #

把销售数据的日期转换为当月第一天，与目标表的MonthOrderDateFixed字段直接关联：

SELECT 
    YEAR(list.OrderDateFixed) AS Year,
    MONTH(list.OrderDateFixed) AS Month,
    SUM(od.Amount) AS TotalAmount,
    SUM(od.profit) AS TotalProfit,
    t.Target,
    SUM(od.Amount) - t.Target AS SalesVsTargetDiff
FROM [List of Orders] list
JOIN [Order Details] od ON list.[Order ID] = od.[Order ID]
JOIN [销售目标表名] t 
    ON DATE_TRUNC('month', list.OrderDateFixed) = t.MonthOrderDateFixed
GROUP BY YEAR(list.OrderDateFixed), MONTH(list.OrderDateFixed), t.Target
ORDER BY YEAR(list.OrderDateFixed), MONTH(list.OrderDateFixed);

适配不同SQL引擎说明：

• SQL Server：用DATEFROMPARTS(YEAR(list.OrderDateFixed), MONTH(list.OrderDateFixed), 1)替代DATE_TRUNC('month', ...)
• MySQL：用DATE_FORMAT(list.OrderDateFixed, '%Y-%m-01')替代DATE_TRUNC('month', ...)

补充说明 #

如果目标表包含多品类数据，可添加WHERE t.Category = 'Furniture'过滤条件，确保关联对应品类的目标值。

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