如何基于日销售数据计算产品年度在售周数？

需求：基于日销售数据计算每个产品每年的在售周数（即每周至少销售一次的周数），该数据将用于后续计算。

现有数据 #

1. 日销售表（数百万条记录），结构如下：

2. 产品表，结构如下：

期望输出结果示例 #

已尝试操作：从BookingDate中提取年份和周数，尝试构建按年周存储产品的新表，但未完成。请问该需求是否可行？如何实现？

该需求完全可行，核心逻辑是先筛选出产品有销售记录的唯一年周组合，再按年和产品统计有效周数，具体实现如下：

1. 提取唯一年周销售记录 #

首先从日销售表中提取每条记录对应的年份、周数，同时对ProductId+年份+周数进行去重——因为只要某周有至少一次销售，就记为有效周，无需重复统计同一周的多条销售数据。

以下是通用SQL逻辑（不同数据库的日期函数略有差异，可按需调整）：

WITH weekly_valid_sales AS (
    SELECT
        DISTINCT
        YEAR(BookingDate) AS Year,
        WEEK(BookingDate) AS WeekNumber,
        ProductId
    FROM 日销售表
    WHERE Pieces > 0 -- 过滤无实际销量的记录（若存在）
)

2. 关联产品表并统计在售周数 #

将上述临时表与产品表关联获取产品名称，再按年份和产品分组，统计每组的周数数量，即为该产品当年的在售周数：

SELECT
    ws.Year,
    p.ProductName AS Product,
    COUNT(ws.WeekNumber) AS WeeksActive
FROM weekly_valid_sales ws
JOIN 产品表 p ON ws.ProductId = p.ProductId
GROUP BY ws.Year, p.ProductName
ORDER BY ws.Year DESC, p.ProductName;

3. 数据库适配细节 #

不同数据库的日期提取函数不同，这里列出常见数据库的对应写法：

• MySQL：YEAR(BookingDate)、WEEK(BookingDate)
• SQL Server：DATEPART(YEAR, BookingDate)、DATEPART(WEEK, BookingDate)
• PostgreSQL：EXTRACT(YEAR FROM BookingDate)、EXTRACT(WEEK FROM BookingDate)
• Oracle：TO_CHAR(BookingDate, 'YYYY')、TO_CHAR(BookingDate, 'IW')（IW为ISO标准周数，确保周计算逻辑统一）

4. 性能优化建议 #

针对数百万条记录的日销售表，可通过以下方式提升查询效率：

• 给BookingDate和ProductId建立联合索引
• 对大表按年份进行分区存储
• 若无需全量历史数据，可在WHERE子句中提前限定时间范围

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