HiveSQL经典面试题（建议点赞收藏）

目录

经典面试系列

每科成绩都大于80分的学生信息

连续登录问题

行列转换问题

留存问题：看当天登录后第N天是否登录

TopN问题

累计计算问题

HSQL进阶版

 直播间在线最大人数

SQL循环

计算中位数

产生连续数值

经典面试系列

1. 每科成绩都大于80分的学生信息

   1. 建表+初始化
      Python

      --创建表scdn_student_score_test 并且初始化三个学生成绩
      create table hdw_tmp_dev.scdn_student_score_test as 
      select '张三' as name,'数学' as subject ,'80' as score 
      union all 
      select '张三' as name,'语文' as subject ,'90' as score
      union all 
      select '张三' as name,'英语' as subject ,'90' as score 
      union all 
      select '李四' as name,'数学' as subject ,'90' as score
      union all 
      select '李四' as name,'语文' as subject ,'90' as score
      union all 
      select '李四' as name,'英语' as subject ,'70' as score 
      union all 
      select '王五' as name,'数学' as subject ,'90' as score
      union all 
      select '王五' as name,'语文' as subject ,'90' as score
      union all 
      select '王五' as name,'英语' as subject ,'50' as score 
      --查询结果显示
      | scdn_student_score_test.name  | scdn_student_score_test.subject  | scdn_student_score_test.score  |
      +-------------------------------+----------------------------------+--------------------------------+
      | 张三                            | 数学                               | 80                             |
      | 张三                            | 语文                               | 90                             |
      | 张三                            | 英语                               | 90                             |
      | 李四                            | 数学                               | 90                             |
      | 李四                            | 语文                               | 90                             |
      | 李四                            | 英语                               | 70                             |
      | 王五                            | 数学                               | 90                             |
      | 王五                            | 语文                               | 90                             |
      | 王五                            | 英语                               | 50                             |
      +-------------------------------+----------------------------------+---------------------
      --求所有学科成绩都大于等于80分的学生姓名

   2. 思路一（思维转换）：所有问题找最小（最大）
      Python

      select 
          t1.name
          ,t1.min_score 
      from 
          (
              select 
                  name 
                  ,min(score)  as min_score
              from hdw_tmp_dev.scdn_student_score_test
              group by name 
          ) as t1 --求出最小的成绩
      where t1.min_score >=80 --最小成绩大于等于80，则这个学生的所有成绩都会大于等于80

   3. 执行结果
   4. 思路二（巧用左关联进行筛选）：
      Python

      select 
          t1.name 
      from 
      (
          select 
              name 
          from hdw_tmp_dev.scdn_student_score_test
          group by name 
      ) as t1 
      left join 
      (
          select 
              name 
          from hdw_tmp_dev.scdn_student_score_test
          where score