如何用SQL的CROSS APPLY和UNPIVOT生成客服呼叫流向统计数据

要将透视格式的客服呼叫数据转换成桑基图需要的「步骤对+计数」结构，下面分别用CROSS APPLY和UNPIVOT两种方法实现：

方法一：使用CROSS APPLY直接生成步骤对 #

如果你的透视表是固定的步骤列（比如step1「语言选择」、step2「主菜单」、step3「客服入口/退出」、step4「接通/退出」），可以直接用CROSS APPLY把每个呼叫的相邻步骤拆成配对，再分组统计。

示例代码 #

假设原始表名为call_flow，结构包含唯一呼叫ID和各步骤列：

SELECT
    flow.from_step AS column1,
    flow.to_step AS column2,
    COUNT(*) AS count
FROM call_flow
CROSS APPLY (
    -- 枚举当前呼叫的所有相邻步骤对
    VALUES
        (step1, step2),
        (step2, step3),
        (step3, step4)
) AS flow(from_step, to_step)
-- 过滤无效的空步骤对
WHERE flow.from_step IS NOT NULL 
  AND flow.to_step IS NOT NULL
GROUP BY flow.from_step, flow.to_step
ORDER BY flow.from_step, flow.to_step;

逻辑说明 #

• CROSS APPLY配合VALUES子句，把单条呼叫记录的多个步骤对转换成多行数据；
• 最后通过GROUP BY统计每个步骤对的出现次数，得到桑基图需要的结构。

方法二：使用UNPIVOT灵活处理多步骤场景 #

如果你的步骤列数量不固定（比如不同呼叫的流程长度不同），用UNPIVOT先把透视列转成「呼叫ID+步骤顺序+步骤名称」的行格式，再通过自连接匹配相邻步骤。

示例代码 #

-- 先将透视表转为行格式
WITH unpivoted_steps AS (
    SELECT
        call_id,
        step_order,
        step_name
    FROM call_flow
    UNPIVOT (
        -- 将step1/step2/step3/step4列的值转为step_name，列名转为step_order
        step_name FOR step_order IN (step1, step2, step3, step4)
    ) AS up
    WHERE step_name IS NOT NULL -- 过滤空步骤
)
-- 自连接匹配相邻步骤并计数
SELECT
    us1.step_name AS column1,
    us2.step_name AS column2,
    COUNT(*) AS count
FROM unpivoted_steps us1
INNER JOIN unpivoted_steps us2
    ON us1.call_id = us2.call_id
    AND us2.step_order = us1.step_order + 1 -- 关联当前步骤的下一个步骤
GROUP BY us1.step_name, us2.step_name
ORDER BY us1.step_name, us2.step_name;

逻辑说明 #

• UNPIVOT把每个呼叫的多列步骤转换成单行步骤记录，保留步骤的顺序标识；
• 通过自连接找到同一个呼叫中顺序相邻的两个步骤，再分组统计步骤对的次数。

注意事项 #

• 必须过滤NULL步骤：避免出现无效的步骤对（比如customer service到NULL的记录）；
• 确保步骤顺序正确：step1/step2等列必须严格对应客服流程的先后顺序，否则步骤对会出错；
• 两种方法的选择：步骤列固定时用CROSS APPLY更简洁，步骤列多变时用UNPIVOT更灵活。

内容的提问来源于stack exchange，提问作者Vinith kumar V