PostgreSQL 按周、月、天 统计问题

PostgreSQL 实现按月按年，按日统计 分组统计：实质是把时间戳格式化成字符串，然后分组统计。

例如：

select
to_char(create_time,'YYYY-MM-DD') as d,
count(id) as total_call
from the_record_table
--where
--create_time between '2010-01-01' and '2010-12-12'
group by d

http://javatophp.iteye.com/blog/768306

---------------------------------------------------------------------------------------------------------

按周统计：这个之能写程序拼sql了或者写存储过程。

愉快的找到7天分界点，http://www.jianshu.com/p/54e851518151

例如:

select
date_series.date,
extract (year from date_series.date) as year,
extract (week from date_series.date) as week
from (
select generate_series(
timestamp '2016-03-07',
timestamp '2016-10-11','1 weeks') as date
) date_series

generate_series的时间递增包含：（前面的数字可以修改）

1 seconds
1 minutes
1 hours
1 days
1 weeks
1 months
1 years
generate_series还有不少用法

怎么拼呢，如下：

select count(case when prevent_rank=1 and risk_rank>=2 and create_time>='2017-05-01 00:00:00' and create_time<'2017-05-08 00:00:00' then 'week1' end) "week0501-0508",count(case when prevent_rank=1 and risk_rank>=2 and create_time>='2017-05-01 00:00:00' and create_time<'2017-05-08 00:00:00' and drug_aspl=1 then 'week1' end) "week0501-0508_drug",sum(case when prevent_rank=1 and risk_rank>=2 and create_time>='2017-05-08 00:00:00' and create_time<'2017-05-15 00:00:00' then 1 else 0 end) "week0508-0515",sum(case when prevent_rank=1 and risk_rank>=2 and create_time>='2017-05-08 00:00:00' and create_time<'2017-05-15 00:00:00' and drug_aspl=1 then 1 else 0 end) "week0508-0515_drug",from record_table

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!