iakioの日記 RSSフィード

2008-10-13

PostgreSQLでボーリングのスコア計算

| 18:40 | PostgreSQLでボーリングのスコア計算 - iakioの日記 を含むブックマーク はてなブックマーク - PostgreSQLでボーリングのスコア計算 - iakioの日記

PostgreSQLでボーリングのスコア計算をしてみます。元ネタは2008年2月に札幌で行なわれた「日本PostgreSQLユーザ会北海道支部 / Ruby札幌合同セミナー」での角谷さんのセッション「スはスペックのス~RSpecによるテスト駆動開発の実演」です。Rubyやテスト駆動に興味が無くても面白いので(角谷さんが)是非見てみて下さい。

また、PostgreSQLは開発中の8.4(2008-10-11くらいのCVS)を使っています。

以下長文注意。

データ構造

最初はpgTAPでも使ってテストを書いてみようかと思ったのですが、今回のメインはテストではないのでいきなり受け入れテストのパターンでいきます。元ネタでは以下のようなリストでした。

[1, 4, 4, 5, 6, 4, 5, 5, 10, 0, 1, 7, 3, 6, 4, 10, 2, 8, 6]

配列を使ってもできなくはないですが今回はこれをテーブルに格納して、

create table score(idx int, pins int);
copy score from stdin;
1   1
2   4
3   4
4   5
5   6
6   4
7   5
8   5
9   10
10  0
11  1
12  7
13  3
14  6
15  4
16  10
17  2
18  8
19  6
\.

ということにしたいと思います。

2つ先の投球を得る

ストライクやスペアのスコアを計算するには、2投先までのピン数が必要になり

ます。ここはself joinで、

select s1.idx, s1.pins, s2.pins, s3.pins
  from score s1
  join score s2 on (s2.idx = s1.idx + 1)
  join score s3 on (s3.idx = s1.idx + 2);

となります。

 idx | pins | pins | pins
-----+------+------+------
   1 |    1 |    4 |    4
   2 |    4 |    4 |    5
   3 |    4 |    5 |    6
   4 |    5 |    6 |    4
   5 |    6 |    4 |    5
   6 |    4 |    5 |    5
   7 |    5 |    5 |   10
   8 |    5 |   10 |    0
   9 |   10 |    0 |    1
  10 |    0 |    1 |    7
  11 |    1 |    7 |    3
  12 |    7 |    3 |    6
  13 |    3 |    6 |    4
  14 |    6 |    4 |   10
  15 |    4 |   10 |    2
  16 |   10 |    2 |    8
  17 |    2 |    8 |    6
(17 rows)

フレームに分ける

各フレームの1投目に10本倒せばストライクですが、2投目に10本倒した場合はスペアになります。このため、どの投球が1投目であったかを知る必要があります。1投目とは、

  • 一番最初の投球は1投目(あたりまえ)
  • 1投球目が10ピンであれば、次の投球は次のフレームの1投目
  • そうでなければ、次の次の投球が次のフレームの1投目

となります。ここでも先程のようにself joinを使うのですが、1投目の本数によってjoinの式が異ります。なのでここで、8.4の新機能であるrecursive query(再帰問い合わせ)を使ってみます。

with recursive f(idx, pins) as (
    select idx, pins from score where idx = 1
     union all
    select score.idx, score.pins
      from score join f
        on (score.idx = f.idx + case when f.pins = 10 then 1 else 2 end)
)
select * from f;
 idx | pins
-----+------
   1 |    1
   3 |    4
   5 |    6
   7 |    5
   9 |   10    -- strike
  10 |    0
  12 |    7
  14 |    6
  16 |   10    -- strike
  17 |    2
  19 |    6
(11 rows)

うん。よさそうです(説明したいけどいい説明が思い浮ばない)。11フレームまで投げてるようにも見えますがこの時点では気にしないことにしますw。

合体

では2つのsqlを合体させます。無理矢理1つにまとめることもできそうですが、今回は前半のsqlもcteに入れてみます。

