2021-04-20

【馬王Z SQL講座】 3つ以上の条件での抽出

1つの条件での抽出に関する記事はこちら

mare-ism.hatenablog.com

2つの条件での抽出に関する記事はこちら

mare-ism.hatenablog.com

3つ以上の条件

3つ以上の条件（条件A～条件Z）を使った基本的な構文は下記の通りです。

SELECT
    [カラム名]
FROM
    [テーブル名]
WHERE
    [条件式A] [論理演算子]
    [条件式B] [論理演算子]
    ...
    [条件式Z]
;

3つ以上の条件があったとしても、それぞれの条件式の間に論理演算子を記述していくことになります。

論理演算子の種類が混在している場合

ここでは論理演算子を AND と OR だけに限定して話を進めていきます。

WHERE句に書かれた複数の論理演算子が AND のみ、もしくは OR のみの場合は特に問題はないのですが、両方が混在している場合には注意が必要です。

今回はレースT テーブルから「条件A : 一着馬名がマツリダブロッコ」もしくは「条件B : 二着馬名がチュードサンデー」で「条件C : 競走名がエクセレント競走」のデータを抽出するケースを考えてみましょう。

これらの条件をWHERE句に書いて、次のようなクエリを作りました。

SELECT
    月日,
    競走名,
    一着馬名,
    二着馬名
FROM
    レースT
WHERE
    一着馬名 = "マツリダブロッコ" OR
    二着馬名 = "チュードサンデー" AND
    競走名 = "エクセレント競走"
;

このクエリを実行すると下図の結果が得られます。

f:id:mare_ism:20210420161508p:plain

競走名がエクセレント競走以外のデータも含まれています。どうしてでしょうか？

論理演算子の評価の順番

SQLではANDとORを混在して記述した場合はANDの方が先に評価されます。

なので、上に書いたクエリではまず「条件B : 二着馬名がチュードサンデー」かつ「条件C : 競走名がエクセレント競走」の部分が評価され（下図の水色部分）、

f:id:mare_ism:20210420162416j:plain

次に「条件A : 一着馬名がマツリダブロッコ」（下図のピンク色の部分）

f:id:mare_ism:20210420162506j:plain

との和集合（下図の濃い青色部分）

f:id:mare_ism:20210420162712j:plain

が抽出対象となります。

先に評価して欲しい条件は括弧でくくる

今回抽出したいデータは、「条件A : 一着馬名がマツリダブロッコ」もしくは「条件B : 二着馬名がチュードサンデー」（下図の水色部分）と

f:id:mare_ism:20210420163040j:plain

「条件C : 競走名がエクセレント競走」（下図のピンク色の部分）

f:id:mare_ism:20210420163349j:plain

との共通部分（下図の濃い青色部分）

f:id:mare_ism:20210420163502j:plain

なので、クエリは下記のようになります。

SELECT
    月日,
    競走名,
    一着馬名,
    二着馬名
FROM
    レースT
WHERE
    (一着馬名 = "マツリダブロッコ" OR 二着馬名 = "チュードサンデー") AND
    競走名 = "エクセレント競走"
;

このクエリを実行すると下図の結果が得られます。

f:id:mare_ism:20210420164038p:plain

このように、括弧の有無で実行結果が変わってきます。

ANDを先に評価して欲しい場合でも明示的に括弧を付けておいた方が抽出ミスを防げると思います。

2021-04-10

JRA公式データを用いたデータ分析マニュアル正誤表

先日面白そうな本を見つけたので早速購入しました。

f:id:mare_ism:20210410182201p:plain:w217:h280

cutt.co.jp

Amazonなどでは品薄状態が続いているようですね。

書籍にざっと目を通し、サンプルコード*1を一通り動かしてみたところ、いくつか誤植が見つかりました。

現時点では公式の正誤表が出ていないようなので、気付いたものからこの記事に書いていきたいと思います。

書籍

第１章

P13 「A. 通常データ種別」のチェック項目

(DIFF-BR) 生産者マスタが2回書かれている

第３章

P75 プログラムソースのファイル名

誤 : sql_test.cpp

正 : sql_test.py

第４章

P174 サンプルコード1枠目

誤

#馬体重差配列
BaTaijyuSas  = ["~-24", "-24~-16", "-16~-8", "-8~0", "0", "0~+8", "+8~+16", "+16~+24", "+24~"]

正

#馬体重差配列
BaTaijyuSas  = ["~-24", "-24", "-16", "-8", "0", "+8", "+16", "+24", "+24~"]

P174 サンプルコード2枠6行目

誤

                    elif( -24 <= ZogenSa < -16 ):
                        BaTaijyuSa = "-24~-16"

正

                    elif( -24 <= ZogenSa < -16 ):
                        BaTaijyuSa = "-24"

