postgresql - 压缩列为 NULL 的行

Question

我有一个表(在 PostgreSQL 数据库中)，其中某些 ID 的多行有一些值，而有些为空。表格看起来像这样(玩具示例):

PSQL:test/me@[local]=> SELECT * FROM t1;
 id |  x  |  y  | z  
----+-----+-----+----
  1 |     | 200 |   
  1 | 100 |     | 42
  2 |  45 |     |    
(3 rows)

最重要的是:我可以保证在相同字段具有非 NULL 值的情况下没有相同 ID 的行。在上面的示例中，id 1 不会有任何其他行。

我希望聚合每个 id 的所有值，压缩非空值。也就是改造成这样的东西

 id |  x  |  y  | z  
----+-----+-----+----
  1 | 100 | 200 | 42  
  2 |  45 |     |    
(2 rows)

我得到的最接近的是使用级联 COALESCE(field1, '') ||合并(field2，'')|| ... 但结果不是我想要的:

PSQL:test/me@[local]=> SELECT id, COALESCE(x::TEXT || ',' , '') || COALESCE(y::TEXT || ',', '') || COALESCE(z::TEXT || ',', '') AS agg FROM t1 GROUP BY id, agg;
 id |   agg   
----+---------
  1 | 100,42,
  1 | 200,
  2 | 45,    
(3 rows)

知道如何解决这个问题吗？

Answer 1

只需使用聚合，例如min() - 它将忽略空值:

select id, 
       min(x) as x, 
       min(y) as y, 
       min(z) as z
from t1
group by id;

这依赖于您的声明“我可以保证相同 id 的行没有相同字段具有非 NULL 值的行” - 否则这当然会返回错误信息.

---

或者，您可以使用 array_agg() 将所有值放入数组中，以防每个 id 可能获得多个值。但是 string_agg() 不会忽略空值，因此您需要为此应用过滤器:

select id, 
       array_agg(x) filter (where x is not null), 
       array_agg(y) filter (where y is not null),
       array_agg(z) filter (where z is not null)
from t1
group by id;