如何从价格历史表中筛选新增商品与下架商品？

嘿，我来帮你搞定这个问题！首先咱们得明确两个核心逻辑：

• 新增商品：某个店铺（Shop）下的某款商品（Product）第一次出现在记录里的那条数据
• 下架商品：某个店铺下的某款商品最后一次出现在记录里的那条数据（假设后续无该商品的价格变动即代表下架）

一、筛选新增商品 #

我们可以用ROW_NUMBER()窗口函数，按Shop和Product分组，再按日期DATE升序排序，取每组里行号为1的记录——这就是该商品在对应店铺的首次上架记录。

SELECT ID, DATE, Shop, Product, Price
FROM (
    SELECT 
        t.*,
        -- 按店铺+商品分组，按日期升序分配行号
        ROW_NUMBER() OVER (PARTITION BY Shop, Product ORDER BY DATE) AS row_rank
    FROM (
        SELECT 1 ID, '09.01.2018' "DATE", 'CandyShop' Shop, 'Pie' Product, 10 Price FROM DUAL 
        UNION 
        SELECT 2, '10.01.2018', 'CandyShop', 'Pie', 11 FROM DUAL 
        UNION 
        SELECT 3, '11.01.2018', 'CandyShop', 'Pie', 10 FROM DUAL 
        UNION 
        SELECT 4, '12.01.2018', 'CandyShop', 'Pie', 9 FROM DUAL 
        UNION 
        SELECT 5, '10.01.2018', 'CandyShop', 'Candy', 9 FROM DUAL 
        UNION 
        SELECT 6, '11.01.2018', 'CandyShop', 'Candy', 9 FROM DUAL 
    ) t
)
WHERE row_rank = 1;

执行后会返回两条记录：Pie的首次上架（ID=1）和Candy的首次上架（ID=5）。

二、筛选下架商品 #

同样用窗口函数，这次按DATE降序排序，取每组行号为1的记录——这就是该商品在对应店铺的最后一条记录，即下架时刻的记录。

SELECT ID, DATE, Shop, Product, Price
FROM (
    SELECT 
        t.*,
        -- 按店铺+商品分组，按日期降序分配行号
        ROW_NUMBER() OVER (PARTITION BY Shop, Product ORDER BY DATE DESC) AS row_rank
    FROM (
        SELECT 1 ID, '09.01.2018' "DATE", 'CandyShop' Shop, 'Pie' Product, 10 Price FROM DUAL 
        UNION 
        SELECT 2, '10.01.2018', 'CandyShop', 'Pie', 11 FROM DUAL 
        UNION 
        SELECT 3, '11.01.2018', 'CandyShop', 'Pie', 10 FROM DUAL 
        UNION 
        SELECT 4, '12.01.2018', 'CandyShop', 'Pie', 9 FROM DUAL 
        UNION 
        SELECT 5, '10.01.2018', 'CandyShop', 'Candy', 9 FROM DUAL 
        UNION 
        SELECT 6, '11.01.2018', 'CandyShop', 'Candy', 9 FROM DUAL 
    ) t
)
WHERE row_rank = 1;

执行后会返回两条记录：Pie的最后一条记录（ID=4）和Candy的最后一条记录（ID=6）。

补充说明 #

如果你的实际数据中，同一个店铺+商品在同一天有多个价格变动记录，想要保留所有首次/末次日期的记录，可以把ROW_NUMBER()换成RANK()，这样同日期的记录会获得相同的排名，不会被过滤掉。

内容的提问来源于stack exchange，提问作者Vlad Kisly