最真实的大数据SQL面试题（一）

1. 行列转换

描述：表中记录了各年份各部门的平均绩效考核成绩。
表名：t1
表结构：

a -- 年份
b -- 部门
c -- 绩效得分

表内容：

 a   b  c
2014  B  9
2015  A  8
2014  A  10
2015  B  7

问题一：多行转多列

问题描述：将上述表内容转为如下输出结果所示：

 a  col_A col_B
2014  10   9
2015  8    7

参考答案：

select 
    a,
    max(case when b="A" then c end) col_A,
    max(case when b="B" then c end) col_B
from t1
group by a;

问题二：如何将结果转成源表？（多列转多行）

问题描述：将问题一的结果转成源表，问题一结果表名为t1_2。

参考答案：

select 
    a,
    b,
    c
from (
    select a,"A" as b,col_a as c from t1_2 
    union all 
    select a,"B" as b,col_b as c from t1_2  
)tmp; 

问题三：同一部门会有多个绩效，求多行转多列结果

问题描述：2014年公司组织架构调整，导致部门出现多个绩效，业务及人员不同，无法合并算绩效，源表内容如下：

参考答案:

select 
    a,
    max(case when b="A" then c end) col_A,
    max(case when b="B" then c end) col_B
from (
    select 
        a,
        b,
        concat_ws(",",collect_set(cast(c as string))) as c
    from t1
    group by a,b
)tmp
group by a;

2. 排名中取他值

表名：t2
表字段及内容：

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

问题一：按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;

问题二：按a分组取b字段排第二时对应的c字段

输出结果如下所示：

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;

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; 

问题四：按a分组取b字段第二小和第二大时对应的c字段

输出结果如下所示：

a    min_c  max_c
2014  1      1
2015  3      4

参考答案：

select
    ret.a
    ,max(case