0- 描述
描述:时间序列–补全数据
表名:t20
表字段及内容:
date_id a b c
2014 AB 12 bc
2015 23
2016 d
2017 BC
1- 问题一
描述:如何使用最新数据补全表格
输出结果如下所示:
date_id a b c
2014 AB 12 bc
2015 AB 23 bc
2016 AB 23 d
2017 BC 23 d
参考答案:
select
date_id,
first_value(a) over(partition by aa order by date_id) as a,
first_value(b) over(partition by bb order by date_id) as b,
first_value(c) over(partition by cc order by date_id) as c
from
(
select
date_id,
a,
b,
c,
count(a) over(order by date_id) as aa,
count(b) over(order by date_id) as bb,
count(c) over(order by date_id) as cc
from t20
)tmp1;