按日期范围分组 [英] Grouping by date range
问题描述
我有一个包含两列,日期和数据的表
我想做一组查询来生成有关数据的统计数据,
,例如月份块和年份块的计数(数据)。什么是最好的
方法来实现这个目标?
dd / mm / yy
date |数据
---------------
01/01/01 | 123
01/01/01 | abc
02/01/01 | def
03/03/01 | hij
有些问题....
年份|伯爵
-------------
01 | 3
我可以看到白天如何分组 - 但我如何将
精度降低到几个月/几年。
谢谢......
---------------------------(播出结束)---------------------------
提示8:解释分析是你的朋友
/>
Mat写道:
我有一个包含两列,日期和数据的表>我想做一组查询来生成数据的统计数据,例如月份块和年块的计数(数据)。什么是最好的方法来实现这个目标?
dd / mm / yy
日期|数据
---------------
01/01/01 | 123
01/01/01 | abc
02/01/01 | def
03/03/01 | hij
一些问题....
年份|伯爵
-------------
01 | 3
我可以看到白天如何分组 - 但我如何将
精度降低到几个月/几年。
SELECT COUNT(*)
来自mytable
GROUP BY date_trunc(''month'',date);
见:
http://www.postgresql.org/docs/7.3/s..DATETIME-TRUNC
了解详情。
希望有所帮助,
Mike Mascari
ma * ****@mascari.com
---------------------------(...结束广播)---------------------------
提示1:订阅和取消订阅命令转到 ma ******* @ postgresql.org
< blockquote> -----开始PGP签名消息-----
哈希:SHA1
我回家你的日期字段有da te型。如果是这样的话:
选择date_part(''year'',date),来自your_table组的count(*)by
date_part(''年'',日期)按date_part(''年'',日期)订购;
月份按date_part添加分组(''月'',日期)
如果你需要处理大量行尝试添加年份列和
月,写入触发器来填充此列,制作索引和事物
应该很快。
date |数据
---------------
01/01/01 | 123
01/01/01 | abc
02/01/01 | def
03/03/01 | hij
我可以看到白天如何分组 - 但我如何将
精度降低到几个月/几年。
- ----开始PGP SIGNATURE -----
版本:GnuPG v1.2.2(GNU / Linux)
iD8DBQE / QdhAV + WKOINIfOYRAhT6AJ42zbMyux2CLLJh1XvAtYBrJhkhNw CfZXH5
AQH6c + qKqwbFZT3yNdTcm5I =
= tmYH
-----结束PGP SIGNATURE -----
---------------------------(广播结束)------------------ ---------
提示7:别忘了增加免费空间地图设置
在星期二,2003-08-19 02:56,Alexander Litvinov写道:----- BEGIN PGP签名消息-----
哈希:SHA1 >
我家的日期字段有日期类型。如果是这样的:
选择date_part(''year'',date),来自your_table组的count(*)by
date_part(''year'',date)order by date_part(''年'',日期);
这里真的需要ORDER BY吗?
月份按date_part添加分组(''月'',日期)
>如果你需要处理大量的行尝试添加年份和/月的列,写入触发器来填充这些列,使索引和事物应该很快。
日期|数据
---------------
01/01/01 | 123
01/01/01 | abc
02/01/01 | def
03/03/01 | hij
我可以看到白天如何分组 - 但我如何将
精度降低到几个月/几年。
< br $> b $ b -
--------------------------------- --------------------------------
Ron Johnson,Jr。 ro *********** @ cox.net
Jefferson,洛杉矶美国
我给你的建议是结婚:如果你找到一个好妻子,
你会很开心;如果没有,你将成为一名哲学家。
苏格拉底
---------------------- -----(广播结束)---------------------------
提示3:如果发布/通过Usenet阅读,请发送适当的
subscribe-nomail命令到 ma ****** *@postgresql.org ,以便您的
消息可以干净地通过邮件列表
Hi,
I have a table with two column, date and data
I would like to do a set of queries to generate statistics on the data,
such as count(data) for month blocks and year blocks. What is the best
way to accomplish this?
dd/mm/yy
date | data
---------------
01/01/01| 123
01/01/01| abc
02/01/01| def
03/03/01| hij
SOME QUERY ....
Year | Count
-------------
01 | 3
I can see how to group by day - but how do i go about decreasing the
precision down to months/years.
Thanks...
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Mat wrote:Hi, I have a table with two column, date and data
I would like to do a set of queries to generate statistics on the data,
such as count(data) for month blocks and year blocks. What is the best
way to accomplish this?
dd/mm/yy
date | data
---------------
01/01/01| 123
01/01/01| abc
02/01/01| def
03/03/01| hij
SOME QUERY ....
Year | Count
-------------
01 | 3
I can see how to group by day - but how do i go about decreasing the
precision down to months/years.
SELECT COUNT(*)
FROM mytable
GROUP BY date_trunc(''month'', date);
See:
http://www.postgresql.org/docs/7.3/s...DATETIME-TRUNC
for details.
Hope that helps,
Mike Mascari
ma*****@mascari.com
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I home your date field have date type. If it is try this:
select date_part(''year'', date), count(*) from your_table group by
date_part(''year'', date) order by date_part(''year'', date);
for month add grouping by date_part(''month'', date)
if you need to handle large number of rows try to add columns with year and
month, write triggers for filling this columns, make indexes and things
should be fast.
date | data
---------------
01/01/01| 123
01/01/01| abc
02/01/01| def
03/03/01| hij
I can see how to group by day - but how do i go about decreasing the
precision down to months/years.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)
iD8DBQE/QdhAV+WKOINIfOYRAhT6AJ42zbMyux2CLLJh1XvAtYBrJhkhNw CfZXH5
AQH6c+qKqwbFZT3yNdTcm5I=
=tmYH
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 7: don''t forget to increase your free space map settings
On Tue, 2003-08-19 at 02:56, Alexander Litvinov wrote:-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I home your date field have date type. If it is try this:
select date_part(''year'', date), count(*) from your_table group by
date_part(''year'', date) order by date_part(''year'', date);
Is the ORDER BY really needed here?
for month add grouping by date_part(''month'', date)
if you need to handle large number of rows try to add columns with year and
month, write triggers for filling this columns, make indexes and things
should be fast.date | data
---------------
01/01/01| 123
01/01/01| abc
02/01/01| def
03/03/01| hij
I can see how to group by day - but how do i go about decreasing the
precision down to months/years.
--
-----------------------------------------------------------------
Ron Johnson, Jr. ro***********@cox.net
Jefferson, LA USA
"My advice to you is to get married: If you find a good wife,
you will be happy; if not, you will become a philosopher."
Socrates
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly
这篇关于按日期范围分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!