我需要创建一个返回
- 一天
- 当天找到的物品数量
重要的是,即使那天没有发现任何东西,也要有every single day appear in the results件.(之前已经讨论过这一点,但在我的具体 case 中,我还没能让事情顺利进行.)
首先,我找到了一个sql query to generate a range of days,我可以加入:
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"的表中,该表有一个"created"列:
Table 'public.sharer_emailshare'
column | type
-------------------
id | integer
created | timestamp with time zone
message | text
to | character varying(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;
结果是:
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
,这是预期的行为,如discussed in the postgres docs.
我浏览了几十种StackOverflow解决方案,所有具有有效查询的解决方案似乎都是针对MySQL/Oracle/MSSQL的,我很难将它们转换为PostgreSQL.
问this question的人找到了自己的答案,用Postgres,但把它放在一个pastebin链接上,该链接在一段时间前就过期了.
我试着切换到LEFT OUTER JOIN
、RIGHT JOIN
、RIGHT OUTER JOIN
、CROSS JOIN
,如果为空,用CASE
语句替换另一个值,用COALESCE
语句提供一个默认值,等等,但我一直无法以我需要的方式使用它们.
感谢您的帮助!我保证我很快就会go 读那本巨大的PostgreSQL书籍;)