Oracle查询,每一个类别每小时取一条数据
现有数据中,有很多个类别的数据,每个类别每小时有大概有300多条数据。
需求:每个类别中一小时只取一条数据。
表名:table
类别字段名:type
时间字段名:time
select r.*
from
(select row_number() over(partition by type,timeh order by type,timeh ) as rowno,
t.*
from
(select a.timeh ,
b.*
from
(select distinct type,
to_char(time,'yyyy-mm-dd hh24') as timeh
from table
) a
left join table b
on a.type=b.type
and a.timeh =to_char(b.time,'yyyy-mm-dd hh24')
order by b.type
)t
)r
where r.rowno=1