サンプルコード

第３章

[3.1.2]sql_test.py 4行目
[3.1.3]sql_race_dictionary.py 4行目
[3.1.3]sql_table_info.py 4行目
[3.1.4]sql_race_GI.py 4行目
[3.2.1]sql_uma_race.py 4行目
[3.2.2]sql_uma_race2.py 4行目

誤

connection = sqlite3.connect("../ecore_2019.db")

正

connection = sqlite3.connect("../ecore.db")

[3.5.7]utility_test_getZensoAgariJyunni.py 33行目

誤

    zensoAgariJyunni = U.getZensoAgariJyunni( KettoNum, zensoRACE )

正

    zensoAgariJyunni = U.getZensoAgariJyuni( KettoNum, zensoRACE )

utility.py 269行目

誤

    HaronTimeL3s = {}

正

    HaronTimeL3s = {}
    myHaronTimeL3 = 0

第４章

[4.5.5]All_BataijuSa_odds_courses.py 141行目

誤

                        BaTaijyuSa = "~-8"

正

                        BaTaijyuSa = "-8"

*1:2021/04/01にダウンロードしたものを使用しています。

2021-03-25

【馬王Z SQL講座】 2つの条件での抽出

1つの条件での抽出に関する記事はこちら

mare-ism.hatenablog.com

2つの条件

データを抽出するときに、複数の条件を使いたいことがあります。

ここで、2つの条件でデータを抽出することを考えてみましょう。

抽出対象としたいデータはどちらか片方の条件を満たしていればいいのか、それとも両方の条件を満たしていなければならないのかなどを区別する必要があります。

ここでは2つの条件として

馬名がリジェネレーション
競走コードが 21210070805

を使いたいと思います。

出走馬T テーブルからそれぞれの条件でデータ抽出したときのクエリと実行結果は以下のようになります。（出力対象のカラムは年月日、競走コード、馬名としています。）

馬名がリジェネレーション

クエリ

SELECT
    年月日,
    競走コード,
    馬名
FROM
    出走馬T
WHERE
    馬名 = "リジェネレーション"
;

実行結果

f:id:mare_ism:20210325033156p:plain

競走コードが 21210070805

クエリ

SELECT
    年月日,
    競走コード,
    馬名
FROM
    出走馬T
WHERE
    競走コード = 21210070805
;

実行結果

f:id:mare_ism:20210325033215p:plain

両方の実行結果に、馬名がリジェネレーションかつ競走コードが 21210070805 のレコードが1件含まれていますね。

論理演算

2つの条件（条件Aと条件B）を使った基本的な構文は下記の通りです。

SELECT
    [カラム名]
FROM
    [テーブル名]
WHERE
    [条件式A] [論理演算子]
    [条件式B]
;

このように2つの条件式の間に論理演算子を記述します。

では、論理演算子にはどんな種類があるんでしょうか？

AND：論理積

2つの条件の両方を満たしているレコードを抽出対象としたい場合には AND（論理積）を使います。

SELECT
    年月日,
    競走コード,
    馬名
FROM
    出走馬T
WHERE
    馬名 = "リジェネレーション" AND
    競走コード = 21210070805
;

このクエリを実行すると、下図のような結果が得られます。

f:id:mare_ism:20210325040021p:plain

OR：論理和

2つの条件の片方を満たしているレコードを抽出対象としたい場合には OR（論理和）を使います。

両方の条件を満たしているレコードも抽出対象となります。

SELECT
    年月日,
    競走コード,
    馬名
FROM
    出走馬T
WHERE
    馬名 = "リジェネレーション" OR
    競走コード = 21210070805
;

このクエリを実行すると、下図のような結果が得られます。

f:id:mare_ism:20210325040035p:plain

XOR：排他的論理和

2つの条件の片方だけを満たしているレコードを抽出対象としたい場合には XOR（排他的論理和）を使います。

両方の条件を満たしているレコードは抽出対象外となります。

SELECT
    年月日,
    競走コード,
    馬名
FROM
    出走馬T
WHERE
    馬名 = "リジェネレーション" XOR
    競走コード = 21210070805
;

このクエリを実行すると、下図のような結果が得られます。

f:id:mare_ism:20210325040045p:plain

OR を使った実行結果と違って馬名がリジェネレーションかつ競走コードが 21210070805 のレコードは抽出されていないことに注意して下さい。

2021-03-24

【馬王Z SQL講座】条件抽出

WHERE句：データの抽出条件を記述する

テーブルの中にあるデータから、何らかの条件に合致するレコードだけ抽出したいときには WHERE句を使います。

WHERE句を使った基本的な構文は下記の通りです。

SELECT
    [カラム名]
FROM
    [テーブル名]
WHERE
    [条件式]
