滴滴大数据面试SQL-取出累计值与1000差值最小的记录
一、题目
已知有表t1_cost_detail包含id和money两列，id为自增，请累加计算money值，并求出累加值与1000差值最小的记录。
样例数据
+-----+--------+
| id | money |
+-----+--------+
| 1 | 200 |
| 2 | 300 |
| 3 | 200 |
| 4 | 100 |
| 5 | 150 |
| 6 | 80 |
| 7 | 100 |
| 8 | 200 |
+-----+--------+二、分析
题目描述比较清晰，基本在题目中就展现出了数据处理的逻辑，属于考察硬性知识点。主要考察了聚合函数开窗中，如果排序即进行累积求和sum()over(order by)，然后考察绝对值函数abs(),最后考察排序函数开窗。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️ |
三、SQL
1.计算累积求和
执行SQL
select
id,
money,
sum(money)over(order by id asc) as sum_money
from t1_cost_detail执行结果
+-----+--------+------------+
| id | money | sum_money |
+-----+--------+------------+
| 1 | 200 | 200 |
| 2 | 300 | 500 |
| 3 | 200 | 700 |
| 4 | 100 | 800 |
| 5 | 150 | 950 |
| 6 | 80 | 1030 |
| 7 | 100 | 1130 |
| 8 | 200 | 1330 |
+-----+--------+------------+2.计算累积求和值与1000的差值的绝对值
根据上面结果的sum_money列，我们能够看出题目想要我们求解的是id=6的记录，下面我们先计算累积求和值与1000的差值的绝对值。
执行SQL
select
id,
money,
sum(money)over(order by id asc) as sum_money,
abs(sum(money)over(order by id asc)-1000) as abs_diff
from t1_cost_detail执行结果
+-----+--------+------------+-----------+
| id | money | sum_money | abs_diff |
+-----+--------+------------+-----------+
| 1 | 200 | 200 | 800 |
| 2 | 300 | 500 | 500 |
| 3 | 200 | 700 | 300 |
| 4 | 100 | 800 | 200 |
| 5 | 150 | 950 | 50 |
| 6 | 80 | 1030 | 30 |
| 7 | 100 | 1130 | 130 |
| 8 | 200 | 1330 | 330 |
+-----+--------+------------+-----------+3.查询差值绝对值最小记录
查询最小差值记录，这里可以使用排序函数，row_number、rank、dense_rank 等，在不同的场景和需求下使用不同的，因为这里题目并没有要求，并且不存在最小差值相同，我们随便选一个就好了，个人习惯使用row_number。
执行SQL
select
id,
money
from
(
select
id,
money,
row_number()over(order by abs_diff asc) as rn
from
(
select
id,
money,
sum(money)over(order by id asc) as sum_money,
abs(sum(money)over(order by id asc)-1000) as abs_diff
from t1_cost_detail
) t
) tt
where rn = 1 执行结果
+-----+--------+
| id | money |
+-----+--------+
| 6 | 80 |
+-----+--------+四、建表语句和数据插入
CREATE TABLE IF NOT EXISTS t1_cost_detail (
id bigint, --id
money bigint -- money
);
insert into t1_cost_detail(id, money) values
(1,200),
(2,300),
(3,200),
(4,100),
(5,150),
(6,80),
(7,100),
(8,200)本文同步在微信公众号”数据仓库技术“和个人博客”数据仓库技术 (opens in a new tab)“发表；