时间戳

首页 > 解决方案 > 具有多个连接导致重复的 SQL

sql - 具有多个连接导致重复的 SQL

问题描述

我正在尝试使用多个左连接进行此查询，但为与项目 ID 关联的每个费用返回重复的更新和科学家（例如，如果有 5 次费用，则每个更新和科学家返回 5 次）。我试图避免使用多个选择语句，但一直遇到问题。

SELECT
  projects.*,
  coalesce(json_agg(updates ORDER BY update_date DESC) FILTER (WHERE updates.id IS NOT NULL), '[]') AS updates,
  coalesce(json_agg(scientists) FILTER (WHERE scientists.user_id IS NOT NULL), '[]') AS scientists,
  coalesce(SUM(charges.amount), 0) AS donated,
  coalesce(COUNT(charges), 0) AS num_donations
FROM projects
LEFT JOIN updates
ON updates.project_id = projects.id
LEFT JOIN scientists
ON scientists.project_id = projects.id
LEFT JOIN charges
ON charges.project_id = projects.id
WHERE projects.id = '${id}'
GROUP BY projects.id;

预期结果（更改为仅返回 id）：

                  id                  |                   updates                |             scientists             | donated | num_donations 
--------------------------------------+------------------------------------------+------------------------------------+---------+---------------
 17191850-9a03-482f-9afe-7dc6b69974ea | ["0c29417f-0afb-44df-a8cf-24dc5cc7962c"] | ["auth0|5efcfb5f652e5a0019ce2193"] |     155 |             5

实际结果：

                  id                  |                                                                                                 updates                                                                                                  |                                                                                 scientists                                                                                 | donated | num_donations 
--------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------
 17191850-9a03-482f-9afe-7dc6b69974ea | ["0c29417f-0afb-44df-a8cf-24dc5cc7962c", "0c29417f-0afb-44df-a8cf-24dc5cc7962c", "0c29417f-0afb-44df-a8cf-24dc5cc7962c", "0c29417f-0afb-44df-a8cf-24dc5cc7962c", "0c29417f-0afb-44df-a8cf-24dc5cc7962c"] | ["auth0|5efcfb5f652e5a0019ce2193", "auth0|5efcfb5f652e5a0019ce2193", "auth0|5efcfb5f652e5a0019ce2193", "auth0|5efcfb5f652e5a0019ce2193", "auth0|5efcfb5f652e5a0019ce2193"] |     155 |             5

标签： sqljsonpostgresqlcountleft-join

解决方案

如果你有这个：

SELECT p.column, s.column, u.column
FROM 
  p 
  JOIN s ON ...
  JOIN u ON ...

它产生一排

p1, s1, u1

然后你加入另一个表：

SELECT p.column, s.column, u.column, c.column
FROM 
  p 
  JOIN s ON ...
  JOIN u ON ...
  JOIN c ON ...

它突然产生5行..

p1, s1, u1, c1
p1, s1, u1, c2
p1, s1, u1, c3
p1, s1, u1, c4
p1, s1, u1, c5

并且您希望它再次生成一行，但另一列的计数为 5：

p1, s1, u1, 5

然后您需要对重复数据进行分组并添加计数：

SELECT p.column, s.column, u.column, count(*)
FROM 
  p 
  JOIN s ON ...
  JOIN u ON ...
  JOIN c ON ...
GROUP BY p.column, s.column, u.column

您会注意到 GROUP BY 部分只是 SELECT 部分的精确重复，减去计数（聚合列）

数据库将根据 GROUP BY 中指定的键对数据进行分组。p1, s1, u1是一个独特的组合，并与 5 个不同的c1 .. c5值相关联。这种情况下的聚合不适用于 cX 数据（因为它是 count(*)，但它可以——如果我们说：

SELECT p.column, s.column, u.column, min(c.column), max(c.column)

然后数据库将此数据集与包含所有 c 值的存储桶一起制作：

p1, s1, u1, [c1, c2, c3, c4, c5]

并将 MIN 和 MAX 函数分别应用于[c1, c2, c3, c4, c5]铲斗拉动c1和c5

在您的脑海中，习惯于将分组操作视为准备 group by 中列的唯一组合，再加上将所有这些其他数据项放在一个大的无序存储桶中，并且 MAX/MIN/AVG 等函数对存储桶内容进行操作并提取相关数据（可能来自任何行，自然 MIN 和 MAX 可能会来自不同的行）。分组失去了“这个输入行”的概念，因为它准备了一组新的行

在各种数据库中最典型的分组情况下，SELECT *如果您正在分组，您将无法使用 - 您在 SELECT 中列出每一列，然后在 GROUP BY 中再次列出。这可能看起来是多余的（实际上某些数据库允许您跳过提供分组依据），但在高级场景中可以按您选择的不同事物进行分组，因此它仅在简单情况下是多余的

现在，希望您对上述所有内容感到失望。一些数据库的函数不仅是 MIN/MAX 等，而且会将存储桶中的所有结果连接起来。像这样的伪SQL：

SELECT p.column, s.column, u.column, STRING_JOIN(c.column, '|')

可以产生：

p1, s1, u1, c1|c2|c3|c4|c5

string_join 函数旨在使用指定为分隔符的管道字符连接存储桶中的所有内容。

但请记住，我们的原始数据是：

p1, s1, u1, c1
p1, s1, u1, c2
p1, s1, u1, c3
p1, s1, u1, c4
p1, s1, u1, c5

如果我们只对 p.column 进行 GROUP BY，那么数据库会将 p1 作为键和更多桶：

p1, [s1,s1,s1,s1,s1], [u1,u1,u1,u1,u1], [c1,c2,c3,c4,c5]

如果你要 STRING_JOIN 中的每一个，你最终会得到你所要求的：

SELECT p.column, STRING_JOIN(s.column, '|'), STRING_JOIN(u.column, '|'), STRING_JOIN(c.column, '|'), 

p1, s1|s1|s1|s1|s1, u1|u1|u1|u1|u1, c1|c2|c3|c4|c5

数据库中没有任何人工智能会看起来并说“我会在加入之前从 s 和 u 存储桶中删除重复项”，也不应该有。正如我之前提到的，当数据进入存储桶进行聚合时，所有行和排序的概念都会丢失。如果您的数据是：

p1, x1, y1
p1, x2, y2

你分组/加入你可能最终得到

p1, x1|x2, y2|y1

看到 Y 字符串中元素的顺序与 x 相比是倒置的 - 不要依赖“集合中元素的顺序”来推断关于它们最初来自的行的任何信息

那么，您的查询发生了什么？好吧，您只按一列分组并聚合其他列，如上所示，因此您可以看到如何获得非分组列的重复。

如果您继续按所有列分组，那么您将拥有自己的科学家和更新。如果您非常希望将它们作为 JSON，那么（假设这确实是 postgres）您将拥有 to_json 和 row_to_json，它们将提供单个 json 值，但它并没有真正增加单个列还没有给您的东西。Postgres（如果这是 postgres）将允许您 GROUP BY * 让 json 工作：

SELECT p.column, row_to_json(s), row_to_json(u), count(*)
...
GROUP BY p.column, s.*, u.*

s.* 和 u.* 的存在将允许 row_to_json 调用生成描述 S 和 U 的单行 json，并且计数将计算 Cs

推荐阅读