1 实验概述

1.1 实验目的

本实验基于“数智教育”大赛数据集，旨在设计并实现一个学生多维度考勤统计ETL转换流。通过该实验，学习者将全面掌握ETL（Extract-Transform-Load）数据处理的全流程技术（包括数据接入、多表关联、标签衍生、多维聚合及结果落地）。本实验旨在解决传统校园考勤人工统计效率低下、统计口径不统一的核心痛点；同时，结合实验数据的实际特征，优化空值处理与文本映射逻辑，确保整个转换流的高效稳定运行，最终输出精准的多维度考勤统计结果，为校园精细化考勤管理提供坚实的数据支撑。

1.2 实验环境

• 实验平台：助容零代码在线实验平台（https://lab.guilian.cn/）。

• 核心工具：本实验采用助睿数智（Uniplore）AI驱动一站式数据科学平台。该平台具备从数据接入、ETL处理、机器学习建模到可视化展示的全链路零代码配置功能，广泛应用于数据分析教学与企业级数据加工。产品官网：https://www.uniplore.com/。

• 数据资源：“数智教育”大赛数据集（包含7张核心业务表）。

• 实验设备：联网计算机（支持助睿ETL平台顺畅运行，并具备相应的数据库连接权限）。

1.3 实验范围

本次实验覆盖助睿ETL转换流的端到端配置。核心验证范围包括：多源数据接入、复杂多表左外连接（LEFT OUTER JOIN）、考勤行为标签动态衍生、多维度分组聚合统计以及最终结果落地入库。实验将重点验证数据关联的精准性与统计指标的准确性，并结合实际数据特征优化异常空值配置。

2 转换流整体设计

2.1 功能用途与业务价值

本转换流致力于替代传统的Excel人工统计模式，实现考勤数据从“原始打卡记录”到“标准化统计结果”的闭环自动化处理，具备大批量、多指标的同步输出能力。通过固化统计口径，不仅大幅提升了全校考勤数据的加工效率，还沉淀了标准的历史考勤台账。此外，该设计具备良好的可扩展性，未来若新增考勤事件类型，无需重构转换流核心逻辑即可实现无缝适配。

2.2 核心处理逻辑

转换流遵循“多源接入→排序关联→行为清洗与标签标记 ​→ 属性补全与标准化 ​→ 分组聚合指标计算 ​→ 落地统计结果”的自动化链条。

3 数据与标签梳理

3.1 源数据说明

原始数据集共包含7张表。本实验聚焦于“考勤主题”，通过深度分析业务关联性，筛选出以下3张核心数据表，构建出标准的“事实表 + 维度表 + 属性表”的星型模型结构：

3.2 标签字段说明

学生考勤主题标签主要涵盖以下三大类：

3.2.1 学生基础属性标签

• 学生ID / 姓名：作为全表唯一标识与人工核对的核心依据。

• 班级ID / 名称：用于班级层面的精细化统计，并作为年级和校区提取的源字段。

• 性别 / 出生日期 / 政治面貌：用于跨维度的考勤差异化行为分析。

• 选表依据：此类字段属于学生的基础身份信息，主要由2_student_info表提供，部分交织字段通过3_kaoqin表进行交叉验证。

3.2.2 学生画像维度标签

• 年级：从班级名称（cla_name）中剥离提取（高一/高二/高三），便于按年级分层管理。

• 是否住校：映射并清洗bf_zhusu字段。住校生与走读生往往具备不同的考勤行为模式。

• 校区类型：依据2017年新校区启用规则，通过班级名称前缀（如“白-”、“东-”）动态判定新旧校区。

• 选表依据：属于动态衍生维度，原始数据未直接提供，需通过ETL规则进行二次加工加工。

3.2.3 考勤行为统计标签

• 迟到次数 / 早退次数：学生行为规范考核的核心违纪指标。

• 请假次数：用于区分正常缺勤与异常违纪。

• 没穿校服次数：学校日常行为规范的重要考核指标。依据数据说明，“校服[移动考勤]”特指未穿校服行为。

