描述:1164.指定日期的产品价格
编写一个解决方案,找出在
2019-08-16
时全部产品的价格,假设所有产品在修改前的价格都是10
输出如下 :
数据准备:
Create table If Not Exists Products (product_id int, new_price int, change_date date)
Truncate table Products
insert into Products (product_id, new_price, change_date) values ('1', '20', '2019-08-14')
insert into Products (product_id, new_price, change_date) values ('2', '50', '2019-08-14')
insert into Products (product_id, new_price, change_date) values ('1', '30', '2019-08-15')
insert into Products (product_id, new_price, change_date) values ('1', '35', '2019-08-16')
insert into Products (product_id, new_price, change_date) values ('2', '65', '2019-08-17')
insert into Products (product_id, new_price, change_date) values ('3', '20', '2019-08-18')
分析:
①先将数据根据product_id排序,同时算出change_date与2019-08-16两日期的差值
select product_id, new_price, change_date, datediff(change_date, '2019-08-16') diff from Products order by product_id
②根据diff筛选出小于等于0 的(即在2019-08-16及之前的数据),在被筛选后的数据中找出max(change_date)即离2019-08-16最近的。
select * from t1 where diff <= 0select product_id, new_price, change_date, max(change_date) over(partition by product_id) r2 from t2select * from t3 where r2 = change_date
③将具有完整product_id的表与该表连接,同时用ifnull(new_price,10)完善数据
select t5.product_id, ifnull(new_price,10) price from(select distinct product_id from products)t5 left join t4 on t5.product_id = t4.product_id
代码:
with t1 as (select product_id,
new_price,
change_date,
datediff(change_date, '2019-08-16') diff
from Products
order by product_id)
, t2 as (select *
from t1
where diff <= 0)
,t3 as (
select product_id,
new_price,
change_date,
max(change_date) over(partition by product_id) r2
from t2
)
, t4 as (select * from t3 where r2 = change_date)
select t5.product_id,
ifnull(new_price,10) price
from(select distinct product_id from products)t5
left join t4 on t5.product_id = t4.product_id
;
总结:
筛选2019-08-16时的各商品价格较难
此处用的是datediff()函数先找差值,然后取最值筛选找到各商品与需求日期最近的数据