题干信息

产品数据表: products

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| new_price     | int     |
| change_date   | date    |
+---------------+---------+
(product_id, change_date) 是此表的主键（具有唯一值的列组合）。
这张表的每一行分别记录了 某产品 在某个日期 更改后 的新价格。

编写一个解决方案，找出在2019-08-16这一天全部产品的价格，假设所有产品在修改前的价格都是 10 。

以 任意顺序 返回结果表。结果格式如下例所示。 示例 1:

diff
复制代码
输入：
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    |
+------------+-------+

题干解析

由于此表，保存某产品在某个日期更改后的新价格。并且并不是每日都有修改。如果在16号这天的价格有价格修改记录，问题解决就很容易。难点1.在于如何求得不在16日变化的数据。难点2.如果在16日这天修改了多次，取最后新一次价格。

扩展：求解任意日期的产品价格。

思路

由于此表，保存某产品在某个日期更改后的新价格。并且并不是每日都有修改。如果能做出某产品的价格所属的日期区间，就可以解决问题。步骤01.将数据按照 product_id，change_date 分组，再按照修改日期降序给一个序号ROW_NUMBER()，筛选ROW_NUMBER=1的数据，就是当日的最新价格的数据。步骤02.结合题干要素：修改前的价格都是 10。制作产品的最初时间（最初修改时间设置为“1000-01-01”）的价格。步骤03.为了使步骤01.最后一条数据的日期区间完整，补充最后修改日期为（'9999-01-01'）步骤04.筛选ROW_NUMBER()=1的数据，相当于去除同一日期的多次修改数据。步骤05.通过窗口函数 LEAD(change_date, 1, NULL)，获取下次修改的日期 next_change_date。步骤06.筛选 '2019-08-16'的条件为change_date <= '2019-08-16' AND next_change_date > '2019-08-16'

SELECT 
        product_id,
        new_price,
        change_date
FROM (
SELECT 
                row_id,
                product_id,
                new_price,
                change_date,
                LEAD(change_date, 1, NULL)OVER(PARTITION BY product_id ORDER BY change_date) AS next_change_date
FROM (
                SELECT	
                        1 AS row_id,
                        product_id,
                        10 AS new_price,
                        CAST('1000-01-01' AS date)  AS change_date
                FROM iboard.products
                group BY product_id
                UNION ALL 
                SELECT 
                        ROW_NUMBER()OVER(PARTITION BY product_id, change_date ORDER BY change_date DES,id DESC) AS row_id,
                        product_id,
                        new_price,
                        change_date
                FROM iboard.products
                UNION ALL 
                SELECT	
                        1 AS row_id,
                        product_id,
                        10 AS new_price,
                        CAST('9999-01-01' AS date)  AS change_date
                FROM iboard.products
                group BY product_id
        ) AS ttt
        WHERE ttt.row_id = 1
)AS tmp

WHERE change_date <= '2019-08-16'
AND next_change_date > '2019-08-16'

leetcode高频SQL [1164. 指定日期的产品价格]

题干信息

题干解析

思路