特定的背景

在数据分析中，我们经常需要对数据集进行分组并从中提取有价值的信息。

一个常见的场景是对数据按照某些关键字段（如用户ID）进行分组后，进一步筛选出每组内满足特定条件的记录。例如，当我们希望找出每个用户的最后一次下单时间时，我们需要基于用户ID分组，并在每个分组内筛选出具有最大下单时间（order_date）的记录。

然而，在实际操作中，我们可能会遇到一些复杂的情况。比如，考虑这样一个问题：我们需要找到每个产品的最新价格，但这个价格是在某个特定日期之前更新的。具体来说，以力扣1164.指定日期的产品价格为例，我们要找到每个产品在2019-08-16或之前的最新价格。这意味着我们需要根据产品ID (product_id) 和价格更新日期 (change_date) 进行筛选，但只能保留每个产品在指定日期范围内的最高更新日期的价格记录。

这个问题的关键在于，如果我们直接对 product_id 和 new_price 同时进行分组，那么我们将无法有效地筛选出每个产品最新的价格更新日期。这是因为分组操作会针对所有分组键的组合生成结果，而不会自动保留每个组内的最大 change_date。因此，我们需要一种方法来确保在分组的同时，还能正确地选择出每个产品ID对应的最新价格更新日期下的价格。

题目背景

产品数据表: Products

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

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

输入：
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    |
+------------+-------+

一个错误的逻辑

select product_id,new_price as price,max(change_date)
from Products
where change_date <='2019-08-16'
group by product_id

输出内容：

| product_id | price | max(change_date) |
| ---------- | ----- | ---------------- |
| 1          | 20    | 2019-08-16       |
| 2          | 50    | 2019-08-14       |

乍一看好像那么一回事，但是仔细一看会发现 product_id=1的price值为20，显然不是2019-8-16所对应的price，于是这条路走不通

正确的方法1（子查询+窗口函数排序）

既然使用简单的sql无法解决这个方案，那么那就使用子查询吧，也就是多一步查询操作，先给chang_date排序，再对排序结果进行筛选

select product_id,new_price
from (
    select product_id,new_price,
    row_number() over (partition by product_id order by change_date desc) as rn
    from Products
    where change_date <='2019-08-16'
) sub
where rn = 1

输出：

| product_id | new_price |
| ---------- | --------- |
| 1          | 35        |
| 2          | 65        |

很显然这样就对了

正确的方法2 （子查询+聚合函数+连接）

select p.product_id,p.new_price
from Products p
join (
    select product_id,max(change_date) as change_date
    from Products
    where change_date<='2019-08-16'
    group by product_id
) sub
on p.product_id = sub.product_id and p.change_date = sub.change_date

正确的方法 推荐（xxx,xxx in +子查询）

select product_id,new_price
from Products
where (product_id,change_date) in (
   select product_id,max(change_date) change_date
   from Products
   where change_date <= '2019-08-16'
   group by product_id
)

题解

至于最后的对于2019-18-16之前没有数据的product_id，但2019-18-16之后出现的id，只需要使用一次查询来获取所有product_id，再使用left join 和ifnull来解决默认所有product_id修改前的price=10这个要求

with pdt as (
    select distinct product_id
    from Products
),
tmp as (
    select product_id,new_price
    from (
        SELECT product_id, new_price,
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY change_date DESC) AS rn
        FROM Products
        WHERE change_date <= '2019-08-16'
    ) sub
    where rn = 1
)
select pdt.product_id,ifnull(tmp.new_price,10) price
from pdt left join tmp
on pdt.product_id = tmp.product_id