ちょっと変わったマニアな作品が集結

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

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

 組 氏名
-----------------
A組 鈴木
A組 木村
B組 山田
B組 佐藤
B組 田中
C組 石井
C組 井上
D組 田口

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

A組 鈴木
B組 田中
C組 石井
D組 田口

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

集合関数には合計や平均などの数学関数はあるのですが、このようなことを実現するものが見あたりません。
現在は、まず先に組でグループ化したクエリを実行し、A組から順番に
SELECT ~ WHERE 組="A組" ORDER BY RND() LIMIT 1
を実行、次に
SELECT ~ WHERE 組="B組" ORDER BY RND() LIMIT 1

というように、いちいち全組分を繰り返していますが、もっと良い方法がありましたら、ご教授お願いいたします。

このQ&Aに関連する最新のQ&A

A 回答 (2件)

order by してから group by すればいいじゃん!とおもったらできないんですね。


よく考えればそりゃそうなんですけど
ソートしてからグループ化ってのは確かにRDBとして効率わるいし。

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

競合とかきになりますが、所詮ヒープだし。
    • good
    • 0
この回答へのお礼

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

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

お礼日時:2005/08/12 13:38

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



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

というのは、どうでしょう。
    • good
    • 0
この回答へのお礼

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

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

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

お礼日時:2005/07/29 17:12

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

関連するカテゴリからQ&Aを探す

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QMySQLのgroup byの選択基準

お世話になります。質問がふたつあります。
このような、IDとTypeのふたつのカラムで重複しているレコードが多いテーブル「tb」があるとします。

ID Type age
1 1 20
1 2 35
1 3 42
1 2 31
1 3 45
1 2 33
2 1 21
2 3 41
2 1 26
2 2 31
2 1 25
2 1 28

これを、以下のようにしたいのです。

ID Type age
1 1 20
1 2 35
1 3 42
2 1 21
2 2 31
2 3 41


group by ID, Typeとしたところ、
似たような形にはなったのですが、IDとTypeが重複した
ID Type age
1 2 35
1 2 31
1 2 33

のうち、group byによってどのような基準でひとつが選択されるのでしょうか?
レコードの追加が新しいものが選ばれるのでしょうか?

また、ageをランダムでひとつを選択するような書き方はできるのでしょうか。

よろしくお願いします。

お世話になります。質問がふたつあります。
このような、IDとTypeのふたつのカラムで重複しているレコードが多いテーブル「tb」があるとします。

ID Type age
1 1 20
1 2 35
1 3 42
1 2 31
1 3 45
1 2 33
2 1 21
2 3 41
2 1 26
2 2 31
2 1 25
2 1 28

これを、以下のようにしたいのです。

ID Type age
1 1 20
1 2 35
1 3 42
2 1 21
2 2 31
2 3 41


group by ID, Typeとしたところ、
似たような形にはなったので...続きを読む

Aベストアンサー

group by 句に無いカラムがどう選択されるかは既にご回答の有るとおり。
要は見つけた順なのだが、indexのアルゴリズムや、挿入削除で物理配置が変わるので、不定となる。

あと、mysql には、集約関数にランダムに取り出す物はないので、サブクエリか、テンポラリーテーブルが必要。mysql 4.1 以降なら、サブクエリでいけそう。最適化も考慮すると以下かな?最後の order by null で既に並んでる物を再度並べ替え作業しないようにして、少々時間短縮。
select id,Type, age
from (select id,Type, age from tb order by id,Type, rand() ) as rand_tbl
group by id,Type
order by null ;

Qgroup byで最後のレコードを抽出したい

group byで特定のカラムをまとめる際に、最後(最新)のレコードを抽出したいのですが
最初(古い)の行が抽出されてしまいます。

例えば、threadカラムとuptimeカラムがあるテーブルBBSで
threadカラムをgroup byでまとめる際にuptimeの降順(desc)でまとめたい場合
どのようにしたらいいでしょうか?

【テーブルBBS】

id thread uptime
1 1 1230100000
2 2 1230200000
3 1 1230300000
4 3 1230400000
5 1 1230500000
6 2 1230600000

select * form bbs group by thread order by uptime desc

だと

thread uptime
3 1230400000
1 1230300000
2 1230200000

になってしまいます。

