sql – 在postgres中的generate_series上加入计数查询,并将Null值检索为“0”

我想得到的是每月来自generate_series的统计数据以及每个月中计算的id的总和.这个SQL适用于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 without time zone);
insert into mytable(id,version,date_from) values

('4084036','1','2006-12-22 22:46:35'),('4084938','2006-12-23 16:19:13'),'2','2006-12-23 16:20:23'),('4084939','2006-12-23 16:29:14'),('4084954','2006-12-23 16:28:28'),('4250653','2007-02-12 21:58:53'),('4250657','2007-03-12 21:58:53')
;

解决方法

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<>小提琴here

在所有噪音,误导性标识符和非常规格式中,实际问题隐藏在此处：

WHERE version = 1

当你正确使用RIGHT [OUTER] JOIN时,你通过添加一个WHERE子句来取消这一努力,该子句需要mytable中的一个不同值 – 有效地将RIGHT JOIN转换为JOIN.

将该子句拉入JOIN条件以使其工作.

我简化了其他一些事情.

有关：

> Generating time series between two dates in PostgreSQL

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!