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
目前测出order by c_time 比 order by dbms_random.value 效率高很多感谢两位。
对于问题二,经过多次测试。效率如下:/* 效率最高*/ 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,trunc(c_time,'hh24') order by c_time) 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;/* 效率次之*/ 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;/*不知为什么,这个巨慢*/ 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 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
改一下就是随机
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
目前测出order by c_time 比 order by dbms_random.value 效率高很多感谢两位。
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,trunc(c_time,'hh24') order by c_time) 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;/* 效率次之*/
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;/*不知为什么,这个巨慢*/
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;