hadoop学习---基于Hive的教育平台数据仓库分析案例（三）

衔接第一部分，第一部分请点击：基于Hive的教育平台数据仓库分析案例（一)

衔接第二部分，第二部分请点击：基于Hive的教育平台数据仓库分析案例（二)

学生出勤模块（全量分析）：

需求指标：

需求一: 统计指定时间段内，不同班级的出勤人数。打卡时间在上课前40分钟(否则认为无效)~上课时间点之内，且未早退，则为正常上课打卡。

需求二: 统计指定时间段内，不同班级的学生出勤率。可以下钻到具体学生的出勤数据。出勤率=出勤人数/当日在读学员人数。

需求三: 统计指定时间段内，不同班级的迟到人数。上课10分钟后视为迟到。可以下钻到具体学生的迟到数据。跨天数据直接累加。

需求四: 统计指定时间段内，不同班级的学生迟到率。上课10分钟后视为迟到。可以下钻到具体学生的迟到数据。迟到率=迟到人数/当日在读学员人数。

需求五: 统计指定时间段内，不同班级的请假人数。跨天数据直接累加。

需求六:统计指定时间段内，不同班级的学生请假率。请假率=请假人数/当日在读学员人数。

需求七: 统计指定时间段内，不同班级的旷课人数。跨天数据直接累加。旷课人数=当日在读学员人数-出勤人数-请假人数。

需求八:统计指定时间段内，不同班级的学生旷课率。旷课率=旷课人数/当日在读学员人数。

总体分析：

第一类: 指标: 计算 出勤人数, 出勤率, 迟到人数, 迟到率

涉及维度:
    时间维度: 年  月  天  上午 下午 晚自习
    班级维度:
    学生维度:

涉及表: 
    course_table_upload_detail:  日志课程明细表 (课表)  (维度表)
    tbh_student_signin_record:   学生打卡记录表   (事实表)
    tbh_class_time_table: 班级作息时间表 (维度表)

关联条件: 
    学生打卡表.class_id = 课程表.class_id
    班级作息时间表.id = 学生打卡表.time_table_id

涉及到字段: 
    时间维度: 课程表.class_date
    班级维度: 课程表.class_id
    学生维度: 学生打卡表.student_id
    指标字段: 学生打卡表.signin_time(打卡时间)
        计算方案:  
            先判断是否出勤 
                情况1: 出勤了, 再次判断是否是正常出勤和迟到出勤
                情况2: 未出勤, 认为没有来
    指标判断指标:
        作息时间表: 
            morning_begin_time
            morning_end_time
            afternoon_begin_time
            afternoon_end_time
            evening_begin_time
            evening_end_time

过滤操作
    1) ifnull(ctud.content,'') != '' 相当于 ctud.content != null and ctud.content != ''
       转换为hive的操作
          nvl(ctud.content,'') != ''
    2) 将content中为开班典礼数据过滤掉
        ctud.content != '开班典礼'
    3) 确保打卡表中学生数据都是开启公屏
        学生打卡表.share_state=1
        
判断学生出勤状态: 0(正常出勤)   1(迟到出勤)     2(缺勤
    首先根据打卡时间, 如果打卡的时间在 上课的前40分钟内 ~ 上课截止时间内
        情况1: 出勤了, 接着判断 是否是正常出勤还是迟到出勤, 
            如果打卡时间在 上课的前40分钟内容 ~ 上课的开始时间后10分内, 认为正常出勤了  返回 0
            否则认为迟到出勤  返回 1
        情况2: 没有出去, 认为没有来   返回  2
    

第二类: 指标: 计算 请假人数, 请假率

涉及维度: 
    时间维度: 年  月  天  上午 下午 晚自习
    班级维度:

涉及表:
    student_leave_apply:  学生请假表 (事实表)
    tbh_class_time_table: 班级作息时间表 (维度表)
    course_table_upload_detail: 课程表 (维度表)

表关联条件: 
    学生请假表.class_id = 班级作息时间表.class_id
    学生请假表.class_id = 课程表.class_id

涉及字段: 
    时间维度:  课程表.class_date
    班级维度:  课程表.class_id
    指标字段:  请假表.student_id
        需要进行去重统计操作

过滤条件:
    课程表: 
        content 不能为空 为null 以及不能为 开班典礼
    
    获取有效的作息时间:
        课程表.class_date between 作息表.use_begin_date and 作息表.use_end_date
    
    学生请假表:保证请假数据有效的
        audit_state =1  -- 审核通过
        cancel_state = 0 -- 没有取消
        valid_state = 1  -- 有效的
    判断是否请假条件:
        请假的开始时间(请假表.begin_time) <= 上课的开始时间 (morning_begin_time |afternon_begin_time | evening_begin_time)
        请假的结束时间(请假表.end_time) >= 上课的开始时间(morning_begin_time |afternon_begin_time | evening_begin_time)

第三类: 计算旷课人数, 旷课率

涉及维度: 
    时间维度: 年  月  天  上午 下午 晚自习
    班级维度:
计算标准:
    旷课人数 = 当日在读人数 - 出勤人数(正常出勤+迟到出勤) -请假人数

数据准备：

将原始数据加载到本地MySQL数据库中

创建数据库

create database teach default character set utf8mb4 collate utf8mb4_unicode_ci;

执行sql文件： 点击下载：学生出勤模块sql文件

表1: 当日在读人数表
表2: 课程日期表
表3: 学生请假表
表4: 班级作息时间表
表5: 学生打卡记录表 

表6：课程日历表（无用）

建模分析：

ODS层:源数据层

作用: 对接数据源, 一般和数据源的表保持相同粒度
一般存放事实表以及少量的维度表

建表方案:  
    构建两张表 :  学生打卡记录表  和 学生请假表

在构建的时候, 需要额外添加一个分区字段: start_time(抽取数据的时间)

DIM层:维度层

作用: 用于存储维度表的数据, 一般和数据源对表保持相同粒度

建表方案: 
    构建三个表:  课程日期表, 班级作息时间表, 当日在读人数表
    
在构建的时候, 需要额外添加一个分区字段: start_time(抽取数据的时间)    

DWD层:明细层

作用:  1) 清洗转换操作   2) 少量的维度退化操作  (不需要)

