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

问题描述

我需要创建一个返回的 PostgreSQL 查询

I need to create a PostgreSQL query that returns

• 一天
• 当天找到的对象数量

重要的是每一天都出现在结果中，即使当天没有找到任何对象.(这之前已经讨论过，但我一直无法在我的特定情况下使事情发挥作用.)

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.)

首先，我发现了一个 sql 查询生成一系列天，我可以加入:

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

结果:

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

现在我试图将它加入一个名为sharer_emailshare"的表中，该表有一个创建"列:

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)

这是我目前最好的 GROUP BY 查询:

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;

结果:

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

预期结果:

    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)

如果我理解正确，这是因为我使用的是普通的(隐含的 INNER)JOIN，这是预期的行为，如 在 postgres 文档中讨论.

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.

我浏览了数十个 StackOverflow 解决方案，所有具有工作查询的解决方案似乎都特定于 MySQL/Oracle/MSSQL，我很难将它们转换为 PostgreSQL.

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.

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

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

我尝试切换到LEFT OUTER JOIN、RIGHT JOIN、RIGHT OUTER JOIN、CROSS JOIN，如果为空，则使用 CASE 语句子在另一个值中，COALESCE 提供默认值等，但我无法以某种方式使用它们这让我得到了我需要的东西.

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.

感谢任何帮助！我保证我很快就会阅读那本巨大的 PostgreSQL 书 ;)

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屋！