当前位置: 首页 > 面试题库 >

PostgreSQL 9.1:如何连接数组中没有重复的行,加入另一个表

巫马欣嘉
2023-03-14
问题内容

我使用的是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,'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

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

我想念什么?由于JOIN,有可能做到这一点吗?


问题答案:

代替使用窗口函数和分类,可以使用查询级别的GROUP BY并使用DISTINCT子句进行聚合:

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子句的聚合。

这也适用于窗口函数方法,除了PostgreSQL(至少9.1)不支持DISTINCT窗口函数:

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...


 类似资料:
  • 我有一个这样的数据帧 已经服用了12天,60毫克百忧解4年 然后我想把它们连接在一个数组中。问题是可能有一些句子重复,但我仍然想保留所有句子,因此结果将是: 我尝试了这个链接和这个都在r。 我也尝试过这种方法: 但是由于我的数据帧中的一些行是重复的,它只给我其中的一个。在我的例子中,返回我这个: 提前感谢:)

  • 所以我有数百个数据库,我想根据我传递的值来改变使用什么数据库 例如,我有许多公司分支机构有branch_code(000,001,002,003等。高达200)。它们都有相同的表,但只是在不同的数据库中创建的。还假设只有DB Name不同。我的问题是,当有数百个数据库时,我如何访问不同的数据库?我应该动态更改. env文件吗?当用户选择另一个分支时,我应该创建一个改变连接的函数吗?如果是的话,怎么

  • 我有一个项目,其中Rabbit被启动: 然后我做了另一个连接到这个队列的项目。没有doker,我只是使用localhost作为主机和端口号5672。 我想在docker中用数据库运行另一个项目: 现在我无法连接到Rabbit。我尝试使用相同的名称创建不同的网络,但每次我都会遇到相同的错误: 连接: 其中 host 是容器名称 rabbitmq。是否可以执行此操作,或者是否有必要以其他方式将程序放入

  • 如何从另一个数组中删除数组的元素?我有第一个数组[“a”“b”“c”]。以及第二阵列[“a”、“e”]、[“e”、“b”、“c”]、[“a”、“c”]。 如何从第二个数组中删除第一个数组的元素? 结果,得到[["e"],["e"], []].

  • 我有一个映射,它返回一个脱节的数组列表。 我怎么能得到

  • 我有两个数组 我想检查是否包含中的任何项。基于这里的帖子,我有以下代码,它只适用于chrome