Athena查询优化：为缺失时段的用户补全零值行

我正在编写Athena查询，用于生成营销邮件发送前后的分析数据。当前执行查询后，部分用户缺失特定时段的记录：如ABCDRF无Pre时段行，IUSTRA和MAHRYS无Post时段行。我希望为这些缺失时段的用户添加对应行，保留其age值，将net_amount和total_transactions设为0。

当前使用的查询代码：

select distinct(customer_ID) as customer_ID, age, sum(net_amount) as net_amount, sum(lineitem) as total_visits, (case 
        when trans_date >= (current_timestamp - interval '18' month) AND trans_date < (current_timestamp - interval '6' month) then 'Post'
        when trans_date >= (current_timestamp - interval '30' month) AND trans_date < (current_timestamp - interval '18' month) then 'Pre'
        else 'nope'
        end) as Period
from trans_history
where trans_date >= (current_timestamp - interval '30' month) AND trans_date < (current_timestamp - interval '6' month)
group by 1, 5
order by 1 desc;

期望每个用户都有Pre和Post时段的记录，缺失时段的金额和交易数为0，请问如何修改Athena查询实现该需求？

要实现每个用户都包含Pre和Post两个时段的记录，缺失时段补0，核心思路是先生成所有用户+时段的完整组合，再与原聚合结果做左关联填充空值，具体修改后的查询如下：

WITH user_base AS (
    -- 提取符合时间范围的所有唯一用户及其固定age值
    SELECT DISTINCT customer_ID, age
    FROM trans_history
    WHERE trans_date >= (current_timestamp - interval '30' month) 
      AND trans_date < (current_timestamp - interval '6' month)
),
period_list AS (
    -- 定义需要的两个分析时段
    SELECT 'Pre' AS Period UNION ALL SELECT 'Post' AS Period
),
full_user_periods AS (
    -- 交叉连接生成每个用户对应两个时段的完整数据集
    SELECT ub.customer_ID, ub.age, pl.Period
    FROM user_base ub
    CROSS JOIN period_list pl
),
aggregated_trans AS (
    -- 按用户+时段聚合交易数据，过滤掉无关的nope时段
    SELECT 
        customer_ID,
        CASE 
            WHEN trans_date >= (current_timestamp - interval '18' month) AND trans_date < (current_timestamp - interval '6' month) THEN 'Post'
            WHEN trans_date >= (current_timestamp - interval '30' month) AND trans_date < (current_timestamp - interval '18' month) THEN 'Pre'
        END AS Period,
        SUM(net_amount) AS net_amount,
        SUM(lineitem) AS total_visits
    FROM trans_history
    WHERE trans_date >= (current_timestamp - interval '30' month) 
      AND trans_date < (current_timestamp - interval '6' month)
    GROUP BY customer_ID, Period
)
-- 左关联补全缺失时段，用0填充空值
SELECT 
    fup.customer_ID,
    fup.age,
    COALESCE(at.net_amount, 0) AS net_amount,
    COALESCE(at.total_visits, 0) AS total_visits,
    fup.Period
FROM full_user_periods fup
LEFT JOIN aggregated_trans at 
    ON fup.customer_ID = at.customer_ID 
    AND fup.Period = at.Period
ORDER BY fup.customer_ID DESC;

关键逻辑说明： #

• user_base：确保每个用户只保留一条含age的记录（假设age是用户固定属性）
• period_list：固定生成Pre和Post两个时段，保证每个用户都能匹配到完整时段
• full_user_periods：通过交叉连接得到用户与时段的全量组合，解决缺失时段的问题
• aggregated_trans：保留原查询的交易聚合逻辑，去掉多余的else 'nope'（where条件已过滤无关时间）
• COALESCE函数：将左关联后缺失时段的聚合字段值替换为0，满足需求

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