with recursive
s(idx, pins1, pins2, pins3) as (
    select s1.idx, s1.pins, s2.pins, s3.pins
      from score s1
      join score s2 on (s2.idx = s1.idx + 1)
      join score s3 on (s3.idx = s1.idx + 2)
),
f(idx, pins1, pins2, pins3) as (
    select idx, pins1, pins2, pins3 from s where idx = 1
     union all
    select s.idx, s.pins1, s.pins2, s.pins3
      from s join f
        on (s.idx = f.idx + case when f.pins1 = 10 then 1 else 2 end)
)
select * from f;
 idx | pins1 | pins2 | pins3
-----+-------+-------+-------
   1 |     1 |     4 |     4
   3 |     4 |     5 |     6
   5 |     6 |     4 |     5
   7 |     5 |     5 |    10
   9 |    10 |     0 |     1
  10 |     0 |     1 |     7
  12 |     7 |     3 |     6
  14 |     6 |     4 |    10
  16 |    10 |     2 |     8
  17 |     2 |     8 |     6
(10 rows)

そして完成へ

材料は揃いました。いよいよスコア計算です。まずフレーム毎のスコアを計算します。

  • ストライクの場合は、pins1(=10) + pins2 + pins3
  • スペアの場合は、(pins1 + pins2)(=10) + pins3

あ、結局一緒か。

  • それ以外の場合は、pins1 + pins2
with recursive
s(idx, pins1, pins2, pins3) as (
    select s1.idx, s1.pins, s2.pins, s3.pins
      from score s1
      join score s2 on (s2.idx = s1.idx + 1)
      join score s3 on (s3.idx = s1.idx + 2)
),
f(idx, pins1, pins2, pins3) as (
    select idx, pins1, pins2, pins3 from s where idx = 1
     union all
    select s.idx, s.pins1, s.pins2, s.pins3
      from s join f
        on (s.idx = f.idx + case when f.pins1 = 10 then 1 else 2 end)
)
select idx
     , pins1, pins2, pins3
     , case when pins1 = 10 then pins1 + pins2 + pins3
            when pins1 + pins2 = 10 then pins1 + pins2 + pins3
            else pins1 + pins2
        end as score_of_frame
       from f;
 idx | pins1 | pins2 | pins3 | 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

勝利は目前。あとはsum()するだけです。普通にsum()してもいいけど折角なのでもうひとつcteを使ってみます。

with recursive
s(idx, pins1, pins2, pins3) as (
    select s1.idx, s1.pins, s2.pins, s3.pins
      from score s1
      join score s2 on (s2.idx = s1.idx + 1)
      join score s3 on (s3.idx = s1.idx + 2)
),
f(idx, pins1, pins2, pins3) as (
    select idx, pins1, pins2, pins3 from s where idx = 1
     union all
    select s.idx, s.pins1, s.pins2, s.pins3
      from s join f
        on (s.idx = f.idx + case when f.pins1 = 10 then 1 else 2 end)
),
sof(idx, pins1, pins2, pins3, score_of_frame) as (
    select idx
         , pins1, pins2, pins3
         , case when pins1 = 10 then pins1 + pins2 + pins3
                when pins1 + pins2 = 10 then pins1 + pins2 + pins3
                else pins1 + pins2
            end as score_of_frame
           from f
)
select sum(score_of_frame) from sof;
 sum
-----
 133
(1 row)

お疲れさまでしたー。

まとめのようなもの

  • Q. SQLでボーリングのスコアを求めることに意味あるの?
  • A. recursive queryを使うと何ができるようになるのかを理解する上では役立つと思います(というか役立ちました。僕は)。
  • A. あと、non-recursiveなcteは、長いsqlを書こうとした時にすごく便利だと思いました。

DainooDainoo2012/07/28 08:02Very valid, pithy, scucinct, and on point. WD.

nuznngnuznng2012/07/28 23:1134cwa1 <a href="http://bhrzmgutvgxx.com/">bhrzmgutvgxx</a>

wvxwizvylavwvxwizvylav2012/07/29 02:09UNbw3z , [url=http://jlthkaiixdwn.com/]jlthkaiixdwn[/url], [link=http://rxzryltstffr.com/]rxzryltstffr[/link], http://zpmtxlcksoln.com/

dbfopzfidbfopzfi2012/07/30 16:29cEGRXs , [url=http://zofvedemkaiz.com/]zofvedemkaiz[/url], [link=http://xxrvtjfzeupf.com/]xxrvtjfzeupf[/link], http://suodmmezyywt.com/