大数据笔试题_第一阶段配套笔试题03

--学生表
Create.table.student
(
……std_id.string.comment‘学号’
……,name …string.comment‘姓名’
……,sex…string.comment‘性别’
);
--课程表
Create.table.course
(
……course_id..string.comment‘课程 ID’
……,name ……string.comment‘课程名称’
)；
--课程成绩表
Create.table.course_score
(
……course_id..string.comment‘课程 ID’
……,std_id.string.comment‘学号’
……,score……double.comment.‘分数’
)

题目：

（1） 没有选修生物课的同学：学号，姓名，性别；

SELECT t.*
  FROM student t
  LEFT JOIN (SELECT s.std_id
               FROM score s
               JOIN course c
                 ON s.course_id = c.course_id
              WHERE c.name = '生物') b
    ON t.std_id = b.std_id
 WHERE b.std_id IS NULL;

（2） 求选修了两门课及以上且成绩不低于 80 分的同学：学号；

select s.学号
  from score s
 WHERE s.分数 >= 80
 group BY s.学号
having count(1) >= 2;

（3） 求每门课程的前三名学生信息：学号，姓名，课程名称，成绩；

WITH t1 AS
 (select s.*,
         row_number() over(PARTITION BY s.course_id order by s.score DESC) rn
    from score s),
t2 AS
 (SELECT t1.* FROM t1 WHERE rn <= 3)

SELECT t2.std_id, t.name, c.name, t2.score
  FROM t2
  join student t
    on t.std_id = s.std_id
  join course c
    on c.course_id = t2.course_id;

（4） 统计每个学生选修的所有课程及成绩：学号，姓名，性别，课程成绩集合【语文-90，
数据-60，英语-80】

select t.std_id,
       t.name,
       t.sex,
       wm_concat(c.name || '-' || s.score) 课程成绩集合
  from student t
  join score s
    on t.std_id = s.std_id
  JOIN course c
    on c.course_id = s.course_id
 GROUP BY t.std_id, t.name, t.sex;

二.表信息

--登录日志表
Create.table.login_log
(
……uid………string.comment.‘用户 ID’
……login_date.string.comment.’登录日期 yyyy-mm-dd’
);

题目：求从 2021 年开始，连续登录过 7 天以上的用户 id

-方法一
WITH t1 as
 (select l.uid,
         l.login_date - row_number() over(PARTITION BY UID order by l.login_date) cz
    from login_log l
   where SUBSTR(l.login_date, 1, 4) >= '2021')
SELECT UID FROM t1 GROUP BY UID, cz HAVING COUNT (1) >= 8;

--方法二
WITH t1 AS
 (select l.uid
         --,l.login_date-row_number()over(PARTITION BY UID order by l.login_date) cz
          LEAD(login_date, 7) OVER(PARTITION BY UID order by l.login_date) l_dt
    from login_log l)
SELECT uid FROM t1 WHERE l_dt - login_date = 7;