sql - PostgreSQL 无法聚合来自多个表的数据

Question

为简单起见，我将在表中写出最少的字段数。假设我有这个表:items、item_photos、items_characteristics。

create table items (
  id               bigserial primary key,
  title            jsonb                                   not null,
);
create table item_photos (
  id         bigserial primary key,
  path       varchar(1000)                not null,
  item_id    bigint references items (id) not null,
  sort_order smallint                     not null,
  unique (path, item_id)
);
create table items_characteristics (
  item_id                  bigint references items (id),
  characteristic_id        bigint references characteristics (id),
  characteristic_option_id bigint references characteristic_options (id),
  numeric_value            numeric(19, 2),
  primary key (item_id, characteristic_id),
  unique (item_id, characteristic_id, characteristic_option_id));

而且我想聚合一个项目的所有照片和特征。首先，我明白了。

select i.id                                                                              as id,
       i.title                                                                           as title,

       array_agg( ip.path)                                 as photos,

       array_agg(
         array [ico.characteristic_id, ico.characteristic_option_id, ico.numeric_value]) as characteristics_array
FROM items i
       LEFT JOIN item_photos ip on i.id = ip.item_id
       LEFT JOIN items_characteristics ico on ico.item_id = i.id
GROUP BY i.id

这里出现的第一个问题是，如果 item_characteristics 中有 4 个条目与一个项目相关，例如，item_photos 没有条目，我会在 photos 字段中得到一个包含四个空元素的数组 {null, null, null, null}。所以我不得不使用 array_remove:

array_remove(array_agg(ip.path), null)                                   as photos

此外，如果我有 1 张照片和 4 个特征，我会得到 4 张照片条目的副本，例如:{img/test-img-1.png,img/test-img-1.png, img/test-img-1.png,img/test-img-1.png

所以我不得不使用不同的:

array_remove(array_agg(distinct ip.path), null)                                   as photos,

array_agg(distinct
         array [ico.characteristic_id, ico.characteristic_option_id, ico.numeric_value]) as characteristics_array

这个决定对我来说是相当尴尬的。由于我不得不向 item_characteristics 添加 2 个字段，情况变得复杂了:

string_value jsonb, --string value
json_value jsonb --custom value

因此我需要从 item_characteristics 中聚合已经存在的 5 个值，其中 2 个已经是 jsonb 并且 distinct 会对性能产生非常负面的影响。有没有更优雅的解决方案？

Answer 1

聚合之前加入:

SELECT i.id as id, i.title as title, ip.paths, null as photos,
       ico.characteristics_array
FROM items i LEFT JOIN
     (SELECT ip.item_id, array_agg( ip.path) as paths
      FROM item_photos ip
      GROUP BY ip.item_ID
     ) ip 
     ON ip.id = i.item_id LEFT JOIN
     (SELECT ico.item_id,
             array_agg(array [ico.characteristic_id, ico.characteristic_option_id, ico.numeric_value]
                       ) as characteristics_array
      FROM items_characteristics ico
      GROUP BY ico.item_id
     ) ico
     ON ico.item_id = i.id