清洗转换操作: 
    可做
        1) 日期转换为 年  月  日
        2) 过滤无效的请假数据
        3) 过滤没有开启公屏的数据

如果没有这些操作, 这些操作可放置在别的层，所以此时DWD层 是不需要存在的

DWM层:中间层

作用: 维度退化  以及 提前聚合操作

处理逻辑: 先 分 在 合的操作，先从原始数据库中拆分出多个表，再根据需要聚合

建表方案: 
    第一个表:  学生出勤状态表 
        作用: 用于统计每天每个班级, 每个学生的出勤状态(0(正常出勤) 1(迟到出勤) 2(没来))
        表字段 : 
           yearinfo, monthinfo,dayinfo, 班级id, 学生id, 上午出勤, 下午出勤, 晚自习出勤
    第二个表: 班级出勤人数表
        作用: 用于统计每天每个班级的出勤人数(出勤人数, 迟到出勤人数)
        表字段:  
            yearinfo, monthinfo,dayinfo, 班级id,上午出勤人数, 上午迟到人数, 下午出勤人数, 下午迟到人数, 晚上出勤人数, 晚上迟到人数
    第三个表: 班级请假人数表
        作用: 用于统计每天每个班级的请假人数
        表字段: 
            yearinfo, monthinfo,dayinfo, 班级id, 上午请假人数, 下午请假人数, 晚上请假人数
    第四个表: 班级旷课人数表
        作用: 用于统计每天每个班级的旷课人数
        表字段:
            yearinfo, monthinfo,dayinfo, 班级id, 上午旷课人数, 下午旷课人数, 晚上旷课人数
            
    第五个表:  班级指标汇总表  (提前聚合表)
        作用: 用于将前几个表相关的指标数据汇总起来, 同时计算出 相关比率操作 (统计每天的上午 下午 晚自习)
        表字段: 
            yearinfo, monthinfo,dayinfo, 班级id, 班级当日在读人数, 
                上午出勤人数, 上午出勤率, 下午出勤人数 下午出勤率, 晚上出勤人数, 晚上出勤率
                上午迟到人数, 上午迟到率, 下午迟到人数, 下午迟到率, 晚上迟到人数, 晚上迟到率
                上午请假人数, 上午请假率, 下午请假人数, 下午请假率, 晚上请假人数, 晚上请假率
                上午旷课人数, 上午旷课率, 下午旷课人数, 下午旷课率, 晚上旷课人数, 晚上旷课率

DWS层:业务层

作用: 细化维度统计操作

建表方案: 
    yearinfo, monthinfo,dayinfo, 班级id, 班级当日在读人数, time_str,time_type
        上午出勤人数, 上午出勤率, 下午出勤人数 下午出勤率, 晚上出勤人数, 晚上出勤率
        上午迟到人数, 上午迟到率, 下午迟到人数, 下午迟到率, 晚上迟到人数, 晚上迟到率
        上午请假人数, 上午请假率, 下午请假人数, 下午请假率, 晚上请假人数, 晚上请假率
        上午旷课人数, 上午旷课率, 下午旷课人数, 下午旷课率, 晚上旷课人数, 晚上旷课率

DA层:

根据需要从DWS层直接抽取对应数据

建模操作（建表）：

ODS层:(仅存储数据表数据)

学生打卡信息表:

CREATE TABLE IF NOT EXISTS itcast_ods.student_signin_ods (id                int,normal_class_flag int           comment '是否正课 1 正课 2 自习 3 休息',time_table_id     int           comment '作息时间id normal_class_flag=2 关联tbh_school_time_table 或者 normal_class_flag=1 关联 tbh_class_time_table',class_id          int           comment '班级id',student_id        int           comment '学员id',signin_time       String       comment '签到时间',signin_date       String          comment '签到日期',inner_flag        int           comment '内外网标志  0 外网 1 内网',signin_type       int           comment '签到类型 1 心跳打卡 2 老师补卡 3 直播打卡',share_state       int            comment '共享屏幕状态 0 否 1是  在上午或下午段有共屏记录，则该段所有记录该字段为1，内网默认为1 外网默认为0   (暂不用)',inner_ip          String          comment '内网ip地址',create_time       String       comment '创建时间')
comment '学生打卡记录表'
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
stored as orcfile
TBLPROPERTIES ('orc.compress'='SNAPPY','orc.bloom.filter.columns'='time_table_id,class_id,signin_date,share_state');

学生请假信息表：

CREATE TABLE IF NOT EXISTS itcast_ods.student_leave_apply_ods (id              int,class_id        int           comment '班级id',student_id      int           comment '学员id',audit_state     int        comment '审核状态 0 待审核 1 通过 2 不通过',audit_person    int           comment '审核人',audit_time      String      comment '审核时间',audit_remark    String     comment '审核备注',leave_type      int           comment '请假类型  1 请假 2 销假 （查询是否请假不用过滤此类型，通过有效状态来判断）',leave_reason    int           comment '请假原因  1 事假 2 病假',begin_time      String      comment '请假开始时间',begin_time_type int           comment '1：上午 2：下午 3：晚自习',end_time        String      comment '请假结束时间',end_time_type   int           comment '1：上午 2：下午 3：晚自习',days            float         comment '请假/已休天数',cancel_state    int         comment '撤销状态  0 未撤销 1 已撤销',cancel_time     String      comment '撤销时间',old_leave_id    int           comment '原请假id，只有leave_type =2 销假的时候才有',leave_remark    String     comment '请假/销假说明',valid_state     int        comment '是否有效（0：无效 1：有效）',create_time     String      comment '创建时间')
comment '学生请假申请表'
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
stored as orcfile
TBLPROPERTIES ('orc.compress'='SNAPPY','orc.bloom.filter.columns'='class_id,audit_state,cancel_state,valid_state');

