Postgres 排名为列

发布于 2024-12-09 19:38:41 字数 639 浏览 1 评论 0原文

我有以下查询：

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: ERROR: column "position" does not excite

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

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

有什么想法吗？

原文

I have the following query:

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

And I'd now like to do a where clause on the rank() function:

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

That is, I want to query the most loved item for each user. However, this results in:

PGError: ERROR: column "position" does not exist

Also, I'm using Rails AREL to do this and would like to enable chaining. This is the Ruby code that creates the query:

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

Any ideas?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

寄与心 2024-12-16 19:38:41

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

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

You need to "wrap" it into a derived table:

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

回复收藏 0 原文

明月夜 2024-12-16 19:38:41

我的第一个想法是，“使用通用表表达式”，就像这个未经测试的表达式一样。

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

您看到的问题与 SQL 标准所需的计算逻辑顺序有关。 SQL 必须表现得好像列别名（AS 运算符的参数）不存在，直到 WHERE 子句求值之后。

My first thought was, "Use a common table expression", like this untested one.

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

The problem you're seeing has to do with the logical order of evaluation required by SQL standards. SQL has to act as if column aliases (arguments to the AS operator) don't exist until after the WHERE clause is evaluated.

回复收藏 0 原文

~没有更多了~