LeetCode Product Price at a Given Date题SQL代码错误排查求助

核心错误点 #

• 错误1：CTE1分组逻辑错误
  你在CTE1中按product_id分组后仅聚合了MAX(change_date)，但直接取new_price作为价格不符合SQL分组逻辑：非分组键、非聚合列的取值是不确定的，你拿到的new_price并非最大change_date对应记录的价格，这就是product_id1返回20而非预期35的直接原因。
• 错误2：CTE2逻辑完全错误
  针对首次调价在2019-08-16之后的产品，要求返回默认价格10，而你写的(new_price - 10) AS price完全不符合需求，也不需要额外分组统计大于目标日期的记录。

修正思路 #

1. 先取出所有产品的唯一ID列表
2. 对每个产品，找到所有<=2019-08-16的调价记录中日期最大的那一条，取对应的new_price
3. 左关联两个结果，没有匹配到调价记录的产品价格默认填10即可

修正后代码 #

子查询实现 #

WITH all_products AS (
    -- 拿到所有产品的唯一ID
    SELECT DISTINCT product_id FROM Products
),
last_valid_price AS (
    -- 找到每个产品在目标日期前最后一次调价的价格
    SELECT product_id, new_price AS price
    FROM Products p
    WHERE (product_id, change_date) IN (
        SELECT product_id, MAX(change_date)
        FROM Products
        WHERE change_date <= '2019-08-16'
        GROUP BY product_id
    )
)
SELECT 
    a.product_id,
    COALESCE(l.price, 10) AS price
FROM all_products a
LEFT JOIN last_valid_price l ON a.product_id = l.product_id

窗口函数实现（逻辑更清晰） #

WITH price_rn AS (
    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'
),
all_products AS (
    SELECT DISTINCT product_id FROM Products
)
SELECT 
    a.product_id,
    COALESCE(p.new_price, 10) AS price
FROM all_products a
LEFT JOIN price_rn p ON a.product_id = p.product_id AND p.rn = 1

内容的提问来源于stack exchange，提问作者Enrique Cool