Script categories

Thursday, 22 September 2011

Analytic functions with Tabibitosan

The requirement for this data;

    4   'pk'
    5,  null  
    6,  null  
    9,  'jk'   
    13, 'jk'   
    14, null

is that a Group identifier be associated with each id,
where the id's form a series.
For example, rows 4, 5 and 6 would form Group 1
whereas 9 would form a group on its own, since
no id precedes it or follows it in a series.
Finally, 13 and 14 would form the final group.

The technique is known as Tabibitosan and has
been well documented by Aketi Jyuuzou in the following thread


-- This is merely an example of the technique;

with t as
select 4 id, 'pk' lk from dual union all
select 5,  null  from dual union all
select 6,  null  from dual union all
select 9,  'jk'   from dual union all
select 13, 'jk'   from dual union all
select 14, null  from dual
select id, lk, 'LKG'|| dense_rank() over (order by sgroup) lkg_grp
    select   id
            ,id - row_number () over (order by id) sgroup
    from t)
order by id;

--- -- -------
  4 pk LKG1
  5    LKG1
  6    LKG1
  9 jk LKG2
 13 jk LKG3
 14    LKG3

1 comment:

  1. I admired those who has able to create a blog as wonderful as this! You are truly a hard working person. Keep up the good work and keep on posting.