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
https://forums.oracle.com/forums/thread.jspa?threadID=1005478&start=0&tstart=0

*/


-- 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
from
    (
    select   id
            ,lk
            ,id - row_number () over (order by id) sgroup
    from t)
order by id;

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

No comments:

Post a Comment