如何在AWS Athena中实现按月批量查询SKU对应金额？

问题背景 #

现有表包含SKU、Amount、start_date、end_date字段，可通过指定日期的SQL查询对应日期的SKU金额（例如查询2022-04-30时，SKU A返回4；查询2023-01-01时，SKU A返回2）。需要按月批量执行查询，获取每月最后一天的SKU金额数据。由于AWS Athena不支持动态SQL，不想采用复制查询再UNION的繁琐方式，已生成每月最后一天的日期列表，需将该列表与原表关联得到目标格式结果。

原表结构 #

单日期查询SQL #

select  SKU, Amount
from    myTable
where   date('2022-04-30') between start_date and end_date;

日期列表生成SQL #

with oneRowPerMonth as(
select sequence(date('2019-01-15'), date('2030-01-15'), interval '1' month) as seq
)
select date_trunc('month', date(midMonth)) + interval '1' month - interval '1' day as LastDayOfMonth
from oneRowPerMonth
cross join unnest(seq) as t(midMonth)

目标结果格式 #

最优实现方案 #

将生成日期列表的CTE与原表进行CROSS JOIN，再通过WHERE条件筛选出日期落在SKU有效期内的记录，即可批量得到所有每月最后一天的SKU金额数据。SQL语句如下：

with oneRowPerMonth as(
    select sequence(date('2019-01-15'), date('2030-01-15'), interval '1' month) as seq
),
month_end_dates as(
    select date_trunc('month', date(midMonth)) + interval '1' month - interval '1' day as record_date
    from oneRowPerMonth
    cross join unnest(seq) as t(midMonth)
)
select 
    mt.SKU, 
    mt.Amount, 
    med.record_date as "Date of Record"
from myTable mt
cross join month_end_dates med
where med.record_date between mt.start_date and mt.end_date
order by med.record_date, mt.SKU;

说明 #

• 用month_end_dates CTE封装日期列表生成逻辑，让整体结构更清晰
• 通过CROSS JOIN将每个SKU的有效期记录与所有月末日期关联，再通过WHERE条件过滤出匹配的组合
• 最后按记录日期和SKU排序，便于数据的查看和整理

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