联接 generate_series() 上的计数查询，并以'0'的形式检索 Null 值

Question

我想使用generate_series()每月计算ID。此查询适用于PostgreSQL 9.1:

SELECT (to_char(serie,'yyyy-mm')) AS year, sum(amount)::int AS eintraege FROM (
SELECT  
COUNT(mytable.id) as amount,   
generate_series::date as serie   
FROM mytable  

RIGHT JOIN generate_series(     
(SELECT min(date_from) FROM mytable)::date,   
(SELECT max(date_from) FROM mytable)::date,  
interval '1 day') ON generate_series = date(date_from)  
WHERE version = 1   
GROUP BY generate_series       
) AS foo
GROUP BY Year   
ORDER BY Year ASC;  

这是我的输出：

"2006-12" | 4  
"2007-02" | 1  
"2007-03" | 1  

但我想得到的是这个输出(1月份的"0"值)：

"2006-12" | 4  
"2007-01" | 0  
"2007-02" | 1  
"2007-03" | 1  

不过，应列出没有id的月份
有什么解决办法吗？

样本数据：

drop table if exists mytable;
create table mytable(id bigint, version smallint, date_from timestamp);
insert into mytable(id, version, date_from) values
(4084036, 1, '2006-12-22 22:46:35'),
(4084938, 1, '2006-12-23 16:19:13'),
(4084938, 2, '2006-12-23 16:20:23'),
(4084939, 1, '2006-12-23 16:29:14'),
(4084954, 1, '2006-12-23 16:28:28'),
(4250653, 1, '2007-02-12 21:58:53'),
(4250657, 1, '2007-03-12 21:58:53')
;

Answer 1

未经处理、简化和修复，它可能看起来像这样：

SELECT to_char(s.tag,'yyyy-mm') AS monat
, count(t.id) AS eintraege
FROM  (
SELECT generate_series(min(date_from)::date
, max(date_from)::date
, interval '1 day'
)::date AS tag
FROM   mytable t
) s
LEFT   JOIN mytable t ON t.date_from::date = s.tag AND t.version = 1   
GROUP  BY 1
ORDER  BY 1;

db<gt；小提琴这里

在所有的噪音、误导性标识符和非常规格式中，实际问题隐藏在这里：

WHERE version = 1

您正确使用了RIGHT [OUTER] JOIN。但是添加一个需要mytable中现有行的WHERE子句可以有效地将RIGHT [OUTER] JOIN转换为[INNER] JOIN。

将该过滤器移动到JOIN条件下，使其工作。

我在做的时候简化了一些其他的事情。

更好，但

SELECT to_char(mon, 'yyyy-mm') AS monat
, COALESCE(t.ct, 0) AS eintraege
FROM  (
SELECT date_trunc('month', date_from)::date AS mon
, count(*) AS ct
FROM   mytable
WHERE  version = 1     
GROUP  BY 1
) t
RIGHT JOIN (
SELECT generate_series(date_trunc('month', min(date_from))
, max(date_from)
, interval '1 mon')::date
FROM   mytable
) m(mon) USING (mon)
ORDER  BY mon;

db<gt；小提琴这里

先聚合后加入要便宜得多——每月加入一行，而不是每天加入一行。

将GROUP BY和ORDER BY基于date值而不是渲染的text更便宜。

count(*)比count(id)快一点，而在这个查询中是等效的。

当基于CCD_ 16而不是CCD_ 17时，CCD_。参见：

• 在PostgreSQL中生成两个日期之间的时间序列