sql - 使用复杂的双连接来获取子对象的数量

Question

请注意，我使用的是 postgresql

我有一个organizations 表、一个users 表、一个jobs 表和一个documents 表。我想获得按组织有权访问的文档总数数量排序的组织列表。

organizations
------------
id (pk)
company_name

users
------------
id (pk)
organization_id

jobs
------------
id (pk)
client_id    (id of an organization)
server_id    (id of an organization)
creator_id   (id of a user)

documents
------------
id (pk)
job_id

期望的结果

organizations.id  |  organizations.company_name  |  document_count
85                |  Big Corporation             |  84
905               |  Some other folks            |  65
403               |  ACME, Inc                   |  14

如您所见，组织可以通过 3 种不同的路径连接到文档:

1. organizations.id => jobs.client_id => documents.job_id
2. organizations.id => jobs.server_id => documents.job_id
3. organizations.id => users.organization_id => jobs.creator_id => documents.job_id

但是我想要一个查询来获取每家公司有权访问的所有文档的数量...

我尝试了一些事情......像这样:

SELECT COUNT(documents.id) document_count, organizations.id, organizations.company_name
FROM organizations
INNER JOIN users ON organizations.id = users.organization_id
INNER JOIN jobs ON (
  jobs.client_id = organizations.id OR
  jobs.server_id = organizations.id OR
  jobs.creator_id = users.id
)
INNER JOIN documents ON documents.job_id = jobs.id
GROUP BY organizations.id, organizations.company_name
ORDER BY document_count DESC
LIMIT 10

查询需要一段时间才能运行，但这并不可怕，因为我正在为一次性报告这样做，但结果...不可能是正确的。

第一个列出的组织报告了 129,834 个文档的数量——但这是不可能的，因为 documents 表中只有 32,820 个记录。我觉得它一定是在计算大量的重复项(由于我的一个连接出错？)但我不确定我哪里出错了。

顺序看起来是正确的，因为系统的最大用户显然位于列表的顶部......但该值不知何故膨胀了。

Answer 1

问题是，如果 jobs.client_id = organizations.id 或 jobs.server_id = organizations.id，则没有什么可以过滤您的 INNER JOIN 用户(除了它的 ON 子句)，因此您将获得属于该组织的每个用户的单独记录。换句话说，对于每个组织，您要添加三个值:

• 它的用户总数乘以属于它作为客户的作业的文档总数
• 它的用户总数乘以属于它作为服务器的作业的文档总数
• 如果其用户是创建者，则属于作业的文档总数

解决此问题的一种方法是删除 INNER JOIN users 行，并将其更改为:

  jobs.creator_id = users.id

为此:

  jobs.creator_id IN (SELECT id FROM users WHERE organization_id = organizations.id)

. . .但这可能会表现得很糟糕。在找到性能可接受的查询之前，您可能需要尝试一些事情。