美文网首页
最强最全面的大数据SQL面试题和答案【一】

最强最全面的大数据SQL面试题和答案【一】

作者: 程序员的隐秘角落 | 来源:发表于2021-12-30 12:09 被阅读0次

本文目录：
一、行列转换
二、排名中取他值
三、累计求值
四、窗口大小控制
五、产生连续数值
六、数据扩充与收缩
七、合并与拆分
八、模拟循环操作
九、不使用distinct或group by去重
十、容器--反转内容
十一、多容器--成对提取数据
十二、多容器--转多行
十三、抽象分组--断点排序
十四、业务逻辑的分类与抽象--时效
十五、时间序列--进度及剩余
十六、时间序列--构造日期
十七、时间序列--构造累积日期
十八、时间序列--构造连续日期
十九、时间序列--取多个字段最新的值
二十、时间序列--补全数据
二十一、时间序列--取最新完成状态的前一个状态
二十二、非等值连接--范围匹配
二十三、非等值连接--最近匹配
二十四、N指标--累计去重

一、行列转换

描述：表中记录了各年份各部门的平均绩效考核成绩。
表名：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年公司组织架构调整，导致部门出现多个绩效，业务及人员不同，无法合并算绩效，源表内容如下：

2014  B  9
2015  A  8
2014  A  10
2015  B  7
2014  B  6

输出结果如下所示：

 a    col_A  col_B
2014   10    6,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 (
    select 
        a,
        b,
        concat_ws(",",collect_set(cast(c as string))) as c
    from t1
    group by a,b
)tmp
group by a;

二、排名中取他值

表名：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字段

输出结果如下所示：

 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;

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

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

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

三、累计求值

表名：t3
表字段及内容：

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

问题一：按a分组按b字段排序，对c累计求和

输出结果如下所示：

a    b   sum_c
2014  A   3
2014  B   4
2014  C   6
2015  A   4
2015  D   7

参考答案：

select 
  a, 
  b, 
  c, 
  sum(c) over(partition by a order by b) as sum_c
from t3;

问题二：按a分组按b字段排序，对c取累计平均值

输出结果如下所示：

a    b   avg_c
2014  A   3
2014  B   2
2014  C   2
2015  A   4
2015  D   3.5

参考答案：

select 
  a, 
  b, 
  c, 
  avg(c) over(partition by a order by b) as avg_c
from t3;

问题三：按a分组按b字段排序，对b取累计排名比例

输出结果如下所示：

a    b   ratio_c
2014  A   0.33
2014  B   0.67
2014  C   1.00
2015  A   0.50
2015  D   1.00

参考答案：

select 
  a, 
  b, 
  c, 
  round(row_number() over(partition by a order by b) / (count(c) over(partition by a)),2) as ratio_c
from t3 
order by a,b;

问题四：按a分组按b字段排序，对b取累计求和比例

输出结果如下所示：

a    b   ratio_c
2014  A   0.50
2014  B   0.67
2014  C   1.00
2015  A   0.57
2015  D   1.00

参考答案：

select 
  a, 
  b, 
  c, 
  round(sum(c) over(partition by a order by b) / (sum(c) over(partition by a)),2) as ratio_c
from t3 
order by a,b;

四、窗口大小控制

表名：t4
表字段及内容：

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

问题一：按a分组按b字段排序，对c取前后各一行的和

输出结果如下所示：

a    b   sum_c
2014  A   1
2014  B   5
2014  C   1
2015  A   3
2015  D   4

参考答案：

select 
  a,
  b,
  lag(c,1,0) over(partition by a order by b)+lead(c,1,0) over(partition by a order by b) as sum_c
from t4;

问题二：按a分组按b字段排序，对c取平均值

问题描述：前一行与当前行的均值！

输出结果如下所示：

a    b   avg_c
2014  A   3
2014  B   2
2014  C   1.5
2015  A   4
2015  D   3.5

参考答案：

select
  a,
  b,
  case when lag_c is null then c
  else (c+lag_c)/2 end as avg_c
from
 (
 select
   a,
   b,
   c,
   lag(c,1) over(partition by a order by b) as lag_c
  from t4
 )temp;

五、产生连续数值

输出结果如下所示：

1
2
3
4
5
...
100

