如何在AWS Athena中计算点击路径的媒介占比？

基于给定的用户路径数据集，我们需要统计各媒介作为第二点击媒介、第三点击媒介时的用户占比，包含媒介名称、第二点击用户占比、第三点击用户占比。以下是对应的Athena SQL解决方案：

完整SQL代码 #

WITH dataset AS (
    SELECT 1 AS total_users, ARRAY['google', 'meta', 'attentive', 'meta'] AS path_list
    UNION ALL
    SELECT 1, ARRAY['google', 'attentive', 'Direct Traffic', 'Direct Traffic', 'Direct Traffic', 'meta']
    UNION ALL
    SELECT 4, ARRAY['google','meta', 'google']
    UNION ALL
    SELECT 1, ARRAY['google', 'meta', 'meta', 'Direct Traffic' , 'meta']
    UNION ALL
    SELECT 1, ARRAY['google', 'meta', 'meta']
    UNION ALL
    SELECT 1, ARRAY['google', 'Direct Traffic', 'Direct Traffic','attentiva', 'attentiva', 'attentiva', 'Direct Traffic', 'meta']
),
click_stats AS (
    -- 提取第二点击的媒介及对应用户数
    SELECT
        'second' AS click_position,
        path_list[2] AS medium,
        total_users AS user_count
    FROM dataset
    WHERE path_list[2] IS NOT NULL
    UNION ALL
    -- 提取第三点击的媒介及对应用户数（仅处理路径长度≥3的情况）
    SELECT
        'third' AS click_position,
        CASE WHEN CARDINALITY(path_list) > 2 THEN path_list[3] ELSE NULL END AS medium,
        total_users AS user_count
    FROM dataset
    WHERE CASE WHEN CARDINALITY(path_list) > 2 THEN path_list[3] ELSE NULL END IS NOT NULL
),
total_users_summary AS (
    -- 计算总用户数，作为占比计算的分母
    SELECT SUM(total_users) AS total_total_users FROM dataset
)

SELECT
    medium AS "媒介名称",
    SUM(CASE WHEN click_position = 'second' THEN user_count ELSE 0 END) AS "第二点击用户数",
    ROUND(
        SUM(CASE WHEN click_position = 'second' THEN user_count ELSE 0 END) * 100.0 / total_total_users,
        2
    ) AS "第二点击占比(%)",
    SUM(CASE WHEN click_position = 'third' THEN user_count ELSE 0 END) AS "第三点击用户数",
    ROUND(
        SUM(CASE WHEN click_position = 'third' THEN user_count ELSE 0 END) * 100.0 / total_total_users,
        2
    ) AS "第三点击占比(%)"
FROM click_stats
CROSS JOIN total_users_summary
GROUP BY medium, total_total_users
ORDER BY medium;

代码逻辑说明 #

1. dataset CTE：定义原始用户路径数据集，存储每条路径的用户数量和媒介访问序列。
2. click_stats CTE：
   • 拆分第二点击、第三点击两个维度的媒介数据，用UNION ALL合并统计项；
   • 对第三点击做合法性判断：仅当路径长度大于2时，才提取第三个位置的媒介（Athena数组索引从1开始）。
3. total_users_summary CTE：计算所有路径的总用户数，确保占比计算的分母准确。
4. 最终查询：
   • 用CASE WHEN条件聚合，分别统计每个媒介在第二、第三点击位置的用户数；
   • 将用户数转换为百分比格式，用ROUND保留2位小数；
   • 按媒介名称排序结果，提升可读性。

查询结果示例 #

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