设计一个教育数据仓库的维度模型，用于分析用户学习效果（如知识点掌握度、课程完课率）。请说明事实表与维度表的设计，以及如何从原始数据（如答题记录、观看日志）中抽取指标。
答案
1) 【一句话结论】
为分析用户学习效果（知识点掌握度、课程完课率），采用星型模型构建教育数据仓库，区分答题与观看行为设计事实表（fact_learning_answer、fact_learning_watch），通过增量ETL从原始数据抽取指标，维度表（用户、课程、知识点、行为类型、时间）支持多维度分析。
2) 【原理/概念讲解】
数据仓库的星型模型以事实表为核心，存储业务事件的核心度量（数值型指标）和代理键，维度表存储描述性信息（字符型字段）并关联事实表。教育场景下，学习行为分为答题和观看，需分别设计事实表以处理数据差异。类比：超市销售事实表记录每一笔交易（事实），商品、用户、时间等维度表描述交易内容（维度），事实表通过事件ID唯一标识，维度表通过外键关联，支持按用户、课程、时间等维度分析学习效果。关键点：事实表聚焦业务事件的核心度量（如答题正确率、观看时长），维度表提供分析维度（如用户属性、课程信息、时间周期）。
3) 【对比与适用场景】
| 类别 | 事实表（答题行为） | 事实表（观看行为） | 维度表 |
|---|---|---|---|
| 定义 | 记录用户答题行为的核心数据 | 记录用户观看行为的核心数据 | 存储用户、课程、知识点等描述性信息 |
| 特性 | 度量：is_correct（正确率）、attempt_count（尝试次数）、completion_time（完成时间） | 度量：watch_duration（观看时长，秒）、completion_rate（观看比例）、start_time（开始时间） | 外键关联事实表，字段：user_id、course_id、knowledge_id等 |
| 使用场景 | 计算知识点掌握度（正确率/尝试次数） | 计算课程完课率（观看时长/课程总时长） | 提供分析维度（如按用户、课程、时间分析） |
| 注意点 | 需包含尝试次数，避免仅看正确率误判熟练度 | 需区分观看时长与完成率，避免时长长但未完成 | 动态字段（如学习习惯）需定期更新 |
4) 【示例】
- 事实表：fact_learning_answer
字段：answer_id（主键，代理键，自增），user_id（外键，关联dim_user），course_id（外键，关联dim_course），knowledge_id（外键，关联dim_knowledge），question_id（外键，关联dim_question），is_correct（度量，布尔型，1为正确），attempt_count（度量，整型，尝试次数），completion_time（度量，时间戳，完成时间），start_time（度量，时间戳，开始时间）。 - 事实表：fact_learning_watch
字段：watch_id（主键，代理键），user_id（外键，关联dim_user），course_id（外键，关联dim_course），knowledge_id（外键，关联dim_knowledge），watch_duration（度量，整型，观看时长，秒），completion_rate（度量，浮点型，观看比例，如0.8表示80%完成），start_time（度量，时间戳，开始时间），end_time（度量，时间戳，结束时间）。 - 维度表：
- dim_user：user_id（主键），user_name（用户姓名，可选），grade（年级），class（班级），study_habit（学习习惯，动态更新，如“勤奋型”“拖延型”）。
- dim_course：course_id（主键），course_name（课程名称），teacher_id（外键，关联教师维度表），duration（课程总时长，分钟），difficulty（课程难度，如“初级”“高级”）。
- dim_knowledge：knowledge_id（主键），knowledge_name（知识点名称），course_id（外键，关联课程），difficulty（知识点难度）。
- dim_behavior_type：behavior_type_id（主键），behavior_type（行为类型，如“答题”“观看”）。
- dim_time：event_time（外键，关联事实表时间字段），day（日期，如2024-01-15），week（星期，如周一），month（月份，如1月），year（年份，如2024）。
5) 【面试口播版答案】
面试官您好，我会设计一个星型模型的教育数据仓库。核心思路是区分答题和观看两种学习行为，分别设计事实表：fact_learning_answer记录答题行为，包含正确率、尝试次数等关键度量；fact_learning_watch记录观看行为，包含观看时长、完成率等指标。维度表则包括用户（用户属性）、课程（课程信息）、知识点（知识点内容）、行为类型（区分答题/观看）、时间（时间维度），通过增量ETL从原始答题记录和观看日志中抽取数据。比如知识点掌握度计算为正确答题数除以尝试答题数，课程完课率为完成课程总时长除以课程总时长。这样能支持按用户、课程、时间等维度分析学习效果，高效计算知识点掌握度和课程完课率，满足业务分析需求。
6) 【追问清单】
- 问题1：如何动态更新用户学习习惯？
回答要点：通过定期（如每周）更新用户维度表中的“学习习惯”字段，结合近期7天的学习行为数据（如学习时长、正确率、答题频率）重新计算，或设计“学习习惯”事实表记录行为模式变化，确保分析维度实时反映用户状态。 - 问题2：课程知识点更新后如何同步维度表？
回答要点：知识点更新时，先更新知识点维度表中的内容、难度等字段，同步更新关联的课程维度表（如课程包含的知识点列表），确保数据一致性，避免分析时出现旧知识点数据。 - 问题3：数据抽取的延迟时间如何控制？
回答要点：根据业务需求设置ETL频率（如每小时增量抽取），对实时性指标（如答题正确率）采用高频抽取，降低延迟；对非实时指标（如完课率）采用每日全量抽取，平衡实时性与数据准确性。 - 问题4：如何处理原始数据中的无效值（如空答题ID、负观看时长）？
回答要点：在ETL前进行数据清洗，过滤无效数据（如空答题ID、负观看时长），使用Spark或Flink处理日志，检查字段有效性，确保分析结果准确。
7) 【常见坑/雷区】
- 事实表遗漏关键度量：如仅记录正确率，未包含尝试次数，导致无法区分用户熟练度（如用户答题正确但尝试次数少，可能只是熟练，而非掌握）。
- 未区分学习行为类型：将答题与观看日志合并，导致完课率计算复杂（观看行为和答题行为的度量不同，合并后无法准确计算）。
- 时间维度粒度不足：仅到天，无法分析特定时间段的学习效率（如上午/下午正确率差异，需更细粒度时间维度，如小时）。
- 数据质量检查缺失：原始数据中无效值（如空答题ID）未处理，影响分析准确性（如计算正确率时除以0）。
- 维度表设计过细：如用户维度表包含非分析字段（如家庭住址），增加存储开销，且可能涉及隐私风险。