SQLでORDER BYをして集計をした後に勝率順に並べたものを抽出する方法を教えてください。

テーブルのレコードには各ユーザの試合ごとの結果が入っています。
サブクエリで勝ち負けそれぞれを集計しようとしたところで行き詰りました。
*postgreSQLを使用

TABLE
userID|result |
---------------------|
1   |win   |
---------------------|
1   |win   |
---------------------|
2   |win   |
---------------------|
1   |lose  |
---------------------|
3   |lose  |
---------------------|
3   |win   |
---------------------|
1   |lose  |
---------------------|
5   |lose  |



こんな感じのランキング(勝率順)
userID|勝率
------
2 |…%
------
1 |…%
------
3 |…%
------
5 |…%

回答の条件
  • 1人2回まで
  • 登録:2009/06/26 18:32:39
  • 終了:2009/06/29 09:59:34

ベストアンサー

id:HALSPECIAL No.1

HALSPECIAL回答回数407ベストアンサー獲得回数862009/06/26 19:04:29

ポイント57pt

こちらでいかがでしょうか?

テーブル名を「テーブル1」としています。


SELECT *
FROM (
	SELECT userID, (winCountSum / (loseCountSum + winCountSum) * 100) AS winRatio
	FROM (
		SELECT userID, SUM(winCount) AS winCountSum,  SUM(loseCount) AS loseCountSum
		FROM (
			SELECT userID, 1 AS winCount, 0 AS loseCount
			FROM テーブル1
			WHERE result ="win"
			UNION ALL
			SELECT userID, 0 AS winCount, 1 AS loseCount
			FROM テーブル1
			WHERE result ="lose"
		) TBL
		GROUP BY userID
	) TBL2
)
ORDER BY winRatio DESC, userID

id:Zero2Flourish

勝率の計算がどうしてもゼロになるのでwinCountSum / (loseCountSum + winCountSum) * 100)→(winCountSum * 100 / (loseCountSum + winCountSum))にしたらできました。

後、一番上のSELECT文にTBL3という名前をつけたら無事動きました。

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

2009/06/29 09:56:55

その他の回答(3件)

id:HALSPECIAL No.1

HALSPECIAL回答回数407ベストアンサー獲得回数862009/06/26 19:04:29ここでベストアンサー

ポイント57pt

こちらでいかがでしょうか?

テーブル名を「テーブル1」としています。


SELECT *
FROM (
	SELECT userID, (winCountSum / (loseCountSum + winCountSum) * 100) AS winRatio
	FROM (
		SELECT userID, SUM(winCount) AS winCountSum,  SUM(loseCount) AS loseCountSum
		FROM (
			SELECT userID, 1 AS winCount, 0 AS loseCount
			FROM テーブル1
			WHERE result ="win"
			UNION ALL
			SELECT userID, 0 AS winCount, 1 AS loseCount
			FROM テーブル1
			WHERE result ="lose"
		) TBL
		GROUP BY userID
	) TBL2
)
ORDER BY winRatio DESC, userID

id:Zero2Flourish

勝率の計算がどうしてもゼロになるのでwinCountSum / (loseCountSum + winCountSum) * 100)→(winCountSum * 100 / (loseCountSum + winCountSum))にしたらできました。

後、一番上のSELECT文にTBL3という名前をつけたら無事動きました。

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

2009/06/29 09:56:55
id:kn1967 No.2

kn1967回答回数2915ベストアンサー獲得回数3012009/06/26 20:29:00

ポイント12pt
-- テスト用一時テーブル作成
CREATE TEMP TABLE T1 (
    userID integer,
    result varchar(4)
);
INSERT INTO T1 VALUES (1, 'win');
INSERT INTO T1 VALUES (1, 'win');
INSERT INTO T1 VALUES (2, 'win');
INSERT INTO T1 VALUES (1, 'lose');
INSERT INTO T1 VALUES (3, 'lose');
INSERT INTO T1 VALUES (1, 'win');
INSERT INTO T1 VALUES (1, 'lose');
INSERT INTO T1 VALUES (5, 'lose');

-- 率計算
SELECT userID, sum(CASE result WHEN 'win' THEN 1 ELSE 0 END) * 100 / count(*) ASFROM T1
GROUP BY userID
ORDER BYDESC
;

当方では全角の%もフィールド名として利用できておりますが、

そちらの環境に合わせて適宜変更してください。

id:sphire No.3

sphire回答回数115ベストアンサー獲得回数122009/06/28 01:32:37

ポイント11pt

正常稼働試験済です。

--テーブル作成
CREATE TABLE `Table1` (
  `userID` int(11) default NULL,
  `result` text
);

--レコード追加
INSERT INTO
  `Table1`
VALUES
  (1, 'win'),
  (1, 'win'),
  (2, 'win'),
  (1, 'lose'),
  (3, 'lose'),
  (3, 'win'),
  (1, 'lose'),
  (5, 'lose');

--回答のSQL
SELECT
  `userID`,
  (`Win`/(`Win`+`Lose`)*100) AS `WinRatio`
FROM (
  SELECT
    `userID`,
    SUM(CASE WHEN `result`='win' THEN 1 ELSE 0 END) AS `Win`,
    SUM(CASE WHEN `result`='lose' THEN 1 ELSE 0 END) AS `Lose`
  FROM
    `Table1`
  GROUP BY
    `userID`
) `SumTable1`
ORDER BY
  `WinRatio` DESC;

質問者が未読の回答一覧

 回答者回答受取ベストアンサー回答時間
1 kn1967 2915 2772 301 2009-06-26 19:34:38
  • id:kn1967
    sphire さんへ

    INSERTでの複数行一括挿入はバージョン8.2以降なので、多分、
    問題ないかと思いますが、もしかしたら使えない環境であるかもしれません。

    それ以前に、バッククォートはMySQL独特の構文ですが、
    PostgreSQLでいかに動かすことが出来たのでしょうか?
    最新のパッチか何かがあるという事でしたら情報いただければ幸いです。
  • id:kmond2
    > 当方では全角の%もフィールド名として利用できておりますが

    いや、いくら出来たとしても、常識的にやらんでしょ、それは(笑)。
    PostgreSQL を知らないというより、SQL を知らないんですかね。
  • id:sphire
    id:kn1967さん
    他の質問でMySQLのやつと混同してました…(汗
    おっしゃる通り、バッククォートはMySQL独自ですね。

    >id:Zero2Flourishさま
    そんなわけで、あまり役にたたない回答でした。すみません;
    テクニック(CASEで特定条件のみのカウント)自体はid:kn1967さんの回答と一緒ですので。
  • id:kn1967
    >あまり役にたたない

    いえ、決してそのような事は無いですよ。
    私の回答は、SQLで条件分岐を使えば、構造がシンプルになったり、
    動作コストも下がる可能性があるという事を示唆しているだけで、
    win/lose以外の値がある場合などは考慮してなかったりしますが、
    そこのところまで、しっかりと対応しておられますので、
    私の回答のほうが稚拙です。

    此度は、勝手なお願いを聞き入れていただき感謝いたします。
    ありがとうございました。


この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません