hive经典面试题7--数据缺失如何通过现有数据填补

背景：
在公司很多业务中，都会涉及数据处理。其中一项数据处理，就是补缺数据。
例如：公司正在投放某个项目，在不同的平台不同的时间投放的成本不一样。由于某些原因，造成打点的成本数据缺失。这时我们就需要通过一定的策略将数据补上。

1.需求：

通过两种策略补数据，补完的数据取平均值，作为要填补的投放成本
补数据策略1：
同一个平台取最近一次投放的有效成本（上一次投放的有效成本）（有效成本：成本不为空）
补数据策略2：
取同一个平台紧接着一次投放的有效成本（下一次投放的有效成本）
结果数据如下：

2.数据准备

---1.创建测试表
create table test.test_advertising
(platform_id string comment '平台id',
put_in_time string comment '投放时间',
cost int comment '成本');

---2.插入测试数据
insert into test.test_advertising values
('1101','2021-01-01',120),
('1102','2021-01-01',150),
('1102','2021-01-02',null),
('1102','2021-01-03',null),
('1102','2021-01-04',200),
('1102','2021-01-05',210),
('1103','2021-01-06',300),
('1103','2021-01-07',null),
('1103','2021-01-08',400),
('1104','2022-01-01',600),
('1104','2022-01-02',null),
('1104','2022-01-03',null),
('1104','2022-01-04',700),
('1104','2022-01-05',null);

3.需求分析

当前成本为空，怎么取上一次投放的有效成本和下一次投放的有效成本？

这里有一个很巧的方式：利用累加的分析函数，给有成本的数据标1，没有成本的数据标0，这样没有成本的数据和最近一次有成本的数据，都是同一个标识。在同一个标识下，取第一条就是上一次投放的有效成本，取最后一条就是下一次投放的有效成本。
代码如下：

spark-sql> select 
         >   platform_id,
         >   put_in_time,
         >   cost,
         >   sum(if(cost is null,0,1)) over(distribute by platform_id sort by put_in_time asc) as group_flag1,
         >   sum(if(cost is null,0,1)) over(distribute by platform_id sort by put_in_time desc) as group_flag2
         > from test.test_advertising
         > order by platform_id,put_in_time;
1101    2021-01-01      120     1       1                                       
1102	2021-01-01		150		1		3
1102	2021-01-02		NULL	1		2
1102	2021-01-03		NULL	1		2
1102	2021-01-04		200		2		2
1102	2021-01-05		210		3		1
1103	2021-01-06		300		1		2
1103	2021-01-07		NULL	1		1
1103	2021-01-08		400		2		1
1104	2022-01-01		600		1		2
1104	2022-01-02		NULL	1		1
1104	2022-01-03		NULL	1		1
1104	2022-01-04		700		2		1
1104	2022-01-05		NULL	2		0
Time taken: 2.183 seconds, Fetched 14 row(s)

4.结果如下：

spark-sql> select
         >   platform_id,
         >   put_in_time,
         >   cost,
         >   first_value(cost) over(partition by platform_id,group_flag1 order by put_in_time asc) as last_cost,
         >   last_value(cost) over(partition by platform_id,group_flag2 order by put_in_time asc rows between current row and unbounded following) as next_cost
         > from
         > (select 
         >   platform_id,
         >   put_in_time,
         >   cost,
         >   sum(if(cost is null,0,1)) over(distribute by platform_id sort by put_in_time asc) as group_flag1,
         >   sum(if(cost is null,0,1)) over(distribute by platform_id sort by put_in_time desc) as group_flag2
         > from test.test_advertising) tmp
         > order by platform_id,put_in_time;
1101    2021-01-01      120     120     120                                     
1102	2021-01-01		150		150		150
1102	2021-01-02		NULL	150		200
1102	2021-01-03		NULL	150		200
1102	2021-01-04		200		200		200
1102	2021-01-05		210		210		210
1103	2021-01-06		300		300		300
1103	2021-01-07		NULL	300		400
1103	2021-01-08		400		400		400
1104	2022-01-01		600		600		600
1104	2022-01-02		NULL	600		700
1104	2022-01-03		NULL	600		700
1104	2022-01-04		700		700		700
1104	2022-01-05		NULL	700		NULL
Time taken: 14.682 seconds, Fetched 14 row(s)