OVER()子句中别名的使用

Question

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

例如：

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.窗口函数调用，它说..it不能输出-列名或数字..

您可以用户子查询：

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

Answer 2

我不使用postgre，但是对于to，您必须这样做：

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