PostgreSQL 实现按月按年,按日统计 分组统计

时间:2021-08-30 22:48:59

参考博客园的文章,暂时在这里整理一下,后期再做补充。


--按年分组查看

   select  to_char(time_field, 'YYYY') as d ,  count(id)  as  total_count,sum (count_field)  as  total_amount from  table_name
  where  time_field  between  start_time  and  end_time group by d

--按月分组查看
   select  to_char(time_field, 'YYYY-MM') as d ,  count(id)  as  total_count,sum (count_field)   as  total_amount from  table_name
  where time_field between  start_time  and  end_time  group by d

--按天分组查看
   select  to_char(time_field, 'YYYY-MM-DD') as d ,  count(id)  as  total_count,sum (count_field)   as  total_amount from  table_name
  where time_field between  start_time  and  end_time  group by d


--按小时分组查看
     select  to_char(time_field, 'YYYY-MM-DD  HH24 ' ) as d ,  count(id)  as  total_count,sum (count_field)   as  total_amount from  table_name
  where time_field  between start_time  and  end_time  group by d  order  by  d

--按秒分组查看
     select  to_char(time_field, 'YYYY-MM-DD  HH24:MI:SS ' ) as d ,  count(id)  as  total_countl,sum (count_field)   as  total_amount from  table_name
  where  time_field  between start_time  and  end_time  group by d