Postgres -从多个有序行中选择非空非空值

Question

有很多来自多个来源的数据，我需要根据优先级进行分组，但是这些数据源的数据质量是不同的--它们可能丢失了一些数据。任务是以尽可能完整的方式将数据分组到一个单独的表中。

例如：

create table grouped_data (
  id serial primary key,
  type text,
  a text,
  b text,
  c int
);

create table raw_data (
  id serial primary key,
  type text,
  a text,
  b text,
  c int,
  priority int
);


insert into raw_data
(type, a,       b,         c,   priority)
values
('one', null,    '',        123, 1),
('one', 'foo',   '',        456, 2),
('one', 'bar',   'baz',     789, 3),
('two', null,    'two-b',   11,  3),
('two', '',      '',        33,  2),
('two', null,    'two-bbb', 22,  1);

现在，我需要按type对记录进行分组，按priority排序，获取第一个非空值和非空值，并将其放入grouped_data。在这种情况下，组a one的值将是foo，因为持有该值的行比具有bar的行具有更高的优先级。c应该是123，因为它拥有最高的prio。对于组two，对于每一列，我们都采用非空、非空、优先级最高的数据，如果没有实际数据，则返回到null。

最后，预计grouped_data将包含以下内容：

('one', 'foo', 'baz',     123),
('two', null,  'two-bbb', 22)

我试过分组，分选，合并，交叉连接.唉，我对PostgreSQL的了解还不足以让它发挥作用。我也想避免的一件事是，一个接一个地浏览专栏，因为在现实世界里，只有几十个专栏可以使用.

一个指向我一直在使用的小提琴的链接：http://sqlfiddle.com/#!17/76699/1

UPD：

谢谢大家！奥列克西·坦博夫采夫的解决方案是最快的。在一组与实际情况非常相似的数据(200万条记录，大约30个字段)上，只需20秒就能生成完全相同的数据集，这组数据以前是以编程方式生成的，花费了20分钟。

eshirvana的解决方案在95 s，Steve‘在125 s和Stefanov.sm -308 s(这仍然比编程快！)

(谢谢大家:)

Answer 1

你应该试试这个：

SELECT
       type,
       (array_agg(a ORDER BY priority ASC) FILTER (WHERE a IS NOT NULL AND a != ''))[1] as a,
       (array_agg(b ORDER BY priority ASC) FILTER (WHERE b IS NOT NULL AND b != ''))[1] as b,
       (array_agg(c ORDER BY priority ASC) FILTER (WHERE c IS NOT NULL))[1] as c
FROM raw_data GROUP BY type ORDER BY type;

Answer 2

您可以使用窗口函数first_value。

select distinct 
    type 
  , first_value(a) over (partition by type order by nullif(a,'') is null, priority) as a
  , first_value(b) over (partition by type order by nullif(b,'') is null, priority)  as b
  , first_value(c) over (partition by type order by priority) as c
from raw_data 

Answer 3

select distinct on (type) type, 
  first_value(a) over (partition by type order by (nullif(a, '') is null), priority) a, 
  first_value(b) over (partition by type order by (nullif(b, '') is null), priority) b, 
  first_value(c) over (partition by type order by (c is null), priority) c
from raw_data;