Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 109 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Tabibitosan method tutorial by Aketi Jyuuzou

Aketi Jyuuzou
Member Posts: 1,072 Bronze Badge
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)
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)
*****************************************************************
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 2In 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
-
**************************
For exsample3 (collect Rows continued Months)create table Ex3 (DateVal primary key) as select date '2009-09-01' from dual union select date '2009-10-01' from dual union select date '2009-12-01' from dual union select date '2010-01-01' from dual union select date '2010-02-01' from dual union select date '2010-04-01' from dual; select min(DateVal),max(DateVal),count(*) from (select DateVal, extract(year from DateVal)*12 +extract(month from DateVal) -Row_Number() over(order by DateVal) as disTance from Ex3) group by disTance order by min(DateVal); MIN(DATE MAX(DATE COUNT(*) -------- -------- ---------- 09-09-01 09-10-01 2 09-12-01 10-02-01 3 10-04-01 10-04-01 1
*****************************************************************
3. What is complex usage of "Tabibitosan method" ? ?:|
**************************
For exsample4 (collect Rows continued and ID is equal)
from 450745create table Ex4 (ID,Val,SortKey) as select 1, 5, 1 from dual union all select 1,10, 2 from dual union all select 2, 2, 3 from dual union all select 2, 5, 4 from dual union all select 1,15, 5 from dual union all select 3,25, 6 from dual union all select 3,10, 7 from dual union all select 3, 5, 8 from dual union all select 3,15, 9 from dual union all select 4, 5,10 from dual; select ID,min(Val),max(Val),count(*) from (select ID,Val,SortKey, Row_Number() over(order by SortKey) -Row_Number() over(partition by ID order by SortKey) as disTance from Ex4) group by ID,disTance order by min(SortKey); ID MIN(VAL) MAX(VAL) COUNT(*) -- -------- -------- -------- 1 5 10 2 2 2 5 2 1 15 15 1 3 5 25 4 4 5 5 1
In above solution,
I imagined that there are 5 people called X,A,B,C, and D.
X always walks 1m. (Row_Number() over(order by SortKey))
When ID = 1, A walks 1m. (Row_Number() over(partition by ID order by SortKey))
When ID = 2, B walks 1m. (Row_Number() over(partition by ID order by SortKey))
When ID = 3, C walks 1m. (Row_Number() over(partition by ID order by SortKey))
When ID = 4, D walks 1m. (Row_Number() over(partition by ID order by SortKey))
Then,
I derived distance between X and A.
I derived distance between X and B.
I derived distance between X and C.
I derived distance between X and D.
Then, I made group using these distances. (group by ID,disTance)
**************************
For exsample5 (collect Rows continued and Val1 is equal and Val2 is equal)
from 941878create table mytable (sortKey,Val1,Val2) as select 1,'A','X' from dual union all select 2,'A','X' from dual union all select 3,'B','Y' from dual union all select 4,'B','Y' from dual union all select 5,'A','X' from dual union all select 5,'B','X' from dual union all select 6,'A','Y' from dual union all select 7,'B','Y' from dual union all select 7,'A','Y' from dual union all select 8,'A','Y' from dual; select Val1,Val2,min(sortKey) as sta,max(sortKey) as end from (select sortKey,Val1,Val2, dense_rank() over(order by sortKey) -Row_Number() over(partition by Val1,Val2 order by sortKey) as distance from mytable) group by Val1,Val2,distance order by min(sortKey); Val1 Val2 sta end ---- ---- --- --- A X 1 2 B Y 3 4 B X 5 5 A X 5 5 A Y 6 8 B Y 7 7
In above solution,
I imagined that there are 5 people called X,A,B,C, and D.
X always walks 1m. (dense_rank() over(order by sortKey))
When Val1 = 'A' and Val2 = 'X', A walks 1m. (Row_Number() over(partition by Val1,Val2 order by SortKey))
When Val1 = 'A' and Val2 = 'Y', B walks 1m. (Row_Number() over(partition by Val1,Val2 order by SortKey))
When Val1 = 'B' and Val2 = 'X', C walks 1m. (Row_Number() over(partition by Val1,Val2 order by SortKey))
When Val1 = 'B' and Val2 = 'Y', D walks 1m. (Row_Number() over(partition by Val1,Val2 order by SortKey))
Then,
I derived distance between X and A.
I derived distance between X and B.
I derived distance between X and C.
I derived distance between X and D.
Then, I made group using these distances. (group by Val1,Val2,disTance)
*****************************************************************
4. What threads are using "Tabibitosan method" ?:|
I will write that threads sorted by easy.
basic usage
1005633
866707
complex usage
920572
450745
1028579
941878
*****************************************************************
5. What URLS mention "Tabibitosan method" ?:|
Boneist's Oracle blog
http://boneist-oracle.livejournal.com/7389.html
Alex Nuijten's Presentation and that PDF document.
http://caat.odtug.com/odtug_caat/caat_abstract_reports.display_presenter_abstract?conference_id=81&presenter_id=117&abstract_id=186
*****************************************************************
OracleSQLPuzzle (MyHomePage)
http://www.geocities.jp/oraclesqlpuzzle/
sister thread
Boolean algebra in some Rows tutorial by Aketi Jyuuzou 1007678 -
Thanks, Learned something new today. This kind of problem comes up quite often.
Keep up the good work. Much Appreciated!! -
here is another example using this with dates. It groups ids by consecutive weeks
WITH A AS ( SELECT 1 ID,5 Val, to_date('2009-08-24','yyyy-mm-dd') StartWeek, to_date('2009-08-30','yyyy-mm-dd') EndWeek FROM dual UNION ALL SELECT 1,10, to_date('2009-08-31','yyyy-mm-dd'), to_date('2009-09-06','yyyy-mm-dd') FROM dual UNION ALL SELECT 1,2, to_date('2009-09-07','yyyy-mm-dd'), to_date('2009-09-13','yyyy-mm-dd') FROM dual UNION ALL SELECT 2,2, to_date('2009-09-07','yyyy-mm-dd'), to_date('2009-09-13','yyyy-mm-dd') FROM dual UNION ALL SELECT 2,5, to_date('2009-09-14','yyyy-mm-dd'), to_date('2009-09-20','yyyy-mm-dd') FROM dual UNION ALL SELECT 3,5, to_date('2009-09-14','yyyy-mm-dd'), to_date('2009-09-20','yyyy-mm-dd') FROM dual UNION ALL SELECT 1,15, to_date('2009-09-21','yyyy-mm-dd'), to_date('2009-09-27','yyyy-mm-dd') FROM dual UNION ALL SELECT 3,25, to_date('2009-09-28','yyyy-mm-dd'), to_date('2009-10-04','yyyy-mm-dd') FROM dual UNION ALL SELECT 3,10, to_date('2009-10-05','yyyy-mm-dd'), to_date('2009-10-11','yyyy-mm-dd') FROM dual UNION ALL SELECT 3,5, to_date('2009-10-12','yyyy-mm-dd'), to_date('2009-10-18','yyyy-mm-dd') FROM dual UNION ALL SELECT 3,15, to_date('2009-10-19','yyyy-mm-dd'), to_date('2009-10-25','yyyy-mm-dd') FROM dual UNION ALL SELECT 4,5, to_date('2009-10-26','yyyy-mm-dd'), to_date('2009-11-01','yyyy-mm-dd') FROM dual) , B AS ( SELECT ID, Val, StartWeek, EndWeek, (Row_Number() over(ORDER BY StartWeek) - Row_Number() over(partition BY ID ORDER BY StartWeek)) AS makeGroup FROM A) SELECT ID, SUM(Val) AS SumVal, MIN(StartWeek), MAX(EndWeek) FROM B GROUP BY ID,makeGroup ORDER BY MIN(StartWeek); ID SumVAL MIN(STARTWEEK) MAX(ENDWEEK) 1 17 8/24/2009 9/13/2009 2 7 9/7/2009 9/20/2009 3 5 9/14/2009 9/20/2009 1 15 9/21/2009 9/27/2009 3 55 9/28/2009 10/25/2009 4 5 10/26/2009 11/1/2009
-
Hi,Aketi Jyuuzou wrote:What is the math problem? It sounds interesting.
...
1. What is Tabibitosan ? ?:|
"Tabibitosan" is Japanese language.
"Tabibitosan" is one of math problem.http://www.manabinoba.com/index.cfm/4,757,73,html?year=2002I love the graphics on the second link!
http://yslibrary.cool.ne.jp/sansub1801.html -
Okay, I am a little dense, I was going to ask for an explanation, in very simple terms, then I figured it out. Basically it is subtracting (or adding if your list is descending) the row number from an ordered list of numbers (or dates) to find consecutive ranges of numbers (or dates). Pretty slick.
As an example, lets take some integers with gaps in the sequence:Num Row Distance (Num-Row) === === ============= 2 1 1 3 2 1 4 3 1 5 4 1 6 5 1 7 6 1 12 7 5 13 8 5 14 9 5 15 10 5 16 11 5 17 12 5 20 13 7 21 14 7 etc ...
Note that the "Distance" delineates consecutive groups, you then group by this etc. Maybe everyone else who viewed this thread got it immediately...
Thanks Aketi, this is a neat trick! -
AlanWms wrote:It looks like one of the simplest cases of grouping "loosely grouped" data.
Okay, I am a little dense, I was going to ask for an explanation, in very simple terms, then I figured it out.
SY. -
Hi, here is another way to solve example 1 using hierarchical queries:
SQL> select min(CONNECT_BY_ROOT NUMVAL) minval, NUMVAL maxval, max(NUMVAL-CONNECT_BY_ROOT NUMVAL+1) countval 2 from ex1 3 where connect_by_isleaf=1 4 connect by numval=prior numval+1 5 group by numval 6 order by 1; MINVAL MAXVAL COUNTVAL ---------- ---------- ---------- 1 3 3 5 7 3 10 12 3 20 21 2
The other examples can be easily derived...
Max
[My Italian Oracle blog|http://oracleitalia.wordpress.com/2009/12/27/inviare-email-dal-db-utilizzando-utl_smtp/] -
@Massimo Ruocchio
Nice one :-)
Hehe I have arranged your solution B-)select min(CONNECT_BY_ROOT NUMVAL) minval, NUMVAL maxval, max(Level) countval from ex1 where connect_by_isleaf=1 connect by numval=prior numval+1 group by numval order by minval; MINVAL MAXVAL COUNTVAL ------ ------ -------- 1 3 3 5 7 3 10 12 3 20 21 2
-
5. What is rival solution of Tabibitosan method ? ?:|
There is the rival solution.
Rival solution derives willSum using Lag function and case expression
And Then derive runSum of willSum using sum function.select min(NumVal),max(NumVal),count(*) from (select NumVal, sum(willSum) over(order by NumVal) as GID from (select NumVal, case when NumVal-1 = Lag(NumVal) over(order by NumVal) then 0 else 1 end as willSum from Ex1)) group by GID order by GID; MIN(NUMVAL) MAX(NUMVAL) COUNT(*) ----------- ----------- --------- 1 3 3 5 7 3 10 12 3 20 21 2
But This rival solution needs 2 InLineView.
Altough TabibitoSan method needs only 1 InLineView.
Therefore if we can use TabibitoSan method,we should use TabibitoSan method.
for example
In below cases,we cannot use TabibitoSan method.
969005
956106 -
Hats off. A clear and concise treatment of a non elementary group of problems. I like the pilgrim interpretation. Never thought about using it when I was asked to explain a solution of this kind. Tabibitosan - a word to remember.
Regards
Etbin
This discussion has been closed.