滴滴大数据面试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(),最后考察排序函数开窗。

维度	评分
题目难度	⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️

四、逐步推导

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     |
+-----+--------+

五、常见坑点

坑1：NULL值的隐式处理 — 聚合函数跳过NULL，窗口函数不跳过。WHERE需显式过滤或用COALESCE替代。

坑2：数据类型不一致导致JOIN失效 — string vs int隐式转换可能触发全表扫描，性能骤降。

六、举一反三

1. 增加时间维度对比：按天/周/月分组，观察指标的趋势和季节性波动

2. 增加分组维度：按品类/地区/渠道拆解，发现差异化的业务洞察

3. 设置预警阈值：在WHERE中加阈值判断，自动筛选异常数据

七、知识点总结

考点	说明
ROW_NUMBER + 差值法	连续问题经典解法：日期-行号=分组标识，相同差值同一连续段

八、建表语句和数据插入

点击展开 DDL & DML

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)

📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题