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

Question

我有几年（2003-2008）的数据分布不均匀（关于日期）。 我想查询给定的开始和结束日期集的数据，按 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() 可以生成一个表格动态时间戳，而且速度非常快。 请参阅：

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

在您加入之前聚合通常会更快。 小提琴测试用例的相关答案：

• PostgreSQL - 按数组排序

将 timestamp 转换为 date (::date) 以获取基本格式。 有关更多信息，请使用 to_char()。

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

使用 date_trunc( ）。

count() 永远不会返回 NULL- 0 表示没有行 - 但 LEFT JOIN 可以。
要在外部 SELECT 中返回 0 而不是 NULL，请使用 COALESCE(some_count, 0) AS some_count。 手册。

对于 <强>更通用的解决方案或任意时间间隔请参阅：

• 按任意时间间隔计算行数的最佳方法

Answer 2

您可以创建去年（例如）所有第一天的列表，

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

然后您可以加入该系列。

Answer 3

您可以在运行时创建一个临时表并在其上进行左连接。 这似乎是最有道理的。