Connor,
I was able to achieve this using Analytics.
rajesh@ORA10G> select depo,count(grp)
2 from (
3 select depo,seq,point,
4 case when point <> max(case when seq=1
5 then point end) over(partition by depo
6 order by seq) then 1 end grp
7 from t
8 )
9 group by depo
10 /
DEPO COUNT(GRP)
---------- ----------
10 6
20 5
2 rows selected.
rajesh@ORA10G>thought of playing this on Oracle 12c using SQL Pattern matching, Able to get upto this.
drop table t purge;
create table t(point varchar2(10),seq int,depo int);
insert into t values('XYZ',1,10);
insert into t values('1',2,10);
insert into t values('2',3,10);
insert into t values('A001',4,10);
insert into t values('A001',5,10);
insert into t values('V308',6,10);
insert into t values('SKDC',99,10);
insert into t values('XYZ',99,10);
insert into t values('ABC',1,20);
insert into t values('83',2,20);
insert into t values('33',3,20);
insert into t values('B001',4,20);
insert into t values('C001',6,20);
insert into t values('DDoo1',99,20);
insert into t values('ABC',99,20);
commit;
rajesh@ORA12C> select *
2 from t
3 match_recognize(
4 partition by depo
5 order by seq
6 measures
7 match_number() as mno,
8 classifier() as classif,
9 strt.point as x ,
10 down.point as y
11 all rows per match
12 pattern( strt down+)
13 define
14 strt as seq=1 ,
15 down as prev(seq) <= down.seq )
16 /
DEPO SEQ MNO CLASS X Y POINT
---------- ---------- ---------- ----- ---------- ---------- ----------
10 1 1 STRT XYZ XYZ
10 2 1 DOWN XYZ 1 1
10 3 1 DOWN XYZ 2 2
10 4 1 DOWN XYZ A001 A001
10 5 1 DOWN XYZ A001 A001
10 6 1 DOWN XYZ V308 V308
10 99 1 DOWN XYZ SKDC SKDC
10 99 1 DOWN XYZ XYZ XYZ
20 1 1 STRT ABC ABC
20 2 1 DOWN ABC 83 83
20 3 1 DOWN ABC 33 33
20 4 1 DOWN ABC B001 B001
20 6 1 DOWN ABC C001 C001
20 99 1 DOWN ABC DDoo1 DDoo1
20 99 1 DOWN ABC ABC ABC
15 rows selected.
but when added up the CASE statement, it ended up with this error. look like Aggregates cannot be defined on two different row pattern variables( strt, down). but when defined on either single row pattern variable / universal row pattern variable this error doesn't show up.
could you help me to solve this using 12c sql pattern matching?
rajesh@ORA12C> select *
2 from t
3 match_recognize(
4 partition by depo
5 order by seq
6 measures
7 count(case when strt.point <>
8 down.point then 1 end) cnt
9 one row per match
10 pattern( strt down+)
11 define
12 strt as seq=1 ,
13 down as prev(seq) <= down.seq )
14 /
count(case when strt.point <>
*
ERROR at line 7:
ORA-62508: illegal use of aggregates or navigation operators in MATCH_RECOGNIZE clause
rajesh@ORA12C>