postgresql 在 postgres 中加入关于 generate_series 的计数查询，并将空值检索为“0”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13413715/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Join a count query on a generate_series in postgres and also retrieve Null-values as "0"
提问by zehpunktbarron
What I want to get is a statistic with each month from a generate_series and the sum of the counted id's in every month. This SQL works in PostgreSQL 9.1:
我想得到的是每个月来自 generate_series 的统计数据以及每个月计数的 id 的总和。此 SQL 在 PostgreSQL 9.1 中有效：
SELECT (to_char(serie,'yyyy-mm')) AS year, sum(amount)::int AS eintraege FROM (
SELECT
COUNT(mytable.id) as amount,
generate_series::date as serie
FROM mytable
RIGHT JOIN generate_series(
(SELECT min(date_from) FROM mytable)::date,
(SELECT max(date_from) FROM mytable)::date,
interval '1 day') ON generate_series = date(date_from)
WHERE version = 1
GROUP BY generate_series
) AS foo
GROUP BY Year
ORDER BY Year ASC;
And this is my output
这是我的输出
"2006-12" | 4
"2007-02" | 1
"2007-03" | 1
But what I want to get is this output ("0" value in January):
但我想得到的是这个输出（一月份的“0”值）：
"2006-12" | 4
"2007-01" | 0
"2007-02" | 1
"2007-03" | 1
So if there is a month with no id it should be listed nevertheless. Any ideas how to solve this?
因此，如果有没有 id 的月份，则应将其列出。任何想法如何解决这个问题？
Here is some sample data:
以下是一些示例数据：
drop table if exists mytable;
create table mytable(id bigint, version smallint, date_from timestamp without time zone);
insert into mytable(id, version, date_from) values
('4084036', '1', '2006-12-22 22:46:35'),
('4084938', '1', '2006-12-23 16:19:13'),
('4084938', '2', '2006-12-23 16:20:23'),
('4084939', '1', '2006-12-23 16:29:14'),
('4084954', '1', '2006-12-23 16:28:28'),
('4250653', '1', '2007-02-12 21:58:53'),
('4250657', '1', '2007-03-12 21:58:53')
;
回答by Erwin Brandstetter
Untangled, simplified and fixed, it might look like this:
解开，简化和固定，它可能看起来像这样：
SELECT to_char(s.tag,'yyyy-mm') AS monat
, count(t.id) AS eintraege
FROM (
SELECT generate_series(min(date_from)::date
, max(date_from)::date
, interval '1 day'
)::date AS tag
FROM mytable t
) s
LEFT JOIN mytable t ON t.date_from::date = s.tag AND t.version = 1
GROUP BY 1
ORDER BY 1;
db<>fiddle here
db<>在这里摆弄
Among all the noise, misleading identifiers and unconventional format the actual problem was hidden here:
在所有的噪音、误导性标识符和非常规格式中，真正的问题隐藏在这里：
WHERE version = 1
While you made correct use of RIGHT [OUTER] JOIN, you voided the effort by adding a WHEREclause that requires a distinct value from mytable- converting the RIGHT JOINto a JOINeffectively.
虽然您正确使用了RIGHT [OUTER] JOIN，但您通过添加一个WHERE需要不同值的子句来使工作无效mytable-有效地将 the 转换RIGHT JOIN为 a JOIN。
Pull the clause down into the JOINcondition to make this work.
将子句下拉到JOIN条件中以使其起作用。
I simplified some other things.
我简化了一些其他的事情。
Related:
有关的：
