在活动大促中,有玩游戏瓜分奖金环节。现有奖金池为 10000元,代表奖金池中的初始额度。用户的分数信息如下表。表中的数据代表每一个用户和其对应的得分,user_id 和 score 都不会有重复值。瓜分奖金的规则如下:按照 score 从高到低依次瓜分,每个人都能分走当前奖金池里面剩余奖金的一半,当奖金池里面剩余的奖金少于 250 时(不含),则停止瓜分奖金。现在需要查询出所有分到奖金的 user_id 和其对应的奖金。
样例数据
+----------+--------+
| user_id | score |
+----------+--------+
| 100 | 60 |
| 101 | 50 |
| 102 | 45 |
| 103 | 35 |
| 104 | 30 |
| 105 | 25 |
| 106 | 15 |
| 107 | 10 |
+----------+--------+
该题目相对简单,给用户根据score进行排名,然后依次给出金额即可。难点是如何计算瓜分金额、如何计算奖池余额。
维度 | 评分 |
---|---|
题目难度 | ⭐️⭐️⭐️ |
题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
业务常见度 | ⭐️⭐️⭐️ |
使用row_number开窗,得到用户排名rn
执行SQL
select
user_id,
score,
row_number() over(order by score desc) as rn
from t15_user_score;
查询结果
+----------+--------+-----+
| user_id | score | rn |
+----------+--------+-----+
| 100 | 60 | 1 |
| 101 | 50 | 2 |
| 102 | 45 | 3 |
| 103 | 35 | 4 |
| 104 | 30 | 5 |
| 105 | 25 | 6 |
| 106 | 15 | 7 |
| 107 | 10 | 8 |
+----------+--------+-----+
每个人得到当前奖池的1/2,排名rn的得到的为1/2^rn10000,奖池剩余的也是1/2^rn10000。如果无限瓜分下去,看下是什么结果.
执行SQL
select user_id,
score,
power(0.5, rn) * 10000 as prize
from (select user_id,
score,
row_number() over (order by score desc) as rn
from t15_user_score) t
查询结果
+----------+--------+----------+
| user_id | score | prize |
+----------+--------+----------+
| 100 | 60 | 5000.0 |
| 101 | 50 | 2500.0 |
| 102 | 45 | 1250.0 |
| 103 | 35 | 625.0 |
| 104 | 30 | 312.5 |
| 105 | 25 | 156.25 |
| 106 | 15 | 78.125 |
| 107 | 10 | 39.0625 |
+----------+--------+----------+
题目中要求:当奖金池里面剩余的奖金少于 250 时(不含),则停止瓜分奖金。我们可以看到104瓜分之后奖池剩余312.5应该继续瓜分,105瓜分完成之后才会停止。
执行SQL
select user_id,
score,
power(0.5, rn) * 10000 as prize
from (select user_id,
score,
row_number() over (order by score desc) as rn
from t15_user_score) t
where power(0.5, rn - 1) * 10000 >=250
执行结果
+----------+--------+---------+
| user_id | score | prize |
+----------+--------+---------+
| 100 | 60 | 5000.0 |
| 101 | 50 | 2500.0 |
| 102 | 45 | 1250.0 |
| 103 | 35 | 625.0 |
| 104 | 30 | 312.5 |
| 105 | 25 | 156.25 |
+----------+--------+---------+
--建表语句
create table if not exists t15_user_score
(
user_id string,
score bigint
);
--插入数据
insert into t15_user_score(user_id, score)
values
('100',60),
('101',50),
('102',45),
('103',35),
('104',30),
('105',25),
('106',15),
('107',10)