Sid Sid - 1 year ago 42
SQL Question

Grouping of Similar data by amount in Oracle

I have a txn table with columns ac_id, txn_amt. It will store the data txn amounts along with account ids. Below is example of data

10 1000
10 1000
10 1010
10 1030
10 5000
10 5010
10 10000
20 32000
20 32200
20 5000

I want to write a query in such a way that all the amounts which are within 10% range of the previous amounts should be grouped together. Output should be something like this:

10 4040 4 1
10 10010 2 2
20 64200 2 3
20 5000 1 4

I tried with LAG function but still clueless. This is the code snippet I tried:

select ac_id, txn_amt, round((((txn_amt - lag(txn_amt, 1) over (partition by ac_id order by ac_id, txn_amt))/txn_amt)*100,2) as amt_diff_pct from txn;

Any clue or help will be highly appreciated.

Answer Source

If by previous you mean "the largest amount less than", then you can do this. You can find where the gaps are (i.e. larger than a 10% difference). Then you can assign a group by counting the number of gaps:

select ac_id, sum(txn_amt) as total_amt, count(*) as total_cnt, grp
from (select t.*,
             sum(case when prev_txn_amt * 1.1 > txn_amt then 0 else 1 end) over
                 (partition by ac_id order by txn_amt) as grp
      from (select t.*,
                   lag(txn_amt) over (partition by ac_id order by txn_amt) as prev_txn_amt
            from txn t
           ) t
     ) t
group by ac_id, grp;