postgresql - 在 Redshift 中合并具有非空值的多行

Question

我只想从表中获取包含非空值的一行，可以从以下代码重现:

select
  1 as unique_id,
  'null' as first,
  'second' as second,
  'null' as third
union all
select
  1 as unique_id,
  'first' as first,
  'null' as second,
  'null' as third
union all
select
  1 as unique_id,
  'null' as first,
  'null' as second,
  'third' as third

Answer 1

您提供的代码的输出(假设“null”应该为 NULL)是:

1, 'first', NULL, NULL
1, NULL, 'second', NULL
1, NULL, NULL, 'third'

通过“合并”，我假设您想要如下所示的输出:

1, 'first', 'second', 'third'

这可以通过以下方式完成:

SELECT MAX(first), MAX(second), MAX(third) FROM ... GROUP BY unique_id

当然，这假设每个值在每个 unique_id 中只出现一次。您可以类似地使用其他函数，例如 MIN 或 LISTAGG - 它们只需要排除 NULL 值即可。