如何在AWS Athena中编写SQL计算路径阶段的订单贡献占比

需求说明 #

现有AWS Athena表包含Order_id、Item_Count、Path_List（字符串数组类型）、order_date列，需计算每个路径节点对商品购买的贡献占比，规则为：

• 贡献占比 = 该路径对应的商品数 / 总路径项数
• 路径对应的商品数：该路径在各订单中出现的次数 × 对应订单的Item_Count之和
• 总路径项数：所有订单的Item_Count × 对应Path_List长度 之和

简洁实现SQL（窗口函数版） #

SELECT 
    path_node,
    CONCAT(SUM(Item_Count), '/', MAX(total_path_items)) AS contribution_ratio
FROM (
    -- 拆分路径数组，同时计算全局总路径项数
    SELECT 
        unnest(Path_List) AS path_node,
        Item_Count,
        SUM(Item_Count * ARRAY_LENGTH(Path_List)) OVER () AS total_path_items
    FROM your_table_name -- 替换为实际表名
) t
GROUP BY path_node
ORDER BY path_node;

分步实现SQL（CTE版） #

如果需要更清晰的分步逻辑，可使用CTE写法：

WITH path_nodes AS (
    -- 拆分每个订单的路径数组为单个节点
    SELECT 
        unnest(Path_List) AS path_node,
        Item_Count
    FROM your_table_name
),
total_calculation AS (
    -- 计算全局总路径项数
    SELECT SUM(Item_Count * ARRAY_LENGTH(Path_List)) AS total_path_items
    FROM your_table_name
)
SELECT 
    pn.path_node,
    CONCAT(SUM(pn.Item_Count), '/', tc.total_path_items) AS contribution_ratio
FROM path_nodes pn
CROSS JOIN total_calculation tc
GROUP BY pn.path_node, tc.total_path_items
ORDER BY pn.path_node;

输出结果示例 #

执行后将得到符合要求的结果：

path_node  contribution_ratio
A          5/17
B          1/17
C          4/17
D          3/17
W          1/17
Z          3/17

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