sql - 为什么在 postgresql 中指定通配符的子查询名称会导致不同的查询结果？

Question

给定下表定义

-- Table: public.my_table

-- DROP TABLE public.my_table;

CREATE TABLE public.my_table
(
    row_id bigint NOT NULL DEFAULT nextval('my_table_id_seq'::regclass),
    user_id text COLLATE pg_catalog."default" NOT NULL,
    item_id text COLLATE pg_catalog."default" NOT NULL,
    my_timestamp timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT my_table_pkey PRIMARY KEY (row_id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.my_table
    OWNER to my_db;

我试图找出有多少项目有多于一行。

这是我的查询。

select count(k.*) from (select count(item_id), item_id
from my_table as t
group by item_id
having count(item_id) > 1) as k

返回

count1 for item1
count2 for item2
....

这不是我想要的

这是另一个查询

select count(*) from (select count(item_id), item_id
from my_table as t
group by item_id
having count(item_id) > 1) as k

返回的正是我想要的

count

我很好奇为什么会有差异，我认为这两个查询是相同的，一个是显式计算 k 的结果行，一个是隐式的。

有人可以指导我了解原因吗？

谢谢

Answer 1

看起来 COUNT(record) 的行为在版本之间是不同的:

数据:

CREATE TABLE my_table
(
    row_id bigint NOT NULL ,
    user_id text COLLATE pg_catalog."default" NOT NULL,
    item_id text COLLATE pg_catalog."default" NOT NULL,
    my_timestamp timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT my_table_pkey PRIMARY KEY (row_id)
);

INSERT INTO my_table VALUES(1,'a','a'),(2,'a','a'),(3,'b','b'),(4,'b','b')
,(5,'c','c'),(6,'c','c'),(7,'c','c'),(8,'d','d'),(9,'d','d');

查询:

select COUNT(k.*)
from (select count(item_id), item_id
      from my_table as t
      group by item_id
      having count(item_id) > 1) as k

-- PostgreSQL 10
-- count
--2
--2
--2
--3

-- PostgreSQL 11
-- count
-- 4

DBFiddle Demo PostgreSQL 10.4

DBFiddle Demo PostgreSQL 11beta2

---

检查执行计划EXPLAIN(ANALYZE、COSTS、VERBOSE、BUFFERS、FORMAT JSON):

_{使用 http://tatiyants.com/pev/#/plans 生成的计划}

正如我们在第一个示例中看到的，根本没有第二次聚合。

---

我会使用简单的 COUNT(*) 或将 COUNT(k.*) 替换为 COUNT(ROW(k.*)) :

select COUNT(*), COUNT(ROW(k.*)) 
from (select count(item_id), item_id
      from my_table as t
      group by item_id
      having count(item_id) > 1) as k;

-- count count
-- 4     4

DBFiddle Demo