Hive SQL经典面试题之最高分问题

哈喽，小伙伴们，欢迎来到小张的频道，今天给大家讲解一道面试中常见的SQL问题------最高分问题，希望能帮助到小伙伴们。还需要给大家说明的是这是一个系列性文章，如果您想了解更多面试题型，希望大家多多关注小张哦~

需求：返回每一门课程和对应的最高分的学生姓名

原始数据 t_score_data.txt：

zhangsan math:90,english:60
lisi chinese:80,math:66,english:77
wangwu chinese:66,math:55,english:80

该题有2种解决方案，分别对应以map类型存储和非map类型存储

1. 以map类型存储

• 建表语句

create TABLE test.t_score(
    name STRING,
    scores map<STRING,INT>
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
    COLLECTION ITEMS TERMINATED BY ','
    MAP KEYS TERMINATED BY ':';

• 导入数据

local inpath '/opt/datas/hive/t_score_data.txt' into table t_score;

+---------------+----------------------------------------+
| t_score.name  |             t_score.scores             |
+---------------+----------------------------------------+
| zhangsan      | {"math":90,"english":60}               |
| lisi          | {"chinese":80,"math":66,"english":77}  |
| wangwu        | {"chinese":66,"math":55,"english":80}  |
+---------------+----------------------------------------+

• 答案：

select name,subject,score from (
    select t2.name,t2.subject,t2.score,
        row_number() over(partition by subject order by score desc ) rank
    from (
        select name,subject,score
        from t_score lateral view explode(scores) t1 as subject,score)t2
) t3
where rank=1;

• 执行结果：

+-----------+----------+--------+
|   name    | subject  | score  |
+-----------+----------+--------+
| lisi      | chinese  | 80     |
| wangwu    | english  | 80     |
| zhangsan  | math     | 90     |
+-----------+----------+--------+

2. 以普通格式进行存储

• 建表语句

create TABLE test.t_score2(
    name STRING,
    scores STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
LINES TERMINATED BY '\n';

• 导入数据

 load data local inpath '/opt/datas/hive/t_score_data.txt' into table t_score2;

+----------------+--------------------------------+
| t_score2.name  |        t_score2.scores         |
+----------------+--------------------------------+
| zhangsan       | math:90,english:60             |
| lisi           | chinese:80,math:66,english:77  |
| wangwu         | chinese:66,math:55,english:80  |
+----------------+--------------------------------+

• 答案

select name,subject,score from (
select name,subject,score,row_number() over (partition by subject order by score desc ) rank from(
    select name,split(subject_score,":")[0] subject,split(subject_score,":")[1] score from
        t_score2 lateral view explode(split(scores,",")) t1 as subject_score) t2)t3
where rank = 1;  

• 执行结果：

+-----------+----------+--------+
|   name    | subject  | score  |
+-----------+----------+--------+
| lisi      | chinese  | 80     |
| wangwu    | english  | 80     |
| zhangsan  | math     | 90     |
+-----------+----------+--------+