HiveSQL 面试题 - 查询指定日期的全部商品价格

1 需求

查询所有商品（sku_info表）截至到2021年10月01号的最新商品价格（需要结合价格修改表进行分析）。

• 需要用到的表

商品信息表：sku_info

sku_id(商品id)	name(商品名称)	category_id(分类id)	from_date(上架日期)	price(商品价格)
1	xiaomi 10	1	2020-01-01	2000
6	洗碗机	2	2020-02-01	2000
9	自行车	3	2020-01-01	1000

商品价格变更明细表：sku_price_modify_detail

sku_id(商品id)	new_price(本次变更之后的价格)	change_date(变更日期)
1	1900.00	2021-09-25
1	2000.00	2021-09-26
2	80.00	2021-09-29
2	10.00	2021-09-30

• 期望结果

sku_id （商品id）	price <decimal(16,2)> （商品价格）
1	2000.00
2	10.00
3	5000.00
4	6000.00
5	500.00
6	2000.00
7	100.00
8	600.00
9	1000.00
10	90.00
11	66.00
12	20.00

2 实现

• 思路

结合商品价格表，Join 之后可以得到每个商品在每一个时间点的价格信息，有了这份中间数据，就可以利用开窗函数（row_number / rank，last_value 等）获取到最新的日期。

• 实现

1 关联商品信息表和价格表获取中间数据

select sku_info.sku_id, change_date, nvl(new_price, price) new_price from sku_info
left join (select * from sku_price_modify_detail where change_date <= '2021-10-01') pc
           on sku_info.sku_id = pc.sku_id

sku_id	change_date	new_price
1	2021-09-25	1900.0
1	2021-09-26	2000.0
2	2021-09-29	80.0
2	2021-09-30	10.0
3	2021-09-25	4999.0
3	2021-09-26	5000.0
4	2021-09-26	5600.0
4	2021-09-27	6000.0
5	2021-09-27	490.0
5	2021-09-28	500.0
6	2021-09-30	1988.0
6	2021-10-01	2000.0
7	2021-09-28	88.0
7	2021-09-29	100.0
8	2021-09-28	800.0
8	2021-09-29	600.0
9	2021-09-27	1100.0
9	2021-09-28	1000.0
10	2021-10-01	90.0
11	2021-10-01	66.0
12	2021-09-28	35.0
12	2021-09-29	20.0

2 使用开窗函数查找到最新的日期

select distinct sku_id,
                last_value(new_price) over (partition by sku_id order by change_date asc rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) price
from 
(
	select sku_info.sku_id, change_date, nvl(new_price, price) new_price from sku_info
	left join (select * from sku_price_modify_detail where change_date <= '2021-10-01') pc
           on sku_info.sku_id = pc.sku_id
) t1

sku_id	price
1	2000.0
10	90.0
11	66.0
12	20.0
2	10.0
3	5000.0
4	6000.0
5	500.0
6	2000.0
7	100.0
8	600.0
9	1000.0