Azure Databricks触点表分组统计SQL问题求助

数据集定义 #

CREATE OR REPLACE TABLE touchpoints_table
(
  List  STRING,
  Path_Length 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：元素单独出现在List中的次数
• Started：元素作为List开头的次数
• Finished：元素作为List结尾的次数
• Middleway：元素出现在List首尾之间的次数

目标结果 #

错误查询及结果 #

以下查询因元素格式问题未正确聚合，出现重复Content行：

WITH tb1 AS(
  SELECT 
    CAST(touch_array AS STRING) AS touch_list,
    EXPLODE(touch_array) AS explode_list,
    ROW_NUMBER()OVER(PARTITION BY CAST(touch_array AS STRING) ORDER BY (SELECT 1)) touch_count,
    COUNT(*)OVER(PARTITION BY touch_array) touch_lenght
  FROM (SELECT SPLIT(List, ',') AS touch_array FROM touchpoints_table) 
  )
  SELECT
     explode_list AS Content,
     SUM(CASE WHEN touch_lenght=1 THEN 1 ELSE 0 END) AS Unique,
     SUM(CASE WHEN touch_count=1 AND touch_lenght > 1 THEN 1 ELSE 0 END) AS Started,
     SUM(CASE WHEN touch_count>1 AND touch_count < touch_lenght THEN 1 ELSE 0 END) AS Middleway,
     SUM(CASE WHEN touch_count>1 AND touch_count = touch_lenght THEN 1 ELSE 0 END) AS Finished  
  FROM tb1 
  GROUP BY explode_list
  ORDER BY explode_list    

错误结果 #

错误原因 #

SPLIT(List, ',')生成的元素包含前导空格（如' AAA'、' BBB'），导致GROUP BY时相同元素因空格被识别为不同值，出现重复行。同时原窗口函数的分区逻辑存在冗余。

正确SQL代码 #

WITH processed_data AS (
  SELECT
    -- 拆分List并去除每个元素的前后空格
    TRANSFORM(SPLIT(List, ','), x -> TRIM(x)) AS touch_array,
    Path_Length
  FROM touchpoints_table
),
exploded_data AS (
  SELECT
    touch_array,
    Path_Length,
    -- 直接获取元素在数组中的位置（从0开始）
    EXPLODE_POSITION(touch_array) AS (pos, content)
  FROM processed_data
)
SELECT
  content AS Content,
  -- 统计单独出现的次数
  SUM(CASE WHEN Path_Length = 1 THEN 1 ELSE 0 END) AS Unique,
  -- 统计作为开头的次数（位置为0，且长度>1）
  SUM(CASE WHEN pos = 0 AND Path_Length > 1 THEN 1 ELSE 0 END) AS Started,
  -- 统计出现在中间的次数（位置不在首尾）
  SUM(CASE WHEN pos > 0 AND pos < Path_Length - 1 THEN 1 ELSE 0 END) AS Middleway,
  -- 统计作为结尾的次数（位置为最后一位）
  SUM(CASE WHEN pos = Path_Length - 1 AND Path_Length > 1 THEN 1 ELSE 0 END) AS Finished
FROM exploded_data
GROUP BY content
ORDER BY content;

代码说明 #

1. processed_data：使用TRANSFORM和TRIM统一元素格式，消除空格干扰。
2. exploded_data：用EXPLODE_POSITION直接获取元素位置，替代冗余的窗口函数，逻辑更简洁。
3. 聚合逻辑：
   • Unique：路径长度为1时计数
   • Started：位置为0且路径长度>1时计数
   • Middleway：位置既不是开头也不是结尾时计数
   • Finished：位置为最后一位且路径长度>1时计数

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