sql - 如何在 Postgres 中使用带有数学运算的 order by 子句聚合结果

Question

我有一个问题如下

select "products".*,
       AVG(score_values.score) as average_scores,
       (select count(*) from "comments" where "products"."id" = "comments"."product_id") as comments_count
from "products"
       inner join "score_values" on "products"."id" = "score_values"."product_id" and "score_values"."active" = 1
group by "products"."id"
order by average_scores desc
limit 5

当我将数学运算符添加到 order 子句时，出现列不存在的错误。

order by average_scores * 0.9 + comments_count * 5 / 1000 desc

[42703] 错误:列“average_scores”不存在

我该如何解决这个问题？

Answer 1

你有两个选择:

1. 重复 ORDER BY 子句中的表达式:

   ORDER BY AVG(score_values.score) * 0.9
            + (select count(*) from "comments"
               where "products"."id" = "comments"."product_id") * 5 / 1000
   

2. 使用 GMB 的答案建议的子查询。

第二个选项更好。

请注意此行为 is documented :

A sort_expression can also be the column label or number of an output column, as in:

SELECT a + b AS sum, c FROM table1 ORDER BY sum;
SELECT a, max(b) FROM table1 GROUP BY a ORDER BY 1;

both of which sort by the first output column. Note that an output column name has to stand alone, that is, it cannot be used in an expression — for example, this is not correct:

SELECT a + b AS sum, c FROM table1 ORDER BY sum + c;          -- wrong

This restriction is made to reduce ambiguity.