PostgreSQL 9.1：如何连接数组中没有重复项的行,JOIN另一个表

发布时间：2020-12-13 16:41:39 所属栏目：百科来源：网络整理

导读：我使用PostgreSQL 9.1并需要帮助，将多个行连接在一起。我需要在2个表中。当我使用两次array_agg()函数时，我得到结果中的重复值。表： CREATE TABLE rnp (id int,grp_id int,cabinets varchar(15) );INSERT INTO rnp VALUES (1,'11','cabs1'),(2,'cabs2'),

我使用PostgreSQL 9.1并需要帮助，将多个行连接在一起。我需要在2个表中。当我使用两次array_agg()函数时，我得到结果中的重复值。

表：

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

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

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

INSERT INTO ips VALUES
 (1,'NY'),'CA'),'DC'),'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,cabs3,cabs4,cabs4     NY,CA,NY,CA
22  c1,c1,c2,c2                                             DC,LA,DC,LA

我需要的是：

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

这个例子在SQLFiddle：http://sqlfiddle.com/#!1/4815e/19

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

我缺少什么？是否可以这样做，因为JOIN？

使用查询级别GROUP BY并使用DISTINCT子句进行聚合，而不是使用窗口函数和编辑

SELECT         
  rnp.grp_id,array_to_string(array_agg(distinct rnp.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,cabs4 | CA,NY
     22 | c1,c2                   | DC,LA
(2 rows)

这里的关键在于使用查询级别GROUP BY并使用DISTINCT子句进行聚合，而不是使用窗口函数和编辑。

除了PostgreSQL(9.1至少)不支持窗口函数中的DISTINCT之外，这也与窗口函数方法一起使用：

regress=# SELECT DISTINCT
  rnp.grp_id,array_to_string(array_agg(distinct rnp.cabinets)OVER (PARTITION BY rnp.grp_id),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...

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!