Spark SQL统计前缀匹配的分类与未分类行数问题求助

方法一：使用 LIKE 匹配前缀 #

先将code和category转为字符串类型，通过CONCAT拼接通配符%匹配前缀，再用CASE WHEN标记分类状态，最后统计各类行数：

SELECT
  classification,
  COUNT(*) AS row_count
FROM (
  SELECT
    CASE
      WHEN CAST(category AS STRING) LIKE CONCAT(CAST(code AS STRING), '%') THEN '已分类'
      ELSE '未分类'
    END AS classification
  FROM your_table
) t
GROUP BY classification;

方法二：使用 SUBSTRING 截取前缀对比 #

通过LENGTH获取code的长度，从category中截取对应长度的子串，与code转成的字符串直接对比：

SELECT
  classification,
  COUNT(*) AS row_count
FROM (
  SELECT
    CASE
      WHEN SUBSTRING(CAST(category AS STRING), 1, LENGTH(CAST(code AS STRING))) = CAST(code AS STRING) THEN '已分类'
      ELSE '未分类'
    END AS classification
  FROM your_table
) t
GROUP BY classification;

关键说明 #

• 必须将code和category转为字符串类型，避免数字类型处理时的格式偏差（如前导零、长度计算错误）。
• 两种方法均可实现需求：LIKE写法更简洁，SUBSTRING对前缀长度的控制更直观。

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