SQL取出累计值与1000相差最小差值的id【滴滴面试】--HiveSQL面试题27_用一个sql取出累计与1000相差最小值的id

目录

0 需求分析

1 数据准备

2 数据分析

3 小 结

---

0 需求分析

问题: table1（id：自增id，money：费用）问题：按id顺序累加money，取出累计值与1000相差绝对值最小差值的id。

表如下：

1 数据准备

（1）数据

vim money.txt

1	100
2	300
3	500
4	700
5	200
6	400
7	500
8	700
9	900
10	100
11	400
12	700

(2) 建表

create table if not exists money(
 
id string,
money string
 
)
 
row format delimited
fields terminated by '\t'
 
;

(3) 加载数据

load data local inpath "/home/centos/dan_test/money.txt" into table money;

(4) 查询数据

hive> select * from money;
OK
1	100
2	300
3	500
4	700
5	200
6	400
7	500
8	700
9	900
10	100
11	400
12	700
Time taken: 0.176 seconds, Fetched: 12 row(s)

2 数据分析

目标：按id顺序累加money，取出累计值与1000相差绝对值最小差值的id。

• （1）按id顺序累加money，比较好计算。sum(money) over(order by id)
•   (2)  取出累计值与1000的差值：abs(sum(money) over(order by id)  -1000) as diff
•   (3) 按差值升序排序，取出第一个即可：first_value(id) (order by diff)

整体SQL如下：

select first_value(id) over(order by diff)
from(
    select id
          ,abs(sum(money) over(order by id) - 1000) as diff
    from money
) t
limit 1

结果如下：

--------------------------------------------------------------------------------
OK
12
Time taken: 13.518 seconds, Fetched: 1 row(s)

另一种写法：

select *
from(
    select id
          ,money
          ,diff
          ,dense_rank() over(order by diff) as dr
    from(
        select id
              ,money
              ,abs(sum(money) over(order by id) - 1000) as diff
        from money
    ) t
) m
where dr=1

结果如下：

--------------------------------------------------------------------------------
OK
12	700	300.0	1
Time taken: 10.546 seconds, Fetched: 1 row(s)

3 小 结

本题为滴滴面试题，难度中等，具体考察知识点如下：

• （1）累积求和问题
• （2）first_value()函数的使用注意去重
• （3）dense_rank（）排名函数的使用

欢迎关注石榴姐公众号"我的SQL呀"，关注我不迷路