1164. 指定日期的产品价格
产品数据表: Products
+---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | new_price | int | | change_date | date | +---------------+---------+ (product_id, change_date) 是此表的主键(具有唯一值的列组合)。 这张表的每一行分别记录了 某产品 在某个日期 更改后 的新价格。
一开始,所有产品价格都为 10。
编写一个解决方案,找出在 2019-08-16
所有产品的价格。
以 任意顺序 返回结果表。
结果格式如下例所示。
示例 1:
输入: Products 表: +------------+-----------+-------------+ | product_id | new_price | change_date | +------------+-----------+-------------+ | 1 | 20 | 2019-08-14 | | 2 | 50 | 2019-08-14 | | 1 | 30 | 2019-08-15 | | 1 | 35 | 2019-08-16 | | 2 | 65 | 2019-08-17 | | 3 | 20 | 2019-08-18 | +------------+-----------+-------------+ 输出: +------------+-------+ | product_id | price | +------------+-------+ | 2 | 50 | | 1 | 35 | | 3 | 10 | +------------+-------+
和之前做过的题非常类似,主要是LEFT JOIN的运用,但是在LEFT JOIN的时候要注意连接的条件问题。
思路:
1.先用rank窗口函数按照产品id分组并按照改变日期倒序排序【要筛选2019-08-16前的日期】,以该查询创建临时表t
2.按照产品id分组建立临时表p左连接表t,rk字段要建立一个虚拟表筛选出最大的数,不能用在WHERE
# Write your MySQL query statement below
with t as (
SELECT product_id,new_price,rank() OVER (PARTITION BY product_id ORDER BY change_date DESC) AS rk
FROM Products
WHERE change_date <= "2019-08-16"
)
SELECT p.product_id,IFNULL(t.new_price,10) AS price
FROM
(SELECT product_id FROM Products GROUP BY product_id
) p LEFT JOIN
(SELECT * FROM t WHERE rk = 1
) t ON p.product_id = t.product_id