一、实验背景1.1实验目的基于数智教育大赛数据集设计并搭建学生多维度考勤统计助睿 ETL 转换流程完整覆盖数据接入、关联匹配、字段衍生、指标聚合、数据落地全 ETL 处理链路。针对校园考勤人工统计效率低下、统计口径不统一等痛点开展专项解决同时结合实验真实数据特征优化空值及异常数据处理逻辑保障 ETL 转换流稳定可靠运行输出高精度、多维度的考勤统计结果为校园考勤精细化管理提供坚实的数据支撑与决策依据。1.2实验环境零代码在线实验平台助睿数智平台“数智教育”大赛数据集共包含7张核心业务表计算机支持助睿ETL平台运行具备数据库连接权限1.3实验流程转换流核心逻辑依次接入三大业务数据源经多表关联融合后完成考勤行为类型标记、考勤核心指标计算再关联学生基础属性信息最终落地生成多维度考勤统计结果实现考勤数据处理全流程自动化整体流程依托助睿 ETL 平台可视化界面即可完成全链路配置与调度。二、实验步骤2.1创建项目点击新建项目并命名为“学生用户画像标签构建”最后点击“确定”即可完成项目创建。2.2 数据获取2.2.1 实验数据集获取点击项目右上角“…”打开项目点击文件库在根目录右键选择新建目录输入目录名称为“数智教育数据集”点击“确定”点击“公告空间”点击“数据资源”点击“3_kaoqin.csv”右上角的“更多”并点击“导出”在弹出的窗口中选择刚刚创建的“数智教育数据集”并点击“确定”接下来重复导出3_kaoqin.csv的操作将本次实验要用到的 4_kaoqintype.csv 和 2_student_info.csv 都导出到“数智教育数据集”2.2.2 建立数据源连接选择元数据关系数据库右键打开菜单选择“新建数据源”在弹出的新窗口中连接类型选择“MySQL”用户名和密码使用课程提供的账号和密码。服务器主机名使用“rm-2vc3qok06bag39a5n.mysql.cn-chengdu.rds.aliyuncs.com”端口号为3306数据库名为用户名驱动类型选择“MySQL 8”连接名称为“团队私有数据库”配置完成后先点“击测试“若测试通过点击”添加“。2.2.3 数据导入数据库2.2.3.1导入原始考勤记录数据选择资源库并右键根目录选择新建转换流并命名为“创建原始_学生考勤表”所以转换流每次打开都是锁定状态想要编辑需要先解除锁定在组件库中搜索“执行一个SQL脚本”组件并拖拽一个该组件放入画布双击画布中的“执行一个SQL脚本”组件按照下图进行配置SQL脚本CREATE TABLE IF NOT EXISTS raw_attendance ( id bigint NOT NULL AUTO_INCREMENT COMMENT 自增ID, attendance_id varchar(64) DEFAULT NULL COMMENT 考勤ID, learn_term varchar(30) DEFAULT NULL COMMENT 学期, data_datetime varchar(50) DEFAULT NULL COMMENT 时间和日期, attendance_type_id varchar(64) DEFAULT NULL COMMENT 考勤类型ID, attendance_name varchar(100) DEFAULT NULL COMMENT 考勤名称, attendance_task_order_id varchar(64) DEFAULT NULL COMMENT 考勤事件ID, stu_id varchar(64) DEFAULT NULL COMMENT 学生ID, stu_name varchar(100) DEFAULT NULL COMMENT 学生姓名, cla_name varchar(100) DEFAULT NULL COMMENT 班级名, cla_id varchar(64) DEFAULT NULL COMMENT 班级ID, create_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT 入库时间, PRIMARY KEY (id), KEY idx_student_id (stu_id), KEY idx_term (learn_term) ) COMMENT原始_学生考勤表;完成后运行转换流运行过程中会定时刷新组件状态可通过画布下方显示的执行日志查看情况新建转换流命名为“导入原始考勤数据”在组件库中搜索拖拽一个“CSV文件输入”组件到该转换流画布双击CSV文件输入组件进入组件配置界面后按照图中步骤进行配置在下方空白处右键即可打开菜单选择获取字段字段获取成功后点击“确认”接下来再搜索并拖拽一个“表输出”组件到画布建立CSV文件输入组件到表输出组件的连接连线类型选择“主输出步骤“双击“表输出”组件在组件配置界面数据库连接选择“团队私有数据库”目标表选择我们使用SQL组件创建的“raw_attendance”具体匹配如图点击“数据库字段”在空白处右键“获取字段”将表字段修改为建表语句中对应的字段点击“确认”完成后运行转换流运行过程中会定时刷新组件状态可通过画布下方显示的执行日志查看运行情况2.2.3.2导入原始考勤类型数据操作同“导入原始考勤记录数据”实验操作先创建“创建原始_考勤类型表“转换流完成表的创建再创建“导入原始考勤类型数据”转换流将4_kaoqintype.csv的数据导入到团队私有数据库详细配置如下“执行一个SQL脚本”组件配置SQL脚本CREATE TABLE IF NOT EXISTS raw_attendance_type ( id bigint NOT NULL AUTO_INCREMENT COMMENT 自增ID, attendance_type_id varchar(64) NOT NULL COMMENT 考勤类型id, attendance_type_name varchar(100) DEFAULT NULL COMMENT 考勤类型名称, attendance_task_order_id varchar(64) DEFAULT NULL COMMENT 考勤事件id, attendance_task_name varchar(100) DEFAULT NULL COMMENT 考勤事件名, create_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT 入库时间, PRIMARY KEY (id), UNIQUE KEY uk_attendance_type_id (attendance_task_order_id) )COMMENT原始_考勤类型表;“CSV文件输入“组件配置特别说明考勤类型原始表的列分隔符和考勤表的不一样在配置中列分隔符为“插入制表符TAB”、编码选择“GB2312”“表输出”组件配置2.2.3.3导入原始学生基本信息数据操作同“导入原始考勤记录数据”实验操作先创建“创建原始_学生信息表“转换流完成表的创建再创建“导入原始学生基本信息数据”转换流将2_student_info.csv的数据导入到团队私有数据库。在导入原始学生基本信息数据转换流中因为原始表中bf_zhusu、bf_qinshihao 这2个字段是Integer为避免出现小数需要使用“字段选择”组件来固化并规范所以CSV文件输入组件和表输出组件之间需要增进一个字段选择组件详细操作与配置如下:SQL脚本CREATE TABLE IF NOT EXISTS raw_student_info ( id bigint NOT NULL AUTO_INCREMENT COMMENT 自增ID, stu_id varchar(64) NOT NULL COMMENT 学生ID, stu_name varchar(100) DEFAULT NULL COMMENT 学生姓名, stu_sex varchar(10) DEFAULT NULL COMMENT 性别, stu_nation varchar(50) DEFAULT NULL COMMENT 民族, born_date varchar(10) DEFAULT NULL COMMENT 出生日期年, cla_name varchar(100) DEFAULT NULL COMMENT 班级名, native_place varchar(200) DEFAULT NULL COMMENT 家庭住址, residence_type varchar(50) DEFAULT NULL COMMENT 家庭类型, policy varchar(50) DEFAULT NULL COMMENT 政治面貌, cla_id varchar(64) DEFAULT NULL COMMENT 班级ID, cla_term varchar(30) DEFAULT NULL COMMENT 班级学期, live_on_campus varchar(10) DEFAULT NULL COMMENT 是否住校, leave_school varchar(10) DEFAULT NULL COMMENT 是否退学, dormitory_no varchar(50) DEFAULT NULL COMMENT 宿舍号, create_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT 入库时间, PRIMARY KEY (id), UNIQUE KEY uk_student_id (stu_id), KEY idx_cla_id (cla_id) ) COMMENT原始_学生信息表;CSV文件输入组件配置注意封闭符改为了“-”建立CSV文件输入组件与字段选择组件的连接,选择主输出步骤字段选择组件配置表输出组件配置2.2.3.4创建学生考勤主题标签表新建转换流并命名为“创建学生考勤主题标签表”在该工作流中拖拽“执行一个SQL脚本”组件配置如下配置完成后执行该转换流SQL脚本CREATE TABLE IF NOT EXISTS student_attendance_stats ( id INT PRIMARY KEY AUTO_INCREMENT COMMENT 自增主键, student_id INT NOT NULL COMMENT 学生ID, student_name VARCHAR(50) NOT NULL COMMENT 学生姓名, class_id INT NOT NULL COMMENT 班级ID, class_name VARCHAR(50) NOT NULL COMMENT 班级名称, grade VARCHAR(10) NOT NULL COMMENT 年级, gender VARCHAR(10) NOT NULL COMMENT 性别, birth_date VARCHAR(10) NOT NULL COMMENT 出生日期, political_status VARCHAR(20) NOT NULL COMMENT 政治面貌, is_boarder VARCHAR(10) NOT NULL COMMENT 是否住校, campus_type VARCHAR(10) NOT NULL COMMENT 校区类型, late_count INT NOT NULL DEFAULT 0 COMMENT 迟到次数, early_leave_count INT NOT NULL DEFAULT 0 COMMENT 早退次数, leave_count INT NOT NULL DEFAULT 0 COMMENT 请假次数, uniform_violate_count INT NOT NULL DEFAULT 0 COMMENT 没穿校服次数, create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 统计入库时间, INDEX idx_student (student_id), INDEX idx_class (class_id), INDEX idx_grade (grade) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT学生考勤主题标签表;2.3 考勤标签构建数据流转严格遵循数据接入、清洗整合、维度拆解、标签标注、指标核算、成果落地全流程核心逻辑数据接入汇聚考勤原始打卡数据、考勤类型对照表、学生基础信息表等源头数据清洗整合完成多表数据关联匹配为打卡记录精准绑定学生班级、住宿属性、考勤事件等基础信息维度拆解拆分统计维度划分不同统计口径与分析层级标签标注依托考勤事件自动归类识别迟到、早退、请假、仪容不规范等异常行为指标核算按日精准测算学生在校时长多维度汇总统计各类考勤异常频次成果落地将最终统计数据统一归档至考勤结果数据表支撑数据查询、报表生成与深度分析应用。2.3.1 数据接入首先我们需要获取考勤记录、考勤类型数据、学生信息数据。有了这三份数据接入并后续关联起来才能完整判断学生的每次考勤是正常还是违纪同时支撑相关维度的统计。创建“学生考勤主题标签”转换流拖拽三个表输入组件到画布双击第一个表输入组件进入配置界面进行如下配置获得完整SQL查询语句后点击确定另外两个表输入组件同上操作分别命名为“考勤类型”和“学生信息”分别获取“raw_attendance_type”考原始_考勤类型表和“raw_student_info”原始_学生信息表所有字段数据2.3.2 数据处理与转换流建立首先关联考勤记录与考勤类型在组件库搜索“记录集连接”组件并将组件拖拽至画布中由于记录集连接组件是按接收数据的顺序进行记录关联的如果接收的数据是无序的可能会造成记录连接结果出错为避免因为排序问题造成连接结果出错我们还需要添加一个排序记录组件到转换流的“考勤记录”与“记录集连接”之间建立连接如下双击“排序记录”组件设置步骤名称为“按照考勤类型和考勤任务类型排序”然后在下方空白右键获取字段接着删除多余字段只保留“attendance_type_id”、“attendance_task_order_id”字段创建“考勤类型”到记录集连接组件的连接线。因为“考勤类型”的数据默认是按“attendance_type_id”、“attendance_task_order_id”这两个字段升序记录的所以无需再次排序双击记录集连接组件第一个Transform选择“按照考勤类型和考勤任务类型排序”第二个Transform选择“考勤类型”连接类型选择LEFT OUTER获取字段后仅保留“attendance_type_id”、“attendance_task_order_id”字段其余字段删除后点击确定接下来通过Javascript脚本生成考勤行为二进制标记为后续聚合统计提供支撑确保标签判断精准添加“JavaScript 代码”组件并命名为“提取异常考勤记录”建立 “记录集连接”组件到JavaScript 代码”组件的连接通过关键词匹配生成二进制判断标签1是0否。双击“JavaScript代码”组件输入JavaScript脚本后点击获取变量脚本代码// 初始化变量 var isLate 0; var isEarly 0; var isLeave 0; var isNoUniform 0; // 核心判断逻辑 if(attendance_type_name ! null attendance_task_name ! null){ // 迟到判断排除请假 if((attendance_type_name.includes(迟到) || attendance_type_name.includes(晚到) || attendance_task_name.includes(迟到) || attendance_task_name.includes(晚到)) !attendance_task_name.includes(请假)){ isLate 1; } // 早退判断排除请假 if((attendance_type_name.includes(早退) || attendance_task_name.includes(早退)) !attendance_task_name.includes(请假)){ isEarly 1; } // 校服违规只要包含“校服”就标记违规 if(attendance_type_name.includes(校服) || attendance_task_name.includes(校服)){ isNoUniform 1; } } // 请假判断 if(attendance_task_name ! null){ if(attendance_task_name.includes(请假)){ isLeave 1; } }获取到如下变量后点击测试脚本在预览数据中确认是否只有0或1两个值并且对应正常确认正常后点击确认然后我们来聚合数据将明细数据转化为统计指标满足多层级考勤管理需求。具体操作如下添加“分组”组件并建立连接线连线选择“主输出步骤”双击“分组”组件设置分组字段为“stu_id”、“stu_name”、“cla_id”、“cla_name”聚合字段为“late_early_count”、“leave_count”、“no_uniform_count”、“compliant_count”、“total_attendance”后点击确认我们接下来再关联“学生信息”数据因为原始学生信息数据中的学号不是升序记录的所以在进行记录关联前也需要对数据进行排序。再次添加“排序记录”并建立“学生信息”表输入组件到“排序记录”组件的连接线双击“排序记录”组件设置步骤名称为“按照学生编号进行排序”通过“获取字段”功能获取字段列表然后删除多余字段只保留“stu_id”字段。拖拽“记录集连接”组件至画布中创建连接由于考勤记录数据不是按“学号”升序记录的所以在进行记录关联前也需要对数据进行排序添加排序记录组件并建立与分组的连接排序记录组件设置如下创建“考勤数据按学号排序”记录排序组件到记录集连接 1组件的连线关联学生信息和考勤记录信息双击记录集连接组件配置如下:经过多表关联和前期接入数据中会包含大量与考勤统计无关的字段需要对关联后的数据进行冗余字段移除保留核心有用字段。搜索添加“字段选择”创建“记录集连接 1”组件到字段选择组件的连接线双击字段选择组件步骤名称设置为“移除冗余字段”点击“移除”右键空白处并点击“获取字段”在获取的字段中删除以下核心字段学生 IDstu_id学生姓名stu_name班级 IDcla_id班级名称cla_name迟到次数late_count早退次数early_count请假次数leave_count没穿校服次数no_uniform_count性别stu_sex出生日期born_date政治面貌policy是否住校live_on_campus在字段选择组件鼠标右键弹出菜单点击“显示输出字段”查看输出字段是否正确3个数据表关联后字段“stu_sex”、“born_date”、“policy”、“live_on_campus”存在空值需要对这些空值进行处理。拖拽“替换NULL值”组件至画布创建“移除冗余字段”字段选择组件到“替换NULL值”组件的连线连线类型选择“主输出步骤”双击“替换NULL值”组件勾选“选择字段”在下方空白中右键点击“插入”插入四行 “stu_sex”、“born_date”、“policy”、“live_on_campus”并将它们的空值均替换为“未知”经过多表关联与字段筛选后缺少年级、校区类型等画像分析必需字段无法直接用于学生考勤标签输出与后续用户画像分析。因此需要对学生基础属性进行标准化映射、缺失字段衍生统一数据格式、补齐分析维度保证标签表规范可用。首先是住校状态映射添加“值映射”组件创建替换NULL值组件到值映射组件的连线并选择“主输出步骤”双击“值映射”组件配置如下接着从班级名提取年级拖拽“JavaScript代码”组件至画布中创建住校状态映射组件到JavaScript代码组件的连线双击“JavaScript代码”组件配置如下脚本var gra_name if (cla_name null){ gra_name未知 }else if(cla_name.includes(高一)){ gra_name高一 }else if (cla_name.includes(高二)){ gra_name高二 }else if (cla_name.includes(高三)){ gra_name高三 } else{ gra_name未知 }再来是校区类型判定步骤参考“从班级名提取年级”JavaScript代码组件配置如下脚本var class_campus_type if (cla_name null){ class_campus_type未知 }else if(cla_name.startsWith(白-) || cla_name.startsWith(东-)){ class_campus_type新校区 }else if (cla_name ! null !isEmpty(cla_name)){ class_campus_type老校区 } else{ class_campus_type未知 }2.2.3 结果入库添加表输出组件并创建“校区类型判定”到表输出组件的连线双击表输出组件配置如下点击数据库字段右键选择“获取字段”双击表字段中的字段名称在下拉框中选择建表时的对应字段转换流执行完成后打开“元数据”在“团队私有数据库”连接上右键选择“加载元数据”然后进入数据探查页面展开“团队私有数据库”双击目标表“student_attendance_stats”在右侧页面选择“查询”检查数据库表数据是否符合预期三、实验结果本次实验针对考勤行为设置了迟到、早退、请假、未穿校服四类标签依托考勤事件名称完成自动识别与标签标记。实验结束后对输出数据进行了全面校验经逐一核查确认所有考勤行为对应的标签添加准确无误标签判断逻辑贴合实验预设规则无漏标、错标情况。最终输出的多维度考勤统计结果精准呈现了学生每日在校时长、各维度考勤异常次数等核心指标数据格式规范、数值准确可直接为校园考勤管理工作提供可靠的数据支撑。四、问题与解决4.1 问题问题移除冗余字段步骤中在删除核心字段时未发现统计聚合的四个违规次数字段4.2 原因原因在分组组件设置聚合字段时忘记设置聚合类型为“Sum”4.3 解决解决重新设置分组组件聚合字段将四个字段聚合类型全部设置为“Sum”五、实验总结本次实验以掌握ETL数据处理全流程、解决校园考勤管理痛点为核心目标通过理论结合实践的方式完成了学生多维度考勤统计ETL转换流的设计与实现整体达成实验预期目标收获了扎实的ETL实操经验与数据处理能力。通过本次实验不仅熟练掌握了ETL数据接入、关联、衍生、聚合、落地的全流程操作更深刻理解了数据处理过程中异常处理、逻辑优化的重要性。同时实验过程中也积累了数据校验、逻辑调试的实操技巧为后续开展更复杂的ETL数据处理项目提供了有力支撑。