Calculate consecutive occurrence in a row in Oracle

Question

I have a table like below: https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=9bb88e4b8ac981eb9cfabb13848a79b9

The expected result is below: https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=04932c1f5d6f49349a49aac63f51c78a

Here T1 is last month, T2 is last to last month, T3 is 3 months back from now and so on.

TOTAL_INACTIVITY = Total number of months where the amount is 0

MAX_CONSECUTIVE_INACTIVITY = Maximum number of consecutive months where the amount is zero

RECENT_INACTIVITY = Maximum number of consecutive months starting from the recent month i.e T1 and going backward where the amount is 0. That means if T1 has any value except zero, RECENT_INACTIVITY will be zero. For example, person_id 5555 has RECENT_INACTIVITY = 4 since T1_Amount,T2_Amount,T3_Amount and T4_Amount are all 0.

NB: I have around 10 million records and looking for an optimized solution

Answer 1

You can use CASE expressions:

SELECT t.*,
       CASE t8_amount WHEN 0 THEN 1 ELSE 0 END
       + CASE t7_amount WHEN 0 THEN 1 ELSE 0 END
       + CASE t6_amount WHEN 0 THEN 1 ELSE 0 END
       + CASE t5_amount WHEN 0 THEN 1 ELSE 0 END
       + CASE t4_amount WHEN 0 THEN 1 ELSE 0 END
       + CASE t3_amount WHEN 0 THEN 1 ELSE 0 END
       + CASE t2_amount WHEN 0 THEN 1 ELSE 0 END
       + CASE t1_amount WHEN 0 THEN 1 ELSE 0 END AS total_inactivity,
       CASE
       WHEN (0,0,0,0,0,0,0,0) IN (
         (t1_amount, t2_amount, t3_amount, t4_amount, t5_amount, t6_amount, t7_amount, t8_amount)
       )
       THEN 8
       WHEN (0,0,0,0,0,0,0) IN (
         (t1_amount, t2_amount, t3_amount, t4_amount, t5_amount, t6_amount, t7_amount),
         (t2_amount, t3_amount, t4_amount, t5_amount, t6_amount, t7_amount, t8_amount)
       )
       THEN 7
       WHEN (0,0,0,0,0,0) IN (
         (t1_amount, t2_amount, t3_amount, t4_amount, t5_amount, t6_amount),
         (t2_amount, t3_amount, t4_amount, t5_amount, t6_amount, t7_amount),
         (t3_amount, t4_amount, t5_amount, t6_amount, t7_amount, t8_amount)
       )
       THEN 6
       WHEN (0,0,0,0,0) IN (
         (t1_amount, t2_amount, t3_amount, t4_amount, t5_amount),
         (t2_amount, t3_amount, t4_amount, t5_amount, t6_amount),
         (t3_amount, t4_amount, t5_amount, t6_amount, t7_amount),
         (t4_amount, t5_amount, t6_amount, t7_amount, t8_amount)
       )
       THEN 5
       WHEN (0,0,0,0) IN (
         (t1_amount, t2_amount, t3_amount, t4_amount),
         (t2_amount, t3_amount, t4_amount, t5_amount),
         (t3_amount, t4_amount, t5_amount, t6_amount),
         (t4_amount, t5_amount, t6_amount, t7_amount),
         (t5_amount, t6_amount, t7_amount, t8_amount)
       )
       THEN 4
       WHEN (0,0,0) IN (
         (t1_amount, t2_amount, t3_amount),
         (t2_amount, t3_amount, t4_amount),
         (t3_amount, t4_amount, t5_amount),
         (t4_amount, t5_amount, t6_amount),
         (t5_amount, t6_amount, t7_amount),
         (t6_amount, t7_amount, t8_amount)
       )
       THEN 3
       WHEN (0,0) IN (
         (t1_amount, t2_amount),
         (t2_amount, t3_amount),
         (t3_amount, t4_amount),
         (t4_amount, t5_amount),
         (t5_amount, t6_amount),
         (t6_amount, t7_amount),
         (t7_amount, t8_amount)
       )
       THEN 2
       WHEN (0) IN (
         (t1_amount), (t2_amount), (t3_amount), (t4_amount),
         (t5_amount), (t6_amount), (t7_amount), (t8_amount)
       )
       THEN 1
       ELSE 0
       END AS max_consecutive_inactivity,
       CASE t1_amount WHEN 0 THEN
         1 + CASE t2_amount WHEN 0 THEN
           1 + CASE t3_amount WHEN 0 THEN
             1 + CASE t4_amount WHEN 0 THEN
               1 + CASE t5_amount WHEN 0 THEN
                 1 + CASE t6_amount WHEN 0 THEN
                   1 + CASE t7_amount WHEN 0 THEN
                     1 + CASE t8_amount WHEN 0 THEN
                       1 
                       ELSE 0
                       END
                     ELSE 0
                     END
                   ELSE 0
                   END
                 ELSE 0
                 END
               ELSE 0
               END
             ELSE 0
             END 
           ELSE 0
           END
       ELSE 0
       END AS recent_inactivity
FROM   Tran_rec t;

Which, for the sample data, outputs:

PERSON_ID	T8_AMOUNT	T7_AMOUNT	T6_AMOUNT	T5_AMOUNT	T4_AMOUNT	T3_AMOUNT	T2_AMOUNT	T1_AMOUNT	TOTAL_INACTIVITY	MAX_CONSECUTIVE_INACTIVITY	RECENT_INACTIVITY
1111	0	0	0	20	34	0	89	33	4	3	0
2222	100	120	0	200	340	110	89	0	2	1	1
3333	0	0	0	0	0	0	0	110	7	7	0
4444	80	0	0	0	0	0	0	60	6	6	0
5555	80	70	60	50	0	0	0	0	4	4	4
6666	80	0	0	50	0	40	0	50	4	2	0

db<>fiddle here