sql - 在左表中获取没有开销的大树

Question

我的问题更具理论性，它是关于为什么 RDBMS/驱动程序以他们所有的方式返回数据，而不是他们如何找到正确的集合，也不是如何找到它。我对 SQL 非常熟悉，但有一件事总是让我的经济意识不佳。

考虑以下“类”图:

A {
    field1, ..., field9
    b_items = [ b1, ..., bN ]
}

B {
    field1, ..., field6
    c_items = [ c1, ..., cM ]
}    

C {
    field1, field2
}

我们有几个 A 对象，每个 A 对象有很多 B 对象，每个 B 对象有很多 C 对象。 count(A) < count(B) << count(C) .

现在我想使用 RDBMS 来存储它，因为关系很酷，优化器很聪明，所以我几乎可以在几毫秒内得到任何东西，只要有一个好的计划和索引集。

我将跳过表创建代码，这应该是显而易见的，直接进入选择:

SELECT *
FROM A
LEFT JOIN B ON B.a_id = A.id
LEFT JOIN C ON C.b_id = B.id
WHERE whatever

数据库服务器返回所有表中所有列的组合结果集，正确连接到排序树中:

A.f1 .... A.f9  B.f1 .... B.f6  C.f1 C.f2
---------------------------------------------------
   1    1    1     1    1    1     1    1
   1    1    1     1    1    1     2    2
   1    1    1     1    1    1     3    3
   ... more rows...
   1    1    1     1    1    1   999  999
                        ↓
   1    1    1     2    2    2     1    1
   1    1    1     2    2    2     2    2
   ... more rows...
   1    1    1     2    2    2   999  999
   ... lots of rows ...
   1    1    1    99   99   99   999  999
        ↓
   2    2    2 -- oh there it is, A[2]
   ...
   5    5    5  NULL NULL NULL  NULL NULL -- A[5] has no b_items
   ...
   9    9    9 ...

问题是，如果A的列很多，尤其是text、json等重数据，为了匹配+B+C join的每一个product，都要重复几千次。为什么 SQL 服务器至少不在加入组中的第一个行之后向我发送相同 {A,B} 行？理想情况下，我希望看到这样的结果:

[
  {
    <A-fields>,
    B = [
      {
        <B-fields>,
        C = [
          {
            <C-fields>
          },
          ... more C rows
        ]
      },
      ... more B rows
    ]
  },
  ... more A rows
]

这与我实际需要在客户端内存中获取的内容非常相似。我知道我可以进行更多查询以获取更少的数据，例如通过A.id IN (ids...)或存储过程在寄生行上返回空值，但关系模型不是用于一次性访问吗？往返很重，计划者的猜测也很重。并且真实的数据图很少只有 3 步高(考虑 5-10)。那么，为什么不通过单次通过，但又不会造成过多的流量呢？

我可以接受 A 和 B 列中的重复单元格，因为通常不会太多，但也许我缺少一些主流的东西，SQL 和 non-hacky 谷歌对我隐藏了这么多年。

谢谢!

Answer 1

避免重复数据传输的唯一方法是使用聚合函数，如 string_agg () 或 array_agg ()。您还可以使用 jsonb 函数聚合数据。您甚至可以获得单个 json 对象而不是表格数据，例如:

select jsonb_agg(taba)
from (
    select to_jsonb(taba) || jsonb_build_object('tabb', jsonb_agg(tabb)) taba
    from taba
    left join (
        select to_jsonb(tabb) || jsonb_build_object('tabc', jsonb_agg(to_jsonb(tabc))) tabb
        from tabb
        join tabc on tabc.bid = tabb.id
        group by tabb.id
        ) tabb 
    on (tabb->>'aid')::int = taba.id
    group by taba.id
) taba

Complete working example.