PostgreSQL 按周、月、天 统计问题

为什么80%的码农都做不了架构师？>>>   

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 
 

 

转载于:https://my.oschina.net/jrrx/blog/1377721