PostgreSQL 9.1：如何连接数组中没有重复的行，加入另一个表 [英] PostgreSQL 9.1: How to concatenate rows in array without duplicates, JOIN another table

问题描述

我正在使用PostgreSQL 9.1，需要帮助将多个行连接为一个。我需要在2个表中执行此操作。当我使用两次 array_agg（）函数时，结果得到重复的值。

I am using PostgreSQL 9.1 and need help with concatenating multiple rows in one. I need to do that in 2 tables. When I use two times array_agg() functions I get duplicated values in result.

表：

CREATE TABLE rnp (id int, grp_id int, cabinets varchar(15) );

INSERT INTO rnp VALUES
 (1,'11','cabs1')
,(2,'11','cabs2')
,(3,'11','cabs3')
,(4,'11','cabs4')
,(5,'22','c1')
,(6,'22','c2');

CREATE TABLE ips (id int, grp_id int, address varchar(15));

INSERT INTO ips VALUES
 (1,'11','NY')
,(2,'11','CA')
,(3,'22','DC')
,(4,'22','LA');

SQL：

SELECT DISTINCT

  rnp.grp_id,
  array_to_string(array_agg(rnp.cabinets)OVER (PARTITION BY rnp.grp_id), ',') AS cabinets,
  array_to_string(array_agg(ips.address) OVER (PARTITION BY ips.grp_id), ',') AS addresses


FROM rnp JOIN ips ON rnp.grp_id=ips.grp_id

结果：

GRP_ID  CABINETS                                             ADDRESSES
11  cabs1,cabs1,cabs2,cabs2,cabs3,cabs3,cabs4,cabs4     NY,CA,NY,CA,NY,CA,NY,CA
22  c1,c1,c2,c2                                             DC,LA,DC,LA

我需要的是：

 GRP_ID     CABINETS                 ADDRESSES
    11  cabs1,cabs2,cabs3,cabs4       NY,CA,
    22  c1,c2                         DC,LA

SQLFiddle中的此示例： http://sqlfiddle.com/#!1/4815e/19

This example in SQLFiddle: http://sqlfiddle.com/#!1/4815e/19

如果使用则没有问题一个表-SQLFiddle： http://sqlfiddle.com/#!1/4815e/20

There is no problem if use one table - SQLFiddle: http://sqlfiddle.com/#!1/4815e/20

我想念什么？

推荐答案

不是使用窗口函数和分区，而是使用查询级GROUP，可以这样做吗？ BY并使用DISTINCT子句进行聚合：

Instead of using window functions and patitioning, use a query-level GROUP BY and aggregate with a DISTINCT clause:

SELECT         
  rnp.grp_id,
  array_to_string(array_agg(distinct rnp.cabinets),',') AS cabinets,
  array_to_string(array_agg(distinct ips.address),',')  AS addresses
FROM rnp JOIN ips ON rnp.grp_id=ips.grp_id GROUP BY rnp.grp_id, ips.grp_id;

结果：

 grp_id |        cabinets         | addresses 
--------+-------------------------+-----------
     11 | cabs1,cabs2,cabs3,cabs4 | CA,NY
     22 | c1,c2                   | DC,LA
(2 rows)

此处的关键是不要使用窗口函数并进行分类，则使用查询级 GROUP BY 并使用 DISTINCT 子句进行汇总。

The key here is that instead of using window functions and patitioning, you use a query-level GROUP BY and aggregate with a DISTINCT clause.

这也适用于窗口函数方法，除了PostgreSQL（至少9.1）不支持窗口函数中的 DISTINCT ：

This'd work with the window function approach too, except that PostgreSQL (9.1 at least) doesn't support DISTINCT in window functions:

regress=# SELECT DISTINCT
  rnp.grp_id,
  array_to_string(array_agg(distinct rnp.cabinets)OVER (PARTITION BY rnp.grp_id), ',') AS cabinets,                    
  array_to_string(array_agg(distinct ips.address) OVER (PARTITION BY ips.grp_id), ',') AS addresses
FROM rnp JOIN ips ON rnp.grp_id=ips.grp_id;
ERROR:  DISTINCT is not implemented for window functions
LINE 3:   array_to_string(array_agg(distinct rnp.cabinets)OVER (PART...

这篇关于PostgreSQL 9.1：如何连接数组中没有重复的行，加入另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！