次のような結果を表示するにはどのようにしたらいいでしょうか。

thread uptime
2 1230600000
1 1230500000
3 1230400000

また、DISTINCTを使うと同じフィールド内に重複したレコードがある場合は最初のレコードのみ表示されますが、最後のレコードのみ表示するにはどのようにすればいいのでしょうか?

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

group byで特定のカラムをまとめる際に、最後(最新)のレコードを抽出したいのですが
最初(古い)の行が抽出されてしまいます。

例えば、threadカラムとuptimeカラムがあるテーブルBBSで
threadカラムをgroup byでまとめる際にuptimeの降順(desc)でまとめたい場合
どのようにしたらいいでしょうか?

【テーブルBBS】

id thread uptime
1 1 1230100000
2 2 1230200000
3 1 1230300000
4 3 1230400000
5 1 1230500000
6 2 123060000...続きを読む

Aベストアンサー

単にthread毎にまとめた最大のuptimeをとるだけですよね?
select thread,max(uptime) as uptime form bbs group by thread order by uptime desc

これにidの情報がほしいということだと、サブクエリが必要になります

Qmysql5でGROUP BYごとにLIMIT??

2011,600
2011,500
2011,450
2011,750
2010,450
2010,540
2010,350
2010,800
2010,700

例えばこのようなデータを年ごとにベスト3までとって次のようにするSQLはありますか

2011,750
2011,600
2011,500
2010,800
2010,700
2010,540

Aベストアンサー

じゃあ、これを試してみてください。
select t.nen, t.gaku
from ( select
@rnk:=if(@prevnen <=> x.nen, if( @prevaku <=> x.gaku, @rnk, @rnk+1), 1 ) as rnk,
@prevnen := x.nen nen,
@prevgaku := x.gaku gaku
from ( select * from xxx order by nen, gaku desc ) as x
cross join ( select @prevnen:=0, @prevgaku:=0, @rnk:=0 ) as dummy ) t
where t.rnk <= 3;

QJOINでの結果取得 サブクエリをJOINする

WordPressのデータベースでテーブル2つを結合して以下の情報を取得しようとしています

メール(wp_users.user_email)|名前(wp_usermeta.meta_value)|登録日時(wp_users.user_registered )
123@a.com,山田,2012-06-17 12:33:44
456@b.jp,田中,2012-06-21 07:26:54
789@c.ne.jp,\N,2009-08-11 12:19:00
1234@d.ne.jp,\N,2009-08-11 12:19:00
321@x.ne.jp,\N,2009-08-11 12:19:00

・ユーザーはwp_usersテーブルにeメール情報(user_email)を必ず持ている
・ユーザーの名前はwp_usermetaテーブルのwp_usermeta.meta_keyがuser_nameなレコードに保存されているが、ユーザーによってあるものとないものがある。無い場合はNULLにする。

テーブル構造は添付した画像に記載されています。

■ユーザ数
mysql> SELECT COUNT(*) FROM wp_users;
18290

まずwp_usersとwp_usermetaをLEFT JOINしてmeta_key = 'user_name'なレコードを取得しようとしましたが、user_nameを持っているユーザーのみしか取得できませんでした。
■user_nameを持っていないユーザーが取得できない
SELECT u.user_email, m.meta_value FROM wp_users AS u LEFT JOIN wp_usermeta AS m ON u.ID = m.user_id WHERE m.meta_key = 'user_name'\G
2075 rows in set (0.00 sec)

次にサブクエリをJOINしました。結果は正しいものを取得できたのですが、実行時間が40秒以上かかってしまい実用性がありません。
■欲しい結果(速度が遅い)
SELECT u.user_email, a.meta_key, a.meta_value, u.user_registered FROM wp_users AS u LEFT JOIN (select * from wp_usermeta where meta_key = 'user_name') as a ON u.ID = a.user_id;
18290 rows in set (45.45 sec)

この結果を取得できる、正しいSQLを教えてください。よろしくお願いします。

WordPressのデータベースでテーブル2つを結合して以下の情報を取得しようとしています

