获取每天创建的条目数

Question

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

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，因此Postgres可以在上使用一个普通索引created_at。对于性能而言，其重要性远胜于其他所有性能。

• 要涵盖一周（包括今天），请从“今天”开始减去6天，而不是7天。

• 假设id已定义NOT NULL，count(*)与count(id)此处相同，但速度稍快。

• 一个CTE将是矫枉过正这里。它更慢，更冗长。

• 先聚集，然后加入。在这种情况下，速度更快。

• now()是标准SQL CURRENT_TIMESTAMP（也可以使用）的Postgres实现的较短和更快的实现。

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