AWS Athena中如何生成无订单日期的客户0值订单统计行

你的问题核心是没有构建日期与目标客户的全量组合，直接左连订单表只会保留有订单的匹配行。下面是针对Athena（Presto引擎）的修正方案：

针对单个客户（client_id 10552）的查询 #

WITH date_range AS (
    -- 生成2023年所有日期序列，Athena用Presto的sequence函数高效生成
    SELECT date AS order_date
    FROM UNNEST(sequence(date '2023-01-01', date '2023-12-31', interval '1' day)) AS t(date)
),
target_client AS (
    -- 指定目标客户，这里是10552
    SELECT 10552 AS client_id
),
client_daily_orders AS (
    -- 先按日期和客户聚合订单数，避免左连后重复计数
    SELECT 
        date(order_time) AS order_date,
        client_id,
        COUNT(*) AS order_count
    FROM "order"
    WHERE date(order_time) BETWEEN '2023-01-01' AND '2023-12-31'
      AND client_id = 10552 -- 提前过滤目标客户，提升性能
    GROUP BY order_date, client_id
)
-- 关键：用交叉连接生成日期+客户的全量组合，再左连订单聚合结果
SELECT 
    dr.order_date,
    tc.client_id,
    COALESCE(cdo.order_count, 0) AS order_count
FROM date_range dr
CROSS JOIN target_client tc
LEFT JOIN client_daily_orders cdo
    ON dr.order_date = cdo.order_date
    AND tc.client_id = cdo.client_id
ORDER BY dr.order_date;

为什么之前的查询失效？ #

你之前的写法应该是直接用date_range LEFT JOIN "order"，但订单表中没有对应日期的行时，左连后客户ID会变成NULL，导致这些行被过滤（或者你没处理NULL的client_id）。通过CROSS JOIN生成每个日期+目标客户的固定组合，再左连聚合后的订单数据，就能保证每个日期都有对应的客户行，无订单时用COALESCE把NULL转为0。

扩展：统计所有客户的每日订单数 #

如果需要覆盖订单表中所有客户，只需把target_client替换为订单表的去重客户列表：

WITH date_range AS (
    SELECT date AS order_date
    FROM UNNEST(sequence(date '2023-01-01', date '2023-12-31', interval '1' day)) AS t(date)
),
all_clients AS (
    -- 从订单表获取所有唯一客户ID
    SELECT DISTINCT client_id
    FROM "order"
),
client_daily_orders AS (
    SELECT 
        date(order_time) AS order_date,
        client_id,
        COUNT(*) AS order_count
    FROM "order"
    WHERE date(order_time) BETWEEN '2023-01-01' AND '2023-12-31'
    GROUP BY order_date, client_id
)
SELECT 
    dr.order_date,
    ac.client_id,
    COALESCE(cdo.order_count, 0) AS order_count
FROM date_range dr
CROSS JOIN all_clients ac
LEFT JOIN client_daily_orders cdo
    ON dr.order_date = cdo.order_date
    AND ac.client_id = cdo.client_id
ORDER BY dr.order_date, ac.client_id;

注意事项 #

• Athena中sequence函数需要Presto引擎版本支持（大多数现代Athena环境都支持），如果你的环境不支持，可以用递归CTE生成日期，但性能会差一些。
• 提前在client_daily_orders中过滤2023年数据并聚合，能减少左连时的数据量，提升查询效率。

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