使用Hive查找分组中的第一行

Question

对于以下格式的学生数据库：

Roll Number | School Name | Name | Age | Gender | Class | Subject | Marks

如何找出每个班级的最高分？下面的查询返回整个组，但我只对找到组中第一行感兴趣。

SELECT * FROM scores WHERE score = (SELECT MAX(score) FROM scores GROUP BY class)

SELECT school,
       class,
       roll,
       Sum(marks) AS total
FROM   students
GROUP  BY school,
          class,
          roll
ORDER  BY school,
          class,
          total DESC; 

- Praveen Sripati

Answer 1

使用row_number()还有另一种方法。

select * from (
    select *, 
    row_number() over (partition by school,class,roll order by marks desc) rn
    from students
) t1 where rn = 1

如果您想返回所有获得最高分的并列记录，请使用rank()而不是row_number()。

- FuzzyTree

---

有没有 first() 函数？ - Aguinore

Answer 2

您需要再进行一次分组和连接操作以获得所需的结果。可以使用以下语句实现此目的：

select q1.*, q2.roll from 

(
select school, class, max(total) as max from 
(
select school,class,roll,sum(marks) as total from students group by school,class,roll order by school, class, total desc
)q3 group by school, class
)q1

LEFT OUTER JOIN

(select school,class,roll,sum(marks) as total from students group by school,class,roll order by school, class, total desc)q2

ON (q1.max = q2.total) AND (q1.school = q2.school) AND (q1.class = q2.class)

- Amar

---

谢谢伙计，这个查询很长，需要仔细查看它。 - Praveen Sripati

Answer 3

我们需要在您提供的查询基础上进行构建：

1. 给定的查询将为您提供每个班级每个学生的分数。要找出每个班级所获得的最高总分，您需要从选择中删除学生编号，然后对此查询进行分组。

2. 现在我们知道了学校、班级和每个学校班级的最高总分。您只需找出与该总分相对应的学生编号。为此，需要进行连接操作。

最终查询将如下所示：

select a.school, a.class, b.roll, a.highest_marks from
    (select q.school as school, q.class as class, max(q.total) as highest_marks from(select school, class, roll, sum(marks) as total from students group by school, class, roll)q group by school, class)a
    join
    (select school, class, roll, sum(marks) as total from students group by school, class, roll)b
    on (a.school = b.school) and (a.class = b.class) and (a.highest_marks = b.total)

- Abhishek Pathak