3.3 标签处理口径

3.3.1 基础属性及衍生维度处理口径

• 性别 / 出生日期 / 政治面貌：若值为空（NULL），统一替换为“未知”。

• 年级衍生：班级名包含“高一”→高一；包含“高二”→高二；包含“高三”→高三；其余→未知。

• 是否住校：bf_zhusu \= 1 ​→ “是”；bf_zhusu \= 0 ​→ “否”；空值 →​ “未知”。

• 校区类型：班级名以“白-”或“东-”开头 →​ “新校区”；其余非空班级 →​ “老校区”；空值 ​→ “未知”。

3.3.2 考勤行为指标统计口径

• 迟到次数：统计考勤事件或类型名称中包含“迟到”或“晚到”，且排除“请假”关键词的记录总条数。

• 早退次数：统计考勤事件或类型名称中包含“早退”，且排除“请假”关键词的记录总条数。

• 请假次数：统计考勤事件名称中包含“请假”关键词（含事假、病假等）的所有记录总条数。

• 没穿校服次数：统计考勤事件名称中包含“校服”关键词的异常记录总条数。

4 实验步骤

4.1 创建实验项目

1. 登录助睿在线实验平台，点击主界面的“新建项目”按钮。

2. 在弹出的对话框中输入项目名称：学生用户画像标签构建，点击确认。

3. 项目创建成功后，在数据集成列表中即可查看当前项目。

4.2 数据资源获取与准备

点击项目卡片右上角的“...”，选择“打开项目”进入工作区。系统左侧包含三个核心菜单：资源库（工作流管理与调度）、文件库（文件存储）、元数据（运行配置与数据库连接）。

4.2.1 获取实验数据集

1. 点击“文件库”，在根目录下右键选择“新建目录”，命名为数智教育数据集。

   

2. 切换至“公共空间” ​ →“数据资源”，找到 3_kaoqin.csv、4_kaoqintype.csv 和 2_student_info.csv，分别点击卡片右上角的“更多”→“导出”，将其全部导出至新建的数智教育数据集目录下。

4.2.2 建立团队私有数据库连接

1. 切换至“元数据”标签页，右键点击“关系数据库”，选择“新建数据源”。

2. 在弹出的配置窗口中填写以下数据库连接参数：

   • 连接类型：MySQL

   • 驱动类型：MySQL 8+

   • 连接名称：团队私有数据库

   • 服务器主机名：rm-2vc3qok06bag39a5n.mysql.cn-chengdu.rds.aliyuncs.com

   • 端口号：3306

   • 用户名/密码/数据库名：按助教提供的实际账号信息填写。

     

3. 填写完毕后点击“测试”，提示“数据库连接成功”后点击“添加”保存。

   

4.2.3 数据源导入关系数据库

4.2.3.1 原始考勤记录表（raw_attendance）导入

2. 数据灌入：新建转换流导入原始考勤数据。拖入“CSV文件输入”组件，浏览选中 3_kaoqin.csv。设置编码为 UTF-8，右键点击“获取字段”。随后拖入“表输出”组件，通过主输出线连接。配置目标表为 raw_attendance，在“数据库字段”页签下获取字段并映射，最后执行该转换流。

   
   

   点击获取字段后，确认

   

   设置表输出组件

   
   

4.2.3.2 原始考勤类型表（raw_attendance_type）导入

参考上述步骤，新建转换流创建原始_考勤类型表利用SQL脚本组件创建 raw_attendance_type 表，完成后运行。

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，获取字段后通过表输出组件。

表输出组件设置

4.2.3.3 原始学生信息表（raw_student_info）导入（与之前相同）

1. 新建转换流创建原始_学生信息表运行SQL脚本创建 raw_student_info 物理表。

   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='原始_学生信息表';

   ​

2. 新建转换流导入原始学生基本信息表数据，配置“CSV文件输入”引入 2_student_info.csv，在获取字段后，手动将 bf_leaveSchool 的字段类型修改为 String。

   
   

