postgresql 空表计数的意外行为

hec6srdp  于 2023-05-22  发布在  PostgreSQL
关注(0)|答案(5)|浏览(409)

我在PostgreSQL 13数据库中有两个表table_atable_b,它们具有UUID主键列。
table_a有多个条目,而table_b是空的(没有条目)。以下查询返回预期结果,即entry_count_a大于0

SELECT COUNT(DISTINCT ta.uuid) AS entry_count_a FROM table_a ta

但是,以下查询将为两个entry_counts返回0

SELECT COUNT(DISTINCT ta.uuid) AS entry_count_a, COUNT(DISTINCT tb.uuid) AS entry_count_b FROM table_a ta, table_b tb

如何编写查询,使entry_count_a包含正确的(预期的)值> 0,而entry_count_b0
附加问题:为什么Postgres会有这样的行为?

tjjdgumg

tjjdgumg1#

您当前的查询等效于以下查询:

SELECT COUNT(DISTINCT ta.uuid) AS entry_count_a, 
       COUNT(DISTINCT tb.uuid) AS entry_count_b 
FROM       table_a ta
CROSS JOIN table_b tb

当您在两个表之间应用笛卡尔积时,您要乘以它们的基数。你没有得到任何行,因为两个表中有一个的基数为0,因此0*n,对于任何n,总是0。
如果要正确显示这两个计数,可以使用两个子查询,如下所示:

SELECT
    (SELECT COUNT(DISTINCT uuid) FROM table_a) AS entry_count_a,
    (SELECT COUNT(DISTINCT uuid) FROM table_b) AS entry_count_b

检查演示here

v64noz0r

v64noz0r2#

您的查询将产生笛卡尔积表a x表b,因为您没有建立想要将它们关联起来的方式。通常,我们使用WHERE条件或JOIN子句。因此,如果B中有n行x B中有0行,则SQL语句的结果将是0行。在这种情况下,最简单的方法是运行两个单独的查询,每个表一个,或者像这样使用子选择:

SELECT COUNT(DISTINCT ta.uuid) AS entry_count_a, SELECT COUNT(DISTINCT tb.uuid) FROM table_b tb) AS entry_count_b FROM table_a ta
zzlelutf

zzlelutf3#

计数 0 是由于FROM table_a ta, table_b tb中的隐式CROSS JOIN,其中一个空表抵消了另一个。
正如其他答案已经指出的那样,加入是一种错误的方法。(除了手头的副作用,构建Cartesian product对于大表来说非常昂贵。而是运行两个单独的计数。
由于uuid是每个表中的PK,因此使用count(*)

SELECT (SELECT count(*) FROM table_a) AS count_a
     , (SELECT count(*) FROM table_b) AS count_b
;

count(DISTINCT uuid)将是一个很大的浪费,因为根据定义,uuidUNIQUE
count(uuid)仍然是浪费的,因为根据定义,该列也是NOT NULL。Postgres有一个separate, faster implementation for count(*),简单地计算行数。
关于错位联接:

  • Postgres中的[FROM x,y]是什么意思?
  • 两个SQL LEFT JOIN生成错误结果

关于更快的计数(*):

  • 在非嵌套jsonb列上优化GROUP BY + COUNT DISTINCT
  • PostgreSQL:运行查询的行数'by minute'
xurqigkl

xurqigkl4#

我现在使用的解决方法:

SELECT a.count,
       b.count FROM
  (SELECT COUNT(DISTINCT uuid) AS count FROM table_a) a,
  (SELECT COUNT(DISTINCT uuid) AS count FROM table_b) b
7uzetpgm

7uzetpgm5#

使用CROSS JOIN的另一种方法:

with cte_table_a as (
  SELECT COUNT(DISTINCT uuid) AS entry_count_a FROM table_a
),
cte_table_b as (
  SELECT COUNT(DISTINCT uuid) AS entry_count_b FROM table_b
)
select * from cte_table_a
cross join cte_table_b

或者简单地说:

SELECT * 
from (SELECT COUNT(DISTINCT uuid) AS entry_count_a FROM table_a) a
CROSS JOIN (SELECT COUNT(DISTINCT uuid) AS entry_count_b FROM table_b) b

Demo here

相关问题