python - 如何返回缺失数据计数为 0 的行？

Question

几年(2003-2008 年)我的数据(wrt date)分布不均匀。我想查询一组给定的开始和结束日期的数据，按 PostgreSQL 8.3 中任何支持的时间间隔(日、周、月、季度、年)对数据进行分组。 .

问题是一些查询在要求的时间段内给出连续的结果，比如这个:

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。

我之前看过关于 Stack Overflow 的讨论，但它们似乎没有解决我的问题，因为我的分组周期是(日、周、月、季度、年)之一，并由应用程序决定运行时间。因此，我猜像左连接与日历表或序列表这样的方法无济于事。

我目前的解决方案是使用日历模块填补 Python 中的这些空白(在 Turbogears 应用程序中)。

有更好的方法吗？

Answer 1

^{这个问题很老了。但由于其他用户选择它作为新副本的主人，我添加了一个正确的答案。}

正确的解决方案

SELECT *
FROM  (
   SELECT day::date
   FROM   generate_series(timestamp '2007-12-01'
                        , timestamp '2008-12-01'
                        , interval  '1 month') day
   ) d
LEFT   JOIN (
   SELECT date_trunc('month', date_col)::date AS day
        , count(*) AS some_count
   FROM   tbl
   WHERE  date_col >= date '2007-12-01'
   AND    date_col <= date '2008-12-06'
-- AND    ... more conditions
   GROUP  BY 1
   ) t USING (day)
ORDER  BY day;

当然是使用 LEFT JOIN。

generate_series()可以动态生成时间戳表，而且速度非常快。见:

• Generating time series between two dates in PostgreSQL

在加入之前聚合通常会更快。 fiddle 中测试用例的相关答案:

• PostgreSQL - order by an array

将 timestamp 转换为 date (::date) 以获得基本格式。更多使用to_char() .

GROUP BY 1 是引用第一个输出列的语法简写。也可以是 GROUP BY day，但这可能与同名的现有列冲突。或者 GROUP BY date_trunc('month', date_col)::date 但对我来说这太长了。

适用于 date_trunc() 的可用区间参数.

count() never returns NULL - 0 表示没有行 - 但是 LEFT JOIN 有。
要在外部 SELECT 中返回 0 而不是 NULL，请使用 COALESCE(some_count, 0) AS some_count。 The manual.

有关更通用的解决方案或任意时间间隔，请参阅:

• Best way to count rows by arbitrary time intervals