如何基于两日期列在AWS Athena中生成数据每日快照？

我有一张产品销售数据表，结构和数据如下：

需要将其转换为每日快照形式，即每个日期区间内的每一天都生成一条对应产品的记录，输出结构如下：

我知道在Pandas里怎么实现，但需要在AWS Athena中完成。尝试过生成日期区间后用unnest展开，但映射时遇到问题，求可行的SQL解决方案。

在Athena中可以通过生成连续日期序列 + 关联原表的方式实现，核心是利用Athena支持的sequence函数生成日期范围，再结合unnest拆分，最后关联原表匹配日期区间。

完整SQL示例 #

WITH date_series AS (
    -- 生成覆盖原表所有销售区间的连续日期
    SELECT unnest(sequence(min_date, max_date, interval '1' day)) AS date
    FROM (
        SELECT MIN(sales_start) AS min_date, MAX(sales_end) AS max_date
        FROM your_table_name -- 替换成你的实际表名
    ) t
)
SELECT 
    p.product_id,
    p.user_id,
    p.quantity,
    ds.date
FROM your_table_name p -- 替换成你的实际表名
JOIN date_series ds 
    ON ds.date BETWEEN p.sales_start AND p.sales_end
ORDER BY p.product_id, ds.date;

关键步骤说明 #

1. 生成日期序列：

   • 先通过子查询获取原表中最早的销售开始日期和最晚的销售结束日期，确定需要覆盖的总日期范围
   • 用sequence(start_date, end_date, interval '1' day)生成连续的日期数组，再用unnest将数组拆分为单行的日期记录

2. 关联匹配：

   • 将原表和日期序列表做JOIN，条件是日期序列的date落在原表的sales_start与sales_end区间内，这样每个日期区间内的每一天都会匹配到对应的产品记录

注意事项 #

• 如果日期范围跨度极大（比如数年），生成全量日期序列可能影响性能，可根据业务需求缩小范围（比如只生成近1年的日期）
• 确保sales_start和sales_end是DATE类型，若为字符串需先转换：date_parse(sales_start, '%Y-%m-%d')
• 若字段是timestamp类型，可先用date_trunc('day', sales_start)转换为日期后再处理

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