Oracle 天内按小时分组查询问题
本帖最后由 hao123yao 于 2013-03-08 11:30:56 编辑 表名c_data_01。表结构：
uuid c_id c_time a b c
1 0001 2013-3-6 00:01:00 1 1 1
2 0002 2013-3-6 00:00:00 1 1 1
3 0001 2013-3-6 00:01:30 1 1 1
4 0002 2013-3-6 00:01:35 1 1 1
其中uuid为主键，自增。c_id和c_time可以唯一确定一条数据。c_id大概有1万，c_time为时间格式。每个c_id大概每半分钟就有一条数据，时间无规律。数据量是有点大，已使用分区表（先以c_time天分区，再对c_id进行哈希分区）。a、b、c等为其它不重要字段。
问题一：
给定一个c_id和一个日期，查出该天内每个小时内的一条数据，即共有24条数据，每个小时一条。该条数据可以是小时内的随机数据，也可以是小时内最大时间对应的数据。
下面的语句是查询最大时间的，但速度有点慢。能否不用最大时间或者有没有更好查询的方法？
select *
from c_data_01
where c_id = '0001'
and c_time in
(select max(collect_time)
from c_data_01
where c_id = '0001'
and c_time >=
to_date('2013-3-6 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and c_time<
to_date('2013-3-7 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
group by trunc(c_time , 'hh24'))
直接选取24条数据，速度还好。但又不能保证每小时有一条数据。
select *
from c_data_01
where c_id = '0001'
and c_time >=
to_date('2013-3-6 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and c_time<
to_date('2013-3-7 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and rownum < 25
问题二：
如果要将每个c_id每个小时取出一条数据（无要求、随机什么的都可以）放入另外一张p_data表中。
若用最大时间或随机取数据，同样的问题，效率太低。有没有改进的方法或者其他更好的办法？
/*小时内最大时间*/
insert into p_data
select a1.c_id,a1.c_time,a1.a,a1.b,a1.c
from c_data_01 a1,
(select c_id, max(c_time) c_time
from c_data_01
where c_time >=
to_date('2013-3-6 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and c_time <
to_date('2013-3-7 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
group by c_id) a2
where a1.c_id = a2.c_id
and a1.c_time = a2.c_time;
oracle sql 小时分组查询
/*随机数据*/
insert into p_data
select c_id, c_time, a, b, c
from (select c_id,
c_time,
a,
b,
c,
row_number() over(partition by c_id order by dbms_random.value) as rn
from c_data_01
where c_time >=
to_date('2013-3-6 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and c_time <
to_date('2013-3-7 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
where rn = 1;
[解决办法]
select * from (
select uuid ,
c_id ,
c_time,
,a , b , c ,
row_number() over(partition by c_id,to_char(c_time,'yyyy-mm-dd hh24') order by c_time desc) rn
from c_data_01
where c_id = '0001'
) t where t.rn = 1
[解决办法]
2L差不多了
改一下就是随机
select *
from (select uuid,
c_id,
c_time,,
a,
b,
c,
row_number() over(partition by c_id, to_char(c_time, 'yyyy-mm-dd hh24') order by dbms_random.value desc) rn
from c_data_01
where c_id = '0001') t
where t.rn = 1