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

电话记录分组查询

最新推荐文章于 2024-04-03 09:22:45 发布

最新推荐文章于 2024-04-03 09:22:45 发布 · 654 阅读

文章标签：

#PostgreSQL

postgresql 专栏收录该内容

13 篇文章

订阅专栏

本文提供了按不同时间粒度（年、月、日、小时、秒）对电话呼叫记录进行分组统计的SQL查询示例。这些查询展示了如何计算每个时间区间内的总呼叫次数和总通话时长。

--按年分组查看
   select     to_char(to_timestamp(start_time_of_date::bigint), 'YYYY') as d ,  count(cdr_id)  as  total_call,sum (call_duration::integer /60 +1)   as  total_duration  from  cdr 
  where  to_timestamp(start_time_of_date::bigint)  between  '2010-01-01'   and    '2010-12-12'   group by d


--按月分组查看
   select     to_char(to_timestamp(start_time_of_date::bigint), 'YYYY-MM') as d ,  count(cdr_id)  as  total_call,sum (call_duration::integer /60 +1)   as  total_duration  from  cdr 
  where  to_timestamp(start_time_of_date::bigint)  between  '2010-01-01'   and    '2010-12-12'   group by d

--按天分组查看
   select     to_char(to_timestamp(start_time_of_date::bigint), 'YYYY-MM-DD') as d ,  count(cdr_id)  as  total_call,sum (call_duration::integer /60 +1)   as  total_duration  from  cdr 
  where  to_timestamp(start_time_of_date::bigint)  between  '2010-01-01'   and    '2010-12-12'   group by d


--按小时分组查看
     select     to_char(to_timestamp(start_time_of_date::bigint), 'YYYY-MM-DD  HH24 ' ) as d ,  count(cdr_id)  as  total_call,sum (call_duration::integer /60 +1)   as  total_duration  from  cdr 
  where  to_timestamp(start_time_of_date::bigint)  between  '2010-01-01'   and    '2010-12-12'   group by d  order  by  d

--按秒分组查看
     select     to_char(to_timestamp(start_time_of_date::bigint), 'YYYY-MM-DD  HH24:MI:SS ' ) as d ,  count(cdr_id)  as  total_call,sum (call_duration::integer /60 +1)   as  total_duration  from  cdr 
  where  to_timestamp(start_time_of_date::bigint)  between  '2010-01-01'   and    '2010-12-12'   group by d

[size=small][/size]