DIM层:(存储维度表数据)

3张维度表

-- 日期课程表
CREATE TABLE IF NOT EXISTS itcast_dimen.course_table_upload_detail_dimen
(id                  int       comment 'id',base_id             int         comment '课程主表id',class_id            int         comment '班级id',class_date          STRING      comment '上课日期',content             STRING     comment '课程内容',teacher_id          int         comment '老师id',teacher_name        STRING     comment '老师名字',job_number          STRING     comment '工号',classroom_id        int         comment '教室id',classroom_name      STRING     comment '教室名称',is_outline          int         comment '是否大纲 0 否 1 是',class_mode          int         comment '上课模式 0 传统全天 1 AB上午 2 AB下午 3 线上直播',is_stage_exam       int         comment '是否阶段考试（0：否 1：是）',is_pay              int         comment '代课费（0：无 1：有）',tutor_teacher_id    int         comment '晚自习辅导老师id',tutor_teacher_name  STRING     comment '辅导老师姓名',tutor_job_number    STRING     comment '晚自习辅导老师工号',is_subsidy          int         comment '晚自习补贴（0：无 1：有）',answer_teacher_id   int         comment '答疑老师id',answer_teacher_name STRING     comment '答疑老师姓名',answer_job_number   STRING     comment '答疑老师工号',remark              STRING        comment '备注',create_time         STRING      comment '创建时间')
comment '班级课表明细表'
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
stored as orcfile
TBLPROPERTIES ('orc.compress'='SNAPPY','orc.bloom.filter.columns'='class_id,class_date');-- 班级作息时间表
CREATE TABLE IF NOT EXISTS itcast_dimen.class_time_dimen (id                     int,class_id              int              comment '班级id',morning_template_id   int              comment '上午出勤模板id',morning_begin_time    STRING         comment '上午开始时间',morning_end_time      STRING         comment '上午结束时间',afternoon_template_id int              comment '下午出勤模板id',afternoon_begin_time  STRING         comment '下午开始时间',afternoon_end_time    STRING         comment '下午结束时间',evening_template_id   int              comment '晚上出勤模板id',evening_begin_time    STRING         comment '晚上开始时间',evening_end_time      STRING         comment '晚上结束时间',use_begin_date        STRING         comment '使用开始日期',use_end_date          STRING         comment '使用结束日期',create_time           STRING       comment '创建时间',create_person         int              comment '创建人',remark                STRING      comment '备注')
comment '班级作息时间表'
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
stored as orcfile
TBLPROPERTIES ('orc.compress'='SNAPPY','orc.bloom.filter.columns'='id,class_id');-- 当日在读人数表:
CREATE TABLE IF NOT EXISTS itcast_dimen.class_studying_student_count_dimen (id                     int,school_id              int  comment '校区id',subject_id             int  comment '学科id',class_id               int  comment '班级id',studying_student_count int  comment '在读班级人数',studying_date          STRING comment '在读日期')
comment '在读班级的每天在读学员人数'
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
stored as orcfile
TBLPROPERTIES ('orc.compress'='SNAPPY','orc.bloom.filter.columns'='studying_student_count,studying_date');

DWD层:

降维操作留到DWM层，此层不需要清洗转换。

DWM层:

-- 学生出勤状态信息表 
CREATE TABLE IF NOT EXISTS itcast_dwm.student_attendance_dwm (dateinfo        String        comment '日期',class_id          int           comment '班级id',student_id        int           comment '学员id',morning_att       String       comment '上午出勤情况：0.正常出勤、1.迟到、2.其他（请假+旷课）',afternoon_att    String        comment '下午出勤情况：0.正常出勤、1.迟到、2.其他（请假+旷课）',evening_att       String        comment '晚自习出勤情况：0.正常出勤、1.迟到、2.其他（请假+旷课）')
comment '学生出勤(正常出勤和迟到)数据'
PARTITIONED BY (yearinfo STRING, monthinfo STRING, dayinfo STRING)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
stored as orcfile
TBLPROPERTIES ('orc.compress'='SNAPPY');-- 班级出勤人数表: 
CREATE TABLE IF NOT EXISTS itcast_dwm.class_attendance_dwm (dateinfo        String        comment '日期',class_id          int           comment '班级id',morning_att_count       String         comment '上午出勤人数',afternoon_att_count      String        comment '下午出勤人数',evening_att_count       String        comment '晚自习出勤人数',morning_late_count       String         comment '上午迟到人数',afternoon_late_count      String        comment '下午迟到人数',evening_late_count       String        comment '晚自习迟到人数')
comment '学生出勤(正常出勤和迟到)数据'
PARTITIONED BY (yearinfo STRING, monthinfo STRING, dayinfo STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
stored as orcfile
TBLPROPERTIES ('orc.compress'='SNAPPY');-- 班级请假人数表
CREATE TABLE IF NOT EXISTS itcast_dwm.class_leave_dwm (dateinfo      String        comment '日期',class_id        int           comment '班级id',morning_leave_count       String       comment '上午请假人数',afternoon_leave_count    String        comment '下午请假人数',evening_leave_count       String        comment '晚自习请假人数')
comment '班级请假数据统计'
PARTITIONED BY (yearinfo STRING, monthinfo STRING, dayinfo STRING)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
stored as orcfile
TBLPROPERTIES ('orc.compress'='SNAPPY');-- 班级旷课人数表
CREATE TABLE IF NOT EXISTS itcast_dwm.class_truant_dwm (dateinfo      String        comment '日期',class_id        int           comment '班级id',morning_truant_count       String          comment '上午旷课人数',afternoon_truant_count   String        comment '下午旷课人数',evening_truant_count       String        comment '晚自习旷课人数')
comment '班级请假数据统计'
PARTITIONED BY (yearinfo STRING, monthinfo STRING, dayinfo STRING)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
stored as orcfile
TBLPROPERTIES ('orc.compress'='SNAPPY');-- 汇总表CREATE TABLE IF NOT EXISTS itcast_dwm.class_all_dwm (dateinfo      String        comment '日期',class_id        int           comment '班级id',studying_student_count int  comment '在读班级人数',morning_att_count       String         comment '上午出勤人数',morning_att_ratio       String         comment '上午出勤率',afternoon_att_count      String        comment '下午出勤人数',afternoon_att_ratio      String        comment '下午出勤率',evening_att_count       String        comment '晚自习出勤人数',evening_att_ratio       String        comment '晚自习出勤率',morning_late_count       String        comment '上午迟到人数',morning_late_ratio       String        comment '上午迟到率',afternoon_late_count     String        comment '下午迟到人数',afternoon_late_ratio     String        comment '下午迟到率',evening_late_count       String        comment '晚自习迟到人数',evening_late_ratio       String        comment '晚自习迟到率',morning_leave_count       String       comment '上午请假人数',morning_leave_ratio       String       comment '上午请假率',afternoon_leave_count    String        comment '下午请假人数',afternoon_leave_ratio    String        comment '下午请假率',evening_leave_count       String        comment '晚自习请假人数',evening_leave_ratio       String        comment '晚自习请假率',morning_truant_count       String          comment '上午旷课人数',morning_truant_ratio       String          comment '上午旷课率',afternoon_truant_count   String        comment '下午旷课人数',afternoon_truant_ratio   String        comment '下午旷课率',evening_truant_count       String        comment '晚自习旷课人数',evening_truant_ratio       String        comment '晚自习旷课率')
comment '班级请假数据统计'
PARTITIONED BY (yearinfo STRING, monthinfo STRING, dayinfo STRING)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
stored as orcfile
TBLPROPERTIES ('orc.compress'='SNAPPY');

DWS层：

CREATE TABLE IF NOT EXISTS itcast_dws.class_attendance_dws (dateinfo      String        comment '日期',class_id        int           comment '班级id',studying_student_count int  comment '在读班级人数',morning_att_count       String         comment '上午出勤人数',morning_att_ratio       String         comment '上午出勤率',afternoon_att_count      String        comment '下午出勤人数',afternoon_att_ratio      String        comment '下午出勤率',evening_att_count       String        comment '晚自习出勤人数',evening_att_ratio       String        comment '晚自习出勤率',morning_late_count       String        comment '上午迟到人数',morning_late_ratio       String        comment '上午迟到率',afternoon_late_count     String        comment '下午迟到人数',afternoon_late_ratio     String        comment '下午迟到率',evening_late_count       String        comment '晚自习迟到人数',evening_late_ratio       String        comment '晚自习迟到率',morning_leave_count       String       comment '上午请假人数',morning_leave_ratio       String       comment '上午请假率',afternoon_leave_count    String        comment '下午请假人数',afternoon_leave_ratio    String        comment '下午请假率',evening_leave_count       String        comment '晚自习请假人数',evening_leave_ratio       String        comment '晚自习请假率',morning_truant_count       String          comment '上午旷课人数',morning_truant_ratio       String          comment '上午旷课率',afternoon_truant_count   String        comment '下午旷课人数',afternoon_truant_ratio   String        comment '下午旷课率',evening_truant_count       String        comment '晚自习旷课人数',evening_truant_ratio       String        comment '晚自习旷课率',time_type            STRING       COMMENT '聚合时间类型：1、按小时聚合；2、按天聚合；3、按周聚合；4、按月聚合；5、按年聚合。')
comment '班级请假数据统计'
PARTITIONED BY (yearinfo STRING, monthinfo STRING, dayinfo STRING)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
stored as orcfile
TBLPROPERTIES ('orc.compress'='SNAPPY');

数据采集：

数据: 目前存储在MySQL中

目的地: 放置hive中

技术: 采用 apache sqoop 来解决

DIM层：

# 当日在读人数表
sqoop import \
--connect jdbc:mysql://192.168.52.150:3306/teach \
--username root \
--password 123456 \
--query 'select *,"2021-10-07" as dt  from class_studying_student_count where 1=1 and $CONDITIONS' \
--hcatalog-database itcast_dimen \
--hcatalog-table class_studying_student_count_dimen \
-m 1# 日期课程表
sqoop import \
--connect jdbc:mysql://192.168.52.150:3306/teach \
--username root \
--password 123456 \
--query 'select *,"2021-10-07" as dt  from course_table_upload_detail where 1=1 and $CONDITIONS' \
--hcatalog-database itcast_dimen \
--hcatalog-table course_table_upload_detail_dimen \
-m 1# 作息时间表
sqoop import \
--connect jdbc:mysql://192.168.52.150:3306/teach \
--username root \
--password 123456 \
--query 'select *,"2021-10-07" as dt  from tbh_class_time_table where 1=1 and $CONDITIONS' \
--hcatalog-database itcast_dimen \
--hcatalog-table  class_time_dimen \
-m 1

ODS层:

# 学生打卡记录表sqoop import \
--connect jdbc:mysql://192.168.52.150:3306/teach \
--username root \
--password 123456 \
--query 'select *,"2021-10-07" as dt  from tbh_student_signin_record where 1=1 and $CONDITIONS' \
--hcatalog-database itcast_ods \
--hcatalog-table student_signin_ods \
-m 1# 学生请假信息表sqoop import \
--connect jdbc:mysql://192.168.52.150:3306/teach \
--username root \
--password 123456 \
--query 'select *,"2021-10-07" as dt  from student_leave_apply where 1=1 and $CONDITIONS' \
--hcatalog-database itcast_ods \
--hcatalog-table student_leave_apply_ods \
-m 1

数据清洗转换：

目的: 主要是用于从ODS以及DIM层 将数据灌入到DWM层操作

生成DWD层数据:

------------DWD层不需要，降维操作留到DWM层

生成DWM层数据:

由于DWM层的字段是来源于事实表和所有维度表中的字段, 此时如果生成DWM层数据, 必须要先将所有的表关联在一起，七表关联数据庞大，要开启各种优化。

学生出勤状态信息表

 先把表与表之间的关联条件，过滤条件，字段依次实现

基本逻辑：

select 
    ctudd.class_date,
    ctudd.class_id,
    student_id,
    '' as morning_att,   --未实现
    '' as afternoon_att, --未实现
    '' as  evening_att,  --未实现
    substr(ctudd.class_date,1,4) as yearinfo,
    substr(ctudd.class_date,6,2) as monthinfo,
    substr(ctudd.class_date,9,2) as dayinfo
from (select * from itcast_dimen.course_table_upload_detail_dimen where nvl(content,'')!='' and content !='开班典礼') ctudd
     left  join (select * from itcast_ods.student_signin_ods where share_state = 1) sso on sso.class_id = ctudd.class_id
     left join itcast_dimen.class_time_dimen ctd on ctd.id = sso.time_table_id
group  by ctudd.class_date , ctudd.class_id,sso.student_id;

如何判断学生出勤状态呢?  ----先判断是否出勤，再判断是否迟到。

基于学生的打卡时间  以上午为例
    如果学生的打卡时间 在 上午上课开始时间前40分钟内  ~~ 上午上课截止时间内
        认为 学生出勤了
            此时接着判断, 如何学生的打卡时间在 上午上课开始时间前40分钟内容  ~~ 上午上课开始时间后10分钟内
                认为 学生是正常出勤  返回 0
                否则 认为学生是迟到出勤 返回 1
        
        否则认为学生没有出勤, 直接返回 2 

 伪代码：不能运行，逻辑推敲

如何实现日期的 相加 和 相减 (对分钟处理)  ？

在hive中, 并没有发现可以对分钟加减的函数, 只有对天的加减函数, 但是不符合要求, 如何解决呢? 
    可以尝试将日期数据转换为时间戳, 然后对时间戳进行加减处理 即可解决问题，时间戳以秒为单位转换
    select unix_timestamp('2021-10-08 15:40:30','yyyy-MM-dd HH:mm:ss') - 40*60;

 发现: 作息时间表的 上课时间内容, 只有 时 分 秒 没有 年 月 日，可以将上课日期时间加入。

 

 Hive的各种优化开启

最后代码实现：数据量太大，只抽取三天：

set hive.auto.convert.join=false;--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
--set hive.enforce.bucketing=true;
--set hive.enforce.sorting=true;
--set hive.optimize.bucketmapjoin = true;
--set hive.auto.convert.sortmerge.join=true;
--set hive.auto.convert.sortmerge.join.noconditionaltask=true;
--并行执行
set hive.exec.parallel=true;
set hive.exec.parallel.thread.number=8;
--小文件合并
-- set mapred.max.split.size=2147483648;
-- set mapred.min.split.size.per.node=1000000000;
-- set mapred.min.split.size.per.rack=1000000000;
--矢量化查询
set hive.vectorized.execution.enabled=true;
--关联优化器
set hive.optimize.correlation=true;
--读取零拷贝
set hive.exec.orc.zerocopy=true;
--join数据倾斜
set hive.optimize.skewjoin=true;
-- set hive.skewjoin.key=100000;
set hive.optimize.skewjoin.compiletime=true;
set hive.optimize.union.remove=true;
-- group倾斜
set hive.groupby.skewindata=true;insert into table itcast_dwm.student_attendance_dwm partition(yearinfo,monthinfo,dayinfo)
select ctudd.class_date,ctudd.class_id,student_id,if(sum(if( unix_timestamp(sso.signin_time,'yyyy-MM-dd HH:mm:ss')between unix_timestamp(concat(ctudd.class_date,' ',ctd.morning_begin_time),'yyyy-MM-dd HH:mm:ss') - 40*60   and  unix_timestamp(concat(ctudd.class_date,' ',ctd.morning_end_time),'yyyy-MM-dd HH:mm:ss'),1,0)) > 0,  -- 如果大于0 认为当天的打卡记录中, 一定是有出勤的打卡记录, 如果小于等于0 认为没有出勤if(sum(if(unix_timestamp(sso.signin_time,'yyyy-MM-dd HH:mm:ss') between unix_timestamp(concat(ctudd.class_date,' ',ctd.morning_begin_time),'yyyy-MM-dd HH:mm:ss') - 40*60  and  unix_timestamp(concat(ctudd.class_date,' ',ctd.morning_begin_time),'yyyy-MM-dd HH:mm:ss') + 10*60, 1 , 0)) >0, -- 如果大于0, 认为当天打卡记录中, 一定是有正常出勤的记录, 否则认为迟到出勤0,1),2)as morning_att, if(sum(if( unix_timestamp(sso.signin_time,'yyyy-MM-dd HH:mm:ss')between unix_timestamp(concat(ctudd.class_date,' ',ctd.afternoon_begin_time),'yyyy-MM-dd HH:mm:ss') - 40*60   and  unix_timestamp(concat(ctudd.class_date,' ',ctd.afternoon_end_time),'yyyy-MM-dd HH:mm:ss'),1,0)) > 0,  -- 如果大于0 认为当天的打卡记录中, 一定是有出勤的打卡记录, 如果小于等于0 认为没有出勤if(sum(if(unix_timestamp(sso.signin_time,'yyyy-MM-dd HH:mm:ss') between unix_timestamp(concat(ctudd.class_date,' ',ctd.afternoon_begin_time),'yyyy-MM-dd HH:mm:ss') - 40*60  and  unix_timestamp(concat(ctudd.class_date,' ',ctd.afternoon_begin_time),'yyyy-MM-dd HH:mm:ss') + 10*60, 1 , 0)) >0, -- 如果大于0, 认为当天打卡记录中, 一定是有正常出勤的记录, 否则认为迟到出勤0,1),2) as afternoon_att, if(sum(if( unix_timestamp(sso.signin_time,'yyyy-MM-dd HH:mm:ss')between unix_timestamp(concat(ctudd.class_date,' ',ctd.evening_begin_time),'yyyy-MM-dd HH:mm:ss') - 40*60   and  unix_timestamp(concat(ctudd.class_date,' ',ctd.evening_end_time),'yyyy-MM-dd HH:mm:ss'),1,0)) > 0,  -- 如果大于0 认为当天的打卡记录中, 一定是有出勤的打卡记录, 如果小于等于0 认为没有出勤if(sum(if(unix_timestamp(sso.signin_time,'yyyy-MM-dd HH:mm:ss') between unix_timestamp(concat(ctudd.class_date,' ',ctd.evening_begin_time),'yyyy-MM-dd HH:mm:ss') - 40*60  and  unix_timestamp(concat(ctudd.class_date,' ',ctd.evening_begin_time),'yyyy-MM-dd HH:mm:ss') + 10*60, 1 , 0)) >0, -- 如果大于0, 认为当天打卡记录中, 一定是有正常出勤的记录, 否则认为迟到出勤0,1),2) as  evening_att,  --未实现substr(ctudd.class_date,1,4) as yearinfo,substr(ctudd.class_date,6,2) as monthinfo,substr(ctudd.class_date,9,2) as dayinfo
from (select * from itcast_dimen.course_table_upload_detail_dimen where nvl(content,'')!='' and content !='开班典礼') ctuddleft  join (select * from itcast_ods.student_signin_ods where share_state = 1) sso on sso.class_id = ctudd.class_idleft join itcast_dimen.class_time_dimen ctd on ctd.id = sso.time_table_id
where ctudd.class_date in ('2019-09-03','2019-09-04','2019-09-05')
group  by ctudd.class_date , ctudd.class_id,sso.student_id;

 班级出勤人数表

insert into  table itcast_dwm.class_attendance_dwm partition(yearinfo,monthinfo,dayinfo)
select dateinfo,class_id,count(case when morning_att in ('0','1') then student_idelse null end) as morning_att_count,count(case when afternoon_att in ('0','1') then student_idelse null end) as afternoon_att_count,count(case when evening_att in ('0','1') then student_idelse null end) as evening_att_count,sum(case when morning_att ='1' then 1else 0 end) as morning_late_count,sum(case when afternoon_att ='1' then 1else 0 end) as afternoon_late_count,sum(case when evening_att ='1' then 1else 0 end) as evening_late_count,yearinfo,monthinfo,dayinfo
from itcast_dwm.student_attendance_dwm
group by  dateinfo,yearinfo,monthinfo,dayinfo,class_id;

班级请假人数表

 计算上午的每天各个班级请假人数：

 计算中午的每天各个班级请假人数：

计算晚上的每天各个班级请假人数：

最后的总表应该合并上面三个表的数据，汇总每个班的早中晚的请假总人数：全外连接

先 分 后 拆 ：从A、B、C表中聚合成temp表，再group by字段组成新表。

set hive.auto.convert.join=false;--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
--set hive.enforce.bucketing=true;
--set hive.enforce.sorting=true;
--set hive.optimize.bucketmapjoin = true;
--set hive.auto.convert.sortmerge.join=true;
--set hive.auto.convert.sortmerge.join.noconditionaltask=true;
--并行执行
set hive.exec.parallel=true;
set hive.exec.parallel.thread.number=8;
--小文件合并
-- set mapred.max.split.size=2147483648;
-- set mapred.min.split.size.per.node=1000000000;
-- set mapred.min.split.size.per.rack=1000000000;
--矢量化查询
set hive.vectorized.execution.enabled=true;
--关联优化器
set hive.optimize.correlation=true;
--读取零拷贝
set hive.exec.orc.zerocopy=true;
--join数据倾斜
set hive.optimize.skewjoin=false;
-- set hive.skewjoin.key=100000;
set hive.optimize.skewjoin.compiletime=false;
set hive.optimize.union.remove=false;
-- group倾斜
set hive.groupby.skewindata=false;with A as (selectctudd.class_date,ctudd.class_id,count(distinct sla.student_id) as morning_leave_count
from (select * from itcast_ods.student_leave_apply_ods where audit_state = 1 and cancel_state = 0 and valid_state = 1) slajoin itcast_dimen.class_time_dimen ctd on sla.class_id = ctd.class_idjoin (select * from itcast_dimen.course_table_upload_detail_dimen where nvl(content,'')!='' and content != '开班典礼') ctudd on ctudd.class_id = sla.class_id
where ctudd.class_date between ctd.use_begin_date  and  ctd.use_end_date and concat(ctudd.class_date,' ',ctd.morning_begin_time) >= sla.begin_timeand concat(ctudd.class_date,' ',ctd.morning_begin_time) <= sla.end_time
group by ctudd.class_date,ctudd.class_id),B AS (selectctudd.class_date,ctudd.class_id,count(distinct sla.student_id) as afternoon_leave_count
from (select * from itcast_ods.student_leave_apply_ods where audit_state = 1 and cancel_state = 0 and valid_state = 1) slajoin itcast_dimen.class_time_dimen ctd on sla.class_id = ctd.class_idjoin (select * from itcast_dimen.course_table_upload_detail_dimen where nvl(content,'')!='' and content != '开班典礼') ctudd on ctudd.class_id = sla.class_id
where ctudd.class_date between ctd.use_begin_date  and  ctd.use_end_date and concat(ctudd.class_date,' ',ctd.afternoon_begin_time) >= sla.begin_timeand concat(ctudd.class_date,' ',ctd.afternoon_begin_time) <= sla.end_time
group by ctudd.class_date,ctudd.class_id),C AS (selectctudd.class_date,ctudd.class_id,count(distinct sla.student_id) as evening_leave_count
from (select * from itcast_ods.student_leave_apply_ods where audit_state = 1 and cancel_state = 0 and valid_state = 1) slajoin itcast_dimen.class_time_dimen ctd on sla.class_id = ctd.class_idjoin (select * from itcast_dimen.course_table_upload_detail_dimen where nvl(content,'')!='' and content != '开班典礼') ctudd on ctudd.class_id = sla.class_id
where ctudd.class_date between ctd.use_begin_date  and  ctd.use_end_date and concat(ctudd.class_date,' ',ctd.evening_begin_time) >= sla.begin_timeand concat(ctudd.class_date,' ',ctd.evening_begin_time) <= sla.end_time
group by ctudd.class_date,ctudd.class_id),
temp as (select  coalesce(A.class_date,B.class_date,C.class_date) AS class_date,coalesce(A.class_id,B.class_id,C.class_id) AS class_id,nvl(A.morning_leave_count,0) as morning_leave_count,nvl(B.afternoon_leave_count,0) as afternoon_leave_count,nvl(C.evening_leave_count,0) as evening_leave_count
from A full join B on A.class_date = B.class_date and A.class_id = B.class_idfull join C on A.class_date = C.class_date and A.class_id = C.class_id)
insert into table itcast_dwm.class_leave_dwm partition(yearinfo,monthinfo,dayinfo)
select class_date,class_id,sum(morning_leave_count) as morning_leave_count,sum(afternoon_leave_count) as afternoon_leave_count,sum(evening_leave_count) as evening_leave_count,substr(class_date,1,4) as yearinfo,substr(class_date,6,2) as monthinfo,substr(class_date,9,2) as dayinfo
from temp group by class_date,class_id;

旷课人数表

 计算规则: 
    旷课人数 = 当日在读人数 - 出勤人数 - 请假人数

insert into table itcast_dwm.class_truant_dwm partition(yearinfo,monthinfo,dayinfo)
select ctudd.class_date as dateinfo,ctudd.class_id,cssc.studying_student_count - nvl(cad.morning_att_count,0) - nvl(cld.morning_leave_count,0) as morning_truant_count,cssc.studying_student_count - nvl(cad.afternoon_att_count,0) - nvl(cld.afternoon_leave_count,0) as afternoon_truant_count,cssc.studying_student_count - nvl(cad.evening_att_count,0) - nvl(cld.evening_leave_count,0) as evening_truant_count,substr(ctudd.class_date,1,4) as yearinfo,substr(ctudd.class_date,6,2) as monthinfo,substr(ctudd.class_date,9,2) as dayinfo
from (select * from itcast_dimen.course_table_upload_detail_dimen where nvl(content,'')!='' and content != '开班典礼') ctudd left join itcast_dimen.class_studying_student_count_dimen cssc on  ctudd.class_date = cssc.studying_date and ctudd.class_id = cssc.class_idleft join itcast_dwm.class_attendance_dwm cad on ctudd.class_id = cad.class_id and ctudd.class_date = cad.dateinfoleft join itcast_dwm.class_leave_dwm cld on ctudd.class_id = cld.class_id and ctudd.class_date = cld.dateinfo
where ctudd.class_date in('2019-09-03','2019-09-04','2019-09-05')

 汇总表：

insert into table itcast_dwm.class_all_dwm partition(yearinfo,monthinfo,dayinfo)
selectctudd.class_date as dateinfo,ctudd.class_id,cssc.studying_student_count,cad.morning_att_count,concat(round(nvl(cad.morning_att_count,0) /  cssc.studying_student_count * 100,2),'%') as morning_att_ratio,cad.afternoon_att_count,concat(round(nvl(cad.afternoon_att_count,0) /  cssc.studying_student_count * 100,2),'%') as afternoon_att_ratio,cad.evening_att_count,concat(round(nvl(cad.evening_att_count,0) /  cssc.studying_student_count * 100,2),'%') as evening_att_ratio,cad.morning_late_count,concat(round(nvl(cad.morning_late_count,0) /  cssc.studying_student_count * 100,2),'%') as morning_late_ratio,cad.afternoon_late_count,concat(round(nvl(cad.afternoon_late_count,0) /  cssc.studying_student_count * 100,2),'%') as afternoon_late_ratio,cad.evening_late_count,concat(round(nvl(cad.evening_late_count,0) /  cssc.studying_student_count * 100,2),'%') as evening_late_ratio,cld.morning_leave_count,concat(round(nvl(cld.morning_leave_count,0) /  cssc.studying_student_count * 100,2),'%') as morning_leave_ratio,cld.afternoon_leave_count,concat(round(nvl(cld.afternoon_leave_count,0) /  cssc.studying_student_count * 100,2),'%') as afternoon_leave_ratio,cld.evening_leave_count,concat(round(nvl(cld.evening_leave_count,0) /  cssc.studying_student_count * 100,2),'%') as evening_leave_ratio,ctd.morning_truant_count,concat(round(nvl(ctd.morning_truant_count,0) /  cssc.studying_student_count * 100,2),'%') as morning_truant_ratio,ctd.afternoon_truant_count,concat(round(nvl(ctd.afternoon_truant_count,0) /  cssc.studying_student_count * 100,2),'%') as afternoon_truant_ratio,ctd.evening_truant_count,concat(round(nvl(ctd.evening_truant_count,0) /  cssc.studying_student_count * 100,2),'%') as evening_truant_ratio,substr(ctudd.class_date,1,4) as yearinfo,substr(ctudd.class_date,6,2) as monthinfo,substr(ctudd.class_date,9,2) as dayinfofrom (select * from itcast_dimen.course_table_upload_detail_dimen where nvl(content,'')!='' and content != '开班典礼') ctuddleft join itcast_dimen.class_studying_student_count_dimen cssc on cssc.class_id = ctudd.class_id and cssc.studying_date = ctudd.class_dateleft join itcast_dwm.class_attendance_dwm cad on ctudd.class_id = cad.class_id and ctudd.class_date = cad.dateinfoleft join itcast_dwm.class_leave_dwm cld on ctudd.class_id = cld.class_id and ctudd.class_date = cld.dateinfoleft join itcast_dwm.class_truant_dwm ctd on ctudd.class_id = ctd.class_id and ctudd.class_date = ctd.dateinfo
where ctudd.class_date in ('2019-09-03','2019-09-04','2019-09-05');

数据分析：DWS

DWS: 细化维度统计操作

需求一: 统计每年 每月 每天 上午, 下午, 晚自习 各个班级的 相关的指标  (指的DWM层的汇总表数据)，建议直接抽取存储即可
需求二: 统计每年 每月 上午, 下午, 晚自习 各个班级的 相关的指标
需求三: 统计每年 上午, 下午, 晚自习 各个班级的 相关的指标

 需求二: 统计每年 每月 上午, 下午, 晚自习 各个班级的 相关的指标：

insert into table itcast_dws.class_attendance_dws partition(yearinfo,monthinfo,dayinfo)
select concat(yearinfo,'-',monthinfo) as dateinfo,class_id,sum(studying_student_count) as studying_student_count,sum(morning_att_count) as  morning_att_count,concat(round(sum(morning_att_count) / sum(studying_student_count) *100,2),'%') as morning_att_ratio,sum(afternoon_att_count) as  afternoon_att_count,concat(round(sum(afternoon_att_count) / sum(studying_student_count) *100,2),'%') as afternoon_att_ratio,sum(evening_att_count) as evening_att_count,concat(round(sum(evening_att_count) / sum(studying_student_count) *100,2),'%') as evening_att_ratio,sum(morning_late_count) as  morning_late_count,concat(round(sum(morning_late_count) / sum(studying_student_count) *100,2),'%') as morning_late_ratio,sum(afternoon_late_count) as  afternoon_late_count,concat(round(sum(afternoon_late_count) / sum(studying_student_count) *100,2),'%') as afternoon_late_ratio,sum(evening_late_count) as evening_late_count,concat(round(sum(evening_late_count) / sum(studying_student_count) *100,2),'%') as evening_late_ratio,sum(morning_leave_count) as  morning_leave_count,concat(round(sum(morning_leave_count) / sum(studying_student_count) *100,2),'%') as morning_leave_ratio,sum(afternoon_leave_count) as  afternoon_leave_count,concat(round(sum(afternoon_leave_count) / sum(studying_student_count) *100,2),'%') as afternoon_leave_ratio,sum(evening_leave_count) as evening_leave_count,concat(round(sum(evening_leave_count) / sum(studying_student_count) *100,2),'%') as evening_leave_ratio,sum(morning_truant_count) as  morning_truant_count,concat(round(sum(morning_truant_count) / sum(studying_student_count) *100,2),'%') as morning_truant_ratio,sum(afternoon_truant_count) as  afternoon_truant_count,concat(round(sum(afternoon_truant_count) / sum(studying_student_count) *100,2),'%') as afternoon_truant_ratio,sum(evening_truant_count) as evening_truant_count,concat(round(sum(evening_truant_count) / sum(studying_student_count) *100,2),'%') as evening_truant_ratio,'4' as  time_type,yearinfo,monthinfo,'-1' as dayinfofrom  itcast_dwm.class_all_dwm
group by yearinfo,monthinfo,class_id;

数据导出： 

类比第一第二模块……

 数据可视化

商业BI系统

商业BI系统的概念：

商业智能系统，通常简称为商业智能系统，是商业智能软件的简称，是为提高企业经营绩效而采用的一系列方法、技术和软件的总和。通常被理解为将企业中的现有数据转换为知识并帮助企业做出明智的业务决策的工具。（数据仓库+数据挖掘+可视化）

商业BI系统的数据来源：

BI系统中的数据来自企业的其他业务系统。例如，一个面向业务的企业，其业务智能系统数据包括业务系统订单、库存、交易账户、客户和供应商信息，以及企业所属行业和竞争对手的数据，以及其他外部环境数据。这些数据可能来自于CRM、SCM和发票等业务系统。

商业BI系统的功能：

首先，需要收集所有的数据。这个过程称为“数据仓库”。数据仓库提供了一个数据存储环境，从多个数据源获取的数据根据特定的主题进行ETL(提取、转换、转储)数据、清理数据和存储。

其次，需要数据分析来辅助企业建模的能力。OLAP是一种基于数据仓库环境的数据分析工具。OLAP解决了基于OLTP分析的多维度分析效率低的缺点。在实际应用中，数据挖掘也常用来挖掘过去和预测未来。它是一个使用知识发现工具来挖掘以前未知的和潜在有用的知识的过程。它是一种主动的自动发现方法。

商业智能的一个重要特性是数据可视化。数据可视化是指通过适当的图表类型以一种视觉上吸引人的方式显示信息，使每个人都能更快更好地理解数据。另外，BI还有终端信息查询和报表生成功能。

到这里，所有的流程就被称为“BI系统”。

离线静态可视化图表：