SQL> SQL> with equ_tmp as ( 2 select equ_code, decode(e.ic_status,'0118','0018',e.ic_status) ic_status, 3 decode(e.camera_status,'0119','0019',e.camera_status) camera_status, 4 decode(e.port_status,'0111','0011',e.port_status) port_status,check_time 5 from equ e 6 ) 7 select equ_code,count(ic_status) ic_fault, 8 count(camera_status) camera_fault,count(port_status) port_fault 9 from ( 10 SELECT e.equ_code, 11 nullif(nullif(e.ic_status, 12 lag(e.ic_status) over(PARTITION BY e.equ_code ORDER BY e.check_time)),'0008') ic_status, 13 nullif(nullif(e.camera_status, 14 lag(e.camera_status) over(PARTITION BY e.equ_code ORDER BY e.check_time)),'0009') camera_status, 15 nullif(nullif(e.port_status, 16 lag(e.port_status) over(PARTITION BY e.equ_code ORDER BY e.check_time)),'0010') port_status 17 FROM equ_tmp e) 18 group by equ_code;
[SYS@orcl] SQL>with t1 as( 2 select '0001' a ,'0008' b, '0009'c, '0010' d,'201106250003'e from dual union all 3 select '0001' a ,'0008' b, '0009'c, '0010' d,'201106250006'e from dual union all 4 select '0001' a ,'0018' b, '0009'c, '0010' d,'201106250009'e from dual union all 5 select '0001' a ,'0008' b, '0019'c, '0011' d,'201106250012'e from dual union all 6 select '0001' a ,'0018' b, '0009'c, '0011' d,'201106250015'e from dual union all 7 select '0001' a ,'0008' b, '0009'c, '0011' d,'201106250018'e from dual union all 8 select '0001' a ,'0008' b, '0019'c, '0111' d,'201106250021'e from dual union all 9 select '0001' a ,'0118' b, '0119'c, '0010' d,'201106250024'e from dual 10 ),t2 as(select a, 11 b,lead(b,1,0)over(partition by a order by e) n_b, 12 c,lead(c,1,0)over(partition by a order by e) n_c, 13 d,lead(d,1,0)over(partition by a order by e) n_d 14 from t1 15 )select a,sum(case when b = '0018' and b<> n_b then 1 else 0 end) s_b, 16 sum(case when c = '0019' and c<> n_c then 1 else 0 end) s_c, 17 sum(case when d = '0011' and d<> n_d then 1 else 0 end) s_d 18 from t2 19 group by a;A S_B S_C S_D ---- ---------- ---------- ---------- 0001 2 2 1
EQU_CODE IC_STATUS CAMERA_STATUS PORT_STATUS CHECK_TIME
-------- --------- ------------- ----------- ------------
0001 0008 0009 0010 201106250003
0001 0008 0009 0010 201106250006
0001 0018 0009 0010 201106250009
0001 0008 0019 0011 201106250012
0001 0018 0009 0011 201106250015
0001 0008 0009 0011 201106250018
0001 0008 0019 0111 201106250021
0001 0118 0119 0010 201106250024
8 rows selected
SQL>
SQL> with equ_tmp as (
2 select equ_code, decode(e.ic_status,'0118','0018',e.ic_status) ic_status,
3 decode(e.camera_status,'0119','0019',e.camera_status) camera_status,
4 decode(e.port_status,'0111','0011',e.port_status) port_status,check_time
5 from equ e
6 )
7 select equ_code,count(ic_status) ic_fault,
8 count(camera_status) camera_fault,count(port_status) port_fault
9 from (
10 SELECT e.equ_code,
11 nullif(nullif(e.ic_status,
12 lag(e.ic_status) over(PARTITION BY e.equ_code ORDER BY e.check_time)),'0008') ic_status,
13 nullif(nullif(e.camera_status,
14 lag(e.camera_status) over(PARTITION BY e.equ_code ORDER BY e.check_time)),'0009') camera_status,
15 nullif(nullif(e.port_status,
16 lag(e.port_status) over(PARTITION BY e.equ_code ORDER BY e.check_time)),'0010') port_status
17 FROM equ_tmp e)
18 group by equ_code;
EQU_CODE IC_FAULT CAMERA_FAULT PORT_FAULT
-------- ---------- ------------ ----------
0001 3 2 1
SQL>
[SYS@orcl] SQL>with t1 as(
2 select '0001' a ,'0008' b, '0009'c, '0010' d,'201106250003'e from dual union all
3 select '0001' a ,'0008' b, '0009'c, '0010' d,'201106250006'e from dual union all
4 select '0001' a ,'0018' b, '0009'c, '0010' d,'201106250009'e from dual union all
5 select '0001' a ,'0008' b, '0019'c, '0011' d,'201106250012'e from dual union all
6 select '0001' a ,'0018' b, '0009'c, '0011' d,'201106250015'e from dual union all
7 select '0001' a ,'0008' b, '0009'c, '0011' d,'201106250018'e from dual union all
8 select '0001' a ,'0008' b, '0019'c, '0111' d,'201106250021'e from dual union all
9 select '0001' a ,'0118' b, '0119'c, '0010' d,'201106250024'e from dual
10 ),t2 as(select a,
11 b,lead(b,1,0)over(partition by a order by e) n_b,
12 c,lead(c,1,0)over(partition by a order by e) n_c,
13 d,lead(d,1,0)over(partition by a order by e) n_d
14 from t1
15 )select a,sum(case when b = '0018' and b<> n_b then 1 else 0 end) s_b,
16 sum(case when c = '0019' and c<> n_c then 1 else 0 end) s_c,
17 sum(case when d = '0011' and d<> n_d then 1 else 0 end) s_d
18 from t2
19 group by a;A S_B S_C S_D
---- ---------- ---------- ----------
0001 2 2 1