Oracle query, how to take the first data for continuous value of the time-CodePudding

Excuse me everybody, grouped by ID, in reverse chronological order, a new type of article value is 2, look for continuous data for the 2, when the type value of discontinuity is a 2, 2 of the first data of the time; If the latest article for 2, do not find out the ID, the chart data, for example, how to write a SQL statement to be able to make the final query result for the data in the red box? Thank you ~

CodePudding user response:

Thinking: 1, first remove each well_id maximum time and type='1' data
Select * from table where well_id in
(select b.w ell_id from table b,
(select well_id, Max time (time) from the table group by well_id) a
Where b.t ype='2' and b.t ime=a.t ime and b.w ell_id=aleem walji ell_id
)
2, take each well_id maximum time and type='1' data and the data of rownum
3, the rownum above 1 data is the data we need

CodePudding user response:

 with tab1 as (
Select 1 id, 1 type, 11 word from dual union all 
Select id 1, type 2, 10 word from dual union all 
Select id 1, type 2, 9 word from dual union all 
Select id 1, type 2, 8 word from dual union all 
Select id 1, type 2, 7 word from dual union all 
Select 1 id, type 1, 6 word from dual union all 
Select 2 id, type 2, 10 word from dual union all 
Select 2 id, 1 type, nine word from dual union all 
Select 3 id, type 2, 10 word from dual union all 
Select 3 id, type 2, 9 word from dual union all 
Select 3 id, type 1, 8 word from dual 
) 
Tab2 as (
Select a t1. Id, 
T1. Type, 
T1. Word, 
Row_number () over (partition by t1. Id order by t1. The word desc) rn 
The from tab1 t1 
The order by t1. Id, word desc 
) 
Select distinct 
T1. Id, 
First_value (t1) word) over (partition by t1. The id order by level desc) time 
The from tab2 t1 
Start with t1. Rn=1 
Connect by the prior t1. Id=t1. Id 
And the prior t1. Rn + 1=t1. Rn 
And not (t1) type!=2 and the prior t1. Type=2) 
;

CodePudding user response:

reference 1st floor wildwolv response:

ideas: 1, first remove each well_id maximum time and type='1' data
Select * from table where well_id in
(select b.w ell_id from table b,
(select well_id, Max time (time) from the table group by well_id) a
Where b.t ype='2' and b.t ime=a.t ime and b.w ell_id=aleem walji ell_id
)
2, take each well_id maximum time and type='1' data and the data of rownum
3, the rownum above 1 data is the data we need

Thank you, get the maximum time of 2 data, but how rownum, tried it on as if can't do - 1 the operation?

CodePudding user response:

I don't have the oracle database here, write a SQL reference, you don't know right,
1, take rownum:
The select t.w ell_id, t.r n, Max (t.t ime) as time
(
The select well_id, type, time, rownum as an rn from the table where well_id in
(select b.w ell_id from table b,
(select well_id, Max time (time) from the table group by well_id) a
Where b.t ype='2' and b.t ime=a.t ime and b.w ell_id=aleem walji ell_id
)
) where t type='1' group by t.w ell_id, t.r n;
2, again from the above table t take well_id=t.w ell_id the and the and rn=t.r n - 1 data

CodePudding user response:

reference 4 floor wildwolv response:

I don't have the oracle database here, write a SQL reference, you don't know right,
1, take rownum:
The select t.w ell_id, t.r n, Max (t.t ime) as time
(
The select well_id, type, time, rownum as an rn from the table where well_id in
(select b.w ell_id from table b,
(select well_id, Max time (time) from the table group by well_id) a
Where b.t ype='2' and b.t ime=a.t ime and b.w ell_id=aleem walji ell_id
)
) where t type='1' group by t.w ell_id, t.r n;
2, again from the above table t take well_id=t.w ell_id the and the and rn=t.r n - 1 data

 
With tab1 as (
Select 1 well_id, 1 type, 11 time from dual union all 
Select id 1, type 2, 10 word from dual union all 
Select id 1, type 2, 9 word from dual union all 
Select id 1, type 2, 8 word from dual union all 
Select id 1, type 2, 7 word from dual union all 
Select 1 id, type 1, 6 word from dual union all 
Select 2 id, type 2, 10 word from dual union all 
Select 2 id, 1 type, nine word from dual union all 
Select 3 id, type 2, 10 word from dual union all 
Select 3 id, type 2, 9 word from dual union all 
Select 3 id, type 2, 8 word from dual 
) 
Tab2 as (
Select a t1. *, 
The 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 
The from tab1 t1 
) 
, tab3 as (
Select a 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 (fv partition by t1. Well_id)) 
The from tab2 t1 
Where a t1 sm!=0 
The order by t1. Well_id, t1. Time desc 
) 
Select * from tab3 t1 where t1. Rn=fv 
; 
;

Ahead of all 2 1 May be more than 1, Max should not directly, if all the type is 2, this case have no direct - 1,
The key is multiple access to the data table is likely to be severe lower efficiency, should use analysis function, as far as possible access to only one data table,

CodePudding user response:

nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull