SQL如何对数组列按行去重后统计各npi值的全局出现次数

实现方案 #

你可以通过数组去重+行展开+分组计数的逻辑实现需求，PostgreSQL环境下的查询语句如下：

SELECT 
  npi,
  COUNT(*) AS count
FROM (
  -- 先对每行的npi数组做去重，避免同一行重复元素被多次计数
  SELECT unnest(array_distinct(array_remove(array[n.npi, n1.npi, n2.npi], null))) AS npi
  FROM lines
) t
GROUP BY npi
ORDER BY npi;

逻辑说明 #

• 内层查询先用array_remove过滤空值，再用array_distinct剔除同一行内重复的npi，保证每行每个npi仅保留1个
• 通过unnest将数组中的每个npi拆分为独立行
• 外层查询按npi分组统计行数，得到的结果就是每个npi出现过的总行数

如果你的PostgreSQL版本低于13（不支持array_distinct函数），可以用以下兼容写法替换内层逻辑：

SELECT 
  npi,
  COUNT(*) AS count
FROM (
  -- lines表无主键时可以把lines.id替换为ctid
  SELECT DISTINCT ON (lines.id, npi)
    unnest(array_remove(array[n.npi, n1.npi, n2.npi], null)) AS npi
  FROM lines
) t
GROUP BY npi
ORDER BY npi;

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