如何基于含价格变动的双表数据计算月度/季度销售总额？

算力阿峰

2026-7-2

你的两张数据表整理如下：

销售明细表（sales_details）

item id	transaction date	location
101	2023.01.20	A
102	2023.02.12	A
103	2023.05.25	B
102	2023.06.04	C
101	2023.11.24	B

商品时段价格表（item_prices）

item ID	price	valid from	valid to
101	1	2023.01.01	2023.05.31
101	3	2023.06.01	2023.12.31
102	5	2023.01.01	2023.12.31
103	4	2023.01.01	2023.12.31

解决方案

你之前关联失败的核心问题是：只靠item id关联会产生多对多匹配，必须加上销售日期落在对应价格有效期内的条件，才能精准匹配每笔销售的对应价格。

以下是具体实现（以MySQL为例，其他数据库仅需调整日期转换函数）：

1. 月度销售总额报表

SELECT
    -- 提取年月，格式如"2023-01"
    DATE_FORMAT(STR_TO_DATE(s.`transaction date`, '%Y.%m.%d'), '%Y-%m') AS sale_month,
    SUM(p.price) AS total_sales
FROM
    sales_details s
JOIN
    item_prices p 
    ON s.`item id` = p.`item ID`
    -- 关键：确保销售日期在价格有效期区间内
    AND STR_TO_DATE(s.`transaction date`, '%Y.%m.%d') BETWEEN STR_TO_DATE(p.`valid from`, '%Y.%m.%d') AND STR_TO_DATE(p.`valid to`, '%Y.%m.%d')
GROUP BY
    sale_month
ORDER BY
    sale_month;

2. 季度销售总额报表

仅需修改日期分组格式，生成季度标识：

SELECT
    DATE_FORMAT(STR_TO_DATE(s.`transaction date`, '%Y.%m.%d'), '%Y-Q%q') AS sale_quarter,
    SUM(p.price) AS total_sales
FROM
    sales_details s
JOIN
    item_prices p 
    ON s.`item id` = p.`item ID`
    AND STR_TO_DATE(s.`transaction date`, '%Y.%m.%d') BETWEEN STR_TO_DATE(p.`valid from`, '%Y.%m.%d') AND STR_TO_DATE(p.`valid to`, '%Y.%m.%d')
GROUP BY
    sale_quarter
ORDER BY
    sale_quarter;