3. 规范化处理：为防止 bf_zhusu 和 bf_qinshihao 两个整型字段在导入时产生浮点尾数，在CSV输入后拖入“字段选择”组件。创建“CSV文件输入”组件到“字段选择”组件的连线，连接线类型选择“主输出步骤”切换至“元数据”页签，手动指定这两个字段的元数据类型为不带小数位的标准格式。最后连接“表输出”组件执行导入。

   
   

   表输出组件设置。

   
   
   

   完成后运行转换流。

4.2.4 创建学生考勤主题标签表（student_attendance_stats）

新建转换工作流 创建学生考勤主题标签表，拖入SQL脚本组件，连接私有关系数据库，执行用于存放最终统计结果的 student_attendance_stats 表的建表语句，建立标准宽表结构并设置索引。

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)
)  ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生考勤主题标签表';

其余参数默认即可

4.3 学生考勤主题标签构建（核心ETL）

完成数据准备后，进入核心转换流设计。新建转换流并命名为学生考勤主题标签，在画布上方点击解锁图标进入可编辑状态。

4.3.1 数据接入

从组件库搜索并拖拽3个“表输入”组件至画布，分别命名为：考勤记录、考勤类型、学生信息。

• 双击各组件，选择连接团队私有数据库。

• 点击“获取SQL查询语句”，分别引入 raw_attendance、raw_attendance_type、raw_student_info 的全量字段。

  
  
  
  

4.3.2 排序与多表左连接（关联考勤记录与类型）

由于ETL平台的“记录集连接”组件要求两侧输入流必须基于关联键严格升序排列，因此必须在连接前置入排序步骤。

1. 拖入“排序记录”组件，命名为按照考勤类型和考勤任务类型排序，置于考勤记录组件后。获取字段后，仅保留 attendance_type_id 和 attendance_task_order_id 作为排序键。

   

2. 拖入“记录集连接”组件。第一个Transform选择排序后的考勤记录流，第二个Transform直接选择默认已排序的考勤类型输入流。

3. 连接类型选择 LEFT OUTER，连接字段两侧均保留 attendance_type_id 与 attendance_task_order_id。

   

4.3.3 行为标签衍生（JavaScript清洗）

1. 拖入“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;
       }
   }

   ​

2. 在脚本窗口中编写逻辑脚本，利用 .includes() 函数对 attendance_type_name 和 attendance_task_name 进行关键词检索，动态衍生出二进制行为标记（isLate、isEarly、isLeave、isNoUniform）。

3. 点击下方的“获取变量”按钮，系统将自动解析脚本并生成输出字段。

   

4. 点击“测试脚本”查看预览数据，确保标记位（0或1）准确无误。

   

4.3.4 多维度分组聚合统计

1. 拖入“分组”组件，连接JavaScript组件的输出流。

   

2. 双击配置“分组字段”，选定高维聚合特征：stu_id、stu_name、cla_id、cla_name。

   

3. 配置“聚合字段”表格，对衍生标记进行累加：

• late_count →​ 类型：SUM ​→ 源字段：isLate

• early_leave_count →​ 类型：SUM ​ → 源字段：isEarly

• leave_count ​→ 类型：SUM →​ 源字段：isLeave

• uniform_violate_count → ​类型：SUM ​→ 源字段：isNoUniform

• 

4.3.5 关联学生静态基本信息

为了将聚合后的考勤指标与学生自身的静态画像（如性别、住校状态等）进行拼接，需再次进行多表关联。

1. 在学生信息表输入组件后拖入“排序记录”组件，重命名为按照学生编号进行排序，排序键设为 stu_id。

   
   

2. 在前级分组组件后同样对接一个“排序记录”组件，命名为考勤数据按学号排序，排序键设为 stu_id。

   

3. 拖入新的“记录集连接”组件，将上述两个排序后的数据流进行 LEFT OUTER 左外连接，关联键统一指定为 stu_id。

   

4.3.6 字段筛选与冗余移除

