likes
comments
collection
share

欢聚笔试题求助帖

作者站长头像
站长
· 阅读数 30

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

欢聚笔试题求助帖

拿到的笔试题是一份pdf的建表语句什么的需要自己手动输入，而且建表语句中存在一些小坑。

这是需要使用hive sql回答的一份笔试题。

建表语句如下：

1、班级课后统计表--ls表

表定义的最后不应该有逗号（原表中一个最大的错误，困扰了我很久，没有发现）

欢聚笔试题求助帖 2、班级信息表--ci表

欢聚笔试题求助帖

3、作业统计表--hs表

欢聚笔试题求助帖

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

欢聚笔试题求助帖

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

解释：已开课：开课时间小于当前时间则为已开课；

正式课：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和班级名。

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

需求字段如下：

欢聚笔试题求助帖

我的答案：

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去重求和就可以了。

其实剩余所有的字段都可以有相同的方法求出来，我的方法就是这么做的。

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

即每个学⽣第⼀次上的正式课，根据上课时间排序，取第⼀个。例如⼀个学⽣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出需求字段的值就行。

4. ps：后记

发这篇求助帖，第一个原因是因为笔试没有通过，我也不知道正确的答案是什么，希望有大神看到可以告诉我一下我错在哪里了，好让我可以改正一下（球球了🥺）。虽然我问了HR可否发一下正确答案，但是HR可能怕答案泄漏并没有发我。

第二个原因是想记录一下我第一次参加笔试，虽然是凉了。没事继续刷题吧，主要是自己的功夫还没有到家。

小刘，要继续加油呀

欢聚笔试题求助帖

转载自:https://juejin.cn/post/7369509651517423651
评论
请登录