PostgreSQL 9.1: как объединить строки в массив без дубликатов, ПРИСОЕДИНЯЙТЕСЬ к другой таблице

Я использую 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?


postgresql sql postgresql-9.1
person lara80    schedule 11.09.2012    source источник
comment
Если бы я мог задать +5 хороший вопрос, я бы это сделал. Указанная версия, тестовые данные, ожидаемые результаты, попытка запроса. Отлично сделано.   -  person Craig Ringer    schedule 11.09.2012

Ответы (1)


arrow_upward
37
arrow_downward

Вместо использования оконных функций и патинирования используйте 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...
person Craig Ringer    schedule 11.09.2012
comment
ДА!!!! БОЛЬШОЙ!!!! Бесконечно благодарен! Я потерял целый день в попытках, а вы дали мне результат за одну минуту! :) - person lara80; 11.09.2012
comment
@ lana80 Иногда вам нужно просто взглянуть свежим взглядом, когда вы весь день смотрите на одну и ту же проблему. Рад помочь. - person Craig Ringer; 11.09.2012