sql - Postgres : How to get row number when ordered by the result of operation?

Question

假设有一个表'items'，它有三个列:'id'、'pos'和'neg'，选择的结果应该按照操作的结果排序pos - neg.

因此，以下应该有效:

SELECT
    id,
    pos - neg AS diff
FROM items
ORDER BY diff DESC

现在我需要获取特定行(在表中)的位置，并通过“差异”对结果进行排序。我试过这个:

WITH summary AS (
    SELECT
        i.id,
        i.pos - i.neg AS diff,
        ROW_NUMBER() OVER(ORDER BY diff) AS position
    FROM items i)
SELECT s.*  FROM summary s WHERE s.id = 351435254

但执行返回错误:“diff”列不存在。

那么，是否有可能获得位置，或者将差异保留在单独的列中会更好？

Answer 1

为避免重复计算，只需将 ROW_NUMBER() 移动到外部查询...

WITH summary AS (
    SELECT
        i.id,
        i.pos - i.neg AS diff       
    FROM
        items i
)
SELECT
    s.*,
    ROW_NUMBER() OVER(ORDER BY s.diff) AS position
FROM
    summary s
WHERE
    s.id = 351435254

在内部查询期间，对diff 的引用将被限制在SELECT 之前 存在的范围内，否则您会得到循环引用。

在外部查询时，您的新字段 diff 已经存在，因此可以作为参数提供给其他计算、函数等。