メール(wp_users.user_email)|名前(wp_usermeta.meta_value)|登録日時(wp_users.user_registered )
123@a.com,山田,2012-06-17 12:33:44
456@b.jp,田中,2012-06-21 07:26:54
789@c.ne.jp,\N,2009-08-11 12:19:00
1234@d.ne.jp,\N,2009-08-11 12:19:00
321@x.ne.jp,\N,2009-08-11 12:19:00

・ユーザーはwp_usersテーブルにeメール情報(user_email)を必ず持ている
・ユーザーの名前はwp_usermetaテーブルのwp_usermeta.meta_ke...続きを読む

Aベストアンサー

いまいち構造がわからないのですが
whereじゃなくて連結の条件にしてみては?

SELECT u.user_email, m.meta_value
FROM wp_users AS u
LEFT JOIN wp_usermeta AS m
ON u.ID = m.user_id
and m.meta_key = 'user_name'

Qmysqlのcase文で複数の条件を指定する

mysql初心者です。以下のデータから勤務時間(end_time-start_time-break
_time)が8時間以上、かつ終了時間が22時以降の場合、(end_time-22:00)を深夜残業時間として、深夜残業時間を計算したいのですが、case文で場合分けしてAかつBの場合というような書き方はどのように書いたらいいでしょうか。よろしくお願いします。

name  start_time  end_time  break_time
田中   9:00      22:30    1:00
佐藤   9:00      21:00    1:00
渡辺   10:00     24:00    1:00
藤原   9:00      18:00    1:00

Aベストアンサー

AかつBならIFをつかってANDでつなげばいい気がしますが・・・
CASEにこだわるなら、WHENで条件外をしぼってELSEで実数を得ればよいかも
ざっくり今回のだとこんな感じ?

SELECT name,start_time,end_time,break_time
,CASE
WHEN end_time<'22:00:00' THEN NULL
WHEN TIMEDIFF(TIMEDIFF(end_time,start_time),break_time)<='08:00:00' THEN NULL
ELSE TIMEDIFF(end_time,'22:00:00')
END AS SHINYA
FROM schedule

QWHERE句で結合。INNER JOINとの違い

MySQLで複数テーブルからデータ取得する際、
FROMの後に、テーブル名を2つ書いて、「WHERE」で繋げる書き方と、
「JOIN ★★ ON」で繋げる書き方では、何が違うのでしょうか?

・「INNER JOIN」と同じ意味でしょうか?
・書き方によっては、「LEFT OUTER JOIN」みたいにも書けるのでしょうか?

・普通は、どちらの書き方で書くとか、そういうお作法的な暗黙の了解はあるでしょうか?
・例えば「WHERE」だと3つ以上繋げられない(?)から、奨励されていない、とか…

Aベストアンサー

select *
from 表A, 表B
where 表A.列1 = 表B.列2 and 表A.列3 = 値



select *
from 表A inner join 表B on 表A.列1 = 表B.列2
where 表A.列3 = 値

は同じ結果となります。前者の方が古くからある構文,後者が新しい構文になります。新しいといっても20年は経っていますけれど。


> 普通は、どちらの書き方で書くとか、
> そういうお作法的な暗黙の了解はあるでしょうか?

旧構文は,結合を指定する要素がfrom句とwhere句に分かれてしまっているのに対して,
新構文では次のように各句の機能が明確ですから,join句の使用をお薦めします。

射影…select *
結合…from 表A inner join 表B on 表A.列1 = 表B.列2
選択…where 表A.列3 = 値


> 書き方によっては「LEFT OUTER JOIN」みたいにも書けるのでしょうか?

旧構文で外部結合を記述することもできますが,各データベース製品の独自構文になります。標準SQLの規格ではありません。


> 「WHERE」だと3つ以上繋げられない(?)から、奨励されていない、とか

そういう制限はありません。

select *
from 表A, 表B
where 表A.列1 = 表B.列2 and 表A.列3 = 値



select *
from 表A inner join 表B on 表A.列1 = 表B.列2
where 表A.列3 = 値

は同じ結果となります。前者の方が古くからある構文,後者が新しい構文になります。新しいといっても20年は経っていますけれど。


> 普通は、どちらの書き方で書くとか、
> そういうお作法的な暗黙の了解はあるでしょうか?

旧構文は,結合を指定する要素がfrom句とwhere句に分かれてしまっているのに対して,
新構文では次のように各句の機能が明確です...続きを読む


人気Q&Aランキング