人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

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 |…%


●質問者: さとなり
●カテゴリ:コンピュータ ウェブ制作
✍キーワード:postgreSQL SQL WIN クエリ ユーザ
○ 状態 :終了
└ 回答数 : 3/4件

▽最新の回答へ

1 ● HALSPECIAL
●57ポイント ベストアンサー

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

テーブル名を「テーブル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

◎質問者からの返答

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

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

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


2 ● kn1967
●12ポイント
-- テスト用一時テーブル作成
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
;

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

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


3 ● sphire
●11ポイント

正常稼働試験済です。

--テーブル作成
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;
関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