This thread explains
Tabibitosan method B-)
*****************************************************************
1. What is Tabibitosan ? ?:|
"Tabibitosan" is Japanese language.
"Tabibitosan" is one of math problem.
http://www.manabinoba.com/index.cfm/7,757,33,1,html
http://yslibrary.cool.ne.jp/sansub1801.html
I do not know what "Tabibitosan" is called in English.
If you know, please teach me.
In Japanese -> English dictionary,
"Tabibito" means "pilgrim","viator" or "wayfaring man".
Japanese "Tabibitosan" book
http://www.amazon.co.jp/dp/4895243702
*****************************************************************
2. What is basic usage of "Tabibitosan method" ? ?:|
"Tabibitosan method" is very effective when we want to collect Rows which are continued a lot of condition.
**************************
For exsample1 (collect Rows continued Number)
create table Ex1 (NumVal primary key) as
select 1 from dual union
select 2 from dual union
select 3 from dual union
select 5 from dual union
select 6 from dual union
select 7 from dual union
select 10 from dual union
select 11 from dual union
select 12 from dual union
select 20 from dual union
select 21 from dual;
I will derive resultSet which collects Rows continued NumVal.
select min(NumVal),max(NumVal),count(*)
from (select NumVal,
NumVal-Row_Number() over(order by NumVal)
as disTance
from Ex1)
group by disTance
order by min(NumVal);
MIN(NUMVAL) MAX(NUMVAL) COUNT(*)
----------- ----------- ----------
1 3 3
5 7 3
10 12 3
20 21 2
In above solution,
I imagined that there are 2 people called X and A.
X always walks 1m.
(Row_Number() over(order by NumVal))
A always walks more than 1m.
(NumVal)
Then,I derived distance between X and A.
Then, I made group using these distance.
(group by disTance)
**************************
For exsample2 (collect Rows continued Days)
create table Ex2 (DateVal primary key) as
select date '2009-12-10' from dual union
select date '2009-12-11' from dual union
select date '2009-12-12' from dual union
select date '2009-12-16' from dual union
select date '2009-12-17' from dual union
select date '2009-12-20' from dual;
select min(DateVal),max(DateVal),count(*)
from (select DateVal,
DateVal-Row_Number() over(order by DateVal)
as disTance
from Ex2)
group by disTance
order by min(DateVal);
MIN(DATE MAX(DATE COUNT(*)
-------- -------- ----------
09-12-10 09-12-12 3
09-12-16 09-12-17 2
09-12-20 09-12-20 1