SELECT文の基本

競艇予想プログラミングに関する記事を「投稿まとめ」で一覧にしてます。この記事は「自動投票」の一覧に含まれてます。一覧の順に読むとステップごとで分かりやすいです。

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

レコードの抽出条件を設定する
テーブルの結合
1. JOIN句
2. キーでテーブルを結合する
グループ化して集計する
検索結果の並べ替え
1. ORDER BY句
コードを名称に変換する
SELECT文の基本のまとめ
1. SELECT文を制するものはSQLを制す

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

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

集計期間は2020年から2021年
集計値はコース別の勝率
進入固定競走のみ

の条件でコース別データを集計することにします。

今回はあくまでSELECT文の学習が目的ですから、集計期間の妥当性やらを考えたり、集計結果の精度や品質にはこだわりません。

以下が、上記の条件を満たすレコードを抽出する第1ステップのSELECT文です。

SELECT
  ap.kyoteijo_code
, ap.teiban
, ap.chakukaisu_1
FROM
  apd_r3 ap
WHERE 1 = 1
AND ap.kaisai_nen >= '2020'
AND ap.kaisai_nen <= '2021'

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

SELECT句
FROM句
WHERE句

データベースがSELECT文を解析する順序

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

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

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

FROM句

では、その順序に従ってFROM句に、見るテーブルを書きます。コース別データを作成するには「艇番払戻」を使うのが便利です。なので、FROM句にそのテーブル名「apd_r3」を書きます。テーブル名の直後に書いてある(宣言している) “ap” はSQLの世界で「別名(べつめい)」と呼びます。別名は、

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

に使います。別名を宣言したらSQL内の項目が、どのテーブルに属しているのか「別名＋”.”(ピリオド)＋項目名」の形で全て明示します。ここでは別名を “ap” にしてますが “t1” でも他でもかまいません。

WHERE句

次はWHERE句に、データの抽出条件を書きます。各項目と値の意味は「PC-KYOTEIデータ仕様書」または「A5:SQL Mk-2」で調べながら書きます。開催年の「範囲」はサンプルのように “>=” と “<=” の演算子でその範囲を指定します。

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

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

SELECT句

最後はSELECT句に、検索結果に表示する列を書きます。今はとりあえず競艇場コード、艇番、1着回数だけ。SELECT句に複数の項目を書く場合は、カンマ(,)でつなぎます。

テーブルの結合

前章で競艇場コード、艇番、1着回数を表示しました。あとは進入固定競走を表す情報が必要です。その情報は「出走表(レースNo)」にあるので、そこから取得して進入固定競走の抽出条件を追加しましょう。

SELECT
  ap.kyoteijo_code
, ap.teiban
, ap.chakukaisu_1
FROM
  apd_r3 ap
INNER JOIN
  brd_l2 l2
ON  l2.kaisai_nen = ap.kaisai_nen
AND l2.kaisai_tsukihi = ap.kaisai_tsukihi
AND l2.kyoteijo_code = ap.kyoteijo_code
AND l2.race_no = ap.race_no
WHERE 1 = 1
AND ap.kaisai_nen >= '2020'
AND ap.kaisai_nen <= '2021'
AND l2.shinnyukotei = '1'

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

実行結果は進入固定競走が行われた競艇場のみ出てきました。

JOIN句

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

INNER JOIN (内部結合)
LEFT JOIN (外部結合)

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

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

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

kaisai_nen (開催年)
kaisai_tsukihi (開催月日)
kyoteijo_code (競艇場コード)
race_no (レースNo)

「艇番払戻」と「出走表(レースNo)」は上記の同じ名前のキー項目をそれぞれ持っているので、双方がどの項目で紐づくのかをON句に書きます。ここで先ほど登場した「テーブルの別名」が役に立つのです。

グループ化して集計する

いよいよコース別データを集計します。赤字の部分が今回追加した部分です。

SELECT
  ap.kyoteijo_code
, ap.teiban
, sum(ap.chakukaisu_1) AS rate_1
FROM
  apd_r3 ap
INNER JOIN
  brd_l2 l2
ON  l2.kaisai_nen = ap.kaisai_nen
AND l2.kaisai_tsukihi = ap.kaisai_tsukihi
AND l2.kyoteijo_code = ap.kyoteijo_code
AND l2.race_no = ap.race_no
WHERE 1 = 1
AND ap.kaisai_nen >= '2020'
AND ap.kaisai_nen <= '2021'
AND l2.shinnyukotei = '1'
GROUP BY
  ap.kyoteijo_code
