备案控制台
探索云世界
开发者社区 大数据 文章 正文

最强最全面的大数据SQL经典面试题（由31位大佬共同协作完成）（四）

2022-04-27 345
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 本套SQL题的答案是由许多小伙伴共同贡献的，1+1的力量是远远大于2的，有不少题目都采用了非常巧妙的解法，也有不少题目有多种解法。本套大数据SQL题不仅题目丰富多样，答案更是精彩绝伦！

十七、时间序列--构造累积日期


表名：t17


表字段及内容：

date_id
2017-08-01
2017-08-02
2017-08-03


问题一：每一日期，都扩展成月初至当天


输出结果如下所示：

date_id    date_to_day
2017-08-01   2017-08-01
2017-08-02   2017-08-01
2017-08-02   2017-08-02
2017-08-03   2017-08-01
2017-08-03   2017-08-02
2017-08-03   2017-08-03


这种累积相关的表，常做桥接表。


参考答案:

select
  date_id,
  date_add(date_start_id,pos) as date_to_day
from
(
  select
    date_id,
    date_sub(date_id,dayofmonth(date_id)-1) as date_start_id
  from t17
) m  lateral view 
posexplode(split(space(datediff(from_unixtime(unix_timestamp(date_id,'yyyy-MM-dd')),from_unixtime(unix_timestamp(date_start_id,'yyyy-MM-dd')))), '')) t as pos, val;


十八、时间序列--构造连续日期


表名：t18


表字段及内容：

a             b         c
101        2018-01-01     10
101        2018-01-03     20
101        2018-01-06     40
102        2018-01-02     20
102        2018-01-04     30
102        2018-01-07     60


问题一：构造连续日期


问题描述：将表中数据的b字段扩充至范围[2018-01-01, 2018-01-07]，并累积对c求和。


b字段的值是较稀疏的。


输出结果如下所示：

a             b          c      d
101        2018-01-01     10     10
101        2018-01-02      0     10
101        2018-01-03     20     30
101        2018-01-04      0     30
101        2018-01-05      0     30
101        2018-01-06     40     70
101        2018-01-07      0     70
102        2018-01-01      0      0
102        2018-01-02     20     20
102        2018-01-03      0     20
102        2018-01-04     30     50
102        2018-01-05      0     50
102        2018-01-06      0     50
102        2018-01-07     60    110


参考答案:

select
  a,
  b,
  c,
  sum(c) over(partition by a order by b) as d
from
(
  select
  t1.a,
  t1.b,
  case
    when t18.b is not null then t18.c
    else 0
  end as c
  from
  (
    select
    a,
    date_add(s,pos) as b
    from
    (
      select
        a, 
       '2018-01-01' as s, 
       '2018-01-07' as r
      from (select a from t18 group by a) ta
    ) m  lateral view 
      posexplode(split(space(datediff(from_unixtime(unix_timestamp(r,'yyyy-MM-dd')),from_unixtime(unix_timestamp(s,'yyyy-MM-dd')))), '')) t as pos, val
  ) t1
    left join t18
    on  t1.a = t18.a and t1.b = t18.b
) ts;


十九、时间序列--取多个字段最新的值


表名：t19


表字段及内容：

date_id   a   b    c
2014     AB  12    bc
2015         23    
2016               d
2017     BC


问题一：如何一并取出最新日期


输出结果如下所示：

date_a   a    date_b    b    date_c   c
2017    BC    2015     23    2016    d


参考答案:


此处给出三种解法，其一：

SELECT  max(CASE WHEN rn_a = 1 THEN date_id else 0 END) AS date_a
        ,max(CASE WHEN rn_a = 1 THEN a else null END) AS a
        ,max(CASE WHEN rn_b = 1 THEN date_id else 0 END) AS date_b
        ,max(CASE WHEN rn_b = 1 THEN b else NULL  END) AS b
        ,max(CASE WHEN rn_c = 1 THEN date_id  else 0 END) AS date_c
        ,max(CASE WHEN rn_c = 1 THEN c else null END) AS c
FROM    (
            SELECT  date_id
                    ,a
                    ,b
                    ,c
                    --对每列上不为null的值  的 日期 进行排序
                    ,row_number()OVER( PARTITION BY 1 ORDER BY CASE WHEN a IS NULL THEN 0 ELSE date_id END DESC) AS rn_a
                    ,row_number()OVER(PARTITION BY 1 ORDER BY CASE WHEN b IS NULL THEN 0 ELSE date_id END DESC) AS rn_b
                    ,row_number()OVER(PARTITION BY 1 ORDER BY CASE WHEN c IS NULL THEN 0 ELSE date_id END DESC) AS rn_c
            FROM    t19
        ) t
