欢聚笔试题求助帖

事情是这样的，这段时间一直在求职投简历，期望在暑假之前接到一份大数据开发的实习工作。投了很多公司，然后就收到了欢聚的笔试邀约，HR说要我一天之内做出来，恰巧第二天还有组会要汇报，我就先放下了，打算开完组会，下午再做，也没有超时。

拿到的笔试题是一份pdf的建表语句什么的需要自己手动输入，而且建表语句中存在一些小坑。
这是需要使用hive sql回答的一份笔试题。
建表语句如下：
1、班级课后统计表–ls表
表定义的最后不应该有逗号（原表中一个最大的错误，困扰了我很久，没有发现）

CREATE TABLE IF NOT EXISTS tb_class_lesson_stat
(
class_id BIGINT COMMENT '大班ID',
class_name STRING COMMENT '大班名称',
uid BIGINT COMMENT '学生uid',
be_attend BIGINT COMMENT '10-到课；20-未到课',
attend_duration BIGINT COMMENT '到课总时长',
be_complete BIGINT COMMENT '10-完课；20-未完课',
term STRING COMMENT '平台类型：1-android；2-ios；3-web',
be_late BIGINT COMMENT '10-正常；20-迟到',
late_duration BIGINT COMMENT '迟到时长',
be_leave_early BIGINT COMMENT '10-未早退；20-早退',
leave_early_duration BIGINT COMMENT '早退时长'
)COMMENT '班级课后统计表'
PARTITIONED BY(dt STRING);

2、班级信息表–ci表

CREATE TABLE IF NOT EXISTS tb_class_info
(
class_id BIGINT COMMENT '⼤班ID',
class_name STRING COMMENT '⼤班名称',
class_no STRING COMMENT '课程期号',
class_type STRING COMMENT '业务类型：10-体验；20-正式；30-测试；40-其他',
class_start_time STRING COMMENT '开课时间，格式2020-01-01 ：13:00:00'
)
COMMENT '班级信息表';

3、作业统计表–hs表

CREATE TABLE IF NOT EXISTS tb_homework_stu_stat
(
	id BIGINT,
	class_id BIGINT COMMENT '大班id',
	uid BIGINT COMMENT '学生uid',
	status BIGINT COMMENT '作业状态0，未提交，20-已提交，10-已批改',
	score BIGINT COMMENT '作业得分，每题批改都更新分数，默认0',
	comment STRING COMMENT '作业总评，json方式存储',
	push_status BIGINT COMMENT '推送状态，0未推送（默认），1-作业已推送，2-报告已推送',
	submit_time STRING COMMENT '作业提交时间时间',
	deadline STRING COMMENT '作业提交截止时间',
	create_time STRING COMMENT '创建时间',
	updateby BIGINT,
	update_time STRING COMMENT '更新时间',
	type BIGINT COMMENT '作业类型:0课后作业,10摸底测,20周测',
	comment_status BIGINT COMMENT '批改状态，0未批改（默认），10暂存，20已批改，30出门测')
	COMMENT '作业统计表';

4、作业报告查看记录表–re表

CREATE TABLE IF NOT EXISTS tb_report_expo
(
	class_id BIGINT COMMENT '大班id',
	uid BIGINT COMMENT '用户id',
	report_type BIGINT COMMENT '报告类型,1作业报告曝光'
) 
COMMENT '作业报告查看记录表'

第一题，求所有已开课的正式课班级的学生到课数据统计

解释：已开课：开课时间小于当前时间则为已开课；
正式课：class_type=20
需求列表：
具体字段如下：

我的答案：

SELECT 
    ci.class_id,
    ci.class_no,
    ci.class_name,
    COUNT(DISTINCT ls.uid) AS `应到学生总数`,
    SUM(IF(ls.be_attend = 10,1,0)) AS `到课学生数`,
    AVG(IF(ls.be_attend = 10,ls.attend_duration,0)) `人均到课时长`
FROM 
    tb_class_info ci
JOIN 
    tb_class_lesson_stat ls 
ON  
    ci.class_id = ls.class_id
WHERE 
    ci.class_type = 20 AND
    ci.class_start_time < CURRENT_TIMESTAMP()
GROUP BY 
    ci.class_id, ci.class_no, ci.class_name;

