在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;

错误和警告： 42803：列“grades.grade”必须出现在GROUP BY子句中或用于聚合函数。

正确的查询语句是什么？

- RogerThat

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;

在你的代码中，你建议的值是第二小的成绩。如果你想要第二大的成绩，请使用 order by 中的 desc。

- Gordon Linoff

Answer 2

从您当前的尝试开始，一个简单的选项使用窗口平均和distinct:

select distinct
    name, 
    avg(grade) over(partition by name) avg_grade, 
    nth_value(grade, 2) over(
        partition by name 
        order by grade
        range between unbounded preceding and unbounded following
    ) grade_n2
from grades;

或者，您可以在子查询中对成绩进行排名，并在外部查询中使用条件聚合：

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

- GMB