Usage of aliases in OVER() clause

May i number results via row_number() OVER() by counted rows?

e.g.

SELECT *,
    users::numeric/population::numeric*100 AS penetration,
    row_number() OVER(ORDER BY penetr开发者_开发知识库ation DESC)
FROM states ORDER BY penetration DESC

generates an error:

ERROR: column "penetration" does not exist

you can't do that in postgresql. Look for expression here:4.2.8. Window Function Calls, it says ..it cannot be output-column names or numbers..

You can user subquery:

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

I don't use postgre but for t-sql you have to do something like this:

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