SELECT文の基本

プログラミング

この記事では競馬予想の基本である基準タイムを作りながら、SQLの基本であるSELECT文を学習します。いきなり完成品を見せるのではなく、SELECT文を書くための順序と考え方を学ぶため、1つずつステップを踏みながら作ります。

レコードの抽出条件を設定する

SELECT文の第一歩はレコードの抽出から。「レコード」とは行単位のデータを指します。今回は、

  • 中央10場(JRA)のデータ
  • 集計期間は2018年から2019年
  • 集計値は1〜3着馬の走破タイム

の集計結果を基準タイムとして採用することにします。今回はあくまでSELECT文の学習が目的ですから、馬場状態やら競走条件やらを加えたり、基準タイムの精度や品質にはこだわりません。以下が、上記の条件を満たすレコードを抽出するSELECT文です。

SELECT
  se.soha_time
FROM
  jvd_se se
WHERE 1 = 1
AND se.data_kubun = '7'
AND se.kaisai_nen >= '2018'
AND se.kaisai_nen <= '2019'
AND se.kakutei_chakujun >= '01'
AND se.kakutei_chakujun <= '03'

SELECT文の「基本の基本」は、この3つの句で構成されます。

  1. SELECT句
  2. FROM句
  3. WHERE句

SELECT文を解析する順序

データベースがSELECT文を解析する時は、次の順序で解析しています。

  1. FROM句→どのテーブル見るの?
  2. WHERE句→データの抽出条件は?
  3. SELECT句→表示する項目は?

なので、人間がSELECT文を考えるときも同じ順序で書いていくと分かりやすいです。

FROM句

では、その順序に従ってFROM句に、見るテーブルを書きます。基準タイムを作成するにはレース結果の走破タイムのデータが必要です。JV-Data仕様書を調べると走破タイムは「馬毎レース情報」にあります。なので、FROM句にそのテーブル名「jvd_se」を書きます。
※「PC-KEIBA Database」のテーブル名は「"jvd_" + レコード種別ID」で統一しています。

テーブル名の直後に書いてある(宣言している) "se" はSQLの世界で「別名(べつめい)」と呼びます。別名は、

  • 重複する名前を分類するため
  • その項目が、どのテーブルに属しているか明示するため
  • 可読性、つまり人間がSQLを読みやすくするため

に使います。別名を宣言したらSQL内の項目が、どのテーブルに属しているのか「別名+"."(ピリオド)+項目名」の形で全て明示します。ここでは別名を "se" にしてますが "t1" でも他でもかまいません。しかし、どのテーブルを指すのか一目で分かるほうが良いので、ここではレコード種別IDと同じ "se" にしています。

WHERE句

次はWHERE句に、データの抽出条件を書きます。まず「データ区分」を指定します。'7'は中央競馬の確定成績を表します。各項目と値の意味はJV-Data仕様書を調べながら書きます。開催年と確定着順の「範囲」はサンプルのように ">=" と "<=" の演算子でその範囲を指定します。

抽出条件を複数書く場合は「AND」でつなぎます。SQLは改行やインデント、大文字小文字の違いなど、書式にうるさくない言語ですが、サンプルのように式や項目を1行ずつ書いたほうが分かりやすくて修正も簡単です。

そして「WHERE 1 = 1」という書き方ですが、これは抽出条件の有無に関係なく、常にWHERE句の構文として成立させるための書き方です。私はいつもこの書き方を習慣にしてします。式をコメントアウトするだけで抽出条件のオンオフを簡単に切り替えできるので、データ検証する時とても便利です。ちなみに抽出条件は不要で全てのデータを取得する、という場合はWHERE句の記述は必要ありません。

SELECT句

最後はSELECT句に、検索結果に表示する列を書きます。今はとりあえず走破タイムだけ。

テーブルの結合

前章で走破タイムを表示したものの、これだけではどのコースの走破タイムか分かりません。トラック(芝ダート)や距離など、コースに関する情報が必要です。その情報は「レース詳細」にあるので、そこから取得して走破タイムと共に表示しましょう。

SELECT
  ra.keibajo_code
, ra.track_code
, ra.kyori
, se.soha_time
FROM
  jvd_se se
INNER JOIN
  jvd_ra ra
ON  ra.kaisai_nen = se.kaisai_nen
AND ra.kaisai_tsukihi = se.kaisai_tsukihi
AND ra.keibajo_code = se.keibajo_code
AND ra.race_bango = se.race_bango
WHERE 1 = 1
AND se.data_kubun = '7'
AND se.kaisai_nen >= '2018'
AND se.kaisai_nen <= '2019'
AND se.kakutei_chakujun >= '01'
AND se.kakutei_chakujun <= '03'

赤字の部分が今回追加した行です。SELECT句に複数の項目を書く場合は、カンマ(,)でつなぎます。
ON句に複数の項目を書く場合は、WHERE句と同じく「AND」でつなぎます。

JOIN句

1つのSELECT文で2つ以上のテーブルを使う場合は「JOIN句」にテーブルとテーブルの結合条件を書きます。テーブルの結合には、次の2つがあります。

  1. INNER JOIN (内部結合)
  2. LEFT JOIN (外部結合)

この他にもあるのですが、私が仕事で10年以上SQLを書き続けて、この2つ以外の結合に出番はありませんでした。なので「結合はこの2種類だけ」と覚えてください。内部結合と外部結合の違いはまた別の機会に説明しますが、今は内部結合の「INNER JOIN」を使います。テーブルを3つ、4つと使いたい場合は、この「JOIN句」の下に同じように2つ目、3つ目の「JOIN句」を続けます。

キーでテーブルを結合する

「キー」とはデータベースがテーブルのレコードを一意に識別するための項目です。主キー(しゅきー)と呼ぶ場合もあります。「PC-KEIBA Database」ではレースを識別するキーを、次の4つの項目で定めています。

  1. kaisai_nen (開催年)
  2. kaisai_tsukihi (開催月日)
  3. keibajo_code (競馬場コード)
  4. race_bango (レース番号)

「レース詳細」と「馬毎レース情報」は上記の同じ名前のキー項目をそれぞれ持っているので、双方がどの項目で紐づくのかをON句に書きます。ここで先ほど登場した「テーブルの別名」が役に立つのです。

走破タイムを秒単位に変換する

JV-Data仕様書を調べると走破タイムは「9分99秒9で設定」の、4桁の文字列になっています。なので関数を使って「4桁の文字列」を「秒単位の数値」に変換します。

SELECT
  ra.keibajo_code
, ra.track_code
, ra.kyori
, to_number(substring(se.soha_time, 1, 1), '9') * 60
    + to_number(substring(se.soha_time, 2, 3), '999') / 10 AS soha_time
FROM
  jvd_se se
INNER JOIN
  jvd_ra ra
ON  ra.kaisai_nen = se.kaisai_nen
AND ra.kaisai_tsukihi = se.kaisai_tsukihi
AND ra.keibajo_code = se.keibajo_code
AND ra.race_bango = se.race_bango
WHERE 1 = 1
AND se.data_kubun = '7'
AND se.kaisai_nen >= '2018'
AND se.kaisai_nen <= '2019'
AND se.kakutei_chakujun >= '01'
AND se.kakutei_chakujun <= '03'

赤字の部分が今回変更した行です。この部分が何をやっているのかというと、次の順序で処理しています。

  1. 走破タイムの文字列を分と秒に切り出す。
  2. 切り出した分と秒の文字列をそれぞれ数値型に変換する。
  3. 合計して秒単位の数値型に変換する。(分×60)+(秒÷10)
  4. 式に別名を付ける。

これらの処理を1つずつ具体的に説明します。

substring で文字列を切り出す

substring はPostgreSQLの関数です。プログラムにおける「関数(かんすう)」とは、渡した値を何らかの値に加工して返してくれる汎用的な部品のことです。関数に渡す値を「引数(ひきすう)」、関数が返す値を「戻り値(もどりち)」と呼びます。この substring を使って4桁の走破タイムを分と秒に分割します。

substringの書式は、

  • substring(切り出す文字列, 切り出しを開始する位置, 切り出す長さ)

となっています。この仕様に従って切り出すと分と秒はそれぞれ、

  • substring(se.soha_time, 1, 1)→「9分99秒9で設定」の9分
  • substring(se.soha_time, 2, 3)→「9分99秒9で設定」の99秒9

となるわけです。

to_number で数値型に変換する

to_number は文字列を数値型に変換するPostgreSQLの関数です。to_numberの書式は、

  • to_number(変換する文字列, 書式)

