一、题目

已知有表t_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 t_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 t_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 t_cost_detail
        ) t
    ) tt
where rn = 1 

执行结果

+-----+--------+
| id  | money  |
+-----+--------+
| 6   | 80     |
+-----+--------+

四、建表语句和数据插入

CREATE TABLE IF NOT EXISTS t_cost_detail (
    id bigint, --id
    money bigint -- money
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS ORC;

insert into t_cost_detail(id, money) values
(1,200),
(2,300),
(3,200),
(4,100),
(5,150),
(6,80),
(7,100),
(8,200)

本文同步在微信公众号”数据仓库技术“和个人博客”数据仓库技术“发表。原文: 同时有“数据仓库技术”社群以及有几十位小伙伴一起讨论数据仓库相关技术，欢迎你的加入，社群免费。