Hi, 我是小萝卜算子
前两天社群里小伙伴提了一个sql问题,让给提供思路,也是一道经典面试题,就是把数据后缺失的日期补充上,其他数据按上一个有值的数据补充
一、表结构
CREATE TABLE `product`(
`name` string comment "名称",
`dt` string comment "日期",
`amount` int comment "销售金额")
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
二、表数据
name dt amount
iphone 2023-02-03 100
iphone 2023-02-05 300
iphone 2023-02-08 150
mac 2023-02-01 200
mac 2023-02-02 400
mac 2023-02-06 700
airpods 2023-02-02 300
airpods 2023-02-04 200
airpods 2023-02-07 100
airpods 2023-02-11 400
三、期待结果
四、问题分析
本题主要考察两个知识点
1:补充日期行
2:补充日期行对应的amount值
补充日期思路:
1:如果数据库维护的有日期维表,根据日期区间段直接关联出来
2:根据日期区间构建数组,利用posexplode展开,然后使用date_add函数利用展开的索引值获取连续的日期数据
补充amount思路:
1:日期补充后,使之与补充目标行数据分成一组,然后再去join一下原表,把amount补充上
2:直接根据区间段posexplode把数据行展开,对应的amount自然补充
五、解决问题
方式一:
select
tmp.name,
tmp.curr_date,
pt.amount
from
(select
tmp.name,
tmp.curr_date,
max(pt.dt) over(partition by tmp.name order by tmp.curr_date ) dt
from
(select
name,
date_add (minDt, col_idx) curr_date
from
(select
name,
min(dt) minDt,
max(dt) maxDt
from
product
group by name) tmp
lateral VIEW posexplode (
split (space( datediff(maxDt, minDt) + 1), " (?!$)")) tbl_idx AS col_idx,col_val) tmp
left join
product pt
on tmp.name=pt.name and tmp.curr_date = pt.dt ) tmp
inner join product pt
on tmp.name= pt.name and tmp.dt = pt.dt ;
思路详解:
1:先获取每种产品的最大日期和最小日期
2:根据最大日期和最小日期区间段,利用space/split/等函数构建posexplode数据,展开成每个产品的连续日期
3:根据名称和日期 left join 原始表, 没关联到数据的行利用窗口函数,补充成有数据的日期
4:再根据名称和日期关联原始表, 即可得到对应的结果
方式二:
select
tmp.name,
tmp.curr_date,
first_value(amount) over(partition by name,dt order by curr_date) amount
from
(
select
tmp.name,
tmp.curr_date,
max(pt.dt) over(partition by tmp.name order by tmp.curr_date ) dt, pt.amount
from
(select
name,
date_add (minDt, col_idx) curr_date
from
(select
name,
min(dt) minDt,
max(dt) maxDt
from product group by name) tmp lateral VIEW posexplode (
split (space( datediff(maxDt, minDt) + 1), " (?!$)")) tbl_idx AS col_idx,col_val) tmp
left join product pt
on tmp.name=pt.name and tmp.curr_date = pt.dt
) tmp ;
思路详解:
1:前三个阶段同方式一
2:利用窗口函数 first_value, 获取同一组中的第一个有数据的值,即可获得相应的结果
方式三:
select
name,
date_add(dt,col_idx) dt,
amount
from
(
select
name,
dt,
amount,
lead(dt,1,dt) over(partition by name order by dt) next_dt
from
product ) tmp
lateral view posexplode (
split (space( datediff(next_dt, dt)), " (?!$)")
) tbl_idx AS col_idx,col_val;
思路详解:
1:利用窗口函数lead,补充同一组的下一个日期
2:根据当前的日期和和补充的下一个日期,利用space/split等函数构建posexplde数据展开
3:利用date_add函数和posexplode函数展开的索引获取相应日期,且把本行的amount数据补充上
六、总结
三种方式,从不同的角度出发,让小伙伴们对窗口函数的掌握更加熟悉,当然每种方式的效率不一样,但都有值得借鉴的思路
方式一:主要考察数据日期展开,以及展开后针对缺失数据,怎么把它们归到有数据的目标组
方式二:主要考察first_value的运作方式
方式三:是3种方式中效率最高的,利用小范围区间段展开,直接把对应的缺失数据补充上
小伙伴们如果有好的思路,欢迎交流啊