となっています。この仕様に従って数値に変換すると分と秒はそれぞれ、

  • to_number(substring(se.soha_time, 1, 1), '9')
  • to_number(substring(se.soha_time, 2, 3), '999')

となるわけです。substringの戻り値をto_numberで数値に変換しています。「書式」の部分には文字列の桁数分を "9" で埋めます。なぜ?と言うよりも、これがPostgreSQLのルールなんだと覚えてください。

合計して秒単位の数値型に変換する

そしてto_numberの戻り値を加工して秒単位の数値に仕上げます。

  • to_number(substring(se.soha_time, 1, 1), '9') * 60
  • to_number(substring(se.soha_time, 2, 3), '999') / 10

赤字が加工した部分です。この、分と秒を足し算して「秒単位の走破タイム」は完成です。

式に別名を付ける

  to_number(substring(se.soha_time, 1, 1), '9') * 60
    + to_number(substring(se.soha_time, 2, 3), '999') / 10 AS soha_time

最後に、文字列から数値型に変換した式に「AS」を使って、検索結果に対して別名を付けています。後ろの赤字がその部分です。別名を付けない場合は式がそのまま列名になります。それでは見た目が良くないでしょ?なので、ここでは元の名前の「soha_time」にしています。「AS」は省略可能ですが可読性を高めるため、私はいつもこの書き方を習慣にしてします。

PC-KEIBA Database のテーブルが全て文字列型の理由

こんな面倒くさいことするより初めから秒単位の数値型で登録しとけよ、と思うでしょう。が、大量のレコードを格納するデータベースでは固定長文字列という形式が、さまざまな場面で有利なんです。そのためJV-Dataのテーブルは全て文字列型の項目で設計しています。システムの世界によくあるトレードオフです。

グループ化して集計する

いよいよ走破タイムの平均値を集計します。赤字の部分が今回追加した部分です。

SELECT
  ra.keibajo_code
, ra.track_code
, ra.kyori
, avg(to_number(substring(se.soha_time, 1, 1), '9') * 60
    + to_number(substring(se.soha_time, 2, 3), '999') / 10) AS soha_time
FROM
  jvd_se se
INNER JOIN
  jvd_ra ra
ON  ra.kaisai_nen = se.kaisai_nen
AND ra.kaisai_tsukihi = se.kaisai_tsukihi
AND ra.keibajo_code = se.keibajo_code
AND ra.race_bango = se.race_bango
WHERE 1 = 1
AND se.data_kubun = '7'
AND se.kaisai_nen >= '2018'
AND se.kaisai_nen <= '2019'
AND se.kakutei_chakujun >= '01'
AND se.kakutei_chakujun <= '03'
GROUP BY
  ra.keibajo_code
, ra.track_code
, ra.kyori

GROUP BY句

まずGROUP BY句に、グループ化する項目を宣言します。GROUP BY句で宣言した項目は、SELECT句にも必ず書きます。書かない場合は文法として成立せず、構文エラーで実行できません。ここでは前章までの結果を、

  • 競馬場
  • トラック
  • 距離

のグループに分類して走破タイムの平均値を求めます。

グループ化のイメージ

データベースのメモリ内では次のような流れで処理しています。まず、GROUP BY句で宣言した項目の値が同じレコードを並べてグループ化します。ここでは下図の赤い囲み線と、青い囲み線がそれぞれ「同じグループ」です。その同じグループを1行にまとめて・・

avg 関数が soha_time の平均値を集計するわけです。

このグループに馬場状態を加えてさらに具体化したり、競馬場をグループから除外してトラックと距離だけのグループで抽象化したりできます。今回は平均値を集計するPostgreSQLの集計関数「avg」を使いました。集計関数には、

  • sum (グループの合計値を集計する)
  • max (グループの最大値を取得する)
  • min (グループの最小値を取得する)
  • avg (グループの平均値を集計する)
  • count (グループのレコード数を取得する)

があります。

検索結果の並べ替え

次は検索結果を見やすく整理するためにデータの並び順を指定します。

SELECT
  ra.keibajo_code
, ra.track_code
, ra.kyori
, avg(to_number(substring(se.soha_time, 1, 1), '9') * 60
    + to_number(substring(se.soha_time, 2, 3), '999') / 10) AS soha_time
FROM
  jvd_se se
INNER JOIN
  jvd_ra ra
