2025年大数据分析师职业技能测试卷：数据仓库设计与实现试题解析

2025年大数据分析师职业技能测试卷：数据仓库设计与实现试题解析考试时间：______分钟总分：______分姓名：______一、关系数据库设计要求：根据以下业务场景，完成实体关系图（ERD）的设计，并给出相应的表结构设计。业务场景：某在线教育平台需要记录用户的学习进度、课程评价、购买课程等信息。1.确定实体：用户（User）、课程（Course）、订单（Order）、评价（Evaluation）、学习进度（LearningProgress）。2.确定属性：用户（User）-用户ID（UserID）、姓名（Name）、年龄（Age）、性别（Gender）、邮箱（Email）；课程（Course）-课程ID（CourseID）、课程名称（CourseName）、课程描述（Description）、课程时长（Duration）、课程难度（Difficulty）；订单（Order）-订单ID（OrderID）、用户ID（UserID）、课程ID（CourseID）、购买时间（PurchaseTime）、购买价格（Price）；评价（Evaluation）-评价ID（EvaluationID）、用户ID（UserID）、课程ID（CourseID）、评价内容（Content）、评价时间（EvaluationTime）；学习进度（LearningProgress）-学习进度ID（LearningProgressID）、用户ID（UserID）、课程ID（CourseID）、完成度（Completion）、开始时间（StartTime）、结束时间（EndTime）。3.确定关系：用户（User）与课程（Course）多对多关系，通过订单（Order）关联；课程（Course）与评价（Evaluation）一对多关系；用户（User）与评价（Evaluation）一对多关系；用户（User）与学习进度（LearningProgress）一对多关系。二、SQL查询要求：根据以下SQL查询语句，完成相应的操作，并给出查询结果。1.查询所有课程的名称和课程难度。SELECTCourseName,DifficultyFROMCourse;2.查询所有用户的姓名、年龄和性别。SELECTName,Age,GenderFROMUser;3.查询所有订单的订单ID、用户ID、课程ID和购买价格。SELECTOrderID,UserID,CourseID,PriceFROMOrder;4.查询所有评价的评论内容、用户ID和课程ID。SELECTContent,UserID,CourseIDFROMEvaluation;5.查询所有学习进度的完成度、用户ID和课程ID。SELECTCompletion,UserID,CourseIDFROMLearningProgress;6.查询所有购买课程为“Java基础”的用户ID和姓名。SELECTu.UserID,u.NameFROMUseruJOINOrderoONu.UserID=o.UserIDJOINCoursecONo.CourseID=c.CourseIDWHEREc.CourseName='Java基础';7.查询所有完成度为100%的课程名称。SELECTc.CourseNameFROMCoursecJOINLearningProgresslpONc.CourseID=lp.CourseIDWHERElp.Completion=100;8.查询所有年龄大于25岁的用户姓名和性别。SELECTName,GenderFROMUserWHEREAge>25;9.查询所有购买课程价格为50元及以上的用户ID和姓名。SELECTu.UserID,u.NameFROMUseruJOINOrderoONu.UserID=o.UserIDJOINCoursecONo.CourseID=c.CourseIDWHEREc.Price>=50;10.查询所有在2022年1月1日之后购买课程的用户ID和姓名。SELECTu.UserID,u.NameFROMUseruJOINOrderoONu.UserID=o.UserIDWHEREo.PurchaseTime>'2022-01-01';四、数据仓库模型设计要求：根据以下业务需求，设计一个数据仓库模型，包括事实表和维度表，并简要说明每个表的设计思路。1.事实表：-事实表名称：销售事实表（SalesFact）-事实表设计：-销售ID（SalesID）：主键，唯一标识一条销售记录。-产品ID（ProductID）：外键，关联产品维度表。-客户ID（CustomerID）：外键，关联客户维度表。-销售金额（SalesAmount）：销售金额，数值类型。-销售数量（SalesQuantity）：销售数量，数值类型。-销售日期（SalesDate）：销售日期，日期类型。2.维度表：-产品维度表（ProductDim）：-产品ID（ProductID）：主键，唯一标识一个产品。-产品名称（ProductName）：产品名称，字符串类型。-产品类别（ProductCategory）：产品类别，字符串类型。-产品描述（ProductDescription）：产品描述，字符串类型。-客户维度表（CustomerDim）：-客户ID（CustomerID）：主键，唯一标识一个客户。-客户名称（CustomerName）：客户名称，字符串类型。-客户地址（CustomerAddress）：客户地址，字符串类型。-客户电话（CustomerPhone）：客户电话，字符串类型。五、数据仓库ETL过程设计要求：设计一个数据仓库的ETL过程，包括数据抽取、转换和加载三个阶段，并简要说明每个阶段的具体操作。1.数据抽取阶段：-从源系统（如在线教育平台数据库）中抽取相关数据。-抽取数据包括：用户信息、课程信息、订单信息、评价信息和学习进度信息。-使用SQL查询语句或ETL工具从源数据库中抽取数据。2.数据转换阶段：-清洗数据，处理缺失值、异常值和重复值。-对数据进行格式转换，如日期格式统一、字符串规范化等。-根据数据仓库模型设计，对数据进行聚合、汇总和计算。-使用ETL工具或编程语言（如Python）进行数据转换操作。3.数据加载阶段：-将转换后的数据加载到数据仓库中。-使用ETL工具或SQL语句将数据加载到事实表和维度表中。-确保数据加载过程中的一致性和完整性。六、数据仓库查询优化要求：针对以下查询需求，提出相应的优化策略，并简要说明优化原因。1.查询需求：查询某个时间段内，某个客户的购买课程数量和总消费金额。优化策略：-使用索引：在销售日期、客户ID和产品ID上建立索引，提高查询效率。-分区表：根据销售日期对销售事实表进行分区，提高查询性能。-缓存：将常用查询结果缓存，减少数据库访问次数。2.查询需求：查询某个课程的所有评价内容和评价时间。优化策略：-使用索引：在课程ID上建立索引，提高查询效率。-查询优化：使用子查询或连接查询，避免全表扫描。-数据库优化：调整数据库参数，如查询缓存大小、查询超时时间等。本次试卷答案如下：一、关系数据库设计1.实体：用户（User）、课程（Course）、订单（Order）、评价（Evaluation）、学习进度（LearningProgress）。2.属性：-用户（User）：UserID（主键）、Name、Age、Gender、Email-课程（Course）：CourseID（主键）、CourseName、Description、Duration、Difficulty-订单（Order）：OrderID（主键）、UserID（外键）、CourseID（外键）、PurchaseTime、Price-评价（Evaluation）：EvaluationID（主键）、UserID（外键）、CourseID（外键）、Content、EvaluationTime-学习进度（LearningProgress）：LearningProgressID（主键）、UserID（外键）、CourseID（外键）、Completion、StartTime、EndTime3.关系：-用户与课程：多对多关系，通过订单关联-课程与评价：一对多关系-用户与评价：一对多关系-用户与学习进度：一对多关系二、SQL查询1.查询所有课程的名称和课程难度。解析思路：直接从Course表中选取CourseName和Difficulty字段。2.查询所有用户的姓名、年龄和性别。解析思路：直接从User表中选取Name、Age和Gender字段。3.查询所有订单的订单ID、用户ID、课程ID和购买价格。解析思路：直接从Order表中选取OrderID、UserID、CourseID和Price字段。4.查询所有评价的评论内容、用户ID和课程ID。解析思路：直接从Evaluation表中选取Content、UserID和CourseID字段。5.查询所有学习进度的完成度、用户ID和课程ID。解析思路：直接从LearningProgress表中选取Completion、UserID和CourseID字段。6.查询所有购买课程为“Java基础”的用户ID和姓名。解析思路：使用JOIN操作连接User、Order和Course表，通过CourseName筛选出“Java基础”课程，并选取UserID和Name字段。7.查询所有完成度为100%的课程名称。解析思路：使用JOIN操作连接Course和LearningProgress表，通过Completion筛选出完成度为100%的记录，并选取CourseName字段。8.查询所有年龄大于25岁的用户姓名和性别。解析思路：使用WHERE子句筛选出Age大于25岁的用户，并选取Name和Gender字段。9.查询所有购买课程价格为50元及以上的用户ID和姓名。解析思路：使用JOIN操作连接User、Order和Course表，通过Price筛选出价格为50元及以上的记录，并选取UserID和Name字段。10.查询所有在2022年1月1日之后购买课程的用户ID和姓名。解析思路：使用WHERE子句筛选出PurchaseTime大于'2022-01-01'的记录，并选取UserID和Name字段。四、数据仓库模型设计1.事实表：销售事实表（SalesFact）-销售ID（SalesID）：主键，唯一标识一条销售记录。-产品ID（ProductID）：外键，关联产品维度表。-客户ID（CustomerID）：外键，关联客户维度表。-销售金额（SalesAmount）：销售金额，数值类型。-销售数量（SalesQuantity）：销售数量，数值类型。-销售日期（SalesDate）：销售日期，日期类型。2.维度表：-产品维度表（ProductDim）：-产品ID（ProductID）：主键，唯一标识一个产品。-产品名称（ProductName）：产品名称，字符串类型。-产品类别（ProductCategory）：产品类别，字符串类型。-产品描述（ProductDescription）：产品描述，字符串类型。-客户维度表（CustomerDim）：-客户ID（CustomerID）：主键，唯一标识一个客户。-客户名称（CustomerName）：客户名称，字符串类型。-客户地址（CustomerAddress）：客户地址，字符串类型。-客户电话（CustomerPhone）：客户电话，字符串类型。五、数据仓库ETL过程设计1.数据抽取阶段：-从源系统（如在线教育平台数据库）中抽取相关数据。-抽取数据包括：用户信息、课程信息、订单信息、评价信息和学习进度信息。-使用SQL查询语句或ETL工具从源数据库中抽取数据。2.数据转换阶段：-清洗数据，处理缺失值、异常值和重复值。-对数据进行格式转换，如日期格式统一、字符串规范化等。-根据数据仓库模型设计，对数据进行聚合、汇总和计算。-使用ETL工具或编程语言（如Python）进行数据转换操作。3.数据加载阶段：-将转换后的数据加载到数据仓库中。-使用ETL工具或SQL语句将数据加载到事实表和维度表中。