Mysqlレコード集計について

Mysqlレコード集計について

以下のようなテーブルがあります。
勝った試合は[win]に1が入っています。
|seq| date |win|
| 1|2010/04/01| 1|
| 2|2010/04/02| 0|
| 3|2010/04/03| 1|
| 4|2010/04/04| 1|
| 5|2010/04/05| 1|
[date] ASC
3連勝以上した回数をsqlで集計したいです。
sqlをいろいろ調べてみましたが、わかりませんでした。
わかる方いらっしゃれば教えてください。
よろしくお願いいたします。

No.1 回答者： noname#111181 回答日時： 2010/04/11 14:42

MySQL単独では難しいと思います。

MySQLを制御する言語（PHP、Javaなど）で制御する方法ではいけませんか？　ご確認ください。

No.2 回答者： root139 回答日時： 2010/04/11 17:22

下記の様に仮定して、考えました。

・win には必ず0(負け)か1(勝ち)の値が入っている
・Dateは重複が無い

---------------------------------------------------------
SELECT COUNT(*) FROM t_game a
WHERE
(
win = 0 AND
(
SELECT COUNT(*) FROM t_game b
WHERE b.win = 1 AND a.date < b.date AND
NOT EXISTS (
SELECT 1 FROM t_game c WHERE c.win = 0 AND a.date < c.date AND c.date < b.date
)
) >= 3
) OR (
win = 1 AND
NOT EXISTS (SELECT 1 FROM t_game d WHERE d.date < a.date) AND
(
SELECT COUNT(*) FROM t_game e
WHERE e.win = 1 AND a.date < e.date AND
NOT EXISTS (
SELECT 1 FROM t_game f WHERE f.win = 0 AND a.date < f.date AND f.date < e.date
)
) >= 2
)
;
---------------------------------------------------------

MySQLでは試してませんが、ver 5.1 では動作すると思います。


考え方としては下記の条件に当てはまるものを合計しました。

・winが0で、次のwinが0の行との間に在る行の数が3以上の行
・先頭行で、winが1で、次のwinが0の行との間に在る行の数が2以上の行

参考URL：http://codezine.jp/article/detail/1076?p=2

この回答への補足

root139様ありがとうございます
回答ありがとうございました。
お蔭様で本来望んでいる通りの結果を出す事が出来ました。

もうひとつ御教授いただけると幸いです。
|seq|player_id|　　date　　|win|
|　1|　　　　1|2010/04/01|　0|
|　2|　　　　1|2010/04/02|　1|
|　3|　　　　1|2010/04/03|　1|
|　4|　　　　1|2010/04/04|　1|
|　5|　　　　1|2010/04/05|　1|
|　6|　　　　1|2010/04/06|　1|
|　7|　　　　1|2010/04/07|　1|
|　8|　　　　2|2010/04/01|　1|
|　9|　　　　2|2010/04/02|　1|
| 10|　　　　2|2010/04/03|　1|

[player_id]=勝利投手
3連勝毎にボーナスポイント1を与えたいと考えております。
（5連勝1ポイント・6連勝2ポイント・7連勝2ポイント）
sqlで可能でしょうか？
以下のような形で出力できればと考えております。

|player_id|point|
|　　　　1|　　 2|
|　　　　2|　　 1|

よろしくお願いいたします。

補足日時：2010/04/12 18:55

この回答へのお礼

root139様ありがとうございます
回答ありがとうございました。
お蔭様で本来望んでいる通りの結果を出す事が出来ました。

もうひとつ御教授いただけると幸いです。
|seq|player_id|　　date　　|win|
|　1|　　　　1|2010/04/01|　0|
|　2|　　　　1|2010/04/02|　1|
|　3|　　　　1|2010/04/03|　1|
|　4|　　　　1|2010/04/04|　1|
|　5|　　　　1|2010/04/05|　1|
|　6|　　　　1|2010/04/06|　1|
|　7|　　　　1|2010/04/07|　1|
|　8|　　　　2|2010/04/01|　1|
|　9|　　　　2|2010/04/02|　1|
| 10|　　　　2|2010/04/03|　1|

