Oracle查询，如何取连续值的第一条数据的时间

Question

请问大家，按ID分组，按时间倒序，最新一条type值为2时，查找连续为2的数据，当type值不连续为非2时，取2的第一条数据的时间；若最新一条不为2，则不查出该ID，例如下图数据，如何编写sql语句可以使最后的查询结果为红框中数据？谢谢~

Answer 1

思路：1、首先去掉每个well_id最大时间且type = '1'的数据
select * from table where well_id in 
(select b.well_id from table b,  
  (select well_id,max(time) time from table group by well_id) a
where b.type = '2' and b.time = a.time  and b.well_id = a.well_id
)
2、取每个well_id最大时间且type = '1'的数据以及这条数据的rownum
3、上面的rownum-1的数据就是我们所需要的数据

Answer 2

with tab1 as (
select 1 id, 1 type, 11 ord from dual union all
select 1 id, 2 type, 10 ord from dual union all
select 1 id, 2 type, 9 ord from dual union all
select 1 id, 2 type, 8 ord from dual union all
select 1 id, 2 type, 7 ord from dual union all
select 1 id, 1 type, 6 ord from dual union all
select 2 id, 2 type, 10 ord from dual union all
select 2 id, 1 type, 9 ord from dual union all
select 3 id, 2 type, 10 ord from dual union all
select 3 id, 2 type, 9 ord from dual union all
select 3 id, 1 type, 8 ord from dual
)
, tab2 as (
select t1.id,
       t1.type,
       t1.ord,
       row_number() over(partition by t1.id order by t1.ord desc) rn
  from tab1 t1
order by t1.id, ord desc
)
select distinct
       t1.id,
       first_value(t1.ord) over(partition by t1.id order by level desc) time
  from tab2 t1
 start with t1.rn = 1
 connect by prior t1.id = t1.id
     and prior t1.rn + 1 = t1.rn
     and not (t1.type != 2 and prior t1.type = 2) 
;

Answer 3

感谢，取到了非2数据的最大时间，但rownum怎么取，试了一下好像不能做-1操作？

Answer 4

我这里没有oracle数据库，写个sql你参考下，不知道对不对。
1、取rownum：
select t.well_id,t.rn,max(t.time) as time
（
select well_id,type,time,rownum as rn from table where well_id in 
   (select b.well_id from table b,  
     (select well_id,max(time) time from table group by well_id) a
 where b.type = '2' and b.time = a.time  and b.well_id = a.well_id
   )
） t where type = '1'  group by t.well_id,t.rn；
2、再从上面的t表取well_id=t.well_id and and rn=t.rn - 1的数据

Answer 5

with tab1 as (
select 1 well_id, 1 type, 11 time from dual union all
select 1 id, 2 type, 10 ord from dual union all
select 1 id, 2 type, 9 ord from dual union all
select 1 id, 2 type, 8 ord from dual union all
select 1 id, 2 type, 7 ord from dual union all
select 1 id, 1 type, 6 ord from dual union all
select 2 id, 2 type, 10 ord from dual union all
select 2 id, 1 type, 9 ord from dual union all
select 3 id, 2 type, 10 ord from dual union all
select 3 id, 2 type, 9 ord from dual union all
select 3 id, 2 type, 8 ord from dual
)
, tab2 as (
select t1.*,
       sum(decode(t1.type, 1, 0, 1)) over(partition by t1.well_id order by t1.time desc) sm,
       row_number() over(partition by t1.well_id order by t1.time desc) rn
  from tab1 t1
)
, tab3 as (
select t1.*,
       decode(1, first_value(t1.type) over(partition by t1.well_id order by decode(t1.type, 1, 1, 2, 2), t1.rn)
       ,first_value(t1.rn) over(partition by t1.well_id order by decode(t1.type, 1, 1, 2, 2), t1.rn) - 1
       ,max(t1.rn) over(partition by t1.well_id )) fv
  from tab2 t1 
 where t1.sm != 0
 order by t1.well_id, t1.time desc
)
select * from tab3 t1 where t1.rn = fv 
;
;排在所有2前面的1可能不止1个，直接max应该不行吧。如果所有type都是2，这种情况下也不能直接-1 。
关键是多次访问数据表很可能会严重降低效率，应该运用分析函数，尽可能只访问一次数据表。

Answer 6

with tab1 as (
select 1 well_id, 1 type, 11 time from dual union all
select 1 id, 2 type, 10 ord from dual union all
select 1 id, 2 type, 9 ord from dual union all
select 1 id, 2 type, 8 ord from dual union all
select 1 id, 2 type, 7 ord from dual union all
select 1 id, 1 type, 6 ord from dual union all
select 2 id, 2 type, 10 ord from dual union all
select 2 id, 1 type, 9 ord from dual union all
select 3 id, 2 type, 10 ord from dual union all
select 3 id, 2 type, 9 ord from dual union all
select 3 id, 2 type, 8 ord from dual
)
, tab2 as (
select t1.*,
       sum(decode(t1.type, 1, 0, 1)) over(partition by t1.well_id order by t1.time desc) sm,
       row_number() over(partition by t1.well_id order by t1.time desc) rn
  from tab1 t1
)
, tab3 as (
select t1.*,
       decode(1, first_value(t1.type) over(partition by t1.well_id order by decode(t1.type, 1, 1, 2, 2), t1.rn)
       ,first_value(t1.rn) over(partition by t1.well_id order by decode(t1.type, 1, 1, 2, 2), t1.rn) - 1
       ,max(t1.rn) over(partition by t1.well_id )) fv
  from tab2 t1 
 where t1.sm != 0
 order by t1.well_id, t1.time desc
)
select * from tab3 t1 where t1.rn = fv 
;
;排在所有2前面的1可能不止1个，直接max应该不行吧。如果所有type都是2，这种情况下也不能直接-1 。
关键是多次访问数据表很可能会严重降低效率，应该运用分析函数，尽可能只访问一次数据表。你说得对，确实实际项目数据很多，两分钟更新一条，需求是找到当前时间往前的连续为2的最早时间，可能实际项目除了1还有其他值，你的这个sql看起来挺复杂，我明天上班研究一下，谢谢～