sql - 与Postgres 9.4一起使用-为什么我的代码无法正常工作？

SELECT
(users.created_at::timestamp - invitations.created_at::timestamp) as elapsed,
percentile_disc(0.50)
      WITHIN GROUP (ORDER BY elapsed) as median
 FROM invitations
 join users on users.id = invitations.user_id

试图找出创建邀请和用户接受邀请之间的中间等待时间。此代码生成错误：

ERROR: column "elapsed" does not exist.

我该怎么修理？

最佳答案

不能在定义别名的同一级别上使用别名。您需要将其包装在派生表中：

select elapsed,
       percentile_disc(0.50) WITHIN GROUP (ORDER BY elapsed) as median
from (
  SELECT users.created_at::timestamp - invitations.created_at::timestamp as elapsed,
  FROM invitations
    join users on users.id = invitations.user_id
) t;