WHERE   t.rn_a = 1
OR      t.rn_b = 1
OR      t.rn_c = 1;


其二：

SELECT  
   a.date_id
  ,a.a
  ,b.date_id
  ,b.b
  ,c.date_id
  ,c.c
FROM
(
   SELECT  
      t.date_id,
      t.a
   FROM  
   (
     SELECT  
       t.date_id
       ,t.a
       ,t.b
       ,t.c
     FROM t19 t INNER JOIN    t19 t1 ON t.date_id = t1.date_id AND t.a IS NOT NULL
   ) t
   ORDER BY t.date_id DESC
   LIMIT 1
) a
LEFT JOIN 
(
  SELECT  
    t.date_id
    ,t.b
  FROM    
  (
    SELECT  
      t.date_id
      ,t.b
    FROM t19 t INNER JOIN t19 t1 ON t.date_id = t1.date_id AND t.b IS NOT NULL
  ) t
  ORDER BY t.date_id DESC
  LIMIT 1
) b ON 1 = 1 
LEFT JOIN
(
  SELECT  
    t.date_id
    ,t.c
  FROM    
  (
    SELECT  
      t.date_id
      ,t.c
    FROM t19 t INNER JOIN t19 t1 ON t.date_id = t1.date_id AND t.c IS NOT NULL
  ) t
  ORDER BY t.date_id DESC
  LIMIT   1
) c
ON 1 = 1;


其三：

select 
  * 
from  
(
  select t1.date_id as date_a,t1.a from (select t1.date_id,t1.a  from t19 t1 where t1.a is not null) t1
  inner join (select max(t1.date_id) as date_id   from t19 t1 where t1.a is not null) t2
  on t1.date_id=t2.date_id
) t1
cross join
(
  select t1.date_b,t1.b from (select t1.date_id as date_b,t1.b  from t19 t1 where t1.b is not null) t1
  inner join (select max(t1.date_id) as date_id   from t19 t1 where t1.b is not null)t2
  on t1.date_b=t2.date_id
) t2
cross join 
(
  select t1.date_c,t1.c from (select t1.date_id as date_c,t1.c  from t19 t1 where t1.c is not null) t1
  inner join (select max(t1.date_id) as date_id   from t19 t1 where t1.c is not null)t2
  on t1.date_c=t2.date_id
) t3;


二十、时间序列--补全数据


表名：t20


表字段及内容：

date_id   a   b    c
2014     AB  12    bc
2015         23    
2016               d
2017     BC


问题一：如何使用最新数据补全表格


输出结果如下所示：

date_id   a   b    c
2014     AB  12    bc
2015     AB  23    bc
2016     AB  23    d
2017     BC  23    d


参考答案:

select 
  date_id, 
  first_value(a) over(partition by aa order by date_id) as a,
  first_value(b) over(partition by bb order by date_id) as b,
  first_value(c) over(partition by cc order by date_id) as c
from
(
  select 
    date_id,
    a,
    b,
    c,
    count(a) over(order by date_id) as aa,
    count(b) over(order by date_id) as bb,
    count(c) over(order by date_id) as cc
  from t20
)tmp1;


二十一、时间序列--取最新完成状态的前一个状态


表名：t21


表字段及内容：

date_id   a    b
2014     1    A
2015     1    B
2016     1    A
2017     1    B
2013     2    A
2014     2    B
2015     2    A
2014     3    A
2015     3    A
2016     3    B
2017     3    A


上表中B为完成状态。


问题一：取最新完成状态的前一个状态


输出结果如下所示：

date_id  a    b
2016     1    A
2013     2    A
2015     3    A


参考答案:


此处给出两种解法，其一：


select
    t21.date_id,
    t21.a,
    t21.b
from
    (
        select
            max(date_id) date_id,
            a
        from
            t21
        where
            b = 'B'
        group by
            a
    ) t1
    inner join t21 on t1.date_id -1 = t21.date_id
and t1.a = t21.a;


其二：

select
  next_date_id as date_id
  ,a
  ,next_b as b
from(
  select
    *,min(nk) over(partition by a,b) as minb
  from(
    select
      *,row_number() over(partition by a order by date_id desc) nk
      ,lead(date_id) over(partition by a order by date_id desc) next_date_id
      ,lead(b) over(partition by a order by date_id desc) next_b
    from(
      select * from t21
    ) t
  ) t
) t
where minb = nk and b = 'B';


问题二：如何将完成状态的过程合并


