postgresql - 在 OVER() 子句中使用别名

Question

我可以通过 row_number() OVER() 按 count 行对结果进行编号吗？

例如

SELECT *,
    users::numeric/population::numeric*100 AS penetration,
    row_number() OVER(ORDER BY penetration DESC)
FROM states ORDER BY penetration DESC

产生一个错误:

ERROR: column "penetration" does not exist

Answer 1

你不能在 postgresql 中这样做。在此处查找 expression:4.2.8. Window Function Calls ，它说 ..it cannot be output-column names or numbers..

您可以使用子查询:

SELECT states.*, penetration, row_number() OVER(ORDER BY penetration DESC)
  FROM states
  JOIN (SELECT id, users::numeric/population::numeric*100 AS penetration
          FROM states) s_pen on spen.id = states.id
 ORDER BY s_pen.penetration DESC