获取每天创建的条目数

Question

假设我有一个这样的this搜索查询：

SELECT COUNT(id), date(created_at)
FROM entries
WHERE date(created_at) >= date(current_date - interval '1 week')
GROUP BY date(created_at)

如你所知，例如，我得到的结果是这样的：

count | date
  2   |  15.01.2014
  1   |  13.01.2014
  9   |  09.01.2014

但我确实没有得到一周中没有创建任何条目的日子。

如何获得这样的搜索结果，包括没有创建条目的天数？

count | date
  2   |  15.01.2014
  0   |  14.01.2014
  1   |  13.01.2014
  0   |  12.01.2014
  0   |  11.01.2014
  0   |  10.01.2014
  9   |  09.01.2014

Answer 1

SELECT day, COALESCE(ct, 0) AS ct
FROM  (SELECT now()::date - d AS day FROM generate_series (0, 6) d) d  -- 6, not 7
LEFT   JOIN (
   SELECT created_at::date AS day, count(*) AS ct 
   FROM   entries
   WHERE  created_at >= date_trunc('day', now()) - interval '6d'
   GROUP  BY 1
   ) e USING (day);

为WHERE条件使用一个sargable表达式，这样Postgres就可以在created_at上使用一个普通索引。对于性能而言，远比其他任何东西都重要

为了覆盖一周（包括今天）；今天"；，而不是7。或者，将一周移动1以结束"1"；昨天"；，作为"；今天"；显然是不完整的。

假设id定义为NOT NULL，则count(*)与count(id)相同，但速度稍快。参见：

• 为什么计数（x.）比计数（）慢

对于简单的情况，不需要CTE。会更慢、更冗长。

先聚合，后连接。那更快。

now()是Postgres对标准SQL CURRENT_TIMESTAMP的简短语法（您也可以使用它）。参见：

• now（）和current_timestamp之间的差异

这应该是最短、最快的查询。用EXPLAIN ANALYZE测试。

相关：

• 使用generate_series选择过去18个月的总和和运行余额
• PostgreSQL：运行查询的行数'；按分钟'

Answer 2

尝试此查询：

with a as (select current_date - n as dt from generate_series(0, 6) as t(n)),
     b as (select count(id) cnt, date(created_at) created_at
           from entries
           where date(created_at) >= date(current_date - interval '1 week')
           group by date(created_at))
select coalesce(b.cnt,0), a.dt
from a
left join b on (a.dt = b.created_at)
order by a.dt;

count函数不会为不存在的行生成0。因此，您必须填写缺少日期的行。使用generate_series和简单的日期运算，您可以为某个时期（在本例中为1周）的日期生成行。然后您可以进行外部联接以生成最终结果。CCD_ 12将用CCD_ 13代替CCD_。

Answer 3

您需要告诉SQL处理NULL。如果NULL ，则返回0

您可以通过COALESCE 进行此操作

http://www.postgresql.org/docs/devel/static/functions-conditional.html

Answer 4

使用generate_series()创建所需日期并加入此日期列表：

SELECT  COUNT(id), 
    date(gdate)
FROM entries
    RIGHT JOIN generate_series(current_date - interval '1 week', current_date, '1 day') g(gdate) 
    ON date(created_at) = date(gdate) AND date(created_at) >= date(current_date - interval '1 week')
GROUP BY 
    date(gdate)
ORDER BY
    date(gdate) DESC;