sql - 在SQL中查找每个组对应的第N个值和平均值

Question

我有一个表，其中包含一些用户的姓名和成绩

CREATE TABLE grades (name varchar(100), grade integer);
insert into grades
values
('Bob', 12),
('Bob', 13),
('Bob', 23),
('Bob', 17),
('James', 15),
('James', 27),
('Nick ', 18),
('Nick ', 16),
('Nick ', 22),
('Nick ', 32),
('Nick ', 19);

我想要一个按名称分组的输出表，以及每个名称的平均成绩和第 n 个最低值。
我尝试使用窗口函数 nth_value() 但是当我尝试执行查询时出现错误(此处为 n = 2)

select name, avg(grade), nth_value(grade, 2) over(partition by name 
                                      order by grade
                                      Range BETWEEN
                                      UNBOUNDED PRECEDING AND
                                      UNBOUNDED FOLLOWING)
                           
from grades group by name;

Error(s), warning(s):

42803: column "grades.grade" must appear in the GROUP BY clause or be used in an aggregate function

编写此类查询的正确方法是什么？

Answer 1

使用条件聚合:

select name, avg(grade), 
       max(grade) filter (where seqnum = 2)                            
from (select g.*,
             row_number() over (partition by name order by grade) as seqnum
      from grades g
     ) g
group by name;

nth_value()是窗口函数而不是聚合函数。
您还可以使用数组:

select name, avg(grade), 
       (array_agg(grade order by grade))[2]
from (select g.*,
             row_number() over (partition by name order by grade) as seqnum
      from grades g
     ) g
group by name;

在您的代码中，您建议的值是第二小的等级。如果你想要第二大，使用 desc为 order by s。