2

More than 3 years have passed since last update.

posted at

updated at

# 旅人算メソッドの解説

Tabibitosan method tutorial by Aketi Jyuuzou
を日本語に翻訳しつつ、全面リニューアルしてみました。

# 01 旅人算とは？

https://www.manabinoba.com/math/757.html

Hello School 算数 旅人算
https://www.hello-school.net/sansub1801.html

https://www.amazon.co.jp/dp/4895243702

# 02 旅人算メソッドとは？

SQLで、何らかの条件で連続した行を、集約したいときに使う、SQLおよび考え方です。

# 03 数値の連続した行を集約

``````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;
``````

``````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
``````

そして、旅人Xと旅人Aとの距離でグループ化してます。`group by DisTance`

# 04 日付の連続した行を集約

``````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(DateVal)  Max(DateVal)  count(*)
------------  ------------  --------
2009-12-10    2009-12-12           3
2009-12-16    2009-12-17           2
2009-12-20    2009-12-20           1
``````

# 05 年月の連続した行を集約

``````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(DateVal)  max(DateVal)  count(*)
------------  ------------  --------
2009-09-01    2009-10-01           2
2009-12-01    2010-02-01           3
2010-04-01    2010-04-01           1
``````

# 06 列の値が一致し続ける行を集約

という用途もあります。

``````create 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;
``````

SortKeyの昇順でIDが一致する行を集約します。

``````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
``````

そして、

を求めます

そして、旅人の種類(A,B,C,Dのいずれか)と、旅人Xとの距離
でグループ化してます。`group by ID,DisTance`

# 07 複数列の値が一致し続ける行を集約

``````create 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;
``````

SortKeyの昇順でVal1とVal2が一致する行を集約します。

``````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
``````

そして、

を求めます

そして、旅人の種類(A,B,C,Dのいずれか)と、旅人Xとの距離
でグループ化してます。`group by Val1,Val2,disTance`

# 08 旅人算メソッドと同じ結果を得る方法

Lag関数とCase式を組み合わせて、WillSumという値を求めてから、
sum関数で累計を求めるという方法で代用できます。

``````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
``````

12cからは、Match_Recognize句を使う方法もあります。
Match_Recognize句を使えば、インラインビューは不要になります。

# 09 旅人算メソッドが使用された質問(日本語)

OTN 連続する項目の件数をカウントするには？

# 10 旅人算メソッドが使用された質問(英語)

sql - Oracle : min max values within a repeating group - Stack Overflow
https://stackoverflow.com/questions/20632922/oracle-min-max-values-within-a-repeating-group

# 11 旅人算メソッドを紹介したブログ(日本語)

http://d.hatena.ne.jp/arn/20120823/p1

キー項目がブレイクしたタイミングでサマリ集計するSQL (ROW_NUMBER分析ファンクション)
https://gonsuke777.hatenablog.com/entries/2014/12/02