代码之家 › 专栏 › 技术社区 › Peter Kozlovsky

PostgreSQL无法从多个表中聚合数据

postgresql sql

Peter Kozlovsky · 技术社区 · 7 年前

假设我有这个表格:物品,物品照片,物品特征。

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字段中得到一个由4个空元素组成的数组 {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}

所以我不得不使用distinct:

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

这个决定对我来说相当尴尬。情况很复杂,因为我必须在项目特性中添加两个字段:

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

所以我需要从item_特性中聚合5个值,其中2个已经是jsonb,distinct会对性能产生非常负面的影响。还有更优雅的解决方案吗?

1 回复 | 直到 7 年前

Gordon Linoff 7 年前

聚合加入:

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