postgresql - 没有子查询的窗口函数分区和排序

Question

给定一个像 postgres 中这样的简单表格:

CREATE TABLE products (
   product_id serial PRIMARY KEY,
   group_id INT NOT NULL,
   price DECIMAL (11, 2)
);

INSERT INTO products (product_id, group_id,price)
VALUES
   (1, 1, 200),
   (2, 1, 400),
   (3, 1, 500),
   (4, 1, 900),
   (5, 2, 1200),
   (6, 2, 700),
   (7, 2, 700),
   (8, 2, 800),
   (9, 3, 700),
   (10, 3, 150),
   (11, 3, 200);

如何使用窗口函数查询 group_id 和 avg_price，按 avg_price 排序？所以我目前的结果只是通过一个子查询:

select * from (
  select 
    distinct group_id, 
    avg(price) over (partition by group_id) avg_price 
  from products) 
a order by avg_price desc; 

但我相信有更优雅的解决方案。

Answer 1

除了SELECT子句外，ORDER BY子句中还可以使用窗口函数，所以下面的查询是有效的:

SELECT
    group_id,
    AVG(price) OVER (PARTITION BY group_id) avg_price
FROM products
ORDER BY
    AVG(price) OVER (PARTITION BY group_id);

但是，鉴于您似乎想要使用 DISTINCT，我怀疑您在这里真正想要的是一个 GROUP BY 查询:

SELECT
    group_id,
    AVG(price) AS avg_price
FROM products
GROUP BY
    group_id
ORDER BY
    AVG(price);