Accessで直近のデータ

質問

以下のようなテーブルがあります

[チーム名]　[日付]　[結果]
阪神　　2/10 ○
中日　　2/12 ●
阪神　　2/23 △
中日　　2/25 ●
阪神　　3/11 ●
中日　　3/12 ○
・
・
・
・

このテーブルから各チームの直近の試合5試合の
勝率を出したいと思っております。
どのようなクエリを使えばよろしいでしょうか？

ちなみに各チーム毎のクロス集計クエリを作成して
結果の日付の上位５件を表示することはできました。
この方法だと全チーム毎にクエリを作成しなければならないのですが
もっと簡潔な方法があればご享受下さい。
access初心者なものでお手柔らかにお願いします。

回答No.4

venzou · ベストアンサー

>が、しかし、accessをいじってまだ3日ほどなので
>よく内容が理解できませんでした。

直近計算のSQL文は、同じテーブルを結合している、少しトリッキーなSQL文です。初心者の方に限らず、理解しにくい内容です。（私個人としては良く使う手法なんですが・・・）

>各チームのホームとアウェイでの各直近10試合の勝率を出すにはどうすればよろしいでしょうか？

テーブル構造を下記のように仮定します。
テーブル名：試合結果
追加したフィールド：ホーム　Yes/No型

----勝率計算----
SELECT A.チーム名, A.ホーム, Avg(IIf(A.結果="○",1,IIf(A.結果="●",0,Null))) AS 勝率
FROM 試合結果 AS A
WHERE
(

select count(*)
from 試合結果 as N
where (
(N.チーム名 = A.チーム名) and
(N.ホーム = A.ホーム) and
(N.日付 <= A.日付)
)

) <= 10
GROUP BY A.チーム名, A.ホーム;
----------------

前回とは違い、「副問い合わせ」を使っています（少し読みやすい内容になったと思います）。

初心者さんということなので、解説します。
少し長いSQL文ですが、基本的な文法の組み合わせなので、一つ一つ見ていけばそれほど難しい内容ではないと思います。

まず、アルファベット大文字で書いてあるメインのSQL文について。（説明の為に、大文字と小文字で分けて書きました。全部大文字でＯＫです。）

　SELECT　 チーム名, ホーム, Avg(計算式) AS 勝率
　FROM　　 試合結果
　WHERE　 (計算式) <= 10
　GROUP BY　 チーム名, ホーム;

骨格は、基本的な集計クエリです。

勝率の計算式は、IIf関数を利用して
　"○" → 1
　"●" → 0
　"△" → Null
数値に変換し、Avgで平均を取ります。（Nullは無視されます）

WHEREの計算式が、今回の最大のポイントの、「副問い合わせ」です。

次に、小文字で書いてある副問い合わせについて
この部分で、現在のレコードが直近から何試合目に当たるのか計算しています。

　select　 count(*)
　from　 試合結果 as N
　where (
　　　(N.チーム名 = A.チーム名)　 and
　　　(N.ホーム = A.ホーム)　 and
　　　(N.日付 <= A.日付)
　)

同じテーブルを参照するので、AS を使って別名をつける必要があります。メインのSQLのテーブルを A 、副問い合わせを N としています。

(N.日付 <= A.日付)この条件で、Aの試合以前に行われた試合を選択し、count(*)で、その数をカウントします。

具体例を見た方が分かりやすいと思います。
例えば、Aのレコードが、　阪神　ホーム　3/1の試合だった場合、

