MySQLの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をランダムでひとつを選択するような書き方はできるのでしょうか。

よろしくお願いします。

回答No.5

mpro-gram · ベストアンサー

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 ;

お礼

おお、時間短縮まで考慮されたクエリをありがとうございます！
order by null は使ったことがありませんでしたが、
とてもわかりやすくて勉強になります！
ありがとうございました！

回答No.4

yambejp

きちんとやるならテンポラリなどにおとすことですね

create temporary table tmp(ID int,Type int,age int,unique key(ID,Type));
insert ignore into tmp select * from tb order by rand();
select * from tmp order by ID,Type;

結局SQLでのランダム処理は全データを総なめすることになるので
無駄が多いですけどね。

お礼

なるほど、こういう時にテンポラリが使えるのですね。
お恥ずかしながら初心者なもので思い付きませんでした…。
勉強になりました！ありがとうございました！

回答No.3

Gaffgarion

ちょっと訂正します。

> pk順だったり、insert順だったり、とその時その時の結果の傾向はありますが、
> もう1度同じクエリを発行した結果が同じとは限りません。
>
pk順っぽい、insert順っぽい、とその時その時の結果の傾向はありますが、
もう1度同じクエリを発行した結果が同じとは限りません。

細かいところですが、なんかミスリードさせそうだったので。

回答No.2

Gaffgarion

order byなしでのselectの順番は保障されていません。
pk順だったり、insert順だったり、とその時その時の結果の傾向はありますが、
もう1度同じクエリを発行した結果が同じとは限りません。
つまり、保障されてないのでランダムとも言えなくはないでしょう。
mysqlに限らず、他のRDBの多くでそうなってます。

また、group byに関しては、
そもそも、グループ化された列や集計関数以外はselectできません。
例で言うなら、group by id,type なので、
selectできるのはid,typeとあと集計関数と定数のみです。
ただ、mysqlではグループ化していない列もselectできます。
しかし、その結果(どれが表示されるか)は保障されていません。
多分、selectが保障できないので、それをグループ化するから保障できないのでしょう。

その上でランダムで出したいなら、
グループ化する前の結果順をランダムにして、それをgroup byするとどうでしょうか。
もっと他に良い方法ありそうですか、ぱっと思いついたのは以下です。

select t.id,t.type,t.age from (select * from tb order by rand()) t group by t.id,t.type order by t.id,t.type;

良いクエリではないと思うので、データが増えた時の性能には十分に気をつけてください。

お礼

返事が遅くなり申し訳ありません。
とても詳しい説明ありがとうございます。
教えていただいたクエリで解決いたしました！
お礼申し上げます。

回答No.1

bin-chan

たいてい、group by は、max()、min()、sum()、count()などの集合関数とセットでは？

> これを、以下のようにしたいのです。
これのルールはなんでしょう？

> ageをランダムでひとつを
ACCESSのクエリなら、前記に加え「先頭」「最後」も可能でしょうケド
ランダムは無さそう。

補足

すみません、説明不足でした。
各IDとTypeのセットをひとつずつ抽出したいのです。

//元データ
create table tb (ID int,Type int,age int);
insert into tb values(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);

//試したもの
select ID, Type, age
from tb
group by ID, Type;

とするとIDとTypeがまとめられますが、どういう基準でageを選んでいるのか、
ランダムにageを選べるのかがわかりません……。