输出结果如下所示:

a   b_merge
1   A、B、A、B
2   A、B
3   A、A、B


参考答案：

select
  a
  ,collect_list(b) as b
from(
  select
    *
    ,min(if(b = 'B',nk,null)) over(partition by a) as minb
  from(
    select
      *,row_number() over(partition by a order by date_id desc) nk
    from(
      select * from t21
    ) t
  ) t
) t
where nk >= minb
group by a;


二十二、非等值连接--范围匹配


表f是事实表，表d是匹配表，在hive中如何将匹配表中的值关联到事实表中？

表d相当于拉链过的变化维，但日期范围可能是不全的。


表f：

date_id  p_id
 2017    C
 2018    B
 2019    A
 2013    C


表d：

d_start    d_end    p_id   p_value
 2016     2018     A       1
 2016     2018     B       2
 2008     2009     C       4
 2010     2015     C       3


问题一：范围匹配


输出结果如下所示：

date_id  p_id   p_value
 2017    C      null
 2018    B      2
 2019    A      null
 2013    C      3


**参考答案：


此处给出两种解法，其一：

select 
  f.date_id,
  f.p_id,
  A.p_value
from f 
left join 
(
  select 
    date_id,
    p_id,
    p_value
  from 
  (
    select 
      f.date_id,
      f.p_id,
      d.p_value
    from f 
    left join d on f.p_id = d.p_id
    where f.date_id >= d.d_start and f.date_id <= d.d_end
  )A
)A
ON f.date_id = A.date_id;


其二：

select 
    date_id,
    p_id,
    flag as p_value
from (
    select 
        f.date_id,
        f.p_id,
        d.d_start,
        d.d_end,
        d.p_value,
        if(f.date_id between d.d_start and d.d_end,d.p_value,null) flag,
        max(d.d_end) over(partition by date_id) max_end
    from f
    left join d
    on f.p_id = d.p_id
) tmp
where d_end = max_end;


二十三、非等值连接--最近匹配


表t23_1和表t23_2通过a和b关联时，有相等的取相等的值匹配，不相等时每一个a的值在b中找差值最小的来匹配。


t23_1和t23_2为两个班的成绩单，t23_1班的每个学生成绩在t23_2班中找出成绩最接近的成绩。


表t23_1：a中无重复值

a
1
2
4
5
8
10


表t23_2：b中无重复值

b
2
3
7
11
13


问题一：单向最近匹配


输出结果如下所示：


注意：b的值可能会被丢弃


a    b
1    2
2    2
4    3
5    3
5    7
8    7
10   11


参考答案：


select 
  * 
from
(
  select 
    ttt1.a,
    ttt1.b 
  from
  (
    select 
      tt1.a,
      t23_2.b,
      dense_rank() over(partition by tt1.a order by abs(tt1.a-t23_2.b)) as dr 
    from 
    (
      select 
        t23_1.a 
      from t23_1 
      left join t23_2 on t23_1.a=t23_2.b 
      where t23_2.b is null
    ) tt1 
    cross join t23_2
  ) ttt1 
  where ttt1.dr=1 
  union all
  select 
    t23_1.a,
    t23_2.b 
  from t23_1 
  inner join t23_2 on t23_1.a=t23_2.b
) result_t 
order by result_t.a;


二十四、N指标--累计去重


假设表A为事件流水表，客户当天有一条记录则视为当天活跃。


表A：

time_id          user_id
2018-01-01 10:00:00    001
2018-01-01 11:03:00    002
2018-01-01 13:18:00    001
2018-01-02 08:34:00    004
2018-01-02 10:08:00    002
2018-01-02 10:40:00    003
2018-01-02 14:21:00    002
2018-01-02 15:39:00    004
2018-01-03 08:34:00    005
2018-01-03 10:08:00    003
2018-01-03 10:40:00    001
2018-01-03 14:21:00    005


假设客户活跃非常，一天产生的事件记录平均达千条。


问题一：累计去重


输出结果如下所示：

日期       当日活跃人数     月累计活跃人数_截至当日
date_id   user_cnt_act    user_cnt_act_month
2018-01-01      2                2
2018-01-02      3                4
2018-01-03      3                5


参考答案：

SELECT  tt1.date_id
       ,tt2.user_cnt_act
       ,tt1.user_cnt_act_month