多表关联后会产生大量重复及无用的级联字段。拖入“字段选择”组件，命名为移除冗余字段。创建“记录集连接 1”组件到字段选择组件的连接线，切换至“移除”页签，获取全量字段后，精确保留以下12个核心轻度汇总层字段，将其余冗余字段一律剔除：

stu_id、stu_name、cla_id、cla_name、late_count、early_leave_count、leave_count、uniform_violate_count、stu_sex、born_date、policy、live_on_campus

字段选择中删除之前提到的字段

完成后右键移除冗余字段选择组件，点击显示输出字段，比对结果。

4.3.7 异常空值替换处理

为防止源数据缺失导致最终报表出现异常空单元格，拖入“替换NULL值”组件。创建“移除冗余字段”字段选择组件到“替换NULL值”组件的连线，连线类型选择“主输出步骤”。双击勾选“选择字段”，手动插入4行记录，针对 stu_sex、born_date、policy、live_on_campus 字段，指定当其值为 NULL 时，统一替换为系统标准文本“未知”。

[

4.3.8 学生画像属性标准化与缺失维度衍生

4.3.8.1 住校状态标准化文本映射

1. 拖入“值映射”组件，并创建替换NULL值组件到值映射组件的连线，并选择“主输出步骤”，重命名为住校状态映射。

2. 设定使用的字段名为 live_on_campus，不匹配时的默认值设为 否。

3. 在字段值映射表内插入映射规则：源值 0 ​→ 目标值 否；源值 1 →​ 目标值 是。

4.3.8.2 JavaScript动态衍生：年级字段提取

1. 拖入“JavaScript代码”组件，创建住校状态映射组件到JavaScript代码组件的连线，命名为从班级提取年级。

2. 插入脚本

   
   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='未知'
   }

3. 在下方表格中插入该变量元数据，指定其名称为 gra_name，类型为 String，替换“字段名”或“重命名”值选择“否”。

   

   ​

4.3.8.3 JavaScript动态衍生：校区类型判定

参考上述步骤，新建一个“JavaScript代码”组件并命名为校区类型判定，创建从班级提取年级组件到JavaScript代码组件的连线。通过检查 cla_name 是否以 白- 或 东- 开头（.startsWith()），动态衍生出校区分类特征变量 class_campus_type（String类型），从而精准划分新老校区。

脚本

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='未知'
}

4.3.9 结果落地入库

1. 添加表输出组件，并创建“校区类型判定”“JavaScript代码组件到表输出组件的连线。

2. 选择数据库连接为团队私有数据库，目标表指定为最终汇总表 student_attendance_stats。

3. 关键安全配置：勾选“裁剪表”（确保每次重跑流时先清空历史数据，防止主键冲突与数据重叠）；勾选“指定数据库字段”。

   

4. 切换至“数据库字段”页签，点击“获取字段”，严格建立ETL流动字段与底层MySQL表结构的映射关系。

   
   

---

4.4 工作流执行与数据探查

1. 配置完成后，点击平台工具栏的“执行”（运行）按钮，在弹出的运行配置窗口中保持默认参数，点击“启动”。

2. 观察画布下方的实时执行日志，当所有组件右上角均出现绿色对勾标记且日志输出结束时，代表转换流全线运行成功。

   

3. 数据探查：切换至“元数据”页签，右键点击团队私有数据库选择“加载元数据”。随后进入平台的“数据探查”页面，双击打开 student_attendance_stats 表，切换至“查询”页签。验证衍生字段（年级、校区、住校状态）映射无误，各类考勤行为（迟到、早退、请假、校服违纪）的计数聚合结果逻辑严密，符合业务预期。

   

5 实验总结与反思

5.1 核心收获

• 深入掌握了零代码 ETL 平台中设计思想，实现了“事实表（打卡明细）+ 维度表（类型码表）+ 属性表（学生静态信息）”的高效集成。

• 深刻体会到“排序记录”组件在“合并连接”前的决定性作用。若两端未严格排序，将直接导致多表关联数据错漏或流运行中断。

5.2 常见问题与优化策略

• 在表输出组件中，数据库字段的表字段与流字段一定要一一对应，否则就会报错。