複数テーブルからのランダム取得を一回のSQLで行いたい

Question

下記のような2つのテーブルがあります。
ランダムに取得した10ユーザーのメッセージを1件ずつランダムに取得
といった事はSQL一回で可能でしょうか。

メッセージを単純にユーザーIDでGROUP BYして
ランダムに返ってきてくれればいいんですが
暗黙的なソートが働くためか毎回同じ値が返ってきてしまいます。

ユーザーテーブル

ユーザーID, 名前, 登録日時

メッセージテーブル

メッセージID, メッセージ, ユーザーID, 登録日時

Answer 1

以下でどうでしょうか？
元データ

create table mes(
message_id int unique key,
message varchar(100),
user_id int,
created datetime
);
insert into mes values
(1,'mes1',1,'2017-09-01 00:00:00'),
(2,'mes2',1,'2017-09-01 00:00:01'),
(3,'mes3',2,'2017-09-01 00:00:02'),
(4,'mes4',1,'2017-09-01 00:00:03'),
(5,'mes5',3,'2017-09-01 00:00:04'),
(6,'mes6',2,'2017-09-01 00:00:05'),
(7,'mes7',3,'2017-09-01 00:00:06'),
(8,'mes8',4,'2017-09-01 00:00:07'),
(9,'mes9',5,'2017-09-01 00:00:08');

抽出

select * from mes as t3 
inner join (
select substring_index(group_concat(message_id order by rand()),',',1) as message_id
from mes as t1
where exists (select user_id from (select distinct user_id from mes order by rand() limit 3 ) as t2 where t1.user_id=t2.user_id)
group by user_id
) as t4 on t3.message_id=t4.message_id
order by rand();

Comments

[megmilk]

回答ありがとうございます。
GROUP_CONCATの存在を知らなかったのでとても参考にさせて頂きました。
ただ、さすがにSQLが複雑になりすぎるので下記に省略してみました。
concatした結果からアプリ側でランダムに1件取得するのは
それほどコストはかからないと思うのでコレがベストではないかなと思っています。

SELECT
*,
GROUP_CONCAT(message_id) as concat
FROM
mes
GROUP BY
user_id
ORDER BY
RAND()
LIMIT 3

[yambejp]

インデックスの貼り方とデータ量によってどの手法が効率的かは
かわってくるかもしれませんね
saziさんの提示されたソースはわかりやすく簡潔なロジックなので
検証してみて下さい

Answer 2

幾つかの手法があります。
こちら（MySQLでランダムにレコードを取得する場合の手法）が参考になるかと。

追記

試してみていませんが、上記中RAND()を使った手法で

select  *  
     , (
            select `メッセージ` from `メッセージテーブル` 
            where `ユーザーid`=usr.`ユーザーid` 
            order  by  rand() limit 1
        ) as `メッセージ`
from  `ユーザーテーブル` as usr
order  by  rand() 
limit  10 

メッセージテーブルの全体を取得したい場合は、メッセージテーブルの主キーに該当するものを取得すればいいのですが、この場合、ユーザーIDと登録日時だと思われるので、登録日時をサブクエリーで取得してさらに結合します。

select *
from (
        select  *  
             , (
                    select `登録日時` from `メッセージテーブル` 
                    where `ユーザーid`=usr.`ユーザーid` 
                    order  by  rand() limit 1
                ) as `メッセージ登録日時`
        from  `ユーザーテーブル` as usr
        order  by  rand() 
        limit  10 
    ) as v1 inner join `メッセージテーブル` msg
    on v1.`ユーザーid`=msg.`ユーザーid` and v1.`メッセージ登録日時`=msg.`登録日時`

Comments

[megmilk]

回答ありがとうございます。
この記事は主にパフォーマンスの観点からランダム取得の手法を紹介しているもののようで
この記事を読んでもちょっと私にはわからなかったです。

[sazi]

追記してみました。

[megmilk]

回答ありがとうございます。
最初の例で問題なくランダムなデータ取得が可能でしたのでベストアンサーにさせて頂きます。
この副問合せはちょっと盲点でした。