ON  ra.kaisai_nen = se.kaisai_nen
AND ra.kaisai_tsukihi = se.kaisai_tsukihi
AND ra.keibajo_code = se.keibajo_code
AND ra.race_bango = se.race_bango
WHERE 1 = 1
AND se.data_kubun = '7'
AND se.kaisai_nen >= '2018'
AND se.kaisai_nen <= '2019'
AND se.kakutei_chakujun >= '01'
AND se.kakutei_chakujun <= '03'
GROUP BY
  ra.keibajo_code
, ra.track_code
, ra.kyori
ORDER BY
  ra.keibajo_code ASC
, ra.track_code ASC
, ra.kyori ASC

ORDER BY句

赤字の部分が今回追加した行です。検索結果の並び順を指定するには「ORDER BY句」を使います。ここでは、次の順序で並べ替えしています。

  1. 競馬場コード
  2. トラックコード
  3. 距離

並び順は項目のあとに、

  • ASC (昇順→小さい順)
  • DESC (降順→大きい順)

のいずれかを指定します。ただし、今回の並べ替えはGROUP BY句と同じ内容なので、ORDER BY句なしでも結果は同じです。

コードを名称に変換する

最後の仕上げです。コードを名称に変換します。次の、変換前と変換後の検索結果を比較してください。資料として優れているのは、どう考えても変換後のほうですね。

コード変換前の検索結果

コード変換後の検索結果

では、SQLです。赤字の部分が今回追加した部分です。JV-Data仕様書の「コード表」に基づいた、コードに対する名称を返す関数を2つ作りました。

  • to_name_keibajo_code(keibajo_code)
  • to_name_track_code2(track_code)

この自作関数は有料会員に公開しています。JV-Data仕様書の「コード表」に記載されている全コードに対応した関数のセットです。ダウンロードは→こちら

SELECT
  to_name_keibajo_code(ra.keibajo_code) AS keibajo_name
, to_name_track_code2(ra.track_code) AS track_name
, ra.kyori
, avg(to_number(substring(se.soha_time, 1, 1), '9') * 60
    + to_number(substring(se.soha_time, 2, 3), '999') / 10) AS soha_time
FROM
  jvd_se se
INNER JOIN
  jvd_ra ra
ON  ra.kaisai_nen = se.kaisai_nen
AND ra.kaisai_tsukihi = se.kaisai_tsukihi
AND ra.keibajo_code = se.keibajo_code
AND ra.race_bango = se.race_bango
WHERE 1 = 1
AND se.data_kubun = '7'
AND se.kaisai_nen >= '2018'
AND se.kaisai_nen <= '2019'
AND se.kakutei_chakujun >= '01'
AND se.kakutei_chakujun <= '03'
GROUP BY
  ra.keibajo_code
, ra.track_code
, ra.kyori
ORDER BY
  ra.keibajo_code ASC
, ra.track_code ASC
, ra.kyori ASC

SELECT文の基本のまとめ

SELECT文は、次の6つの句で構成されています。

  1. SELECT句
  2. FROM句
  3. JOIN句
  4. WHERE句
  5. GROUP BY句
  6. ORDER BY句

この他に、集計関数の演算結果でフィルタする「HAVING句」がありますが、出番はあまり無いです。なので、必要になるまで知らなくて良いです。ほとんどのSELECT文は上記の「6つの句」だけで出来ています。

SELECT文を制するものはSQLを制す

この格言は、仕事で10年以上SQLを書き続けて私が得た格言です。SQLはそれだけで1冊の本が書けるほどの種類がありますが全て覚える必要はありません。SELECT文を集中的に学習してください。他のSQLは出番が少ないので必要になったときググればよいのです。WHERE句とJOIN句で目的のレコードを得て、自由自在に検索結果を作れるようになれば、SELECT文の文法しか知らなくても「私はSQL書けます」と言っていいです。UPDATE文も、DELETE文も、SELECT文と同じWHERE句を知っていれば書けますから。

最後に。プログラミングの下手な人や初心者が陥りがちな思考は、最初に知ったやり方が1番正しいとか、この方法しか無いとか、そういった凝り固まった思考です。プログラムは料理と似ていて、作り方に正解はありません。例えばカレーの場合、自分にとって美味しくできれば材料の種類や分量、煮込んだ時間など、多少の違いはどうでもよいのです。良いモノをたくさん見て、なるべく自分で調べながら作りまくって上達を目指してください。

コメント

タイトルとURLをコピーしました