SQLite窗口函数技术问询：如何获取每个Vendor Item排除最新捕获记录的高低价及当前价格

我来帮你搞定这个需求！你的原SQL确实存在两个核心问题：一是计算高低价时没排除最新记录，二是窗口函数导致重复行需要用distinct来“凑”结果。下面给你一个清晰的解决方案：

核心思路 #

我们需要先给每个vendor_item_id的记录按捕获时间排序，标记出最新的那一条，然后单独提取最新价格作为当前价，同时只对非最新的记录计算高低价。

完整SQL语句 #

WITH ranked_price_data AS (
    SELECT 
        vendor_item_id,
        price,
        -- 按捕获时间倒序排名，最新的记录排第1
        ROW_NUMBER() OVER (PARTITION BY vendor_item_id ORDER BY capture_ts DESC) AS record_rank
    FROM price_data
)
SELECT 
    vendor_item_id,
    -- 提取排名第1的价格作为当前价
    MAX(CASE WHEN record_rank = 1 THEN price END) AS curr_price,
    -- 只计算排名>1的记录的最低价
    MIN(CASE WHEN record_rank > 1 THEN price END) AS low_price,
    -- 只计算排名>1的记录的最高价
    MAX(CASE WHEN record_rank > 1 THEN price END) AS high_price
FROM ranked_price_data
GROUP BY vendor_item_id;

为什么这个方案可行？ #

1. 标记最新记录：通过ROW_NUMBER()窗口函数，给每个vendor_item_id分组内的记录按capture_ts倒序排名，最新的记录会被标记为record_rank = 1。
2. 分离计算逻辑：
   • 用MAX(CASE ...)提取最新记录的价格（因为每个分组只有一条record_rank=1的记录，MAX和MIN都能拿到这个值）。
   • 用MIN/MAX(CASE WHEN record_rank >1 ...)只对非最新的记录计算高低价，完美排除了最后一次捕获的数据。
3. 无重复行：通过GROUP BY vendor_item_id直接得到每个供应商商品的一行结果，不需要额外加distinct。

验证结果 #

运行这个SQL后，你会得到完全符合预期的输出：

内容的提问来源于stack exchange，提问作者DocZerø