　select　count(*)
　from　試合結果
　where (
　　　(チーム名 = "阪神")　and
　　　(ホーム = True)　and
　　　(日付 <= #2007/3/1#)
　)

上記のSQL文で、Aの試合以前の試合数がわかります。つまり、Aの試合が何試合目に当たるかですね。（#は日付型であることを表します。）

Accessを使って３日ほどと言う事なので、SQL文についてどれほど理解されているか分かりませんが、今後データベースを使う上で必要な知識ですので、SQLの勉強はされた方が良いと思います。

今回の内容は、初心者向けでは有りませんので、理解しにくい場合はもう少し簡単なところから始めた方が良いでしょう。「SQL入門」等のキーワードで検索すれば、初心者向けに解説しているサイトは見付かると思います。

お礼

ご回答ありがとうございます。

大変参考になりました。
疑問に思っていたことがいくつも解決して胸がすく思いです。
Accessを始めたばかりで、どうしてもデザインビューでの作成が
簡単でそちらで済ませようとしていたのですが、
SQLも挑戦してみようと思います。

ありがとうございました。

回答No.3

CHRONOS_0

>この部分の特にDcountの引数と#の意味など、
>参考になるサイトがあれば教えていただけないでしょうか。
ヘルプに詳しく載っていますよ

>各チームのホームとアウェイでの各直近10試合の
>勝率を出すにはどうすればよろしいでしょうか？
仕様があいまいですね
（１）ホーム10試合、アウェイ10試合の勝率ですか?
（２）過去10試合のホームとアウェイ別の勝率ですか?

>グループ化するところを変更すればいいとは思うのですが・・。
(1)の場合にはグループ化とDCountの第3項の式も変わります
(2)の場合なら抽出条件と勝率の式が変わります

なんにしても丸投げせずにご自分でトライした方がいいのでは
うまく行かなかったらその式をアップしてください
添削してあげます

お礼

ご回答ありがとうございました。

丸投げのつもりはなかったのですが、いつのまにかクレクレ君に
なっていました。
ご迷惑おかけしました。
もう少し勉強して出直したいと思います。
ありがとうございました。

回答No.2

CHRONOS_0

チームごとに日付の降順に順位をつけ5以下を抽出すれば直近の5試合が得られます

SELECT テーブル名.チーム名, Sum(IIf([結果]="○",1,0))/(Sum(IIf([結果]="○",1,0))+Sum(IIf([結果]="●",1,0))) AS 勝率
FROM テーブル名
WHERE (((DCount("*","テーブル名","チーム名='" & [チーム名] & "' and 日付>=#" & [日付] & "#"))<6))
GROUP BY テーブル名.チーム名;

このSQLはデザインビューで表示できます
テーブル名をそちらのものに変え、SQLビューに貼り付けてから
デザインビューで見てください

お礼

ご回答ありがとうございました。
完全に理解できたわけではないのですが、やりたいことはできました。
ありがとうございます。
ちなみに

WHERE (((DCount("*","テーブル名","チーム名='" & [チーム名] & "' and 日付>=#" & [日付] & "#"))<6))
GROUP BY テーブル名.チーム名;

この部分の特にDcountの引数と#の意味など、参考になるサイトがあれば教えていただけないでしょうか。

それと追加で質問なのですが、
上記のテーブルに[ホームアウェイ]という列を追加しました。
各チームのホームとアウェイでの各直近10試合の勝率を出すにはどうすればよろしいでしょうか？

グループ化するところを変更すればいいとは思うのですが・・。
よろしくお願いします。

回答No.1

venzou

勝率の計算って

　勝ち数÷（勝ち数＋負け数）　（引き分けは計算しない）

ですよね？

直近を計算するクエリ

----直近計算----
SELECT A.チーム名, A.日付, A.結果, Count(*) AS 直近
FROM テーブル名 AS A INNER JOIN テーブル名 AS N ON A.チーム名 = N.チーム名
WHERE N.日付 <= A.日付
GROUP BY A.チーム名, A.日付, A.結果;
----------------

勝率を計算するクエリ

----勝率計算----
SELECT Q.チーム名, Avg(IIf(Q.結果="○",1,IIf(Q.結果="●",0,Null))) AS 勝率
FROM 直近計算 AS Q
WHERE Q.直近<=5
GROUP BY Q.チーム名;
----------------

こんな感じで、クエリ２つで計算できると思います。

上記のクエリを合わせて、下記のクエリ１つでも計算できます。

----勝率計算----
SELECT Q.チーム名, Avg(IIf(Q.結果="○",1,IIf(Q.結果="●",0,Null))) AS 勝率
FROM
(
SELECT A.チーム名, A.日付, A.結果, Count(*) AS 直近
FROM テーブル名 AS A INNER JOIN テーブル名 AS N ON A.チーム名 = N.チーム名
WHERE N.日付 <= A.日付
GROUP BY A.チーム名, A.日付, A.結果
) AS Q
WHERE Q.直近<=5
GROUP BY Q.チーム名;
----------------

計算のチェックは簡単にしかしてませんので、間違ってたらごめんなさい。

お礼

ご回答ありがとうございました。
が、しかし、accessをいじってまだ3日ほどなので
よく内容が理解できませんでした。
さらに勉強して今後の参考にさせていただきます。
ありがとうございました。