パズル59. 期間を結合する
以下の問題を解きます。なお、問題は『SQLパズル 第2版』242ページより引用、簡潔に改変したものです。
スケジュール表の中でOverLapした期間をまとめるSelect文を考えます。
TimeSheetsテーブル
TaskID StartDate EndDate
------ ---------- ----------
1 2019-01-01 2019-01-03
2 2019-01-02 2019-01-04
3 2019-01-04 2019-01-05
4 2019-01-06 2019-01-09
5 2019-01-09 2019-01-09
6 2019-01-09 2019-01-09
7 2019-01-12 2019-01-15
8 2019-01-13 2019-01-14
9 2019-01-14 2019-01-14
10 2019-01-17 2019-01-17
11 2019-02-01 2019-02-05
12 2019-02-03 2019-02-28
13 2019-02-07 2019-02-11
14 2019-03-01 2019-03-31
15 2019-03-01 2019-03-15
TimeSheetsテーブルの重複した期間をまとめて、期間の開始と終了を表示します。
StartDate EndDate ---------- ---------- 2019-01-01 2019-01-05 2019-01-06 2019-01-09 2019-01-12 2019-01-15 2019-01-17 2019-01-17 2019-02-01 2019-02-28 2019-03-01 2019-03-31
create table TimeSheets(
TaskID int primary key,
StartDate date,
EndDate date);
insert into TimeSheets
values( 1,date '2019-01-01',date '2019-01-03'),
( 2,date '2019-01-02',date '2019-01-04'),
( 3,date '2019-01-04',date '2019-01-05'),
( 4,date '2019-01-06',date '2019-01-09'),
( 5,date '2019-01-09',date '2019-01-09'),
( 6,date '2019-01-09',date '2019-01-09'),
( 7,date '2019-01-12',date '2019-01-15'),
( 8,date '2019-01-13',date '2019-01-14'),
( 9,date '2019-01-14',date '2019-01-14'),
(10,date '2019-01-17',date '2019-01-17'),
(11,date '2019-02-01',date '2019-02-05'),
(12,date '2019-02-03',date '2019-02-28'),
(13,date '2019-02-07',date '2019-02-11'),
(14,date '2019-03-01',date '2019-03-31'),
(15,date '2019-03-01',date '2019-03-15');
解として、以下の方法が考えられます。
with tmp1 as(
select StartDate,max(EndDate) as EndDate,
case when StartDate
<= max(max(EndDate)) over(order by StartDate
Rows between Unbounded Preceding
and 1 Preceding)
then 0 else 1 end as WillSum
from TimeSheets
group by StartDate),
tmp2 as(
select StartDate,EndDate,
sum(WillSum) over(order by StartDate) as GID
from tmp1)
select min(StartDate) as StartDate,max(EndDate) as EndDate
from tmp2
group by GID
order by GID;
最初に、StartDateの重複排除で、group by StartDateとし、EndDateは、max(EndDate)とします。
期間の連結について、考察してみます。行Xの期間が他の行Yの期間と連結するのは、下記の2つの場合が考えられます。
場合1 X.StartDate < Y.StartDate かつ Y.StartDate <= X.EndDate 場合2 X.StartDate > Y.StartDate かつ X.StartDate >= Y.EndDate
場合1と場合2の数式で、行Aから見て他の行Bと連結しているなら、行Bから見ても行Aと連結していると分かります。
以上により、StartDateの昇順に行を見ていって、StartDateが自分未満の行（下界の行）と連結してたら0、連結してなかったら1とする数の累計で、グループ化すればいいと分かります。
SQLのイメージ（第1段階）は下記となります。group by StartDateで赤線を引いています。
SQLのイメージ（第2段階）は下記となります。max(max(EndDate)) over(order by StartDate Rows between Unbounded Preceding and 1 Preceding) に対応する黄緑線を引いています。
SQLのイメージ（第3段階）は下記となります。sum(WillSum) over(order by StartDate) に対応する黄緑線と、group by GID に対応する赤線を引いています。
パズル62. レポートの整形
以下の問題を解きます。なお、問題は『SQLパズル 第2版』252、253ページより引用、簡潔に改変したものです。
決まった列に値を配置してレポートのような形で表示するSelect文を考えます。
Namesテーブル name ------- Al Ben Charlie David Ed Frank Greg Howard Ida Joe Ken Larry Mike Neal
Namesテーブルのname列を、3行ごとに、1行で表示します（最終行の余った列は、nullとします）。出力順は、行も列も、Namesテーブルのname列の昇順とします。
name1 name2 name3 ----- ------ ------- Al Ben Charlie David Ed Frank Greg Howard Ida Joe Ken Larry Mike Neal null
create table Names(name char(7) primary key);
insert into Names values('Al'),
('Ben'),
('Charlie'),
('David'),
('Ed'),
('Frank'),
('Greg'),
('Howard'),
('Ida'),
('Joe'),
('Ken'),
('Larry'),
('Mike'),
('Neal');
解として、以下の方法が考えられます。
with tmp as( select name, -1 + Row_Number() over(order by name) as rn from Names) select max(case rn%3 when 0 then name end) as name1, max(case rn%3 when 1 then name end) as name2, max(case rn%3 when 2 then name end) as name3 from tmp group by truncate(rn/3 , 0) order by truncate(rn/3 , 0);
0以上の整数を3で割った時の、商と余りは下記となります。
0 割る 3 = 0 余り 0 1 割る 3 = 0 余り 1 2 割る 3 = 0 余り 2 3 割る 3 = 1 余り 0 4 割る 3 = 1 余り 1 5 割る 3 = 1 余り 2 6 割る 3 = 2 余り 0 7 割る 3 = 2 余り 1 8 割る 3 = 2 余り 2 省略
以上により、商でグループ化しつつ、余りに対応したname列を、max関数とcase式を組み合わせて表示すればいいと分かります。
SQLのイメージは下記となります。group by truncate(rn/3 , 0) で赤線を引いています。
パズル63. 連続的なグルーピング
以下の問題を解きます。なお、問題は『SQLパズル 第2版』261ページより引用、簡潔に改変したものです。
指定列の値が連続した区間をまとめる、Select文を考えます。
Tテーブル num data --- ---- 1 aaaa 2 aaaa 3 bbbb 6 bbbb 8 aaaa 10 cccc 12 bbbb 15 bbbb
各data値がnumの何番から何番まで連続しているかを、その出現順でまとめます。
Low High data --- ---- ---- 1 2 aaaa 3 6 bbbb 8 8 aaaa 10 10 cccc 12 15 bbbb
create table T(
num int primary key,
data char(4));
insert into T values( 1,'aaaa'),
( 2,'aaaa'),
( 3,'bbbb'),
( 6,'bbbb'),
( 8,'aaaa'),
(10,'cccc'),
(12,'bbbb'),
(15,'bbbb');
解として、以下の2つの方法が考えられます。
with tmp1 as(
select num,data,
case when data = Lag(data) over(order by num)
then 0 else 1 end as WillSum
from T),
tmp2 as(
select num,data,
sum(WillSum) over(order by num) as GID
from tmp1)
select min(num) as low,max(num) as high,data
from tmp2
group by GID,data
order by GID;
numの昇順で行を見ていって、Lag関数を使って、シーケンス開始なら1、シーケンス開始でなければ0とする数を求め、その累計でグループ化しています。
SQLのイメージは下記となります。sum(WillSum) over(order by num) as GIDに対応する黄緑線と、group by GID,dataに対応する赤線を引いています。
with tmp as( select num,data, Row_Number() over(order by num) -Row_Number() over(partition by data order by num) as distance from T) select min(num) as low,max(num) as high,data from tmp group by data,distance order by min(num);
中学受験の算数で有名な旅人算の感覚を使う方法もあります。
1進む条件が異なる4人の旅人（旅人X, 旅人A, 旅人B, 旅人C）が、数直線の原点からプラス方向に同時にスタートしたとして、下記のように考えています。
・必ず1進む旅人Xの位置 = Row_Number() over(order by num) ・dataがaaaaなら1進む旅人Aの位置 = Row_Number() over(partition by data order by num) ・dataがbbbbなら1進む旅人Bの位置 = Row_Number() over(partition by data order by num) ・dataがccccなら1進む旅人Cの位置 = Row_Number() over(partition by data order by num)
そして、group by data, distance によって、dataに対応した旅人の種類（旅人A, 旅人B, 旅人Cのどれか）と、旅人Xとの距離でグループ化してます。
SQLのイメージは下記となります。Row_Number() over(order by num)に対応する1人の旅人と、Row_Number() over(partition by data order by num)に対応する3人の旅人をイメージし、group by data, distanceに対応する赤線を引いてます。
パズル66. 数独パズル
ニコリの数独の問題を解きます。なお、問題は『SQLパズル 第2版』270、271ページより引用、簡潔に改変したものです。
数独を再帰With句で解いてみましょう。
select InStr(Val,'a') as tes1, InStr(Val,'X') as tes2, SubStr(Val,1,2) as tes3, SubStr(Val,3,2) as tes4, Insert(Val,2,3,'XYZ') as tes5, Insert(Val,2,0,'XYZ') as tes6 from (select 'abcdef' as Val) tmp; +------+------+------+------+--------+-----------+ | tes1 | tes2 | tes3 | tes4 | tes5 | tes6 | +------+------+------+------+--------+-----------+ | 1 | 0 | ab | cd | aXYZef | aXYZbcdef | +------+------+------+------+--------+-----------+
数独を解くのに使う文字列関数の紹介です。
解として、以下の方法が考えられます。
with recursive Question(Val) as(
select concat('800005100',
'001000800',
'040200090',
'000030002',
'123406789',
'600010000',
'080009050',
'002000400',
'007600001')),
Renban1To9(Cnt) as(
select 1 union select 2 union select 3 union
select 4 union select 5 union select 6 union
select 7 union select 8 union select 9),
rec(LV,Val) as(
select 1,Val from Question
union all
select LV+1,
case when SubStr(Val,LV,1) = '0'
then Insert(Val,LV,1,Cnt)
else Val end
from rec a Join Renban1To9 b
on a.LV <= 81
and (SubStr(a.Val,a.LV,1) = '0' or b.Cnt=1)
where SubStr(Val,LV,1) != '0'
or (InStr(SubStr(Val,truncate((LV-1)/9,0)*9+1,9),Cnt) = 0 -- 横チェック
and InStr(SubStr(Val,(LV-1)%9+1 ,1),Cnt) = 0 -- 縦チェック
and InStr(SubStr(Val,(LV-1)%9+1+ 9,1),Cnt) = 0
and InStr(SubStr(Val,(LV-1)%9+1+18,1),Cnt) = 0
and InStr(SubStr(Val,(LV-1)%9+1+27,1),Cnt) = 0
and InStr(SubStr(Val,(LV-1)%9+1+36,1),Cnt) = 0
and InStr(SubStr(Val,(LV-1)%9+1+45,1),Cnt) = 0
and InStr(SubStr(Val,(LV-1)%9+1+54,1),Cnt) = 0
and InStr(SubStr(Val,(LV-1)%9+1+63,1),Cnt) = 0
and InStr(SubStr(Val,(LV-1)%9+1+72,1),Cnt) = 0
-- 正方形チェック
and InStr(SubStr(Val,truncate((LV-1)%9/3,0)*3+1
+9*(truncate(truncate((LV-1)/9,0)/3,0)*3) ,3),Cnt)=0
and InStr(SubStr(Val,truncate((LV-1)%9/3,0)*3+1
+9*(truncate(truncate((LV-1)/9,0)/3,0)*3)+ 9,3),Cnt)=0
and InStr(SubStr(Val,truncate((LV-1)%9/3,0)*3+1
+9*(truncate(truncate((LV-1)/9,0)/3,0)*3)+18,3),Cnt)=0))
select SubStr(a.Val,1+9*(b.Cnt-1),9) as Answer
from rec a,Renban1To9 b
where a.LV=82
order by b.Cnt;
+-----------+
| Answer |
+-----------+
| 839765124 |
| 261394875 |
| 745281396 |
| 594837612 |
| 123456789 |
| 678912543 |
| 386149257 |
| 912573468 |
| 457628931 |
+-----------+
再帰項で、1から9までの連番テーブルとの内部結合を繰り返しつつ、where句で、その連番でマスを埋めることが可能かをチェックしています。
SQLのイメージ（探索木の深さは3まで）は下記となります。幅優先探索の探索木をイメージしています。未確定マスは、1から9までのノードが作成候補になりつつ、枝切りされていくイメージです。
高さ1のノードが、問題の1番上の行の1番左のマスに対応し、高さ2のノードが、問題の1番上の行の左から2番目のマスに対応し、高さ3のノードが、問題の1番上の行の左から3番目のマスに対応します。
最後に
3回に分けて、MySQL 8.0のSQLの新機能である、Window関数と再帰With句の解説を行いました。本連載は、今回で終了となります。
