如何扩展AWS Athena SQL以按渠道统计全用户Top3访问页面

问题描述 #

在指定时间区间内，识别所有触发过“Checkout”事件的用户中，按medium（渠道）分组的Top3访问页面及其总访问量。使用AWS Athena中的events_table表，字段包括user_id、medium、event_name、event_date，需将原单个用户的统计SQL扩展为全用户范围的统计。

原单个用户SQL #

select medium, page_path, count(page_path)
from events_table 
where date_parse(event_date,'%Y-%m-%d') >= date_parse('2022-09-16','%Y-%m-%d')
  and date_parse(event_date,'%Y-%m-%d') <= date_parse('2022-09-22','%Y-%m-%d')
  and event_name = 'Checkout'
  and user_id = '002300-166240'
group by 1, 2
order by 2 desc

扩展后的全用户统计SQL #

要实现全用户范围、按渠道分组取Top3页面的需求，需要先筛选目标用户，再统计页面访问量，最后用窗口函数排序取前3。具体SQL如下：

WITH checkout_users AS (
    -- 筛选指定时间内触发过Checkout事件的所有用户
    SELECT DISTINCT user_id
    FROM events_table
    WHERE date_parse(event_date, '%Y-%m-%d') BETWEEN date_parse('2022-09-16', '%Y-%m-%d') 
                                                  AND date_parse('2022-09-22', '%Y-%m-%d')
      AND event_name = 'Checkout'
),
page_stats AS (
    -- 统计目标用户各渠道下的页面访问总量
    SELECT 
        medium,
        page_path,
        COUNT(page_path) AS total_visits
    FROM events_table
    WHERE user_id IN (SELECT user_id FROM checkout_users)
      AND date_parse(event_date, '%Y-%m-%d') BETWEEN date_parse('2022-09-16', '%Y-%m-%d') 
                                                    AND date_parse('2022-09-22', '%Y-%m-%d')
    GROUP BY medium, page_path
),
ranked_pages AS (
    -- 按渠道对页面访问量降序排名
    SELECT 
        medium,
        page_path,
        total_visits,
        ROW_NUMBER() OVER (PARTITION BY medium ORDER BY total_visits DESC) AS page_rank
    FROM page_stats
)
-- 筛选各渠道的Top3页面
SELECT medium, page_path, total_visits
FROM ranked_pages
WHERE page_rank <= 3
ORDER BY medium, page_rank;

逻辑说明 #

1. checkout_users CTE：先获取指定时间内所有触发过Checkout事件的用户ID，去重避免重复统计同一用户。
2. page_stats CTE：基于上述用户集合，统计他们在指定时间内各渠道下每个页面的访问总量。
3. ranked_pages CTE：使用ROW_NUMBER()窗口函数，按渠道分组，对每个渠道内的页面按访问量降序排名。
4. 最后筛选出排名≤3的记录，得到各渠道的Top3访问页面及对应访问量。

示例结果 #

根据提供的示例数据，执行后会得到类似如下结果：

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