postgresql - 在 Postgresql 上使用 group by 计数

Question

我有一个 postgresql 类型和一个表

CREATE TYPE mem_status AS ENUM('waiting', 'active', 'expired');

CREATE TABLE mems (
  id BIGSERIAL PRIMARY KEY,
  status mem_status NOT NULL
);

数据集

INSERT INTO mems(id, status) VALUES
  (1, 'active'), (2, 'active'), (3, 'expired');

我想查询按状态分组的计数。所以我尝试了下面的查询。

WITH mem_statuses AS (
  SELECT unnest(enum_range(NULL::mem_status)) AS status
)
SELECT m.status, count(1)
  FROM mems m
  RIGHT JOIN mem_statuses ms ON ms.status = m.status
  GROUP BY m.status;

但是如果没有waiting mems，结果如下所示。

status     |  count  
================
NULL       |  1      <- problem
'active'   |  2
'expired'  |  1

我想得到这样的结果。

status     |  count  
================
'waiting'  |  0
'active'   |  2
'expired'  |  1

我该怎么做？

Answer 1

使用count(id):

WITH mem_statuses AS (
  SELECT unnest(enum_range(NULL::mem_status)) AS status
)
SELECT ms.status, count(id)
  FROM mems m
  RIGHT JOIN mem_statuses ms ON ms.status = m.status
  GROUP BY ms.status;

或:

select status, count(id)
from unnest(enum_range(null::mem_status)) as status
left join mems using(status)
group by status


 status  | count 
---------+-------
 waiting |     0
 active  |     2
 expired |     1
(3 rows)

根据 the documentation count(expression) 给出

number of input rows for which the value of expression is not null