在AWS Athena中按日期统计各类型独立用户数（行转列）

问题概述 #

现有多个国家的用户数据表，每个表包含user、user_type、date三列。全球user_type固定为National、International、Unknown三类，部分国家表可能缺少某类用户数据，需确保缺失类别的统计值显示为0。要求按日期一行展示各类用户的独立用户数，输出格式为date、first_user_type（对应National）、second_user_type（对应International）、third_user_type（对应Unknown）。

示例原始数据 #

期望输出格式 #

当前查询的问题 #

执行以下SQL后，得到同日期多行展示的结果，不符合行转列的需求：

SELECT date, user_type, COUNT(distinct user) as num_users 
FROM "country"."table" 
WHERE date between '2022-10-01' AND '2022-10-03' 
GROUP BY date, user_type 
ORDER BY date, user_type

当前输出结果：

解决方案（兼容AWS Athena） #

核心逻辑 #

1. 生成目标日期范围与三类user_type的全组合，确保每个日期下的所有用户类型都被覆盖
2. 对原始数据按日期和用户类型聚合独立用户数
3. 左连接全组合与聚合结果，用COALESCE填充缺失值为0
4. 通过条件聚合实现行转列，得到期望格式

完整SQL代码 #

WITH date_range AS (
    -- 生成查询范围内的所有日期，可替换为动态范围
    SELECT date
    FROM UNNEST(SEQUENCE(DATE '2022-10-01', DATE '2022-10-03', INTERVAL '1' DAY)) AS t(date)
),
all_user_types AS (
    -- 定义全球所有用户类型
    SELECT user_type
    FROM UNNEST(ARRAY['National', 'International', 'Unknown']) AS t(user_type)
),
date_type_combinations AS (
    -- 生成日期与用户类型的全量组合
    SELECT dr.date, aut.user_type
    FROM date_range dr
    CROSS JOIN all_user_types aut
),
aggregated_data AS (
    -- 原始数据按日期+用户类型聚合独立用户数
    SELECT 
        date,
        user_type,
        COUNT(DISTINCT user) AS num_users
    FROM "your_country"."your_table"  -- 仅需替换此处的国家和表名
    WHERE date BETWEEN '2022-10-01' AND '2022-10-03'
    GROUP BY date, user_type
)
-- 行转列并填充缺失值为0
SELECT 
    dtc.date,
    SUM(CASE WHEN dtc.user_type = 'National' THEN COALESCE(ad.num_users, 0) ELSE 0 END) AS first_user_type,
    SUM(CASE WHEN dtc.user_type = 'International' THEN COALESCE(ad.num_users, 0) ELSE 0 END) AS second_user_type,
    SUM(CASE WHEN dtc.user_type = 'Unknown' THEN COALESCE(ad.num_users, 0) ELSE 0 END) AS third_user_type
FROM date_type_combinations dtc
LEFT JOIN aggregated_data ad 
    ON dtc.date = ad.date AND dtc.user_type = ad.user_type
GROUP BY dtc.date
ORDER BY dtc.date;

关键说明 #

• 动态日期范围：若需生成动态日期（如最近30天），可将date_range替换为：

  SELECT date
  FROM UNNEST(SEQUENCE(CURRENT_DATE - INTERVAL '30' DAY, CURRENT_DATE, INTERVAL '1' DAY)) AS t(date)
  

• 通用性：仅需修改aggregated_data中的"your_country"."your_table"即可适配不同国家的表
• Athena兼容性：使用Athena原生支持的UNNEST、SEQUENCE、COALESCE等函数，确保在AWS Athena环境正常运行
• 缺失值处理：通过COALESCE将无数据的用户类型统计值填充为0，满足需求

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