[hiveSQL面试题1]

一、示例数据：分数信息表数据如下（表名：score_info）

（一）需求：分组求TopN：求每一门科目，成绩排名前两名的同学的所有信息。

select
    b.name   as name,
    b.course as course,
    b.score  as score,
    b.rank   as rank
from(
    select
        a.name   as name,
        a.course as course,
        a.score  as score,
        row_number() over(partition by course order by score desc) as rank
    from score_info a
)b where b.rank <= 2;

输出结果如下图：

sql解析：
这里用row_number()函数基于over对象按course分组、分组内按score排序。计算的结果值为每组内部排序后顺序编号。最后查询结果时在语句中加入限制条件rank<=2来过滤选取TopN。
函数扩展：
row_number():无论数据有无重复值，排序均顺序递增(如：1，2，3，4……n)
rank():数据有重复值时，后面排序会跳跃增加(如：1，1，3，3，5……n)
dense_rank():数据有重复值时，后面排序不会跳跃 (如：1，1，2，2，3……n)

（二）需求：行转列

select
    a.name as name,
    max(case when course = '语文' then score end) as chinese,
    max(case when course = '数学' then score end) as math,
    max(case when course = '英语' then score end) as english,
    max(case when course = '政治' then score end) as political
from score_info a
group by name;

输出结果如下图：

sql解析：
按照name分组的结果进行case when判断，外面套一层聚合函数（sum和max都可以），这样可以避开最终select出的字段必须包含在group by后面字段的限制 。因为每个用户每门课程只能有一个成绩，所以这样就可以得到最终结果。

（三）需求：列转行，将(二)中结果数据表转换成原始表的样子

select
    a.name as name,
    case when tmp.course = 'chinese'