SQLで集計する

前回まででJ１リーグの試合情報を入力できるようになったので、順位表を作っていこうと思います。

J1リーグの順位は以下の順序で決まります。

1. 試合ごとの勝点 (勝利: 3, 引分: 1, 敗北: 0) の合計
2. 試合ごとの得失点差の合計
3. 試合ごとの得点の合計
4. １から３までが同順位のチーム同士の直接対戦成績 (上記１から３に準ずる)
5. 試合ごとに犯した反則の種類と数によって決まる反則ポイントの合計
6. 抽選

４から後の順位決定方法はちょっと面倒そう、というか抽選に至ってはデータベースで検索できる質のものではないので、とりあえず１から３までの条件で順位表を作っていきたいと思います。

現在広く使われているリレーショナルデータベース (RDB) は情報をテーブル (表) の形で記録しています。

SELECT文

今回使用している試合情報データベースであれば、試合番号 (match_no)、節 (section)、試合開催日 (date)、ホームチーム名 (home)、アウェイチーム名 (away)、ホームチーム得点 (goals_for)、ホームチーム失点 (goals_against)がこの順でカラム (列) として並んでいます。

そしてテーブルの各行には試合ごとの上記の値が並んでいます。

上の画像は前回までに作成したプログラムで使用しているデータベースの内容をMySQLのコンソールからselect文を発行して表示させたものです。

この画像の１行目に表示されている「select * from matches;」が発行したselect文です。

これを簡単に説明すると、以下のようになります。

select *: 参照先テーブルのすべてのカラムを指定しています。

from matches: 参照先テーブルとしてmatchesを指定しています。

最後のセミコロンはselect文の終わりを示すものです。

このselect文により新しいテーブルが作られ、コンソールの機能として表示されています。

selectの後ろにカラム名を並べると指定されたカラムを並べた順序にして表を作り出します。

上の画像は「select section, date, home, goals_for, goals_against, away from matches」というselect文を発行したもので、表のカラム順が指定した順になっており、指定されていないmatch_noは出力されていないことがわかります。

WHERE句

順位表には直接関係ありませんが、select文の機能として基本的な行の選択についてここで見ておきます。

次に川崎Fがホームとなっている試合だけで構成された表を作ってみます。

「where 条件式」という指定を「from テーブル名」の後に記述すると条件式に該当する行だけが抽出された表となります。

条件式にはand/orも使用することができます。

次の例は川崎Fがホームまたはアウェイの試合を取り出した例です。

前回までのプログラムで同じ節や同じ日に同じチームが試合をしていないかを確認する処理ではwhere句の機能を使っていました。

これまでは使ってきませんでしたが、条件式には数式を使用することもできます。

うえの画像ではwhereの条件式として「abs(goals_for - goals_against) >= 3」という式で得点と失点の差の絶対値が３以上の試合を取り出しています。

カラムでの計算式の利用

where句に関してはここまでにしておいて、本題の順位表の作成に戻ります。

カラム指定にはカラム名のほかに計算式を使用することもできます。

次にホームチームの勝点を表に追加してみます。

計算式が長すぎて行が折り返してしまい、見にくくなってしまいましたが、簡単に説明します。

select文のカラム指定で使用できる式の一つにif式があります。if式は「if (条件, 正, 否)」の形式で記述し、条件が成り立つ場合には正の値、成り立たない場合は否の値が出力されます。

ここでは「if (goals_for > goals_against, 3, if (goals_for = goals_against, 1, 0))」という式が指定されているのでホームチームの得点がアウェイチームの得点より大きければ３、同じ場合には１、それ以外は０が式の値となります。

ところで、カラム名や式が長いと、表示した際に折り返すだけでなくいろいろと扱いにくい場合があります。このため、カラム名や式には「カラム名または式 as 名前」という形で別名を付けることができます。

上の画像ではsectionに節、dateに開催日、goals_forに得点、goals_againstに失点、勝点を計算する式に勝点という別名を付けたので、表が見やすくなったと思います。

それでは順位を決めるのに必要な得失点差のカラムも作成し、おまけとして勝利、引分、敗北のカラムも作っておきます。

SUM関数とGROUP BY句

ではこれを集計します。

カラム指定では値の合計を計算するsumという関数が用意されています。

試しにgoals_for (得点) と goals_against (失点) の合計を見てみましょう。

これまでのすべての試合の得点と失点の総合計が得られましたが、これでは役に立たないので、チームごとの総合計を計算します。

チームごとという場合にはgroup by句を使用します。「group by カラム名」と「from テーブル名」の後 (where句がある場合はwhere句の後) の記述すると指定したカラムの値が同じものだけで計算を行うようになります。

これとこれまでに見てきた計算式とを組み合わせることで順位表に必要な集計ができます。

と、ここまでで完成かと思いきや、残念ながら足りないところがあります。