;

数値で条件を指定

では、出走馬T テーブルから単勝配当が10,000円のデータを抽出するクエリを書いてみましょう。

SELECT
    *
FROM
    出走馬T
WHERE
    単勝配当 = 10000
;

このクエリのように、テーブルのデータを全件チェックするようなクエリは実行終了まで少し時間がかかります。*1

このクエリを実行すると、下図のような結果が得られます。*2

f:id:mare_ism:20210324071958p:plain

文字列で条件を指定

次に、出走馬T テーブルから馬名がアーモンドアイのデータを抽出するクエリを書いてみましょう。

SELECT
    *
FROM
    出走馬T
WHERE
    馬名 = "アーモンドアイ"
;

このように条件式に文字列を使う場合には「 " （ダブルクォート）」もしくは「 ' （シングルクォート）」で囲みます。

このクエリを実行すると、下図のような結果が得られます。

f:id:mare_ism:20210324072918p:plain

カラム同士の比較で抽出

抽出条件には上でやったように数値や文字列での指定もできますが、カラム同士の比較を用いることもできます。

出走馬T テーブルから入線順位と確定着順が等しくないデータを5件抽出するクエリを書いてみましょう。

出力対象のカラムは

年月日
馬名
入線順位
確定着順

とします。

SELECT TOP 5
    年月日,
    馬名,
    入線順位,
    確定着順
FROM
    出走馬T
WHERE
    入線順位 <> 確定着順
;

このクエリを実行すると、下図のような結果が得られます。

f:id:mare_ism:20210324073431p:plain

比較演算子

条件式を記述するときに使える比較演算子には以下のようなものがあります。

比較演算子	使い方	説明	備考
=	A = B	AとBは等しい
<>	A <> B	AとBは等しくない	!= は使えない
<	A < B	AはBより小さい
<=	A <= B	AはB以下
>	A > B	AはBより大きい
>=	A >= B	AはB以上

では、出走馬T テーブルから馬体重が630kg以上のデータを重い順に抽出するクエリを書いてみましょう。

出力対象のカラムは

年月日
馬名
馬体重

とします。

SELECT
    年月日,
    馬名,
    馬体重
FROM
    出走馬T
WHERE
    馬体重 >= 630
ORDER BY
    馬体重 DESC
;

このクエリを実行すると、下図のような結果が得られます。

f:id:mare_ism:20210324083057p:plain

やっぱりショーグンは大きいですね！

*1:ブログ主の環境では30～40秒ほどかかりました。

*2:馬王Zのデータ取得状況によっては結果が一致しないことがあります。

2021-03-23

【馬王Z SQL講座】データの並び替え

SELECT文でデータの取得

まずは前回の復習として、競走馬マスタテーブルから

血統登録番号
馬名

を5行取得してみましょう。

SELECT TOP 5
    血統登録番号,
    馬名
FROM
    競走馬マスタ
;

このクエリを実行すると、下図のような結果が得られます。*1

f:id:mare_ism:20210323060855p:plain

この結果はたまたま競走馬マスタテーブルの先頭に格納されていたデータを取得しただけなので、あまり意味がありません。

ORDER BY 句：昇順に並び替え

SELECT文で取得した結果を並び替えるには ORDER BY 句を使います。

ORDER BY 句に並び替えに使いたいカラムを書きます。

SELECT TOP 5
    血統登録番号,
    馬名
FROM
    競走馬マスタ
ORDER BY
    血統登録番号
;

このクエリを実行すると、血統登録番号が昇順（小さい順）に5件データが出力されます。*2

f:id:mare_ism:20210323061050p:plain

DESC：降順に並び替え

降順（大きい順）に並び替えたい場合は ORDER BY 句で指定したカラムの後ろに DESC を付け加えます。

SELECT TOP 5
    血統登録番号,
    馬名
FROM
    競走馬マスタ
ORDER BY
    血統登録番号 DESC
;

このクエリを実行すると、血統登録番号が降順に5件データが出力されます。

f:id:mare_ism:20210323062259p:plain

同値のデータがあった場合

競走馬マスタテーブルから、生年月日を降順に5件取得するクエリを書いてみましょう。

SELECT TOP 5
    血統登録番号,
    馬名,
    生年月日
FROM
    競走馬マスタ
ORDER BY
    生年月日 DESC
;

このクエリを実行すると、下図のデータが出力されました。

f:id:mare_ism:20210323064328p:plain

TOP 5 と指定したはずなのに6行ありますね。何故でしょう？

生年月日が大きい方から4番目のデータは 2019/06/09 で、同じ生年月日の馬は3頭います。

このように TOP と ORDER BY を併用すると、上位n番目（nは TOP で指定した数）に同値のデータがあった場合n件より多いデータが出力されるので注意が必要です。*3

