Postgres-如何返回缺少计数的0行？

Question

我几年（2003-2008年）的数据分布不均（写日期）。我想查询给定开始日期和结束日期的数据，按PostgreSQL
8.3（http://www.postgresql.org/docs中支持的任何间隔（天，周，月，季度，年）将数据分组/8.3/static/functions-datetime.html#FUNCTIONS-
DATETIME-TRUNC
）。

问题在于，某些查询会在要求的时间内连续提供结果，如下所示：

select to_char(date_trunc('month',date), 'YYYY-MM-DD'),count(distinct post_id) 
from some_table where category_id=1 and entity_id = 77  and entity2_id = 115 
and date <= '2008-12-06' and date >= '2007-12-01' group by 
date_trunc('month',date) order by date_trunc('month',date);
          to_char   | count 
        ------------+-------
         2007-12-01 |    64
         2008-01-01 |    31
         2008-02-01 |    14
         2008-03-01 |    21
         2008-04-01 |    28
         2008-05-01 |    44
         2008-06-01 |   100
         2008-07-01 |    72
         2008-08-01 |    91
         2008-09-01 |    92
         2008-10-01 |    79
         2008-11-01 |    65
        (12 rows)

但是其中一些会因为没有数据而错过一些时间间隔，例如：

select to_char(date_trunc('month',date), 'YYYY-MM-DD'),count(distinct post_id) 
from some_table where category_id=1 and entity_id = 75  and entity2_id = 115 
and date <= '2008-12-06' and date >= '2007-12-01' group by 
date_trunc('month',date) order by date_trunc('month',date);

        to_char   | count 
    ------------+-------

     2007-12-01 |     2
     2008-01-01 |     2
     2008-03-01 |     1
     2008-04-01 |     2
     2008-06-01 |     1
     2008-08-01 |     3
     2008-10-01 |     2
    (7 rows)

所需结果集为：

  to_char   | count 
------------+-------
 2007-12-01 |     2
 2008-01-01 |     2
 2008-02-01 |     0
 2008-03-01 |     1
 2008-04-01 |     2
 2008-05-01 |     0
 2008-06-01 |     1
 2008-07-01 |     0
 2008-08-01 |     3
 2008-09-01 |     0
 2008-10-01 |     2
 2008-11-01 |     0
(12 rows)

缺少条目的计数为0。

我已经看到了关于堆栈溢出的较早讨论，但是它们似乎并不能解决我的问题，因为我的分组时间是（天，周，月，季度，年）之一，并且由应用程序决定运行时。因此，我猜想像日历表或序列表的左联接之类的方法将无济于事。

我目前的解决方案是使用日历模块填补Python（在Turbogears App中）的这些空白。

有一个更好的方法吗。

Answer 1

您可以使用以下方法创建去年（例如）的所有前几天的列表

select distinct date_trunc('month', (current_date - offs)) as date 
from generate_series(0,365,28) as offs;
          date
------------------------
 2007-12-01 00:00:00+01
 2008-01-01 00:00:00+01
 2008-02-01 00:00:00+01
 2008-03-01 00:00:00+01
 2008-04-01 00:00:00+02
 2008-05-01 00:00:00+02
 2008-06-01 00:00:00+02
 2008-07-01 00:00:00+02
 2008-08-01 00:00:00+02
 2008-09-01 00:00:00+02
 2008-10-01 00:00:00+02
 2008-11-01 00:00:00+01
 2008-12-01 00:00:00+01

然后，您可以加入该系列。