ホームチームの成績だけをまとめたものなので、アウェイチームとして試合を行った際の成績が反映されていません。

アウェイチームとして試合を行った際の成績表は以下のようになります。

要は先ほどのselect文でgoals_forとgoals_againstを入れ替えて、awayカラムで集計したものになります。

最終的な順位表を作るにはこれら２個の表をまとめる必要があります。

ホームチームとしての試合成績とアウェイチームとして試合成績を一つの表にするにはいくつかの方法がありますが、これら２個の表を縦に連結して１個の長い表にしたうえで再度集計を行うというのが一つです。 

表を縦に連結する際に注意しなければいけないのは、カラム名が同じカラムが同じ列に並べられるということです。

表の縦連結には「UNION ALL」が使用できます。

select文の「from テーブル名」のテーブル名の代わりに「テーブル名 UNION ALL テーブル名」と記述すると、２個のテーブルが１個のテーブルに縦連結されて処理されます。

また、テーブル名の代わりにselect文を指定するとselect文の処理結果として作成されるテーブルが使用されます。

ここで上げた２個の成績表、得点から後はすべて名前がそろっていますが、先頭のチーム名のところがhomeとawayというようにカラム名が異なっています。 このため、これらの表を連結するとhome/awayのカラムの扱いが保証されないので、homeとawayの両方のカラムに共通の名前を付ける必要があります。

SQL文が長くなったため、表が十分に見えていませんが、２個の表が縦に連結しています。画像の最後近くでFC東京がたまたま２回出現していますが、ここが２個の表の切れ目になります。

さらにもう一度sum関数を適用してgroup by句で集計すると成績表が完成します。

あまりにSQL文が長いので追いかけるのが嫌になるかもしれませんが、SQL文は後程別途説明しますので、ここではとりあえずできていることを確認してください。

ORDER BY句による並べ替え

いま作っているのは順位表なので、成績順に並べる必要があります。

行の並べ替えにはorder by句を使用します。

order by句での並べ替えはgroup by句の後に並べ替えの優先順にカラム名を並べていきます。

既定では昇順での並べ替えになりますので、降順での並べ替えが必要な場合にはカラム名の後にdescを記入します。

今回は勝点、得失点差、得点の順ですべて降順ですので「order by sum(勝点) desc, sum(得失点差) desc, sum(得点) desc」となります。

以上で順位表の完成です。

最後に今回使ったSQL文をもう一度振り返ります。

1. select チーム, sum(勝点), sum(勝), sum(分), sum(敗), sum(得点), sum(失点), sum(得失点差) 
2. from (
3. select home as チーム, sum(goals_for) as 得点, sum(goals_against) as 失点, sum(if(goals_for > goals_against, 3, if(goals_for = goals_against, 1, 0))) as 勝点, sum(goals_for - goals_against) as 得失点差, sum(if (goals_for > goals_against, 1, 0)) as 勝, sum(if (goals_for = goals_against, 1, 0)) as 分, sum(if (goals_for < goals_against, 1, 0)) as 敗 from matches group by home 
4. union all 
5. select away as チーム, sum(goals_against) as 得点, sum(goals_for) as 失点, sum(if(goals_against > goals_for, 3, if(goals_for = goals_against, 1, 0))) as 勝点, sum(goals_against - goals_for) as 得失点差, sum(if (goals_against > goals_for, 1, 0)) as 勝, sum(if (goals_for = goals_against, 1, 0)) as 分, sum(if (goals_against < goals_for, 1, 0)) as 敗 from matches group by away
6. ) cmb 
7. group by チーム order by sum(勝点) desc, sum(得失点差) desc, sum(得点) desc;

１行目で順位表に出力するカラムを定義しています。チーム名、チームごとの勝点合計、勝利数合計、引分数合計、敗北数合計、得点合計、失点合計、得失点差合計の順に出力するという指定です。

２行目は集計に使用するテーブルの指定ですが、今回はselect文などを用いて生成したテーブルを使用するので、まず「(」を書いておきます。

３行目はホームチームを対象としてチームごとに集計した成績テーブルを生成するselect文です。

４行目は３行目のselect文で生成されたテーブルに５行目のselect文で生成されたテーブルを縦連結することを指示するunion句です。

５行目はアウェイチームを対象としてチームごとに集計した成績テーブルを生成するselect文です。

６行目はテーブルを生成する分の終わりを示す「)」と生成されたテーブルにつける一時的な名前です。今回のSQL文ではここで指定した名前を使用していませんが、何らかの名前を付けないとエラーになります。また、tableやdatabaseのようにSQLのキーワードも使えません。

７行目は集計方法の指定で、チームごとに集計を行い、勝点、得失点差、得点の順で降順で並べ替えることを指定しています。

順位表はできましたので、あとはこれからHTMLを作成するだけで前回までのWebアプリケーションに組み込むことができます。