如何用Athena SQL将纽约客户旅行数据转为桑基图格式？

核心思路 #

先提取每个客户到访过的州（含常住地NY），按NY→VA→FL→TX→CA→WA→MN的指定优先级排序；再通过窗口函数生成相邻州的源-目标对，同时单独处理无旅行记录的客户（直接输出NY→No Travel）。

完整SQL代码 #

WITH customer_travel AS (
    SELECT 
        ID,
        -- 筛选到访州并按指定顺序排序
        array_sort(
            array_filter(
                ARRAY[
                    CAST(ROW('NY', 1) AS ROW(state VARCHAR, priority INT)),
                    CAST(ROW('VA', 2) AS ROW(state VARCHAR, priority INT)),
                    CAST(ROW('FL', 3) AS ROW(state VARCHAR, priority INT)),
                    CAST(ROW('TX', 4) AS ROW(state VARCHAR, priority INT)),
                    CAST(ROW('CA', 5) AS ROW(state VARCHAR, priority INT)),
                    CAST(ROW('WA', 6) AS ROW(state VARCHAR, priority INT)),
                    CAST(ROW('MN', 7) AS ROW(state VARCHAR, priority INT))
                ],
                x -> CASE x.state
                    WHEN 'NY' THEN "Home - NY" = 'Y'
                    WHEN 'VA' THEN VA = 'Y'
                    WHEN 'FL' THEN FL = 'Y'
                    WHEN 'TX' THEN TX = 'Y'
                    WHEN 'CA' THEN CA = 'Y'
                    WHEN 'WA' THEN WA = 'Y'
                    WHEN 'MN' THEN MN = 'Y'
                END
            ),
            (a, b) -> a.priority < b.priority
        ) AS sorted_states,
        "No Travel" AS no_travel_flag
    FROM your_table_name -- 替换为你的S3表名
),
travel_flow AS (
    -- 生成有旅行记录的客户流
    SELECT 
        s.state AS Source,
        LEAD(s.state) OVER (PARTITION BY ct.ID ORDER BY s.priority) AS Destination,
        ct.ID
    FROM customer_travel ct
    CROSS JOIN UNNEST(ct.sorted_states) AS t(s)
    WHERE ct.no_travel_flag = 'N'
    AND LEAD(s.state) OVER (PARTITION BY ct.ID ORDER BY s.priority) IS NOT NULL
    UNION ALL
    -- 处理无旅行记录的客户
    SELECT 
        'NY' AS Source,
        'No Travel' AS Destination,
        ID
    FROM customer_travel
    WHERE no_travel_flag = 'Y'
)
SELECT Source, Destination, ID
FROM travel_flow
ORDER BY ID, 
    CASE Source
        WHEN 'NY' THEN 1
        WHEN 'VA' THEN 2
        WHEN 'FL' THEN 3
        WHEN 'TX' THEN 4
        WHEN 'CA' THEN 5
        WHEN 'WA' THEN 6
        WHEN 'MN' THEN 7
        WHEN 'No Travel' THEN 8
    END;

关键逻辑说明 #

1. 数组筛选与排序：
   • 用array_filter匹配每个州对应的Y/N标记，只保留到访过的州；
   • 给每个州分配优先级数值，通过array_sort强制按要求的顺序排列，避免自然排序的混乱。
2. 生成相邻流对：
   • 用UNNEST把排序后的州数组拆分为单行记录；
   • LEAD窗口函数获取当前州的下一个州，自动生成Source→Destination的连续流关系。
3. 特殊情况处理：
   • 单独提取No Travel标记为Y的客户，直接输出NY→No Travel的固定行。
4. 最终排序：
   • 按客户ID分组，再按Source的优先级排序，确保每个客户的旅行流顺序完全符合要求。

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