如何在数据库端合并列多行数据并去重？LISTAGG无法去重

需要将同一col0对应的col1多行数据合并为一行并去除重复值。尝试使用LISTAGG函数，但该函数无法消除重复值，希望能在数据库端直接完成去重，而非拉取到服务端后处理。

数据集 #

col0   col1
1      7
1      8
1      8
1      2
1      9
2      9
3      10
3      11
3      12

期望结果 #

col0   col1
1      2,7,8,9
2      9
3      10,11,12

已尝试的SQL（无法去重，存在语法错误） #

SELECT 
  col0, 
  SUM(col2 + col3) AS mycount, -- 原SQL此处缺失逗号
  LISTAGG(col1, ',') within GROUP (ORDER BY col1) 
FROM mytable 
WHERE 
  _time >= TIMESTAMP '2024-12-03 00:00:00' AND
  _time <  TIMESTAMP '2024-12-03 01:00:00'
GROUP BY col0
ORDER BY mycount DESC
LIMIT 10
;

核心思路是先对col0和col1分组去重，再基于去重后的结果使用LISTAGG合并，全程在数据库端完成操作。

通用调整方案 #

通过子查询先对col0、col1去重，再执行聚合和合并：

SELECT 
  t.col0,
  SUM(t.col2 + t.col3) AS mycount,
  LISTAGG(t.col1, ',') WITHIN GROUP (ORDER BY t.col1) AS col1_merged
FROM (
  -- 子查询去重，保留需要参与聚合的字段
  SELECT DISTINCT 
    col0, 
    col1,
    col2,
    col3
  FROM mytable 
  WHERE 
    _time >= TIMESTAMP '2024-12-03 00:00:00' AND
    _time <  TIMESTAMP '2024-12-03 01:00:00'
) t
GROUP BY t.col0
ORDER BY mycount DESC
LIMIT 10;

分数据库优化方案 #

Oracle 专属优化

若col2/col3的聚合需基于(col0,col1)分组计算，可改用子查询分组去重，避免DISTINCT：

SELECT 
  col0,
  SUM(total) AS mycount,
  LISTAGG(col1, ',') WITHIN GROUP (ORDER BY col1) AS col1_merged
FROM (
  SELECT 
    col0,
    col1,
    SUM(col2 + col3) AS total
  FROM mytable 
  WHERE 
    _time >= TIMESTAMP '2024-12-03 00:00:00' AND
    _time <  TIMESTAMP '2024-12-03 01:00:00'
  GROUP BY col0, col1
) t
GROUP BY col0
ORDER BY mycount DESC
FETCH FIRST 10 ROWS ONLY;

PostgreSQL 专属优化

PostgreSQL支持直接在STRING_AGG中使用DISTINCT，无需子查询：

SELECT 
  col0,
  SUM(col2 + col3) AS mycount,
  STRING_AGG(DISTINCT col1, ',' ORDER BY col1) AS col1_merged
FROM mytable 
WHERE 
  _time >= TIMESTAMP '2024-12-03 00:00:00' AND
  _time <  TIMESTAMP '2024-12-03 01:00:00'
GROUP BY col0
ORDER BY mycount DESC
LIMIT 10;

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