我的思路：
求所有已开课的正式课班的学生到课数据统计，
主要是聚焦两张表，班级课后统计表–ls表和班级信息表–ci表，关联条件就是“大班ID”。这道题要说难的话，我认为有两点：
一是，字段比较难把握，但是这个其实还好，题目中已经给了需求字段，前三个字段直接写上。“应到学生数”，就是课程id关联上的，ls表中学生id，去重计数就是应到学生数了；“到课学生数”，在ls表中有个字段“be_attend”为“10”时，对满足条件的行求和就是“应到学生数”了；“人均到课时长”，是用到avg函数，当“be_attend”为“10”时，加上“ls.attend_duration”字段数值，也就是到课时长，否则就加0。
二是，已开课如何表示？题目中已经提示了，时间小于现在时间，如何表示现在的时间了，sql中与时间相关的函数是date，timestamp类似的单词，在这里我用到的是current_timestamp。
至此，拼接上述思路，我的答案就出来了，对了记得group by一下班级，为了准确度，同时分组一下班级id、班级no和班级名。

第二题，求到课用户的课后作业完成情况

需求字段如下：

我的答案：

SELECT 
    ci.class_id,
    ci.class_no,
    ci.class_name,
    COUNT(DISTINCT ls.uid) AS `到课学生数`,
    COUNT(DISTINCT CASE WHEN (hs.push_status = 1 OR hs.push_status = 2) THEN hs.uid ELSE NULL END) AS `下发作业学生数`,
    COUNT(DISTINCT CASE WHEN (hs.status = 20 OR hs.status = 10)  THEN hs.uid ELSE NULL END) AS `完成作业学生数`,
    COUNT(DISTINCT CASE WHEN hs.push_status = 2 THEN hs.uid ELSE NULL END) AS `下发报告学生数`,
    COUNT(DISTINCT CASE WHEN re.report_type = 1 THEN re.uid ELSE NULL END) AS `查看报告学生数`
FROM 
    tb_class_info ci
JOIN 
    tb_class_lesson_stat ls ON ci.class_id = ls.class_id AND ls.be_attend = 10
LEFT JOIN 
    tb_homework_stu_stat hs ON ci.class_id = hs.class_id AND hs.type = 0
LEFT JOIN 
    tb_report_expo re ON ci.class_id = re.class_id
GROUP BY 
    ci.class_id, ci.class_no, ci.class_name;

求到课(ls.be_attend = 10)用户的课后作业(hs.type=0)完成情况。
我的思路：
这一题虽然看起来很复杂，应该是要连接四张表，才可以知道用户的课后作业的各种情况，而且需求字段看起来也好复杂的。别急慢慢来，一个一个看，而且需求字段中，其实已经提示我们字段所在位置，以及属性值。
（上个题目说过的字段就不在重复说了）
“下发作业学生数”：hs.push_status字段的值为1和0，在这里，我用到了一个case when函数，如果满足条件，就输出hs.uid，否则输出NULL，然后对所有的uid去重求和就可以了。
其实剩余所有的字段都可以有相同的方法求出来，我的方法就是这么做的。

第三题，求每个学生首个上课的正式课信息

即每个学⽣第⼀次上的正式课，根据上课时间排序，取第⼀个。例如⼀个学⽣2023801，2023802，
2023803分别上了课，只需保留2023801那节课的class_id、class_no等信息。
需求字段如下：

SELECT 
    uid,
    first_lesson_month,
    class_id,
    class_no,
    class_name
FROM (
    SELECT 
        ls.uid,
        DATE_FORMAT(ci.class_start_time, 'yyyy-MM') AS first_lesson_month,
        ci.class_id,
        ci.class_no,
        ci.class_name,
        ROW_NUMBER() OVER(PARTITION BY ls.uid ORDER BY ci.class_start_time) as rn
    FROM 
        tb_class_lesson_stat ls
    JOIN 
        tb_class_info ci 
    ON ls.class_id = ci.class_id AND ci.class_type = 20          
) t
WHERE 
    t.rn = 1;

求每个学生首个上课的正式课信息
我的思路：
“首个”上课信息，我一开始并不知道如何解决，百度了一下知道可以用min函数或者排序函数就行了，还有开课的月份，对于时间如何转变为年月呢。首先对于uid使用row_number开窗函数，按开课时间排序，然后排序为1的这一行就可以了。然后select出需求字段的值就行。

ps：后记

发这篇求助帖，第一个原因是因为笔试没有通过，我也不知道正确的答案是什么，希望有大神看到可以告诉我一下我错在哪里了，好让我可以改正一下（球球了🥺）。虽然我问了HR可否发一下正确答案，但是HR可能怕答案泄漏并没有发我。
第二个原因是想记录一下我第一次参加笔试，虽然是凉了。没事继续刷题吧，主要是自己的功夫还没有到家。
小刘，要继续加油呀