Azure Databricks中基于列表生成触点表的SQL查询问题求助

数据集 #

CREATE OR REPLACE TABLE touchpoints_table
(
  List  STRING,
  Path_Lenght INT
);

INSERT INTO touchpoints_table VALUES
  ('BBB, AAA, CCC', 3),  
  ('BBB', 1),
  ('DDD, AAA', 2), 
  ('DDD, BBB, AAA, EEE, CCC', 5), 
  ('EEE, AAA, EEE, CCC', 4);
  
SELECT * FROM touchpoints_table

需求目标 #

生成包含以下字段的目标表：

• Content：List中的元素
• Unique：元素单独出现在列表中的次数（即列表长度为1时的出现次数）
• Started：元素出现在列表开头的次数（列表长度>1时的首位）
• Finished：元素出现在列表末尾的次数（列表长度>1时的末位）
• Middleway：元素出现在列表首尾之间的次数（列表长度>1时的中间位置）

原代码问题分析 #

原查询出现重复Content记录的核心原因是：拆分List字段时未处理元素前后的空格，SPLIT(List, ',')生成的数组元素包含前导空格（比如' AAA'和'AAA'被视为不同值），最终导致分组错误。此外，窗口函数的使用可以进一步优化，简化逻辑。

正确SQL代码 #

WITH tb1 AS (
  SELECT 
    touch_array,
    -- 去除元素前后空格，确保相同元素正确分组
    TRIM(explode_list) AS Content,
    -- 为每个元素在列表中的位置编号
    ROW_NUMBER() OVER(PARTITION BY touch_array ORDER BY pos) AS touch_count,
    -- 直接获取列表总长度
    SIZE(touch_array) AS touch_length
  FROM (
    SELECT 
      SPLIT(List, ',') AS touch_array,
      -- 同时获取元素和位置索引，简化位置判断
      POS_EXPLODE(SPLIT(List, ',')) AS (pos, explode_list)
    FROM touchpoints_table
  )
)
SELECT
  Content,
  -- 统计列表长度为1时的出现次数
  SUM(CASE WHEN touch_length = 1 THEN 1 ELSE 0 END) AS Unique,
  -- 统计列表长度>1时作为首位的次数
  SUM(CASE WHEN touch_count = 1 AND touch_length > 1 THEN 1 ELSE 0 END) AS Started,
  -- 统计列表长度>1时处于中间位置的次数
  SUM(CASE WHEN touch_count > 1 AND touch_count < touch_length THEN 1 ELSE 0 END) AS Middleway,
  -- 统计列表长度>1时作为末位的次数
  SUM(CASE WHEN touch_count = touch_length AND touch_length > 1 THEN 1 ELSE 0 END) AS Finished
FROM tb1
GROUP BY Content
ORDER BY Content

代码说明 #

1. 用POS_EXPLODE替代EXPLODE，同时获取元素的位置索引，简化位置编号逻辑
2. 通过TRIM()清理元素前后空格，消除分组时的重复值问题
3. 使用SIZE(touch_array)直接获取列表长度，避免冗余的窗口函数计算
4. 调整条件判断逻辑，确保各字段统计完全符合需求定义

内容的提问来源于stack exchange，提问作者Wagner André Yamada Vieira