hiveql查询选择classid，studname，table1的studname，classid，studname classID;

Question

很抱歉在主题上写了一个明显的不良查询，但它准确地描述了我想要的结果。

我有一个带有classid int，studname string，grade int的表。我需要一个结果，列出了每个课程中获得最高成绩的每个类别的studname。多个学生可以达到成绩，每个学生应首先按ClassID列出，然后由StudName列出。

样本输出： | classid | StudName | | ---------- | ----------- | | 101 |玛丽| | 101 | Nate | | 101 |克里斯| | 102 |本杰明| | 103 | Nate | | 103 |汤姆| etc...

my first guess is:

SELECT classID, studName from table1 where grade = MAX(grade) group by classID, studName;

but this gives an error: ... not yet supported place for UDAF 'max'

I have also tried creating a view:

CREATE VIEW newView as select classID, MAX(grade) from table1 group by classID;

and using it in a subquery in the where statement:

select classID, studName from table1 where grade IN (select * from newView) group by classID, studName;

but it seems that : "SubQuery can contain only 1 item in SELECT List"

I have poured over "Apache Hive Essentials" book with no luck either.

我是Hiveql的新手，这让我在晚上起床。任何帮助将不胜感激。

谢谢

Answer 1

您的SQL -
您需要命名最大列，然后在子句中的子查询中使用它。
更改下面的视图。

CREATE VIEW newView as select classID, MAX(grade) as maxgrade from table1 group by classID;

更改您的SQL

select classID, studName from table1 where grade IN (select maxgrade from newView) group by classID, studName;

解决方案
但是，此SQL将通过调整您的SQL来帮助您实现“在每个主题中获得最高成绩的学生”所需的目标。

select classID, studName, grade
from table1 
join (select classID mxid, MAX(grade) as maxgrade from table1 group by classID) mx -- this subquery will pick maximum grade in each class.
ON grade =mx.maxgrade and classID = mx.mxid -- This join will select only the students with maximum grade in each class.