MySQLで重複をはじいて当選者を抽出

Question

実現したいこと

何かの懸賞のようなイメージで以下の様な応募履歴テーブル（drawings）があったとします。
この中からランダムで2名を当選者として抽出したいです。

※１レコードが応募１口

id	user_id	created
1	23	2023-02-16
2	45	2023-02-19
3	45	2023-02-22
4	99	2023-02-24
5	62	2023-02-26
6	23	2023-03-01
7	99	2023-03-04
8	45	2023-03-09
9	23	2023-03-11
10	23	2023-03-14

前提

応募してきた数だけ当選確率が上がるようなSQLを作りたいです。
例えば、user_id:23は4回応募してきているので、1回応募の人より4倍の確率で抽出されるようにしたいです。

ただし、重複をはじいて当選者を抽出したいです。
何回応募しても確率は上がりますが同じuser_idが2件抽出されることが無いようにしたいです。

発生している問題・エラーメッセージ

当初以下の様なSQLを思いついたのですが、 ORDER BY RAND() は GROUP BY の後に実行されるため、応募口数が無視されることに気づきました。
また DISTINCT を利用しても同様だと思います。

重み付き抽選のようなことがＳＱＬで再現できればと思っています。

SELECT
  *
FROM
  drawings
GROUP BY user_id
ORDER BY RAND()
LIMIT 2

補足情報（FW/ツールのバージョンなど）

MySQL 8.0 を利用しています

Comments

[tmp]

確率の問題ではないですよね？
抽選の１回あたり当選率が応募数nに応じて確率がn倍ですよね？（２名なので２回抽選）
ガチャとか確率で問題になるのでちょっと気になりました。

[suzushin0619]

> 抽選の１回あたり当選率が応募数nに応じて確率がn倍ですよね？

はい、応募数ｎに応じて確率がｎ倍としたいです。

> （２名なので２回抽選）

今回は簡略化するために2名としましたが、仮に重複のない100名を応募者からピックアップする場合に100回抽選するのが手間だと思い、1度のSQLで取得する方法がないかなと模索しています。

[tmp]

単に抽選２回（２名）当選率だと、
この例の１０応募データ場合、応募数１だと20%で応募数２だと37.7%になり、２倍にはならない、
そこで応募数２で２倍になりますとか、単純に宣伝してしまうと詐欺だという人がでてくるかもしれないのでコメントしました。

[suzushin0619]

実際はB2Cなサービスで利用するわけではなく、社内ツールで今回のようなＳＱＬが必要となり、わかりやすくするために懸賞のような表現を用いました。

ただ、ロジックや仕様を説明するときに「〇倍の確率で」と謳うと仰る通り語弊が生まれそうですね..汗
補足をして頂きありがとうございました！

Answer 1

普通にorder by rand()して上位2名（LIMIT 2）をとればいいだけですね
複数いればそれだけ確率はあがります。
ただし、同じ人間が2度あたる可能性もあるので、結果に対してダブりが
あった場合再抽選するのがわかりやすい気がします

追記

create table tbl(id int primary key auto_increment,user varchar(10),rank double);

insert into tbl(user,rank) values
('a',rand()),
('a',rand()),
('b',rand()),
('b',rand()),
('a',rand()),
('a',rand()),
('c',rand()),
('d',rand());

select user,min(rank) as min_rank from tbl group by user order by min_rank;

上記当選者分でLimitを指定すればよいでしょう

Comments

[suzushin0619]

ご回答ありがとうございます！

ちなみに同じ人間が2度当たる事を防ぐSQLは可能でしょうか？
今は当選者を2人としていますが、仮に1000人の当選者を選ぶ場合は再抽選を繰り返し行う必要が出てしまいそうで、重複の当選者を防ぐ様なSQL1発で取得できたらと思っています。

[yambejp]

テーブルの構成を調整すればいけそうですね。追記しておきます

追記：randは都度発生するみたいなのでテーブルの構造変えなくても大丈夫かも
select user_id,min(rand()) as min_rand from tbl group by user_id order by min_rand asc limit 2

[suzushin0619]

追記ありがとうございます！

頂いたSQLをテストしたところうまく動いているように思えます。
ただ、なんとなくロジックを理解しているような状態です（脳みそが足らずスミマセン...笑）

頭の中で整理完了したらベストアンサーをお渡しして質問をクローズさせて頂きたく思います。

[suzushin0619]

理解できました
こちらの要望通りのご回答かつSQLのテクニックを学べました
ありがとうございました