表结构如下,假如我想统计每个id,在每个整点的十分钟区间内有多少:
十分钟区间逻辑如下:
select upg.name,count(tmp.id) totalNum,tmp.newTime
from(
select t.id, -- ID,状态
to_char(t.Time,'yyyymmddhh24mi') oldTime, -- 原来的时间
to_char(t.Time,'mm-dd日 hh24:')||(substr(to_char(t.Time,'MI'),1,1)::integer+1)*10
as newTime -- 时间段伪列
from tmp.test02 t
order by t.Time asc
) tmp
inner join ods.t_photographer pg on tmp.id::integer=pg.id
inner join ods.t_user upg on upg.id=pg.user_id
group by tmp.newTime,upg.name
order by tmp.newTime asc
五分钟区间逻辑如下:
select
upg.name,
count ( tmp.id ) totalNum,
tmp.newTime
from
(
select t.id,-- ID,状态
to_char( t.Time, 'yyyymmddhh24mi' ) oldTime,-- 原来的时间
case