获取每天创建的条目数 [英] Get count of created entries for each day

问题描述

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

Let's say I have a this search query like this:

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

例如，您知道像这样的结果：

As you know then for example I get a result back like this:

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

但是我不知道星期几

如何获得如下所示的搜索结果，包括没有创建任何条目的日子？

How can I get a search result that looks like this, including the days where no entries where created?

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

推荐答案

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 。对于性能而言，它比其他所有方面都更为重要。

  • Use a sargable expression for your WHERE condition, so Postgres can use a plain index on created_at. Far more important for performance than all the rest.

    要涵盖一个星期（包括今天），请从今天开始减去6天，而不是7天。

    To cover a week (including today), subtract 6 days from the start of "today", not 7.

    假定已定义 id 不为空， count（*）与此处的 count（id）相同，但速度稍快。

    Assuming that id is defined NOT NULL, count(*) is identical to count(id) here, but slightly faster.

    A CTE 在这里可能会过大。

    A CTE would be overkill here. It's slower and more verbose.

    先汇总，然后再加入。

    now（）是Postgres实现的略短且更快的实现。标准SQL CURRENT_TIMESTAMP （也可以使用）。

    now() is the slightly shorter and faster Postgres implementation of the standard SQL CURRENT_TIMESTAMP (which you can use as well).

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

    This should be the shortest and fastest query. Test with EXPLAIN ANALYZE.

    相关：

    
    
    • 使用generate_series
    
    
    • PostgreSQL：按分钟查询的运行行数
    
    
    • Selecting sum and running balance for last 18 months with generate_series
    • PostgreSQL: running count of rows for a query 'by minute'

    这篇关于获取每天创建的条目数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！