PostgreSQL查询按天计数/分组，并显示没有数据的日子 [英] PostgreSQL query to count/group by day and display days with no data

问题描述

我需要创建一个PostgreSQL查询，它返回一天中的

• 
  
• 当天找到的对象
  

即使没有任何物品，每一天都会显示在结果中在那天被发现。 （这已经在之前讨论过了，但我没有能够在我的具体情况下工作。）

首先，我找到了一个查询生成一系列天，我可以join：

  SELECT to_char（date_trunc（'day'，（current_date  -  offs）），'YYYY-MM-DD'） 
 AS date 
 FROM generate_series（0，365，1）
 AS offs 
  

结果为：

 日期
 ------------ 
 2013-03-28 
 2013-03-27 
 2013-03-26 
 2013-03-25 
 ... 
 2012-03-28 
（366行）
  

现在我试图将它加入名为' sharer_emailshare'有一个'created'列：

 表'public.sharer_emailshare'
列|键入
 ------------------- 
 id |整数
创建|带时区的时间戳
 message |文本
到|字符变化（75）
  

以下是最好的 GROUP BY 查询我到目前为止：

  SELECT d.date，count（se.id）FROM（
 select to_char（date_trunc（'day'，（current_date  -  offs）），'YYYY-MM-DD'）
 AS date 
 FROM generate_series（0，365，1）
 AS offs 
）d 
 JOIN sharer_emailshare se 
 ON（d.date = to_char（date_trunc（'day'，se.created），'YYYY-MM-DD'））
 GROUP BY d 。日期; 
  

结果：

  date | count 
 ------------ + ------- 
 2013-03-27 | 11 
 2013-03-24 | 2 
 2013-02-14 | 2 
（3行）
  

预期结果：

  date | count 
 ------------ + ------- 
 2013-03-28 | 0 
 2013-03-27 | 11 
 2013-03-26 | 0 
 2013-03-25 | 0 
 2013-03-24 | 2 
 2013-03-23 | 0 
 ... 
 2012-03-28 | 0 
（366 rows）
  

如果我理解正确，这是因为我使用plain（隐含 INNER ） JOIN ，这是预期的行为，如在postgres文档中讨论。

我已经浏览了数十种StackOverflow解决方案，并且所有带有工作查询的解决方案似乎都是针对MySQL / Oracle / MSSQL的，而且我很难将它们转换为PostgreSQL。

这个人询问这个问题用Postgres找到了他的答案，但是把它放在了一个前一段时间过期的pastebin链接上。

我试着切换到 LEFT OUTER JOIN ， RIGHT JOIN ， RIGHT OUTER JOIN ， CROSS JOIN ，使用 CASE 语句在其他值如果为null， COALESCE 来提供一个默认值等，但是我没有能够以我需要的方式使用它们。

任何帮助表示赞赏！我承诺我会尽快阅读那本庞大的PostgreSQL书籍）

您只需要一个 left outer join 而不是内部连接：

  SELECT d.date，count（se .id）
 FROM（SELECT to_char（date_trunc（'day'，（current_date  -  offs）），'YYYY-MM-DD'）AS date 
 FROM generate_series（0，365，1）AS offs 
）d LEFT OUTER JOIN 
 sharer_emailshare se 
 ON d.date = to_char（date_trunc（'day'，se.created），'YYYY-MM-DD'））
 GROUP BY d.date; 
  

I need to create a PostgreSQL query that returns

• a day
• the number of objects found for that day

It's important that every single day appear in the results, even if no objects were found on that day. (This has been discussed before but I haven't been able to get things working in my specific case.)

First, I found a sql query to generate a range of days, with which I can join:

SELECT to_char(date_trunc('day', (current_date - offs)), 'YYYY-MM-DD')
AS date 
FROM generate_series(0, 365, 1) 
AS offs

Results in:

    date    
------------
 2013-03-28
 2013-03-27
 2013-03-26
 2013-03-25
 ...
 2012-03-28
(366 rows)

Now I'm trying to join that to a table named 'sharer_emailshare' which has a 'created' column:

Table 'public.sharer_emailshare'
column    |   type  
-------------------
id        | integer
created   | timestamp with time zone
message   | text
to        | character varying(75)

Here's the best GROUP BY query I have so far:

SELECT d.date, count(se.id) FROM (
    select to_char(date_trunc('day', (current_date - offs)), 'YYYY-MM-DD')
    AS date 
    FROM generate_series(0, 365, 1) 
    AS offs
    ) d 
JOIN sharer_emailshare se 
ON (d.date=to_char(date_trunc('day', se.created), 'YYYY-MM-DD'))  
GROUP BY d.date;

The results:

    date    | count 
------------+-------
 2013-03-27 |    11
 2013-03-24 |     2
 2013-02-14 |     2
(3 rows)

Desired results:

    date    | count 
------------+-------
 2013-03-28 |     0
 2013-03-27 |    11
 2013-03-26 |     0
 2013-03-25 |     0
 2013-03-24 |     2
 2013-03-23 |     0
 ...
 2012-03-28 |     0
(366 rows)

If I understand correctly this is because I'm using a plain (implied INNER) JOIN, and this is the expected behavior, as discussed in the postgres docs.

I've looked through dozens of StackOverflow solutions, and all the ones with working queries seem specific to MySQL/Oracle/MSSQL and I'm having a hard time translating them to PostgreSQL.

The guy asking this question found his answer, with Postgres, but put it on a pastebin link that expired some time ago.

I've tried to switch to LEFT OUTER JOIN, RIGHT JOIN, RIGHT OUTER JOIN, CROSS JOIN, use a CASE statement to sub in another value if null, COALESCE to provide a default value, etc, but I haven't been able to use them in a way that gets me what I need.

Any assistance is appreciated! And I promise I'll get around to reading that giant PostgreSQL book soon ;)

解决方案

You just need a left outer join instead of an inner join:

SELECT d.date, count(se.id)
FROM (SELECT to_char(date_trunc('day', (current_date - offs)), 'YYYY-MM-DD') AS date 
      FROM generate_series(0, 365, 1) AS offs
     ) d LEFT OUTER JOIN
     sharer_emailshare se 
     ON d.date = to_char(date_trunc('day', se.created), 'YYYY-MM-DD'))  
GROUP BY d.date;

这篇关于PostgreSQL查询按天计数/分组，并显示没有数据的日子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！