合并、合并、合并行

Question

我所做的每一次搜索都会为寻求array_agg的人将一行中的多个列组合成列带来结果。这不是我想要找出的，也许我没有使用正确的搜索词(例如，合并)。

我试图通过在字段中填充值来组合行..。我不知道用什么最好的方法来描述这个问题，只是举个例子：

Current:
--------------------------------
 id  num_1  num_2  num_3  num_4 
--------------------------------
 1    111    222     0      0   
 2    111    333     0      0   
 3    111     0      0     444  
 4     0     222    555     0   
 5    777    999     0      0   
 6     0     999    888     0   

After Processing:
--------------------------------
 id  num_1  num_2  num_3  num_4 
--------------------------------
 1    111    222    555    444  
 2    111    333    555    444  
 3    111    333    555    444  
 4    111    222    555    444  
 5    777    999    888     0   
 6    777    999    888     0   


After Deleting Duplicate Rows:
--------------------------------
 id  num_1  num_2  num_3  num_4 
--------------------------------
 1    111    222    555    444  
 2    111    333    555    444  
 3    777    999    888     0   

这可能是一个2步的过程..。首先填空，然后查找/删除副本。我可以执行第二步，但是很难弄清楚如何首先用另一行的值填充0值，其中可能有两个不同的值( num_2列的id 1/2 )，但num_1只有一个值(例如，111个)。

我可以在PHP中完成它，但是我想知道如何使用Postgres来完成它。

编辑：我的示例表是一个关系表。我有多个具有相似信息的数据集(例如用户名)，但注册ID号不同。因此，我在用户名相同的表1和表2(例如)上做了一个内部连接。然后，我从每个表中获取注册I(这是不同的)，并将其作为一行插入到关系表中。在上面的示例表中，第1行有来自我加入…的两个表的两个不同的注册ID将值111 (num_1)和222 (num_2)插入到表中，并为num_3和num_4插入零。然后比较表1和表4，并将值111 (num_1)和444 (num_4)插入到关系表中，num_2和num_3为零。由于注册ID 111与注册ID 222相关，注册ID 111与注册ID 444相关，那么注册ID 111、222和444都是相关的(这意味着每个注册ID的用户名是相同的)。这有帮助澄清吗？

编辑2: --我更正了表2和表3。希望现在它是有意义的。用户名列不是唯一的。我有四张这样的桌子：

表1:

bob  - 111
mary - 777

表2:

bob  - 222
bob  - 333
mary - 999

表3:

bob  - 555
mary - 888

表4:

bob  - 444  -- mary does not exist in this table

因此，在我的relations表中，我应该以3行结束，如上面的表3所示。

Answer 1

如果您的值总是在增加(如示例中所示)，则只需使用累积最大值，然后使用select distinct

select row_number() over (order by min(id)) as id,
       t.num1, t.num2, t.num3, t.num4
from (select id,
             max(num1) over (order by id) as num1,
             max(num2) over (order by id) as num2,
             max(num3) over (order by id) as num3,
             max(num4) over (order by id) as num4
      from t
     ) t
group by t.num1, t.num2, t.num3, t.num4;

如果max()不能工作，那么您真正想要的是lag( . . . ignore nulls)。还没有这方面的资料。也许最简单的方法是对每一列进行关联子查询：

select row_number() over (order by min(id)) as id,
       t.num1, t.num2, t.num3, t.num4
from (select id,
             (select t2.num1 from t t2 where t2.id <= t.id and t2.num1 <> 0 order by t2.id desc limit 1
             ) as num1,
             (select t2.num2 from t t2 where t2.id <= t.id and t2.num2 <> 0 order by t2.id desc limit 1
             ) as num2,
             (select t2.num3 from t t2 where t2.id <= t.id and t2.num3 <> 0 order by t2.id desc limit 1
             ) as num3,
             (select t2.num4 from t t2 where t2.id <= t.id and t2.num4 <> 0 order by t2.id desc limit 1
             ) as num4
      from t
     ) t
group by t.num1, t.num2, t.num3, t.num4;

即使在中等大小的表上，此版本也不太有效。

更有效的版本更复杂：

select row_number() over (order by id) as id,
       t1.num1, t2.num2, t3.num3, t4.num4
from (select min(id) as id,
      from (select id,
                   max(case when num1 > 0 then id end) over (order by id) as num1_id,
                   max(case when num2 > 0 then id end) over (order by id) as num2_id,
                   max(case when num3 > 0 then id end) over (order by id) as num3_id,
                   max(case when num4 > 0 then id end) over (order by id) as num4_id
            from t
           ) t
      group by num1_id, num2_id, num3_id, num4_id
     ) t left join
     t t1
     on t1.id = t.num1_id left join
     t t2
     on t2.id = t.num2_id left join
     t t3
     on t3.id = t.num3_id left join
     t t4
     on t4.id = t.num4_id left join        
group by t.num1, t.num2, t.num3, t.num4;

编辑：

有点傻。有一种使用first_value()的更简单的方法(不幸的是Postgres不支持它作为聚合函数)：

select row_number() over (order by min(id)) as id,
       num1, num2, num3, num4
from (select id,
             first_value(num1) over (order by (case when num1 is not null then id en) nulls last
                                    ) as num1,
             first_value(num2) over (order by (case when num2 is not null then id end) nulls last
                                    ) as num2,
             first_value(num3) over (order by (case when num3 is not null then id end) nulls last
                                    ) as num3,
             first_value(num4) over (order by (case when num4 is not null then id end) nulls last
                                    ) as num4
      from t
     ) t
group by num1, num2, num3, num4;

Answer 2

似乎你是从一个假定的解决方案中开始的，忘记了提出最初的问题。根据您添加的信息，我提出了一个完全不同的、简单得多的解决方案。你有：

CREATE TABLE table1 (username text, registration_id int);
CREATE TABLE table2 (LIKE table1);
CREATE TABLE table3 (LIKE table1);
CREATE TABLE table4 (LIKE table1);

INSERT INTO table1 VALUES ('bob', 111), ('mary', 777);
INSERT INTO table2 VALUES ('bob', 222), ('bob', 333), ('mary', 999);
INSERT INTO table3 VALUES ('bob', 555), ('mary', 888);
INSERT INTO table4 VALUES ('bob', 444); -- no mary

解决方案

你真正需要的是FULL [OUTER] JOIN.FROM和JOIN手册中的详细信息。

-- CREATE TABLE relations AS
SELECT username
     , t1.registration_id AS reg1
     , t2.registration_id AS reg2
     , t3.registration_id AS reg3
     , t4.registration_id AS reg4
FROM   table1     t1
FULL   JOIN table2 t2 USING (username)
FULL   JOIN table3 t3 USING (username)
FULL   JOIN table4 t4 USING (username)
ORDER  BY username;

就这样。直接产生你想要的结果。

username  reg1  reg2  reg3  reg4
---------------------------------
bob       111   222   555   444
bob       111   333   555   444
mary      777   999   888   (null)

您给定的示例也适用于LEFT JOIN，因为所有缺失的条目都在右边。但这在其他星座中会失败。我在中添加了一些更具启发性的测试用例

SQL Fiddle.

我假设您知道，多个表中的多个条目将产生大量的输出行：

• Two SQL LEFT JOINS produce incorrect result