参考答案：
不借助其他任何外表，实现产生连续数值
此处给出两种解法，其一：

select
id_start+pos as id
from(
    select
    1 as id_start,
    1000000 as id_end
) m  lateral view posexplode(split(space(id_end-id_start), '')) t as pos, val

其二：

select
  row_number() over() as id
from  
  (select split(space(99), ' ') as x) t
lateral view
explode(x) ex;

那如何产生1至1000000连续数值？

参考答案：

select
  row_number() over() as id
from  
  (select split(space(999999), ' ') as x) t
lateral view
explode(x) ex;

六、数据扩充与收缩

表名：t6
表字段及内容：

a
3
2
4

问题一：数据扩充
输出结果如下所示：

a     b
3   3、2、1
2   2、1
4   4、3、2、1

参考答案：

select  
  t.a,
  concat_ws('、',collect_set(cast(t.rn as string))) as b
from
(  
  select  
    t6.a,
    b.rn
  from t6
  left join
  ( 
   select
     row_number() over() as rn
   from  
   (select split(space(5), ' ') as x) t -- space(5)可根据t6表的最大值灵活调整
   lateral view
   explode(x) pe
  ) b
  on 1 = 1
  where t6.a >= b.rn
  order by t6.a, b.rn desc 
) t
group by  t.a;

问题二：数据扩充，排除偶数
输出结果如下所示：

a     b
3   3、1
2   1
4   3、1

参考答案：

select  
  t.a,
  concat_ws('、',collect_set(cast(t.rn as string))) as b
from
(  
  select  
    t6.a,
    b.rn
  from t6
  left join
  ( 
   select
     row_number() over() as rn
   from  
   (select split(space(5), ' ') as x) t
   lateral view
   explode(x) pe
  ) b
  on 1 = 1
  where t6.a >= b.rn and b.rn % 2 = 1
  order by t6.a, b.rn desc 
) t
group by  t.a;

问题三：如何处理字符串累计拼接

问题描述：将小于等于a字段的值聚合拼接起来

输出结果如下所示：

a     b
3     2、3
2     2
4     2、3、4

参考答案：

select  
  t.a,
  concat_ws('、',collect_set(cast(t.a1 as string))) as b
from
(   
  select  
    t6.a,
    b.a1
  from t6
  left join
  (   
   select  a as a1 
   from t6
  ) b
  on 1 = 1
  where t6.a >= b.a1
  order by t6.a, b.a1 
) t
group by  t.a;

问题四：如果a字段有重复，如何实现字符串累计拼接

输出结果如下所示：

a     b
2     2
3     2、3
3     2、3、3
4     2、3、3、4

参考答案：

select 
  a,
  b
from 
(
 select  
   t.a,
   t.rn,
   concat_ws('、',collect_list(cast(t.a1 as string))) as b
 from
  (   
    select  
     a.a,
     a.rn,
     b.a1
    from
    (
     select  
       a,
       row_number() over(order by a ) as rn 
     from t6
    ) a
    left join
    (   
     select  a as a1,
     row_number() over(order by a ) as rn  
     from t6
    ) b
    on 1 = 1
    where a.a >= b.a1 and a.rn >= b.rn 
    order by a.a, b.a1 
  ) t
  group by  t.a,t.rn
  order by t.a,t.rn
) tt;

问题五：数据展开

问题描述：如何将字符串"1-5,16,11-13,9"扩展成"1,2,3,4,5,16,11,12,13,9"？注意顺序不变。

参考答案：

select  
  concat_ws(',',collect_list(cast(rn as string)))
from
(
  select  
   a.rn,
   b.num,
   b.pos
  from
   (
    select
     row_number() over() as rn
    from (select split(space(20), ' ') as x) t -- space(20)可灵活调整
    lateral view
    explode(x) pe
   ) a lateral view outer 
   posexplode(split('1-5,16,11-13,9', ',')) b as pos, num
   where a.rn between cast(split(num, '-')[0] as int) and cast(split(num, '-')[1] as int) or a.rn = num
   order by pos, rn 
) t;

相关文章

网友评论

      本文标题：最强最全面的大数据SQL面试题和答案【一】

      本文链接：https://www.haomeiwen.com/subject/ywrvqrtx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|最强最全面的大数据SQL面试题和答案【一】|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！