PostgreSQL无法聚合来自多个表的数据 [英] PostgreSQL can't aggregate data from many tables

问题描述

为简单起见，我将在表中写入最少数量的字段。
假设我有此表：items，item_photos，item_characteristics。

For simplicity, I will write the minimum number of fields in the tables. Suppose I have this tables: 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));

我想汇总一项的所有照片和特征。
首先，我明白了这一点。

And I want to aggregate all the photos and characteristics of one item. For a start, I got this.

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} 中得到了一个包含四个null元素的数组。
所以我不得不使用array_remove：

The first problem here arises in the fact that if there are 4 entries in item_characteristics that relate to one item, and, for example, item_photos did not have entries, I get an array of four null elements in the photos field {null, null, null, null}. So I had to use 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}

Further, if I have 1 photo and 4 characteristics, I get a duplicate of 4 photo entries, for example: {img/test-img-1.png,img/test-img-1.png,img/test-img-1.png,img/test-img-1.png}

所以我不得不使用不同的方法：

So I had to use 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

对我来说，这个决定很尴尬。
我不得不在item_characteristics中再添加2个字段，使情况变得复杂：

The decision is rather awkward as for me. The situation is complicated by the fact that I had to add 2 more fields to item_characteristics:

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

所以我需要汇总5来自item_characteristics的值，其中2已经是jsonb并且与众不同可能会对性能产生非常负面的影响。
还有更优雅的解决方案吗？

And so I need to aggregate already 5 values ​​from item_characteristics, where 2 are already jsonb and distinct can have a very negative impact on performance. Is there any more elegant solution?

推荐答案

在之前加入：

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

这篇关于PostgreSQL无法聚合来自多个表的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！