题目:找出在 2019-08-16 时全部产品的价格,假设所有产品在修改前的价格都是 10 。
题目:找出在 2019-08-16 时全部产品的价格,假设所有产品在修改前的价格都是 10 。
准备数据
## 创建库
create tadabase db;
use db;
## 创建表
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', '5', '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');
输入表
分析数据
遇到这种分组后而找其中一行,可以用开窗函数进行排序。
第一步:
## 第一步:利用开窗函数将日期在2019-08-16之前的排序
select product_id,
new_price,
change_date,
row_number() over (partition by product_id order by change_date desc) num
from Products
where change_date <= '2019-08-16';
第二步:
## 第二步:将第一步的开窗作为一个临时表,选出最新的价格
select product_id, new_price, change_date
from (select product_id,
new_price,
change_date,
row_number() over (partition by product_id order by change_date desc) num
from Products
where change_date <= '2019-08-16') t
where t.num = 1) t1;
第三步:
## 第三步:将原表左连接t1表,此时就会出现id=3的列,但是price是null。再利用ifnull()函数将null替换成10
select distinct p1.product_id, ifnull(t1.new_price, 10) as price
from Products p1
left join
(select product_id, new_price, change_date
from (select product_id,
new_price,
change_date,
row_number() over (partition by product_id order by change_date desc) num
from Products
where change_date <= '2019-08-16') t
where t.num = 1) t1
on p1.product_id = t1.product_id;
注意:
ifnull()可以替换成coalesce()
- COALESCE(value1, value2, ..., valueN):返回这些参数的第一个非空值。
- IFNULL(expression_1,expression_2):只有两个参数,不是null返回第一个参数,如果是null,则返回第二个参数。