ruby-on-rails - Postgres 列为列

Question

我有以下查询:

SELECT name, rank() OVER (PARTITION BY user_id ORDER BY love_count DESC) AS position FROM items

现在我想在 rank() 函数上做一个 where 子句:

SELECT name, rank() OVER (PARTITION BY user_id ORDER BY love_count DESC) AS position FROM items WHERE position = 1

也就是我要查询每个用户最喜欢的商品。但是，这会导致:

PGError:错误:列“位置”不存在

此外，我正在使用 Rails AREL 来执行此操作并希望启用链接。这是创建查询的 Ruby 代码:

Item.select("name, rank() OVER (PARTITION BY user_id ORDER BY love_count DESC) AS position").where("position = 1")

有什么想法吗？

Answer 1

您需要将其“包装”到派生表中:

SELECT * 
FROM (
    SELECT name, 
           rank() OVER (PARTITION BY user_id ORDER BY love_count DESC) AS position 
    FROM items
) t
WHERE position = 1