CODEWITHSUNDEEP
sqloracle-databasewindow-functions
nkalis
nkalis

Reputation: 13

Aggregations on Lead & LAG in oracle

This is with respect to oracle Input

CUSTID          FROMDT         ACTIVITY     NEXTDATE
100000914   31/01/2015 14:23:51 Bet         3.999996
100000914   31/01/2015 14:29:07 Bet         3.999996
100000914   31/01/2015 14:32:59 Bet         2
100000914   31/01/2015 14:35:35 Bet         1.999998
100000914   31/01/2015 16:52:32 Settlement  3.999996
100000914   31/01/2015 16:54:39 Settlement  1.999998
100000914   31/01/2015 16:55:04 Settlement  2
100000914   31/01/2015 16:57:00 Settlement  3.999996
100000914   31/01/2015 16:57:10 Bet         3
100000914   31/01/2015 19:21:15 Settlement  3

Result

CUSTID      ACTIVITY    AMOUNT      
100000914   Bet         11.99999    
100000914   Settlement  11.99999    
100000914   Bet          3          
100000914   Settlement   3

Result should have sum of amount for every activity change

Thanks

Upvotes: 0

Views: 71

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You need to identify groups of consecutive values. The rest is then aggregation.

One method is the difference of row numbers approach:

select custid, activity, sum(amount)
from (select t.*,
             (row_number() over (partition by custid order by fromdt) -
              row_number() over (partition by custid, activity order by fromdt)
             ) as grp
      from t
     ) t
group by custid, grp, activity
order by custid, max(fromdt);

Upvotes: 0

nkalis
nkalis

Reputation: 13

select custid, activity, sum(amount)
from (select jg_dig_test.*,
             (row_number() over (partition by custid order by fromdate) - row_number() over (partition by custid, activity order by fromdate)
             ) as grp
      from jg_dig_test
     ) jg_dig_test
group by custid, grp, activity
ORDER BY CUSTID, MAX( FROMDaTe )
;

Upvotes: 0

MT0
MT0

Reputation: 168281

SELECT CUSTID,
       ACTIVITY,
       total - LAG( total, 1, 0 ) OVER ( PARTITION BY CUSTID ORDER BY FROMDT ) AS total
FROM   (
  SELECT CUSTID,
         FROMDT,
         ACTIVITY,
         SUM( NEXTDATE ) OVER ( PARTITION BY CUSTID ORDER BY FROMDT ) AS total,
         CASE ACTIVITY
              WHEN LEAD( ACTIVITY ) OVER ( PARTITION BY CUSTID ORDER BY FROMDT )
              THEN 0
              ELSE 1
              END AS has_changed
  FROM   your_table
)
WHERE  has_changed = 1;

Outputs:

CUSTID    ACTIVITY   TOTAL
--------- ---------- --------
100000914 Bet        11.99999
100000914 Settlement 11.99999
100000914 Bet               3
100000914 Settlement        3

Upvotes: 1

Related Questions