Rails 5.2+PostgreSQL：筛选最后两次价格下跌的Item

需求明确 #

基于PostgreSQL的Rails 5.2应用中，Item与ItemPrice为一对多关联（一个Item对应多条ItemPrice记录）。需要筛选出最新关联的ItemPrice价格小于上一次记录价格的Item——比如Item1最新价6<上一次价8符合条件，Item2最新价22>上一次价20不符合。

前提：确保模型关联正确 #

先确认模型关联定义（如果还没设置）：

# app/models/item.rb
class Item < ApplicationRecord
  has_many :item_prices, -> { order(created_at: :desc) }, dependent: :destroy
end

# app/models/item_price.rb
class ItemPrice < ApplicationRecord
  belongs_to :item
end

注：用created_at排序比id更严谨，若业务依赖id排序，可替换为order(id: :desc)

解决方案1：用PostgreSQL窗口函数（高效推荐） #

利用PostgreSQL的LAG窗口函数，一次性获取每个Item的最近两次价格，再筛选符合条件的记录：

# 生成子查询：获取每个Item的最新价与上一次价格
price_change_subquery = ItemPrice.select(
  :item_id,
  'price AS current_price',
  'LAG(price) OVER (PARTITION BY item_id ORDER BY created_at DESC) AS previous_price'
)

# 关联Item表，筛选最新价小于上一次价格的Item，去重避免重复记录
Item.joins("INNER JOIN (#{price_change_subquery.to_sql}) AS price_changes ON items.id = price_changes.item_id")
    .where('price_changes.current_price < price_changes.previous_price')
    .distinct

逻辑说明 #

• PARTITION BY item_id：按Item分组处理价格记录
• ORDER BY created_at DESC：每组内按时间倒序排列，确保最新价格在前
• LAG(price)：获取当前记录的上一条（即上一次）价格
• 最后通过关联筛选出价格下跌的Item，并用distinct避免同一Item被多次返回

解决方案2：子查询分别获取最新/上一次价格（直观易读） #

如果对窗口函数不熟悉，可通过嵌套子查询分别获取每个Item的最新和上一次价格，再做比较：

Item.where(id: ItemPrice.select(:item_id)
                        .where("(SELECT price FROM item_prices ip2 WHERE ip2.item_id = item_prices.item_id ORDER BY created_at DESC LIMIT 1) < (SELECT price FROM item_prices ip3 WHERE ip3.item_id = item_prices.item_id ORDER BY created_at DESC LIMIT 1 OFFSET 1)")
                        .distinct)

逻辑说明 #

• LIMIT 1：获取当前Item的最新价格
• LIMIT 1 OFFSET 1：跳过最新记录，获取上一次价格
• 比较两者大小，筛选出符合条件的item_id，再关联Item表

之前写法的问题 #

你之前尝试的EXISTS子查询未通过item_id关联到具体Item，导致查询的是全局所有item_prices的最新价格，而非每个Item自身的价格记录，这是筛选失败的核心原因。

内容的提问来源于stack exchange，提问作者Guillaume Bihet