SQL如何统计同一列中多个is_contains()匹配项的各自计数

核心逻辑：针对单行存储多个归属人的场景，先将多值的owner字段按逗号分隔符拆分为独立的单行归属人记录，再基于拆分后的结果做分组聚合即可得到准确统计值。

不同SQL引擎的实现示例 #

• Spark SQL / Hive 实现
  用LATERAL VIEW + EXPLODE组合完成多值字段拆分：

  SELECT
    TRIM(owner_single) AS owner,
    COUNT(DISTINCT task_id) AS task_count
  FROM tasks
  LATERAL VIEW EXPLODE(SPLIT(owner, ',')) t AS owner_single
  GROUP BY TRIM(owner_single)
  ORDER BY task_count DESC
  

• PostgreSQL 实现
  用UNNEST函数拆分数组完成行扩展：

  SELECT
    TRIM(owner_single) AS owner,
    COUNT(DISTINCT task_id) AS task_count
  FROM tasks,
       UNNEST(STRING_TO_ARRAY(owner, ',')) AS owner_single
  GROUP BY TRIM(owner_single)
  ORDER BY task_count DESC
  

• MySQL 8.0+ 实现
  用递归CTE完成多值拆分：

  WITH RECURSIVE split_owners AS (
    SELECT 
      task_id,
      owner,
      1 AS idx,
      TRIM(SUBSTRING_INDEX(owner, ',', 1)) AS owner_single
    FROM tasks
    UNION ALL
    SELECT 
      task_id,
      owner,
      idx + 1,
      TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(owner, ',', idx + 1), ',', -1))
    FROM split_owners
    WHERE idx + 1 <= LENGTH(owner) - LENGTH(REPLACE(owner, ',', '')) + 1
  )
  SELECT 
    owner_single AS owner,
    COUNT(DISTINCT task_id) AS task_count
  FROM split_owners
  GROUP BY owner_single
  ORDER BY task_count DESC
  

方案优势 #

• 无需提前穷举所有归属人列表，新增归属人时无需调整查询逻辑
• 代码简洁易维护，执行效率远优于多UNION拼接、单列计数后行转列的实现
• 自动适配归属人数量不固定的场景，兼容owner字段存在多余空格的情况

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