6-12 ボウリングのスコア計算

SQLパズル

score
idx  pins
---  ----
  1     1
  3     4
  5     6
  7     5
  9    10
 10     0
 12     7
 14     6
 16    10
 17     2

ボウリングのスコアを計算する。

出力結果
idx  pins  next1  next2  score_of_frame
---  ----  -----  -----  --------------
  1     1      4      4               5
  3     4      5      6               9
  5     6      4      5              15
  7     5      5     10              20
  9    10      0      1              11
 10     0      1      7               1
 12     7      3      6              16
 14     6      4     10              20
 16    10      2      8              20
 17     2      8      6              16

こちらを参考にさせていただきました

データ作成スクリプト

create table score(idx,pins) as
select  1, 1 from dual union
select  2, 4 from dual union
select  3, 4 from dual union
select  4, 5 from dual union
select  5, 6 from dual union
select  6, 4 from dual union
select  7, 5 from dual union
select  8, 5 from dual union
select  9,10 from dual union
select 10, 0 from dual union
select 11, 1 from dual union
select 12, 7 from dual union
select 13, 3 from dual union
select 14, 6 from dual union
select 15, 4 from dual union
select 16,10 from dual union
select 17, 2 from dual union
select 18, 8 from dual union
select 19, 6 from dual;

SQL

select idx,pins,next1,next2,
case when pins = 10 then pins + next1 + next2
     when pins + next1 = 10 then pins + next1 + next2
     else pins + next1 end as score_of_frame
from (select idx,idx+case pins when 10 then 1 else 2 end as nextIdx,
      pins,
      Lead(pins,1,0) over(order by idx) as next1,
      Lead(pins,2,0) over(order by idx) as next2,
      max(idx) over() as maxIdx
        from score)
start with idx = 1
connect by prior nextIdx = idx
             and idx != maxIdx;

解説

インラインビューで、
case式で次のフレームの1投目を求めつつ、
Lead関数で、次に倒した数と、次の次に倒した数を求めてます。

それから階層問い合わせを使い各フレームの1投目の行のみ取得してます。

後は、sum関数でscore_of_frameの合計を求めればいいでしょう。

10-245 5分以上未来の最小データで経路作成

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
model句でフレームを求めるSQLです。
case式で場合分けを使ってます。

select idx,frame,pins
  from score
 model
dimension by(idx)
measures(1 as frame,pins,max(idx) over() as maxIdx)
rules(
frame[idx] order by idx = case when cv(idx) = 1 then 1
                               when maxIdx[cv()]  = cv(idx) then frame[cv()-1]
                               when pins[cv()-1] = 10 then frame[cv()-1]+1
                               when frame[cv()-1] = frame[cv()-2] then frame[cv()-1]+1
                               else frame[cv()-1] end);

idx  frame  pins
---  -----  ----
  1      1     1
  2      1     4
  3      2     4
  4      2     5
  5      3     6
  6      3     4
  7      4     5
  8      4     5
  9      5    10
 10      6     0
 11      6     1
 12      7     7
 13      7     3
 14      8     6
 15      8     4
 16      9    10
 17     10     2
 18     10     8
 19     10     6

---