グループからランダムに抽出する方法

質問

レコードをグループ化して、そのグループから1レコードをランダムに選択する方法はないでしょうか？
具体的には、たとえば以下のようなことです。

以下のようなテーブルがあるとします。

　組　氏名
-----------------
Ａ組　鈴木
Ａ組　木村
Ｂ組　山田
Ｂ組　佐藤
Ｂ組　田中
Ｃ組　石井
Ｃ組　井上
Ｄ組　田口

このテーブルから、各組から代表を1人ずつランダムに選びたいです。
たとえば、

Ａ組　鈴木
Ｂ組　田中
Ｃ組　石井
Ｄ組　田口

というように、Ａ組には2人いますが1人をランダムに、Ｂ組には3人いますが1人をランダムに、…、という感じです。

集合関数には合計や平均などの数学関数はあるのですが、このようなことを実現するものが見あたりません。
現在は、まず先に組でグループ化したクエリを実行し、Ａ組から順番に
SELECT ～ WHERE 組="Ａ組" ORDER BY RND() LIMIT 1
を実行、次に
SELECT ～ WHERE 組="Ｂ組" ORDER BY RND() LIMIT 1
…
というように、いちいち全組分を繰り返していますが、もっと良い方法がありましたら、ご教授お願いいたします。

回答No.2

yambejp · ベストアンサー

order by してから group by すればいいじゃん！とおもったらできないんですね。
よく考えればそりゃそうなんですけど
ソートしてからグループ化ってのは確かにＲＤＢとして効率わるいし。

というわけで、非効率ではありますが、ヒープで処理してみてはいかがですか？
CREATE TABLE `tmp_table` TYPE=HEAP SELECT `*` FROM `org_table` ORDER BY RAND();
SELECT `*` FROM `tmp_table` GROUP BY `class`;
DROP TABLE `tmp_table`;

競合とかきになりますが、所詮ヒープだし。

お礼

ご回答ありがとうございます。
今は、ご提案いただいたのと同じような、HEAPテーブルではないですがTEMPORARY TABLEを作って、ORDER BY RANDを付けてコピーしておいて、その後にGROUP BYする、という方法を取ることにしています。
やはりHEAPテーブルは競合が気になりますので、同時に実行されたことを考えてTEMPORARY TABLEにしました。
まぁ結果的に「order by してから group by すればいいじゃん」の方法なんですけどね。2段階になっただけで。

ご指摘のように効率的じゃないかもしれませんが、
SELECTの入れ子とか使えたら楽なんだろうけど、とりあえず、これしかないのかなと…。
それにしても、このような要求ってよくありそうなんですがねぇ…。

回答No.1

masayuki0511

結果を後で、分解しないといけませんが、

SELECT 組,MAX(CONCAT(CAST(RAND() AS CHAR),CONCAT('-',氏名)))
FROM テーブル
GROUP BY 組;

というのは、どうでしょう。

お礼

ご回答ありがとうございます。
なるほどつまり、グループ化の際にグループ内の要素に数値をランダムにふって、その最大を取り出す、ということですね？
早速試してみようと思ったのですが、なぜか文法エラー、調べてみると、今借りているサーバーのMySQLのバージョンが3.23.56で、CASTが使えないとのこと…。何か別の方法でCASTのようなことをすればよさそうですが。

しかし、せっかくご回答いただいたのに恐縮なのですが率直に申し上げると、
後で分解するということは、全組分のループをすることになり、質問文に書いた、組ごとにランダムに１つ選ぶ処理と、結局は大差がないような気がしまして…。
できればループなどは使わずSQLだけで抽出できたらと思うのですが。

このような処理ってよくありそうなんですが、常套手段のようなものはないのでしょうか…。