Finding the Previous Row in a Different Group

View All Scripts Login to Run Script

Script Name Finding the Previous Row in a Different Group
Description This script uses a table of transactions, classed either as credits or debits. It explores three different methods for finding the previous transaction id for the other transaction type. i.e. if the current row is a credit, return the id of the last debit ordered by transaction date. It does this using standard analytics, the model clause and pattern matching (match_recognize)
Area SQL General / Analytics
Contributor Chris Saxon (Oracle)
Created Wednesday January 06, 2016

Statement 1

create table transactions ( 
  trans_id       integer, 
  trans_datetime date, 
  trans_type     varchar2(10) 
    check (trans_type in ('CREDIT', 'DEBIT')), 
  amount         number(10, 2) 
)

Table created.

Statement 2

insert into transactions values (1, date'2015-01-06', 'DEBIT', 100)

1 row(s) inserted.

Statement 3

insert into transactions values (2, date'2015-01-05', 'DEBIT', 200)

1 row(s) inserted.

Statement 4

insert into transactions values (3, date'2015-01-04', 'CREDIT', 50)

1 row(s) inserted.

Statement 5

insert into transactions values (4, date'2015-01-03', 'DEBIT', 99)

1 row(s) inserted.

Statement 6

insert into transactions values (5, date'2015-01-02', 'CREDIT', 200)

1 row(s) inserted.

Statement 7

insert into transactions values (6, date'2015-01-01', 'CREDIT', 40)

1 row(s) inserted.

Statement 8

insert into transactions values (7, date'2015-01-01', 'CREDIT', 90)

1 row(s) inserted.

Statement 9

insert into transactions values (8, date'2015-01-01', 'DEBIT', 80)

1 row(s) inserted.

Statement 10
```
commit
```
```
Statement processed.
```

Statement 11

For this table, the following three statements will generate a prev_id_other_type column. This returns the last trans_id for transactions of the other type.

select * 
from   transactions 
order  by trans_datetime, trans_id


TRANS_ID TRANS_DATETIME TRANS_TYPE AMOUNT
6 01-JAN-15 CREDIT 40
7 01-JAN-15 CREDIT 90
8 01-JAN-15 DEBIT 80
5 02-JAN-15 CREDIT 200
4 03-JAN-15 DEBIT 99
3 04-JAN-15 CREDIT 50
2 05-JAN-15 DEBIT 200
1 06-JAN-15 DEBIT 100

8 rows selected.

TRANS_ID	TRANS_DATETIME	TRANS_TYPE	AMOUNT
6	01-JAN-15	CREDIT	40
7	01-JAN-15	CREDIT	90
8	01-JAN-15	DEBIT	80
5	02-JAN-15	CREDIT	200
4	03-JAN-15	DEBIT	99
3	04-JAN-15	CREDIT	50
2	05-JAN-15	DEBIT	200
1	06-JAN-15	DEBIT	100

Statement 12

Standard Analytics Method

select t.*,  
	   case   
	     when trans_type = 'CREDIT' then  
		   last_value( case when trans_type <> 'CREDIT' then trans_id end ) ignore nulls  
		 	 over( order by trans_datetime, trans_id )  
	     else  
		   last_value( case when trans_type <> 'DEBIT' then trans_id end ) ignore nulls  
			 over( order by trans_datetime, trans_id )  
	   end prev_id_other_trans_type  
from   transactions t  
order  by trans_datetime, trans_id


TRANS_ID TRANS_DATETIME TRANS_TYPE AMOUNT PREV_ID_OTHER_TRANS_TYPE
6 01-JAN-15 CREDIT 40  - 
7 01-JAN-15 CREDIT 90  - 
8 01-JAN-15 DEBIT 80 7
5 02-JAN-15 CREDIT 200 8
4 03-JAN-15 DEBIT 99 5
3 04-JAN-15 CREDIT 50 4
2 05-JAN-15 DEBIT 200 3
1 06-JAN-15 DEBIT 100 3

8 rows selected.

TRANS_ID	TRANS_DATETIME	TRANS_TYPE	AMOUNT	PREV_ID_OTHER_TRANS_TYPE
6	01-JAN-15	CREDIT	40	-
7	01-JAN-15	CREDIT	90	-
8	01-JAN-15	DEBIT	80	7
5	02-JAN-15	CREDIT	200	8
4	03-JAN-15	DEBIT	99	5
3	04-JAN-15	CREDIT	50	4
2	05-JAN-15	DEBIT	200	3
1	06-JAN-15	DEBIT	100	3

Statement 13

The Model Clause Method

select *    
from   transactions t    
model    
  dimension by (     
    trans_type tp,     
    row_number() over (order by trans_datetime, trans_id) rn    
  )    
  measures ( trans_datetime, trans_type, trans_id, 0 prev_id_other_trans_type )    
  rules (    
    prev_id_other_trans_type['CREDIT', any] =   
			max(trans_id) keep (  
			  dense_rank last order by trans_datetime, trans_id  
			) ['DEBIT', rn <= cv()],  
    prev_id_other_trans_type['DEBIT', any] =   
      max(trans_id) keep (  
			  dense_rank last order by trans_datetime, trans_id  
			) ['CREDIT', rn <= cv()]  
  )    
order by trans_datetime, trans_id


TP RN TRANS_DATETIME TRANS_TYPE TRANS_ID PREV_ID_OTHER_TRANS_TYPE
CREDIT 1 01-JAN-15 CREDIT 6  - 
CREDIT 2 01-JAN-15 CREDIT 7  - 
DEBIT 3 01-JAN-15 DEBIT 8 7
CREDIT 4 02-JAN-15 CREDIT 5 8
DEBIT 5 03-JAN-15 DEBIT 4 5
CREDIT 6 04-JAN-15 CREDIT 3 4
DEBIT 7 05-JAN-15 DEBIT 2 3
DEBIT 8 06-JAN-15 DEBIT 1 3

8 rows selected.

TP	RN	TRANS_DATETIME	TRANS_TYPE	TRANS_ID	PREV_ID_OTHER_TRANS_TYPE
CREDIT	1	01-JAN-15	CREDIT	6	-
CREDIT	2	01-JAN-15	CREDIT	7	-
DEBIT	3	01-JAN-15	DEBIT	8	7
CREDIT	4	02-JAN-15	CREDIT	5	8
DEBIT	5	03-JAN-15	DEBIT	4	5
CREDIT	6	04-JAN-15	CREDIT	3	4
DEBIT	7	05-JAN-15	DEBIT	2	3
DEBIT	8	06-JAN-15	DEBIT	1	3

Statement 14

12c Pattern Matching Method

select *   
from transactions  
match_recognize (  
  order by trans_datetime, trans_id  
  measures decode(classifier(), 'D', c.trans_id, d.trans_id) prev_id_other_trans_type  
  all rows per match  
  pattern( (c|d)+ )  
  define c as trans_type = 'CREDIT',  
         d as trans_type = 'DEBIT'   
)  
order  by trans_datetime, trans_id


TRANS_DATETIME TRANS_ID PREV_ID_OTHER_TRANS_TYPE TRANS_TYPE AMOUNT
01-JAN-15 6  - CREDIT 40
01-JAN-15 7  - CREDIT 90
01-JAN-15 8 7 DEBIT 80
02-JAN-15 5 8 CREDIT 200
03-JAN-15 4 5 DEBIT 99
04-JAN-15 3 4 CREDIT 50
05-JAN-15 2 3 DEBIT 200
06-JAN-15 1 3 DEBIT 100

8 rows selected.

TRANS_DATETIME	TRANS_ID	PREV_ID_OTHER_TRANS_TYPE	TRANS_TYPE	AMOUNT
01-JAN-15	6	-	CREDIT	40
01-JAN-15	7	-	CREDIT	90
01-JAN-15	8	7	DEBIT	80
02-JAN-15	5	8	CREDIT	200
03-JAN-15	4	5	DEBIT	99
04-JAN-15	3	4	CREDIT	50
05-JAN-15	2	3	DEBIT	200
06-JAN-15	1	3	DEBIT	100

Statement 15

truncate table transactions

Table truncated.

Statement 16

Loading 10,000 rows in the table for performance analysis.

insert into transactions  
  select rownum trans_id,   
		 date'2015-01-01'+floor(rownum/100)+mod(rownum, 24)/24 trans_datetime,   
		 case when mod(rownum, 7) in (0, 3, 5) then 'DEBIT' else 'CREDIT' end trans_type,  
		 100 amount   
  from   dual  
  connect by level <= 10000

10000 row(s) inserted.

Statement 17
```
commit
```
```
Statement processed.
```

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database