包含 AS 的 SQL SELECT 语句

Question

我有 SQL 查询：

select title, scale / next_scale, c
from ( select title, scale, scale*D0 AS c, 
              lead(scale) over(partition by title order by scale asc) as next_scale,
              row_number() over(partition by title order by scale asc) as agg_row
       from signatures
     ) agg
where agg_row = 1;

并且它按预期工作。但是，我真正希望排序“scale”值是多个列之间的算术运算，因此我尝试使用 AS 子句（如上所示）并将查询修改为：

select title, scale / next_scale, c
from ( select title, scale, scale*D0 AS c, 
              lead(scale) over(partition by title order by c asc) as next_scale,
              row_number() over(partition by title order by c asc) as agg_row
       from signatures
     ) agg
where agg_row = 1;

但是，它在 ORDER BY c 处失败。这是为什么呢？我可以替换 ORDER BY scale*D0 并且它工作得很好。然而，我最终会想使用这样的术语：scale*D0*D1*D2*...*D100;我不想计算 3 次不同的时间 - 更不用说查询的物理长度了。我希望有 scale*D0*D1*D2*...*D100 AS c 然后 ORDER BY c。

这可能吗？

我正在使用 PostgreSQL。

非常感谢， 布雷特

Answer 1

在子查询中计算 c：

select title, scale / next_scale, c
from ( select title, scale, c, 
              lead(scale) over(partition by title order by c asc) as next_scale,
              row_number() over(partition by title order by c asc) as agg_row
       from (select title, scale, scale * D0 AS c from signatures) signatures_calc
     ) agg
where agg_row = 1;

Answer 2

您可以按列的序号进行排序：

select title, scale / next_scale, c
from ( select title, scale, scale*D0 AS c, 
          lead(scale) over(partition by title order by scale asc) as next_scale,
          row_number() over(partition by title order by scale asc) as agg_row
   from signatures
 ) agg
where agg_row = 1
order by 3;

Answer 3

下订单时是子查询，应该填单

select title, scale / next_scale, c
from ( select title, scale, scale*D0 AS c, 
          lead(scale) over(partition by title order by scale asc) as next_scale,
          row_number() over(partition by title order by scale asc) as agg_row
       from signatures
       order by 3
 ) agg
where agg_row = 1;