, ap.teiban

GROUP BY句

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

競艇場コード
艇番

のグループに分類して1着回数の合計値を求めます。

グループ化のイメージ

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

グループ化する前の実行結果

sum 関数が chakukaisu_1 の合計値を集計するわけです。

グループ化した後の実行結果

このグループに他の項目を加えてさらに具体化したり、グループから除外して抽象化したりできます。今回は合計値を集計するPostgreSQLの集計関数「sum」を使いました。集計関数には、

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

があります。

そして1着回数の合計値をグループのレコード数で割れば勝率が出せます。

SELECT
  ap.kyoteijo_code
, ap.teiban
, sum(ap.chakukaisu_1) / count(*) * 100 AS rate_1
FROM
  apd_r3 ap
INNER JOIN
  brd_l2 l2
ON  l2.kaisai_nen = ap.kaisai_nen
AND l2.kaisai_tsukihi = ap.kaisai_tsukihi
AND l2.kyoteijo_code = ap.kyoteijo_code
AND l2.race_no = ap.race_no
WHERE 1 = 1
AND ap.kaisai_nen >= '2020'
AND ap.kaisai_nen <= '2021'
AND l2.shinnyukotei = '1'
GROUP BY
  ap.kyoteijo_code
, ap.teiban

式に別名を付ける

最後に、集計関数で編集した式に「AS」を使って、検索結果に対して別名を付けています。別名を付けない場合は式がそのまま列名になります。それでは見た目が良くないでしょ？なので、ここでは1着率を意味する「rate_1」にしています。「AS」は省略可能ですが可読性を高めるため、私はいつもこの書き方を習慣にしてします。

検索結果の並べ替え

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

SELECT
  ap.kyoteijo_code
, ap.teiban
, sum(ap.chakukaisu_1) / count(*) * 100 AS rate_1
FROM
  apd_r3 ap
INNER JOIN
  brd_l2 l2
ON  l2.kaisai_nen = ap.kaisai_nen
AND l2.kaisai_tsukihi = ap.kaisai_tsukihi
AND l2.kyoteijo_code = ap.kyoteijo_code
AND l2.race_no = ap.race_no
WHERE 1 = 1
AND ap.kaisai_nen >= '2020'
AND ap.kaisai_nen <= '2021'
AND l2.shinnyukotei = '1'
GROUP BY
  ap.kyoteijo_code
, ap.teiban
ORDER BY
  ap.kyoteijo_code ASC
, ap.teiban ASC

ORDER BY句

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

競艇場コード
艇番

並び順は項目のあとに、

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

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

コードを名称に変換する

最後の仕上げです。コードを名称に変換します。

SELECT
  to_name_kyoteijo_code(ap.kyoteijo_code) AS kyoteijo_name
, ap.teiban
, sum(ap.chakukaisu_1) / count(*) * 100 AS rate_1
FROM
  apd_r3 ap
INNER JOIN
  brd_l2 l2
ON  l2.kaisai_nen = ap.kaisai_nen
AND l2.kaisai_tsukihi = ap.kaisai_tsukihi
AND l2.kyoteijo_code = ap.kyoteijo_code
AND l2.race_no = ap.race_no
WHERE 1 = 1
AND ap.kaisai_nen >= '2020'
AND ap.kaisai_nen <= '2021'
AND l2.shinnyukotei = '1'
GROUP BY
  ap.kyoteijo_code
, ap.teiban
ORDER BY
  ap.kyoteijo_code ASC
, ap.teiban ASC

次の、変換前と変換後の検索結果を比較してください。資料として優れているのは、どう考えても変換後のほうですね。

コード変換前の実行結果

コード変換後の実行結果

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

to_name_kyoteijo_code(kyoteijo_code)

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

SELECT文の基本のまとめ

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

SELECT句
FROM句
JOIN句
WHERE句
GROUP BY句
ORDER BY句

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

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

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

最後に。プログラミングが苦手な人や初心者さんによくあるのは、最初に覚えたやり方が絶対に正しいとか、この方法しか無いとかで、考えるのをやめてしまうことです。プログラムは料理と似ていて、作り方に正解はありません。例えばカレーの場合、材料の種類や分量とか、煮込み時間とか、自分にとって美味しかったら多少の違いなんかどうでも良いですよね？なるべく自分で調べながら、良いプログラムをたくさん見て知って体験して、作りまくって上達を目指してください。

「投稿まとめ」にもどる