在PostgreSQL中连接三个表后对行进行计数 [英] Count rows after joining three tables in PostgreSQL

问题描述

假设我在PostgreSQL中有三个表：

table1 - id1, a_id, updated_by_id
table2 - id2, a_id, updated_by_id
Users  - id, display_name

假设我正在使用使用以下查询的：

select count(t1.id1) from table1 t1 
left join table2 t2 on (t1.a_id=t2.a_id) 
full outer join users u1 t1.updated_by_id=u1.id)
full outer join users u2 t2.updated_by_id=u2.id) 
where u1.id=100;

我得到50作为计数。

而使用：

select count(t1.id1) from table1 t1 
left join table2 t2 on (t1.a_id=t2.a_id) 
full outer join users u1 t1.updated_by_id=u1.id)
full outer join users u2 t2.updated_by_id=u2.id) 
where u2.id=100;

我只得到25作为计数。

我在第二个查询中的错误是什么？我如何才能获得相同的计数？

我的要求是有一个由多个表引用的单个用户表。我想获取完整的用户列表，并从不同的表中获取ID的计数。

但是我单独加入的表会返回正确的计数，但是它们中的睡觉不会返回正确的计数。有人能建议一种方法来修改我的第二个查询以获得正确的计数吗？

推荐答案

若要简化逻辑，请先聚合，然后连接。

猜测缺少的详细信息，此查询将为您提供准确的计数，即所有用户在table1和table2中分别被引用的次数：

SELECT *
FROM   users u
LEFT   JOIN (
   SELECT updated_by_id AS id, count(*) AS t1_ct
   FROM   table1
   GROUP  BY 1
   ) t1 USING (id)
LEFT   JOIN (
   SELECT updated_by_id AS id, count(*) AS t2_ct
   FROM   table2
   GROUP  BY 1
   ) t2 USING (id);

尤其要避免多个1-n关系在连接在一起时相互相乘：

• Two SQL LEFT JOINS produce incorrect result

若要仅检索单个或少数用户，LATERAL联接会更快(Postgres 9.3+)：

SELECT *
FROM   users u
LEFT   JOIN  LATERAL (
   SELECT count(*) AS t1_ct
   FROM   table1
   WHERE  updated_by_id = u.id
   ) ON true
LEFT   JOIN  LATERAL (
   SELECT count(*) AS t2_ct
   FROM   table2
   WHERE  updated_by_id = u.id
   ) ON true
WHERE  u.id = 100;

• What is the difference between LATERAL JOIN and a subquery in PostgreSQL?

解释感知差异

您报告的特定不匹配是由于FULL OUTER JOIN的详细说明：

首先，执行内部联接。然后，对于T1中的每一行， 不满足与T2中的任何行的联接条件，联接的行为 在T2的列中添加了空值。另外，对于T2的每一行， 不满足连接条件，T1中的任何行都是连接的行 在T1的列中添加了空值。

因此，对于缺少匹配的情况，您会在相应的另一端追加空值。count()不计算空值。因此，根据您是在u1.id=100还是u2.id=100上过滤，您可以得到不同的结果。

这只是为了说明，这里不需要FULL JOIN。请改用显示的备选方案。

这篇关于在PostgreSQL中连接三个表后对行进行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！