有意思的sql: 直播间抽奖

今天和同事排队做核酸时，讨论了个问题：

直播间发起弹幕抽奖，会将用户每一次参与分配一个id，最后需要抽取3个中奖用户;

如果用程序实现比较简单，如果用sql如何实现呢

下面将实现细节分享下，只做了关键点的说明，其他的复现下应该可以理解

-- 场景转化后的sql题如下
-- 1. 10张彩票 被超过3个人买 一个人可以买多张 想找到3张彩票号码正好让3人中奖
-- 2. 在1的基础上 如果1个人买了多张彩票 应该让其中奖概率大一点 满足吗

-- 在hive中建表并插入数据

create table temp_hyk_20220711(
    tid bigint comment '彩票号码'，
    uid String comment '用户ID'
)；

insert into table temp_hyk_20220711 values
(1,'a')
,(2,'a')
,(3,'b')
,(4,'b')
,(5,'c')
,(6,'d')
,(7,'e')
,(8,'a')
,(9,'f')
,(10,'c')
;

-- 查看数据是否正常
select * from temp_hyk_20220711;


-- 问题1 主要是随机抽取 通过随机函数rand()加一个随机数 按照大小排序 
select
uid,
rand(unix_timestamp(getdate())) as num, 
-- rand() num,   
max(tid)
from temp_hyk_20220711
group by uid
order by num
limit 3
;

-- 遇到的问题 
执行下面sql 发现多次执行 num2的值是不变的 导致如果用num2的话 如果抽取三次，每次抽到的都是同样的三个人 

使用rand(seed)使每次的结果不相同

看了下rand函数感觉没啥问题，猜测是缓存的问题，即多次执行时，只执行了一次，后面都是从缓存
但getdate()函数又会每次取执行时的时间，感觉应该会重新执行sql 而不是走缓存，。 
暂时就不讨论了 两次执行的结果见末尾图
select   
tid, 
uid,
rand(unix_timestamp(getdate())) as num, 
rand() as num2
from temp_hyk_20220711
order by tid 
;


问题2：
上面的解法等于是一个用户只生成了一次rand() 按照这个取 
如果需要增加多次参与的人的概率  修改为一个用户参与一次就生成一次rand()

select
uid,
tid,
num
from
(
    select 
    tid,
    uid,
    num,
    row_number()over(partition by uid order by num desc) as rn  -- 用户买的多 其产生大的随机数的可能性就越大 
    from 
    (
        select
        tid,
        uid,
        rand(unix_timestamp(getdate())) as num -- 如果用户买多次 产生多次随机数 
        from temp_hyk_20220711
    )t1
)t2
where rn = 1
order by num desc
limit 3;

补充
rand()函数还可以用作抽样
select 
*
from 
(
    select 
    *,
    rand() as num
    from temp_hyk_20220711
)t 
where num between 0 and 0.2; 

rand()函数导致两次结果不同

最后别忘了删除临时表

drop table temp_hyk_20220711;