トップページに戻る
次のSQLパズルへ
前のSQLパズルへ
9-52 最大のリージョンを求める(境界なし)
SQLパズル
Seats2テーブル
Seat Status
---- ------
1 占
2 占
3 空
4 空
5 空
6 占
7 空
8 空
9 空
10 空
11 空
12 占
13 占
14 空
15 空
16 空
17 空
18 空
Seatの昇順で、Statusが最も長く連続して空なSeatの、
startとEndとCountを以下の形式で出力する。
出力結果
SeatStart SeatEnd SeatCount
--------- ------- ---------
7 11 5
14 18 5
プログラマのためのSQL第2版の24章[リージョン、ラン、シーケンス]を参考にさせていただきました
データ作成スクリプト
create table Seats2 as
select 1 as 座席,'占' as 状態 from dual
union select 2,'占' from dual
union select 3,'空' from dual
union select 4,'空' from dual
union select 5,'空' from dual
union select 6,'占' from dual
union select 7,'空' from dual
union select 8,'空' from dual
union select 9,'空' from dual
union select 10,'空' from dual
union select 11,'空' from dual
union select 12,'占' from dual
union select 13,'占' from dual
union select 14,'空' from dual
union select 15,'空' from dual
union select 16,'空' from dual
union select 17,'空' from dual
union select 18,'空' from dual;
SQL
--■■■lnnvl述語を使う方法■■■
select start_seat,end_seat,seat_cnt
from (select min(座席) as start_seat,
max(座席) as end_seat,
count(*) as seat_cnt,
max(count(*)) over() as maxseat_cnt
from (select 座席,sum(willSum) over(order by 座席) as makeGroup
from (select 座席,状態,
case when lnnvl(Lag(状態) over(order by 座席) = '空')
then 1 else 0 end as willSum
from Seats2)
where 状態 = '空')
group by makeGroup)
where seat_cnt = maxseat_cnt;
--■■■lnnvl述語を使わない方法■■■
select start_seat,end_seat,seat_cnt
from (select min(座席) as start_seat,
max(座席) as end_seat,
count(*) as seat_cnt,
max(count(*)) over() as maxseat_cnt
from (select 座席,sum(willSum) over(order by 座席) as makeGroup
from (select 座席,状態,
case when Lag(状態) over(order by 座席) = '空'
then 0 else 1 end as willSum
from Seats2)
where 状態 = '空')
group by makeGroup)
where seat_cnt = maxseat_cnt;
--■■■旅人算の感覚を使う方法■■■
select start_seat,end_seat,seat_cnt
from (select min(座席) as start_seat,
max(座席) as end_seat,
count(*) as seat_cnt,
max(count(*)) over() as maxseat_cnt
from (select 座席,状態,
Row_Number() over(order by 座席)
- Row_Number() over(partition by 状態 order by 座席) as makeGroup
from Seats2)
where 状態 = '空'
group by makeGroup)
where seat_cnt = maxseat_cnt;
--■■■旅人算の感覚を使う方法(先にwhere句でフィルタ)■■■
select start_seat,end_seat,seat_cnt
from (select min(座席) as start_seat,
max(座席) as end_seat,
count(*) as seat_cnt,
max(count(*)) over() as maxseat_cnt
from(select 座席,状態,
座席-Row_Number() over(order by 座席) as distance
from Seats2
where 状態 = '空')
group by distance)
where seat_cnt = maxseat_cnt;
解説
空が、連続した状態かを調べて、グループ化してます。
lnnvl述語を使う方法のほうが分かりやすいと思います。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
旅人算の感覚を使う方法では、
Row_Number() over(order by 座席)が必ず進むシーケンス、
Row_Number() over(partition by 状態 order by 座席)が、
状態が'空'の時のみ進むシーケンスと、
状態が'占'の時のみ進むシーケンスと
3つのシーケンスがあると考えて、
状態('空'または'占')と、必ず進むシーケンスとの差でグループ化してます。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
3人旅人算の感覚で言いかえると
Row_Number() over(order by 座席)が必ず1進む旅人、
Row_Number() over(partition by 状態 order by 座席)が、
状態が'空'の時のみ1進む旅人、
状態が'占'の時のみ1進む旅人
3人の旅人がいると考えて、
状態('空'または'占')によって1進む旅人と、
必ず1進む旅人との距離の差でグループ化してます。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
group by句に状態も含めたほうが、冗長だけど分かりやすいかもしれませんね。
select start_seat,end_seat,seat_cnt
from (select min(座席) as start_seat,
max(座席) as end_seat,
count(*) as seat_cnt,
max(count(*)) over() as maxseat_cnt
from (select 座席,状態,
Row_Number() over(order by 座席)
- Row_Number() over(partition by 状態 order by 座席) as makeGroup
from Seats2)
where 状態 = '空'
group by 状態,makeGroup)
where seat_cnt = maxseat_cnt;
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
旅人Aが、毎回1進む旅人 -- Row_Number() over(order by 座席)
旅人Bが、状態が'空'の時1進む旅人 -- Row_Number() over(partition by 状態 order by 座席)
旅人Cが、状態が'占'の時1進む旅人 -- Row_Number() over(partition by 状態 order by 座席)
という3人の旅人がいるとして、下記の図をイメージすると分かりやすいかもしれません。
図からも明らかですが、
旅人Aと旅人Bとの距離は、広義の単調増加となります。
旅人Aと旅人Cとの距離も、広義の単調増加となります。
-------- 0- 1- 2- 3- 4- 5- 6- 7- 8- 9-10-11-12-13-14-15-16-17-18-
1 占 |B |AC| | | | | | | | | | | | | | | | | |
2 占 |B | |AC| | | | | | | | | | | | | | | | |
3 空 | |B | C|A | | | | | | | | | | | | | | | |
4 空 | | |BC| |A | | | | | | | | | | | | | | |
5 空 | | | C|B | |A | | | | | | | | | | | | | |
6 占 | | | |BC| | |A | | | | | | | | | | | | |
7 空 | | | | C|B | | |A | | | | | | | | | | | |
8 空 | | | | C| |B | | |A | | | | | | | | | | |
9 空 | | | | C| | |B | | |A | | | | | | | | | |
10 空 | | | | C| | | |B | | |A | | | | | | | | |
11 空 | | | | C| | | | |B | | |A | | | | | | | |
12 占 | | | | | C| | | |B | | | |A | | | | | | |
13 占 | | | | | | C| | |B | | | | |A | | | | | |
14 空 | | | | | | C| | | |B | | | | |A | | | | |
15 空 | | | | | | C| | | | |B | | | | |A | | | |
16 空 | | | | | | C| | | | | |B | | | | |A | | |
17 空 | | | | | | C| | | | | | |B | | | | |A | |
18 空 | | | | | | C| | | | | | | |B | | | | |A |
Tabibitosan method tutorial by Aketi Jyuuzou
今月は旅人算 - 学びの場.com
旅人算 とは
7-50 前後の値で分岐
9-54 最大のリージョンを求める(境界あり)
10-130 ignore nullsをsum関数で代用
10-187 パーティションごとの連続したグループを求める
10-188 連続してなかったらインクリメント
10-189 連続したIDごとの最大値と最小値(重複を考慮)
10-206 連続範囲の累計と非連続範囲の累計
10-208 旅人算の感覚を使うクエリ(日付型バージョン)
10-250 12進数変換を行って、旅人算の感覚
10-279 旅人算の代わりにmodel句
10-310 ソートキーに重複がある旅人算の感覚
10-316 旅人算の感覚で連続数を求める
OTNのスレッド
CodeZine：SQLで数列を扱う
分析関数の衝撃3(後編)
分析関数の衝撃5(総集編)