SQLでボートレース新概念データを取得するクエリを書いた

はじめに

もともとデータベースにボートレースの過去のレース情報と選手情報を保存していたので、そのデータベースから新概念データを取得するSQLのクエリを書いてみました。

データベースの構造

データベースには現在以下の4つのテーブルがあります。

1. レーサーテーブル
2. 出走表テーブル
3. 進入コーステーブル
4. 結果テーブル

以下1つずつテーブルの構造の内、必要な列名のみを説明します。

レーサーテーブル

MRacer

出走表テーブル

DCard

進入コーステーブル

DCourse

結果テーブル

DResult

分かりづらいかもしれませんが、レースナンバーは第何レースか、ボートナンバーは何号艇かのデータを持つ列です。

新概念データを取得するクエリ

コードは汚いですが、新概念データを取得するためのクエリを載せます。

パラメータは以下の通りです。

SELECT
T2.RacerID AS RacerID,
T2.Name AS Name,
T2.CountCourseNO1 AS CountCourseNO1,
T2.CountCourseNO1Nige/T2.CountCourseNO1*100 AS RateCourseNO1Nige,
T2.CountCourseNO1Makurare/T2.CountCourseNO1*100 AS RateCourseNO1Makurare,
T2.CountCourseNO1Sasare/T2.CountCourseNO1*100 AS RateCourseNO1Sasare,
T2.CountCourseNO1Makurizasare/T2.CountCourseNO1*100 AS RateCourseNO1Makurizasare,
T2.CountCourseNO2 AS CountCourseNO2,
T2.CountCourseNO2Nigashi/T2.CountCourseNO2*100 AS RateCourseNO2Nigashi,
T2.CountCourseNO2Makuri/T2.CountCourseNO2*100 AS RateCourseNO2Makuri,
T2.CountCourseNO2Sashi/T2.CountCourseNO2*100 AS RateCourseNO2Sashi,
T2.CountCourseNO3 AS CountCourseNO3,
T2.CountCourseNO3Makuri/T2.CountCourseNO3*100 AS RateCourseNO3Makuri,
T2.CountCourseNO3Sashi/T2.CountCourseNO3*100 AS RateCourseNO3Sashi,
T2.CountCourseNO3Makurizashi/T2.CountCourseNO3*100 AS RateCourseNO3Makurizashi,
T2.CountCourseNO4 AS CountCourseNO4,
T2.CountCourseNO4Makuri/T2.CountCourseNO4*100 AS RateCourseNO4Makuri,
T2.CountCourseNO4Sashi/T2.CountCourseNO4*100 AS RateCourseNO4Sashi,
T2.CountCourseNO4Makurizashi/T2.CountCourseNO4*100 AS RateCourseNO4Makurizashi,
T2.CountCourseNO5 AS CountCourseNO5,
T2.CountCourseNO5Makuri/T2.CountCourseNO5*100 AS RateCourseNO5Makuri,
T2.CountCourseNO5Sashi/T2.CountCourseNO5*100 AS RateCourseNO5Sashi,
T2.CountCourseNO5Makurizashi/T2.CountCourseNO5*100 AS RateCourseNO5Makurizashi,
T2.CountCourseNO6 AS CountCourseNO6,
T2.CountCourseNO6Makuri/T2.CountCourseNO6*100 AS RateCourseNO6Makuri,
T2.CountCourseNO6Sashi/T2.CountCourseNO6*100 AS RateCourseNO6Sashi,
T2.CountCourseNO6Makurizashi/T2.CountCourseNO6*100 AS RateCourseNO6Makurizashi
FROM (
    SELECT
    T1.RacerID AS RacerID,
    MAX(T1.Name) AS Name,
    SUM(CASE WHEN T1.CourseNO=1 THEN 1 ELSE 0 END) AS CountCourseNO1,
    SUM(CASE WHEN T1.CourseNO=1 AND T1.Ranking=1 AND T1.Technique='逃げ' THEN 1 ELSE 0 END) AS CountCourseNO1Nige,
    SUM(CASE WHEN T1.CourseNO=1 AND T1.Ranking<>1 AND T1.Technique='まくり' THEN 1 ELSE 0 END) AS CountCourseNO1Makurare,
    SUM(CASE WHEN T1.CourseNO=1 AND T1.Ranking<>1 AND T1.Technique='差し' THEN 1 ELSE 0 END) AS CountCourseNO1Sasare,
    SUM(CASE WHEN T1.CourseNO=1 AND T1.Ranking<>1 AND T1.Technique='まくり差し' THEN 1 ELSE 0 END) AS CountCourseNO1Makurizasare,
    SUM(CASE WHEN T1.CourseNO=2 THEN 1 ELSE 0 END) AS CountCourseNO2,
    SUM(CASE WHEN T1.CourseNO=2 AND T1.Technique='逃げ' THEN 1 ELSE 0 END) AS CountCourseNO2Nigashi,
    SUM(CASE WHEN T1.CourseNO=2 AND T1.Ranking=1 AND T1.Technique='まくり' THEN 1 ELSE 0 END) AS CountCourseNO2Makuri,
    SUM(CASE WHEN T1.CourseNO=2 AND T1.Ranking=1 AND T1.Technique='差し' THEN 1 ELSE 0 END) AS CountCourseNO2Sashi,
    SUM(CASE WHEN T1.CourseNO=3 THEN 1 ELSE 0 END) AS CountCourseNO3,
    SUM(CASE WHEN T1.CourseNO=3 AND T1.Ranking=1 AND T1.Technique='まくり' THEN 1 ELSE 0 END) AS CountCourseNO3Makuri,
    SUM(CASE WHEN T1.CourseNO=3 AND T1.Ranking=1 AND T1.Technique='差し' THEN 1 ELSE 0 END) AS CountCourseNO3Sashi,
    SUM(CASE WHEN T1.CourseNO=3 AND T1.Ranking=1 AND T1.Technique='まくり差し' THEN 1 ELSE 0 END) AS CountCourseNO3Makurizashi,
    SUM(CASE WHEN T1.CourseNO=4 THEN 1 ELSE 0 END) AS CountCourseNO4,
    SUM(CASE WHEN T1.CourseNO=4 AND T1.Ranking=1 AND T1.Technique='まくり' THEN 1 ELSE 0 END) AS CountCourseNO4Makuri,
    SUM(CASE WHEN T1.CourseNO=4 AND T1.Ranking=1 AND T1.Technique='差し' THEN 1 ELSE 0 END) AS CountCourseNO4Sashi,
    SUM(CASE WHEN T1.CourseNO=4 AND T1.Ranking=1 AND T1.Technique='まくり差し' THEN 1 ELSE 0 END) AS CountCourseNO4Makurizashi,
    SUM(CASE WHEN T1.CourseNO=5 THEN 1 ELSE 0 END) AS CountCourseNO5,
    SUM(CASE WHEN T1.CourseNO=5 AND T1.Ranking=1 AND T1.Technique='まくり' THEN 1 ELSE 0 END) AS CountCourseNO5Makuri,
    SUM(CASE WHEN T1.CourseNO=5 AND T1.Ranking=1 AND T1.Technique='差し' THEN 1 ELSE 0 END) AS CountCourseNO5Sashi,
    SUM(CASE WHEN T1.CourseNO=5 AND T1.Ranking=1 AND T1.Technique='まくり差し' THEN 1 ELSE 0 END) AS CountCourseNO5Makurizashi,
    SUM(CASE WHEN T1.CourseNO=6 THEN 1 ELSE 0 END) AS CountCourseNO6,
    SUM(CASE WHEN T1.CourseNO=6 AND T1.Ranking=1 AND T1.Technique='まくり' THEN 1 ELSE 0 END) AS CountCourseNO6Makuri,
    SUM(CASE WHEN T1.CourseNO=6 AND T1.Ranking=1 AND T1.Technique='差し' THEN 1 ELSE 0 END) AS CountCourseNO6Sashi,
    SUM(CASE WHEN T1.CourseNO=6 AND T1.Ranking=1 AND T1.Technique='まくり差し' THEN 1 ELSE 0 END) AS CountCourseNO6Makurizashi
    FROM (
        SELECT 
        MRacer.RacerID AS RacerID,
        MRacer.Name AS Name,
        DCourse.CourseNO AS CourseNO,
        DResult.Ranking AS Ranking ,
        DResult.Technique AS Technique
        FROM MRacer
        INNER JOIN DCard
        ON MRacer.RacerID=DCard.RacerID
        INNER JOIN DCourse
        ON DCard.RaceDate=DCourse.RaceDate
        AND DCard.JoCD=DCourse.JoCD
        AND DCard.RaceNO=DCourse.RaceNO
        AND DCard.BoatNO=DCourse.BoatNO
        INNER JOIN DResult
        ON DCard.RaceDate=DResult.RaceDate
        AND DCard.JoCD=DResult.JoCD
        AND DCard.RaceNO=DResult.RaceNO
        AND DCard.BoatNO=DResult.BoatNO
        WHERE DCard.RaceDate>=p_RaceDateFrom
        AND DCard.RaceDate<=p_RaceDateTo
        ORDER BY RacerID
    ) AS T1
    GROUP BY T1.RacerID
) AS T2

まとめ

クエリを書いてボートレース新概念データを取得出来ました。