FROM
(   -- ④ 按照t.date_id分组求出user_cnt_act_month，得到tt1
  SELECT  t.date_id
         ,COUNT(user_id) AS user_cnt_act_month
  FROM
  (   -- ③ 表a和表b进行笛卡尔积，按照a.date_id,b.user_id分组，保证截止到当日的用户唯一，得出表t。
    SELECT  a.date_id
           ,b.user_id
    FROM
    (   -- ① 按照日期分组，取出date_id字段当主表的维度字段 得出表a
      SELECT  from_unixtime(unix_timestamp(time_id),'yyyy-MM-dd') AS date_id
      FROM test.temp_tanhaidi_20211213_1
      GROUP BY  from_unixtime(unix_timestamp(time_id),'yyyy-MM-dd')
    ) a
    INNER JOIN
    (   -- ② 按照date_id、user_id分组，保证每天每个用户只有一条记录，得出表b
      SELECT  from_unixtime(unix_timestamp(time_id),'yyyy-MM-dd') AS date_id
             ,user_id
      FROM test.temp_tanhaidi_20211213_1
      GROUP BY  from_unixtime(unix_timestamp(time_id),'yyyy-MM-dd')
               ,user_id
    ) b
    ON 1 = 1
    WHERE a.date_id >= b.date_id
    GROUP BY  a.date_id
             ,b.user_id
  ) t
  GROUP BY  t.date_id
) tt1
LEFT JOIN
(   -- ⑥ 按照date_id分组求出user_cnt_act，得到tt2
  SELECT  date_id
         ,COUNT(user_id) AS user_cnt_act
  FROM
  (   -- ⑤ 按照日期分组，取出date_id字段当主表的维度字段 得出表a
    SELECT  from_unixtime(unix_timestamp(time_id),'yyyy-MM-dd') AS date_id
           ,user_id
    FROM test.temp_tanhaidi_20211213_1
    GROUP BY  from_unixtime(unix_timestamp(time_id),'yyyy-MM-dd')
             ,user_id
  ) a
  GROUP BY date_id
) tt2
ON tt2.date_id = tt1.date_id
SQL 复制 全屏

参考链接：


最强最全面的大数据SQL经典面试题完整PDF版

文章标签：
云原生大数据计算服务 MaxCompute
SQL
HIVE
大数据
关键词：
云原生大数据计算服务 MaxCompute sql
大数据SQL
SQL大数据
SQL面试
SQL面试题
相关实践学习
简单用户画像分析
本场景主要介绍基于海量日志数据进行简单用户画像分析为背景，如何通过使用DataWorks完成数据采集 、加工数据、配置数据质量监控和数据可视化展现等任务。
SaaS 模式云数据仓库必修课
本课程由阿里云开发者社区和阿里云大数据团队共同出品，是SaaS模式云原生数据仓库领导者MaxCompute核心课程。本课程由阿里云资深产品和技术专家们从概念到方法，从场景到实践，体系化的将阿里巴巴飞天大数据平台10多年的经过验证的方法与实践深入浅出的讲给开发者们。帮助大数据开发者快速了解并掌握SaaS模式的云原生的数据仓库，助力开发者学习了解先进的技术栈，并能在实际业务中敏捷的进行大数据分析，赋能企业业务。 通过本课程可以了解SaaS模式云原生数据仓库领导者MaxCompute核心功能及典型适用场景，可应用MaxCompute实现数仓搭建，快速进行大数据分析。适合大数据工程师、大数据分析师 大量数据需要处理、存储和管理，需要搭建数据仓库？学它！ 没有足够人员和经验来运维大数据平台，不想自建IDC买机器，需要免运维的大数据平台？会SQL就等于会大数据？学它！ 想知道大数据用得对不对，想用更少的钱得到持续演进的数仓能力？获得极致弹性的计算资源和更好的性能，以及持续保护数据安全的生产环境？学它！ 想要获得灵活的分析能力，快速洞察数据规律特征？想要兼得数据湖的灵活性与数据仓库的成长性？学它！ 出品人：阿里云大数据产品及研发团队专家 产品 MaxCompute 官网 https://www.aliyun.com/product/odps&nbsp;
skdg26tulds2a
目录
相关文章
GoodTime
|
1月前
|
SQL 数据库 C#
C# .NET面试系列十一：数据库SQL查询（附建表语句）
#### 第1题 用一条 SQL 语句 查询出每门课都大于80 分的学生姓名 建表语句： ```sql create table tableA ( name varchar(10), kecheng varchar(10), fenshu int(11) ) DEFAULT CHARSET = 'utf8'; ``` 插入数据 ```sql insert into tableA values ('张三', '语文', 81); insert into tableA values ('张三', '数学', 75); insert into tableA values ('李四',
GoodTime
66 2
C# .NET面试系列十一：数据库SQL查询（附建表语句）
阿萨聊测试
|
2月前
|
SQL 存储 数据库
面试题19: 如何优化SQL查询？
面试题19: 如何优化SQL查询？
阿萨聊测试
57 1
面试题19: 如何优化SQL查询？
前端技术栈
|
3月前
|
SQL 前端开发 程序员
【面试题】前端开发中如何高效渲染大数据量？
【面试题】前端开发中如何高效渲染大数据量？
前端技术栈
39 0
1024小神
|
1月前
|
存储 关系型数据库 MySQL
最全MySQL面试60题（含答案）：存储引擎+数据库锁+索引+SQL优化等
最全MySQL面试60题（含答案）：存储引擎+数据库锁+索引+SQL优化等
1024小神
178 0
是阿超
|
23天前
|
SQL 关系型数据库 MySQL
SQL常见面试题总结2
SQL常见面试题总结
是阿超
51 2
Sunny_yiyi
|
29天前
|
SQL 关系型数据库 MySQL
MySQL SQL语句面试准备
MySQL SQL语句面试准备
Sunny_yiyi
13 0
多多数据库
|
2月前
|
SQL 关系型数据库 MySQL
MySQL 面试题——日常工作中你是怎么优化SQL的？
日常工作中你是怎么优化SQL的？
多多数据库
37 0
开朗觉觉
|
2月前
|
SQL 关系型数据库 MySQL
慢SQL（面试题）
慢SQL（面试题）
开朗觉觉
31 1
大数据启示录
|
3月前
|
消息中间件 分布式计算 Kafka
50道大数据精选面试题
50道大数据精选面试题
大数据启示录
89 0
编程千纸鹤
|
3月前
|
SQL
面试必备杀技：SQL查询专项训练（二）
面试必备杀技：SQL查询专项训练
编程千纸鹤
29 0

