常见大数据面试SQL-奖金瓜分问题
一、题目
在活动大促中，有玩游戏瓜分奖金环节。现有奖金池为 10000元，代表奖金池中的初始额度。用户的分数信息如下表。表中的数据代表每一个用户和其对应的得分，user_id 和 score 都不会有重复值。瓜分奖金的规则如下：按照 score 从高到低依次瓜分，每个人都能分走当前奖金池里面剩余奖金的一半，当奖金池里面剩余的奖金少于 250 时（不含），则停止瓜分奖金。 现在需要查询出所有分到奖金的 user_id 和其对应的奖金。
样例数据
+----------+--------+
| user_id | score |
+----------+--------+
| 100 | 60 |
| 101 | 45 |
| 102 | 45 |
| 103 | 35 |
| 104 | 30 |
| 105 | 25 |
| 106 | 15 |
| 107 | 10 |
+----------+--------+二、分析
该题目相对简单，给用户根据score进行排名，然后依次给出金额即可。难点是如何计算瓜分金额、如何计算奖池余额。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️ |
三、SQL
1.计算排名
使用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 |
| 102 | 45 | 2 |
| 101 | 45 | 3 |
| 103 | 35 | 4 |
| 104 | 30 | 5 |
| 105 | 25 | 6 |
| 106 | 15 | 7 |
| 107 | 10 | 8 |
+----------+--------+-----+2.瓜分奖金
每个人得到当前奖池的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 | 45 | 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 |
+----------+--------+----------+3.限制瓜分条件，得到最终结果
题目中要求：当奖金池里面剩余的奖金少于 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 | 45 | 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',45),
('102',45),
('103',35),
('104',30),
('105',25),
('106',15),
('107',10)本文同步在微信公众号”数据仓库技术“和个人博客”数据仓库技术 (opens in a new tab)“发表；