4

I've imported data ("Amount" and "Narration") from a spreadsheet into a table and need help with a query to group consecutive records according to their "Narration", for example:

Expected output:

line_no  amount   narration   calc_group <-Not part of table
----------------------------------------
1          10      Reason 1       1
2         -10      Reason 1       1
3           5      Reason 2       2
4           5      Reason 2       2
5         -10      Reason 2       2
6          -8      Reason 1       3
7           8      Reason 1       3
8          11      Reason 1       3
9          99      Reason 3       4
10        -99      Reason 3       4

I've tried some analytical functions:

select   line_no, amount, narration,
         first_value (line_no) over 
         (partition by narration order by line_no) "calc_group"
from     test
order by line_no

But that does not work because the Narration of line 6 to 8 is the same as line 1 and 2.

line_no  amount   narration   calc_group
----------------------------------------
1          10      Reason 1       1
2         -10      Reason 1       1
3           5      Reason 2       3
4           5      Reason 2       3
5         -10      Reason 2       3
6          -8      Reason 1       1
7           8      Reason 1       1
8          11      Reason 1       1
9          99      Reason 3       4
10        -99      Reason 3       4

UPDATE

I've managed to do it using lag analytical function and sequences, not very elegant but it works. There should be a better way, please comment!

create or replace function get_next_test_seq
   return number
as
begin
   return test_seq.nextval;
end get_next_test_seq;

create or replace function get_curr_test_seq
   return number
as
begin
   return test_seq.currval;
end get_curr_test_seq;

update test
set    group_no =
          (with cte1
                as (select   line_no, amount, narration,
                             lag (narration) over (order by line_no) prev_narration, group_no
                    from     test
                    order by line_no),
                cte2
                as (select line_no, amount, narration, group_no,
                           case when prev_narration is null or prev_narration <> narration then get_next_test_seq else get_curr_test_seq end new_group_no
                    from   cte1)
           select new_group_no
           from   cte2
           where  cte2.line_no = test.line_no);

UPDATE 2

I'm satisfied with the better accepted answer. Thanks kordiko!

Kobus Smit
  • 1,775
  • 2
  • 18
  • 30
  • Please provide expected output. What are you trying to accomplish? `Narration` in lines 6 to 8 is the same as in lines 1 and 2 because you are grouping by the value of `Narration` which is the same for those records. – Przemyslaw Kruglej Dec 09 '13 at 01:28
  • The expected outputs are the values for "calc_group" as I've indicated in the first result. I know because of the grouping lines 6 to 8 will have the same value as lines 1 and 2, but I want lines 6 to 8 to have a different unique value. – Kobus Smit Dec 09 '13 at 01:45
  • 1
    Kobus - The query for this logic is not as straightforward. Take a look at one of my questions. http://stackoverflow.com/questions/17824432/oracle-analytic-functions-resetting-a-windowing-clause. You first need to identify "where the narration changes" and then do analytics on top of it. – Rajesh Chamarthi Dec 09 '13 at 01:51
  • Thanks Rajesh, I've used lag to identify "where the narration changes". I've update my question with an ugly but working solution – Kobus Smit Dec 09 '13 at 02:02
  • 1
    If your Update works for you, pls consider posting it as an answer, so someone looking for a solution can find it more easily – Armunin Dec 09 '13 at 08:22
  • It was better for me to wait a day for a better answer, mine was not a good enough answer. – Kobus Smit Dec 10 '13 at 05:35

1 Answers1

6

Try this query:

SELECT line_no,
       amount,
       narration,
       SUM( x ) OVER ( ORDER BY line_no
                       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 
       ) as calc_group
FROM (
  SELECT t.*,
         CASE lag( narration ) OVER (order by line_no ) 
         WHEN narration THEN 0
         ELSE 1 END x
  FROM test t

)
ORDER BY line_no

demo --> http://www.sqlfiddle.com/#!4/6d7aa/9

krokodilko
  • 35,300
  • 7
  • 55
  • 79