热门文章

最新文章

  • 1
    一文搞懂SQL优化——如何高效添加数据
  • 2
    【SQL server】玩转SQL server数据库：第三章 关系数据库标准语言SQL（二）数据查询
  • 3
    【SQL server】玩转SQL server数据库：第二章 关系数据库
  • 4
    SQL常见面试题总结2
  • 5
    NL2SQL实践系列(2)：2024最新模型实战效果(Chat2DB-GLM、书生·浦语2、InternLM2-SQL等)以及工业级案例教学
  • 6
    NL2SQL进阶系列(5)：论文解读业界前沿方案（DIN-SQL、C3-SQL、DAIL-SQL）、新一代数据集BIRD-SQL解读
  • 7
    数据库管理与电脑监控软件：SQL代码优化与实践
  • 8
    【MySQL技术之旅】（7）总结和盘点优化方案系列之常用SQL的优化
  • 9
    大模型与数据分析：探索Text-to-SQL（上）
  • 10
    SQL，Group By 真扎心，原来是这样
  • 1
    数据仓库(13)大数据数仓经典最值得阅读书籍推荐
    244
  • 2
    大数据计算MaxCompute等长时间没有查出来结果的原因可能有以下几点：
    20
  • 3
    DataWorks常见问题之dataworks100g大小的csv文件上传到odps失败如何解决
    45
  • 4
    DataWorks报错问题之dataworks同步rds数据到maxcompute时报错如何解决
    25
  • 5
    漫谈大数据时代的个人信息安全（四）——“位所欲为”
    12
  • 6
    漫谈大数据时代的个人信息安全（三）——“点赞之交”
    23
  • 7
    漫谈大数据时代的个人信息安全（二）——“逢脸造戏”
    22
  • 8
    漫谈大数据时代的个人信息安全（一）——“按图索骥”
    19
  • 9
    DataWorks报错问题之DataWorks报错odps-0433121: User is not added in the list - Only users in the operator account white list have permission to do that如何解决
    20
  • 10
    DataWorks常见问题之dataworks弹外申请odps相关权限失败如何解决
    31

    • 相关课程

    更多
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询
  • Java面试疑难点解析 - 面试技巧及语言基础
  • Java面试疑难点解析 - Java Web开发
  • Java面试疑难点解析 - 系统架构及项目设计

    • 相关电子书

    更多
  • 阿里云技术面试红宝书
  • 超全算法笔试-模拟题精解合集
  • 程序员面试宝典

    • 相关实验场景

    更多
  • 倚天大数据电商数据分析快速实践
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 玩转MaxCompute SQL! 30分钟搞定数据分析挖掘
  • 使用SQL语句实现数据插入、修改和删除操作
  • 使用SQL语句实现数据查询操作
  • 使用SQL语句管理索引
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）