时间戳

首页 > 解决方案 > 具有多列的表中每组的第一个非空值

sql - 具有多列的表中每组的第一个非空值

问题描述

在 SO 上广泛讨论了查找第一个非空值的问题，但由于某种原因，所有解决方案都存在问题，可能是由于我对 PostgreSQL 缺乏经验。我在表中有以下结构：

group           submitted   num1  num2  num3  str1  str2  str3 ...
   32  14:04:21 4.5.2020.      2     1  Null  Null  Null  Null
   32  13:01:23 4.5.2020.   Null     1     0   abc   def  Null
   37  15:01:44 3.2.2020.      2  Null     0  Null  Null   aar
   37  03:04:21 2.1.2020.   Null     1     0   abc   def  Null
   37  23:04:00 1.1.2020.      2  Null     0   trt   def   abg
   ...

上述示例的预期结果是：

group  num1  num2  num3  str1  str2  str3 ...
   32     2     1     0   abc   def  Null
   37     2     1     0   abc   def   aar
...

有 40 多列，性能很重要。我需要每组一行包含及时的最新非空值，或上述（排序）数据集中的第一个非空值。到目前为止我尝试的是：

1）按提交的分组排序，然后我找不到找到第一个非空值的聚合函数。有没有办法以某种方式创建自定义聚合函数？

2）使用具有窗口功能的解决方案：

...
first_value(education) OVER (PARTITION BY CASE ORDER BY submitted WHEN education IS NOT NULL THEN submitted END) as education,
...

非常慢（比使用 MAX 的 groupby 慢数百倍），我觉得因为所有表都有相同的分区，所以不应该对每一列重复。有没有办法对整个表执行分区，然后查找每列的第一个值？

3）一些解决方案提到ROWNUM。这个（以及大多数解决方案）的问题是，在第一次排序和分区之后，不需要遍历列中的所有值。

我希望的那种解决方案是 pandas first的类似物。

标签： sqlpandaspostgresql

解决方案

有一个技巧，就是使用array_agg()和删除空值。那将是：

select groupid,
       (array_remove(array_agg(num1 order by submitted desc), null))[1] as num1,
       (array_remove(array_agg(num2 order by submitted desc), null))[1] as num2,
       . . .
from t
group by groupid;

这是一个 db<>fiddle。

推荐阅读