[player_id]=勝利投手
3連勝毎にボーナスポイント1を与えたいと考えております。
（5連勝1ポイント・6連勝2ポイント・7連勝2ポイント）
sqlで可能でしょうか？
以下のような形で出力できればと考えております。

|player_id|point|
|　　　　1|　　 2|
|　　　　2|　　 1|

よろしくお願いいたします。

お礼日時：2010/04/13 11:09

No.3 回答者： yambejp 回答日時： 2010/04/12 13:57

こんな感じでいけませんか？

ちなみにテーブル名＝hoge、dateは予約語なのでdとしました

SELECT count(*) from(
SELECT
coalesce((
SELECT MAX(d) FROM hoge where h1.d>d and h1.win!=win
) ,d - interval 1 day)AS pre
,win,count(*) as c
FROM hoge AS h1
group by pre,win) as sub
where win=1 and c>=3

この回答へのお礼

yambejp様ありがとうございます

これだけ短いSQLで実現可能だとは・・・
ただ私には、SQL文が理解できませんでした・・・

ありがとうございました

お礼日時：2010/04/12 18:59

No.4 回答者： yambejp 回答日時： 2010/04/13 11:53

>SQL文が理解できませんでした・・・

すみません。はしょりすぎましたね。
くわえて、バグがありましたのでついでになおします。
以下のように段階を分けて組んでいけばよいでしょう。

(1)まず、自分より以前でwinが自分以外のものの最大値をえます。
SELECT(
SELECT MAX(d) FROM hoge where h1.d>d and h1.win!=win
)AS pre
,win
FROM hoge AS h1

こうすると連勝、連敗中の直前データがpreに表示されます。

(2)preとwinの組み合わせで連勝連敗記録を拾います。

SELECT
(
SELECT MAX(d) FROM hoge where h1.d>d and h1.win!=win
) AS pre
,win,count(*) as c
FROM hoge AS h1
group by pre,win

(3)これに条件を設定(3連勝以上=winが1でcが3以上)してカウントすればよいでしょう。
SELECT count(*) FROM(
SELECT
(
SELECT MAX(d) FROM hoge where h1.d>d and h1.win!=win
)AS pre
,win,count(*) as c
FROM hoge AS h1
group by pre,win
) AS sub
WHERE win=1 AND c>=3

この回答へのお礼

yambejp様ありがとうございます。
補足までつけていただきありがとうございました。
もうひとつ御教授いただけると幸いです。
|seq|player_id|　　date　　|win|
|　1|　　　　1|2010/04/01|　0|
|　2|　　　　1|2010/04/02|　1|
|　3|　　　　1|2010/04/03|　1|
|　4|　　　　1|2010/04/04|　1|
|　5|　　　　1|2010/04/05|　1|
|　6|　　　　1|2010/04/06|　1|
|　7|　　　　1|2010/04/07|　1|
|　8|　　　　2|2010/04/01|　1|
|　9|　　　　2|2010/04/02|　1|
| 10|　　　　2|2010/04/03|　1|

[player_id]=勝利投手
3連勝毎にボーナスポイント1を与えたいと考えております。
（5連勝1ポイント・6連勝2ポイント・7連勝2ポイント）
sqlで可能でしょうか？
以下のような形で出力できればと考えております。

|player_id|point|
|　　　　1|　　 2|
|　　　　2|　　 1|

よろしくお願いいたします。

お礼日時：2010/04/13 13:39

No.5 ベストアンサー 回答者： yambejp 回答日時： 2010/04/13 16:30

前回とほぼロジックは同じです。

SELECT player_id,sum(truncate(c/3,0)) as point from
(
SELECT(
SELECT MAX(d) FROM hoge where h1.d>d and h1.win!=win and h1.player_id=player_id
)AS pre
,win,player_id,count(*) as c
FROM hoge AS h1
GROUP BY pre,win,player_id
) as sub
where win=1
group by player_id

この回答へのお礼

yambejp様
集計することができました。
本当にありがとうございました。

お礼日時：2010/04/14 11:43