在Postgresql上按组计数

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微机电系统，结果如下所示。

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)

文献资料 count(expression)给出

表达式值不为空的输入行数

Answer 2

你需要修改连接并聚合一点-

select ms.status, count(m.status)
from (select unnest(enum_range(null::mem_status))) as ms(status)
left join mems as m
  on ms.status = m.status
group by ms.status;