hiveSQL面试题2__排名中取他值

0- 描述

表名：t2
表字段及内容：

a    b   c
2014  A   3
2014  B   1
2014  C   2
2015  A   4
2015  D   3

1- 问题一

描述：按a分组取b字段最小时对应的c字段
输出结果如下所示：

a   min_c
2014  3
2015  4

参考答案:

select
  a,
  c as min_c
from
(
      select
        a,
        b,
        c,
        row_number() over(partition by a order by b) as rn 
      from t2 
)a
where rn = 1;

2- 问题二

描述：按a分组取b字段排第二时对应的c字段
输出结果如下所示：

 a  second_c
2014  1
2015  3

参考答案：

select
  a,
  c as second_c
from
(
      select
        a,
        b,
        c,
        row_number() over(partition by a order by b) as rn 
      from t2 
)a
where rn = 2;

3- 问题三

描述：按a分组取b字段最小和最大时对应的c字段
输出结果如下所示：

a    min_c  max_c
2014  3      2
2015  4      3

参考答案:

select
  a,
  min(if(asc_rn = 1, c, null)) as min_c,
  max(if(desc_rn = 1, c, null)) as max_c
from
(
      select
        a,
        b,
        c,
        row_number() over(partition by a order by b) as asc_rn,
        row_number() over(partition by a order by b desc) as desc_rn 
      from t2 
)a
where asc_rn = 1 or desc_rn = 1
group by a; 

4- 问题四

描述：按a分组取b字段第二小和第二大时对应的c字段
输出结果如下所示：

a    min_c  max_c
2014  1      1
2015  3      4

参考答案：

select
    ret.a
    ,max(case when ret.rn_min = 2 then ret.c else null end) as min_c
    ,max(case when ret.rn_max = 2 then ret.c else null end) as max_c
from (
    select
        *
        ,row_number() over(partition by t2.a order by t2.b) as rn_min
        ,row_number() over(partition by t2.a order by t2.b desc) as rn_max
    from t2
) as ret
where ret.rn_min = 2
or ret.rn_max = 2
group by ret.a;

5- 问题五

描述：按a分组取b字段前两小和前两大时对应的c字段

注意：需保持b字段最小、最大排首位
输出结果如下所示：

a    min_c  max_c
2014  3,1     2,1
2015  4,3     3,4

参考答案：

select
  tmp1.a as a,
  min_c,
  max_c
from 
(
  select 
    a,
    concat_ws(',', collect_list(c)) as min_c
  from
    (
     select
       a,
       b,
       c,
       row_number() over(partition by a order by b) as asc_rn
     from t2
     )a
    where asc_rn <= 2 
    group by a 
)tmp1 
join 
(
  select 
    a,
    concat_ws(',', collect_list(c)) as max_c
  from
    (
     select
        a,
        b,
        c,
        row_number() over(partition by a order by b desc) as desc_rn 
     from t2
    )a
    where desc_rn <= 2
    group by a 
)tmp2 
on tmp1.a = tmp2.a; 

总结：以上主要考察相关窗口函数的深入理解。