postgresql 使用 postgres 中的一年记录从数据库中获取每月数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17063401/
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
fetching monthwise data from a database with a year's record in postgres
提问by Deepak
I am having a table with a years data with the following columns:
我有一个包含以下列的年份数据表：
Table "myData" ((
"Status" character varying,
"Project" character varying,
"Product" character varying,
"Identifier" character varying,
"Submittedon" date
)
etc.,
等等。，
Now to fetch a count of records submitted on a particular month. Say like April 2013's Record count, I am using:
现在获取特定月份提交的记录计数。比如说 2013 年 4 月的记录计数，我正在使用：
select count("Status") as April2013
from "myData"
where (
"SubmittedOn" > (current_date - 90)
and "SubmittedOn" < (current_date - 60)
)
Result:
结果：
April2013
--------
62
Now my requirement is to fetch the count of records for the past 6 months. I mean i want my output in any of the below formats:
现在我的要求是获取过去 6 个月的记录数。我的意思是我希望我的输出采用以下任何一种格式：
FORMAT 1:
FORMAT 2:
6MonthsCount
-------------
34
23
44
41
18
9
采纳答案by Clodoaldo Neto
select
date_trunc('month', submittedOn) "month",
count("Status") total
from "myData"
group by 1
order by 1
回答by lathspell
This looks like a "Pivot"-Table so use the crosstab() function of the tablefunc extention (http://www.postgresql.org/docs/current/static/tablefunc.html):
这看起来像一个“数据透视表”，所以使用 tablefunc 扩展的 crosstab() 函数（http://www.postgresql.org/docs/current/static/tablefunc.html）：
CREATE TABLE mydata (status text, submitteton date);
INSERT INTO mydata VALUES ('a', '2013-01-02'), ('b', '2013-01-05'), ('c', '2013-02-09'), ('d', '2013-04-11');
SELECT extract(month from submitteton) as month, count(*) FROM mydata GROUP BY month;
month | count
-------+-------
1 | 2
2 | 1
4 | 1
CREATE EXTENSION tablefunc;
SELECT
*
FROM
crosstab(
'SELECT
extract(year from submitteton)::int as year,
extract(month from submitteton) as month,
count(*)::int
FROM
mydata
GROUP BY 1,2
ORDER BY 1,2',
'SELECT * FROM generate_series(1, 12)'
) as ct(
year int,
jan int, feb int, mar int, apr int, may int, jun int,
jul int, aug int, sep int, oct int, nov int, dec int
)
ORDER BY
year
;
year | jan | feb | mar | apr | may | jun | jul | aug | sep | oct | nov | dec
------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----
2013 | 2 | 1 | | 1 | | | | | | | |
回答by Denis de Bernardy
You ought to use intervals and date_trunc, rather than explicit numbers of days:
您应该使用间隔和 date_trunc，而不是明确的天数：
denis=# select date_trunc('month', now()) as d;
d
------------------------
2013-06-01 00:00:00+02
(1 row)
denis=# select date_trunc('month', now()) - interval '2 months' as d;
d
------------------------
2013-04-01 00:00:00+02
(1 row)
To generate the past 6 months, use generate_series():
要生成过去 6 个月，请使用generate_series()：
denis=# select d as start, d + interval '1 month' as stop
from generate_series(date_trunc('month', now()) - interval '6 month',
date_trunc('month', now()),
'1 month') d;
start | stop
------------------------+------------------------
2012-12-01 00:00:00+01 | 2013-01-01 00:00:00+01
2013-01-01 00:00:00+01 | 2013-02-01 00:00:00+01
2013-02-01 00:00:00+01 | 2013-03-01 00:00:00+01
2013-03-01 00:00:00+01 | 2013-04-01 00:00:00+02
2013-04-01 00:00:00+02 | 2013-05-01 00:00:00+02
2013-05-01 00:00:00+02 | 2013-06-01 00:00:00+02
2013-06-01 00:00:00+02 | 2013-07-01 00:00:00+02
(7 rows)
http://www.postgresql.org/docs/current/static/functions-srf.html
http://www.postgresql.org/docs/current/static/functions-srf.html
From there, a simple join with a count/group by will yield the expected result.
从那里，与计数/组的简单连接将产生预期的结果。
(Note: if you need timestamps without a timezone, replace now()with e.g. (now() at time zone 'utc')::timestamp(0)
（注意：如果您需要没有时区的时间戳，请替换now()为例如(now() at time zone 'utc')::timestamp(0)
