如何在 Postgres 中使用按子句排序的聚合结果和数学运算

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 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 的答案建议的那样的子查询。

第二种选择是更好的选择。

请注意，记录了此行为：

sort_expression 也可以是输出列的列标签或编号，如下所示：

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

两者都按第一个输出列排序。请注意，输出列名称必须独立，也就是说，它不能在表达式中使用 — 例如，这是不正确的：

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

进行此限制是为了减少歧义。

Answer 2

可以在order by子句中使用聚合表达式。聚合将计算一次。

select 
products.*,
avg(score_values.score) as average_scores,
count(comments.*) as comments_count
from products
inner join comments on products.id = comments.product_id
inner join score_values on products.id = score_values.product_id and score_values.active = 1
group by products.id
order by avg(score_values.score) * 0.9 + count(comments.*) * 5 / 1000 desc
limit 5

Answer 3

您可以通过将查询包装为子查询以及外部查询中的顺序来解决此问题，例如：

select *
from (
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"
limit 5
) x
order by average_scores * 0.9 + comments_count * 5 / 1000 desc