上位n番目に同値のデータがない場合

挙動を理解するために、同様の条件で上位9件取得してみましょう。

SELECT TOP 9
    血統登録番号,
    馬名,
    生年月日
FROM
    競走馬マスタ
ORDER BY
    生年月日 DESC
;

このクエリを実行すると、下図のデータが出力されました。

f:id:mare_ism:20210323065945p:plain

生年月日が 2019/06/09 のデータが3件、2019/06/06 のデータが2件ありますが、9件目には同値のデータがないので指定したとおり9件出力されています。

*1:馬王Zのデータ取得状況によっては結果が一致しないことがあります。

*2:出力結果が並び替えられているだけで、テーブル内のデータに変化はありません。

*3:MySQLなどで使える LIMIT 句とは挙動が違います。

2021-03-23

【馬王Z SQL講座】 SELECT文でデータを取得

SELECT文：テーブルのデータを取得する

SQLの初歩はテーブルからデータを取得するSELECT文です。

一番シンプルなSELECT文は

SELECT * FROM [任意のテーブル名];

ですが、これだと指定したテーブルの全レコードを取得しようとします。

馬王Z内のほとんどのテーブルは大量のレコードが入っているため、全レコードを取得するクエリを実行すると処理が終わるまでにかなりの時間がかかったり、場合によっては下図のようなメモリ不足の例外が発生してしまいます。

f:id:mare_ism:20210323050829p:plain

できる限り、そんなクエリは実行しないようにしましょう。

TOP：テーブルの先頭n行を取得する

テーブルの中にどんなデータが入っているか確認するときはTOPを使用します。

SELECTの後ろにTOP n（nは取得したい行数）を記述するとテーブルの先頭n行を取得することができます。

では、競走馬マスタテーブルから先頭5行を取得してみましょう。

SELECT TOP 5 * FROM 競走馬マスタ;

このクエリを実行すると、下図のような結果が出力されます。*1

f:id:mare_ism:20210323051155p:plain

取得する列を指定する

テーブルにはたくさんの列があります。

先程書いたSELECT文の $*$ （アスタリスク）は「全ての列」という意味になります。

取得したい列を指定するには、 $*$ の代わりに列名を書きます。複数の列を指定したい場合はカンマで区切って列挙します。

では、競走馬マスタテーブルから

血統登録番号
馬名
生年月日

を5行分取得するクエリを書いてみましょう。

SELECT TOP 5 血統登録番号, 馬名, 生年月日 FROM 競走馬マスタ;

これでもいいのですが、クエリが少し長くなってきました。

横に長いクエリは読みづらいので、可読性を上げるため改行とインデント（文字下げ）を追加します。

SELECT TOP 5
    血統登録番号,
    馬名,
    生年月日
FROM
    競走馬マスタ
;

SQLでは改行やインデントは実行時に無視されるので、どちらのクエリでも同じ結果が得られます。

f:id:mare_ism:20210323052010p:plain

*1:馬王Zのデータ取得状況によっては結果が一致しないことがあります。

2021-03-23

【馬王Z SQL講座】データベース操作機能の呼び出し方

馬王Zのデータベース操作機能

競馬ソフトの馬王Zでは[ツール] > [データベース操作]からSQLによるデータ操作機能を呼び出すことができます。

f:id:mare_ism:20210323042758p:plain

すると、下図のようなデータベース操作の画面が開きます。

f:id:mare_ism:20210323042931p:plain

馬王Zにあるデータ

馬王Zのデータベースにはたくさんのテーブルがあり、その中に様々なデータが入っています。

どんなテーブルがあるかは、データベース操作画面右上のドロップダウンリストから確認することができます。

f:id:mare_ism:20210323043202p:plain

競走馬T
レースT
競走馬マスタ
騎手マスタ
調教師マスタ

などのテーブルがあることが分かります。

テーブルのカラム

各テーブルには1つ以上のカラム（列）があります。

ドロップダウンリストでテーブルを指定すると、そのテーブルに含まれるカラムの一覧が下の枠に表示されます。

f:id:mare_ism:20210323043901p:plain

出走馬T テーブルには

データ区分
データ作成年月日
競走コード
馬番
枠番
馬名
血統登録番号

などのカラムがあることが分かります。

SQLのクエリを実行してみよう

SQLとは「Structured Query Language」の頭文字をとった略称で、データベースを操作するための言語です。

SQLで書かれた命令文のことをクエリと呼びます。

ではここで、とてもシンプルなクエリを書いて実行してみましょう。

SQLと書かれた枠の中に、

SELECT 1;

と書いて、右にある[実行]ボタンを押します。

f:id:mare_ism:20210323045254p:plain

画面の下側に1と表示されればOKです。