Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Tabibitosan method tutorial by Aketi Jyuuzou

Aketi JyuuzouDec 24 2009 — edited May 22 2011
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

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 13 2010
Added on Dec 24 2009
17 comments
25,644 views