2025年大數(shù)據(jù)分析師職業(yè)技能測試卷：數(shù)據(jù)倉庫設(shè)計與數(shù)據(jù)建模實戰(zhàn)試題匯編

2025年大數(shù)據(jù)分析師職業(yè)技能測試卷：數(shù)據(jù)倉庫設(shè)計與數(shù)據(jù)建模實戰(zhàn)試題匯編考試時間：______分鐘總分：______分姓名：______一、SQL基礎(chǔ)與數(shù)據(jù)查詢要求：請根據(jù)所給數(shù)據(jù)庫表結(jié)構(gòu)和SQL語句，完成以下查詢操作。1.假設(shè)有一個名為“Employees”的表，包含以下字段：EmployeeID（員工ID）、Name（姓名）、Department（部門）、Salary（工資）。請寫出查詢所有部門為“IT”的員工姓名和工資的SQL語句。2.在“Employees”表中，請查詢工資高于平均工資的員工姓名、部門和工資。3.在“Employees”表中，請查詢姓名中包含字母“a”的員工姓名和部門。4.在“Employees”表中，請查詢部門為“IT”且工資在5000到8000之間的員工姓名和工資。5.在“Employees”表中，請查詢工資排名前5的員工姓名、部門和工資。6.在“Employees”表中，請查詢部門為“IT”或“Finance”的員工姓名和部門。7.在“Employees”表中，請查詢工資排名最后5的員工姓名、部門和工資。8.在“Employees”表中，請查詢姓名中不包含字母“a”的員工姓名和部門。9.在“Employees”表中，請查詢工資在5000到8000之間的員工姓名、部門和工資。10.在“Employees”表中，請查詢部門為“IT”或“Finance”且工資高于平均工資的員工姓名和工資。二、數(shù)據(jù)倉庫設(shè)計與數(shù)據(jù)建模要求：請根據(jù)以下場景，完成數(shù)據(jù)倉庫設(shè)計與數(shù)據(jù)建模。1.假設(shè)某公司需要建立數(shù)據(jù)倉庫，存儲員工信息、部門信息和薪資信息。請設(shè)計一個數(shù)據(jù)倉庫模型，包括所需表及其字段。2.在數(shù)據(jù)倉庫模型中，請設(shè)計一個員工表，包含以下字段：EmployeeID（員工ID）、Name（姓名）、DepartmentID（部門ID）、Salary（工資）、HireDate（入職日期）。3.在數(shù)據(jù)倉庫模型中，請設(shè)計一個部門表，包含以下字段：DepartmentID（部門ID）、DepartmentName（部門名稱）。4.在數(shù)據(jù)倉庫模型中，請設(shè)計一個薪資表，包含以下字段：EmployeeID（員工ID）、Salary（工資）、SalaryDate（薪資日期）。5.在數(shù)據(jù)倉庫模型中，請設(shè)計一個員工薪資歷史表，包含以下字段：EmployeeID（員工ID）、Salary（工資）、SalaryDate（薪資日期）、SalaryChange（薪資變動）。6.在數(shù)據(jù)倉庫模型中，請設(shè)計一個員工部門歷史表，包含以下字段：EmployeeID（員工ID）、DepartmentID（部門ID）、DepartmentName（部門名稱）、ChangeDate（變動日期）。7.在數(shù)據(jù)倉庫模型中，請設(shè)計一個員工晉升歷史表，包含以下字段：EmployeeID（員工ID）、Position（職位）、ChangeDate（變動日期）。8.在數(shù)據(jù)倉庫模型中，請設(shè)計一個員工培訓(xùn)歷史表，包含以下字段：EmployeeID（員工ID）、TrainingName（培訓(xùn)名稱）、TrainingDate（培訓(xùn)日期）。9.在數(shù)據(jù)倉庫模型中，請設(shè)計一個員工績效歷史表，包含以下字段：EmployeeID（員工ID）、PerformanceRating（績效評級）、RatingDate（評級日期）。10.在數(shù)據(jù)倉庫模型中，請設(shè)計一個員工離職歷史表，包含以下字段：EmployeeID（員工ID）、LeaveDate（離職日期）、Reason（離職原因）。四、數(shù)據(jù)倉庫ETL過程要求：請根據(jù)以下場景，完成數(shù)據(jù)倉庫ETL過程的設(shè)計。1.設(shè)計一個ETL過程，用于從源系統(tǒng)（如ERP系統(tǒng)）抽取員工信息，包括EmployeeID、Name、DepartmentID、Salary和HireDate字段。2.設(shè)計一個ETL過程，用于清洗員工信息數(shù)據(jù)，包括去除重復(fù)記錄、修正錯誤數(shù)據(jù)、填補缺失值等。3.設(shè)計一個ETL過程，用于轉(zhuǎn)換員工信息數(shù)據(jù)，包括將日期格式統(tǒng)一、將薪資數(shù)據(jù)轉(zhuǎn)換為月度薪資等。4.設(shè)計一個ETL過程，用于加載清洗和轉(zhuǎn)換后的員工信息數(shù)據(jù)到數(shù)據(jù)倉庫的員工表中。5.設(shè)計一個ETL過程，用于從源系統(tǒng)抽取部門信息，包括DepartmentID和DepartmentName字段。6.設(shè)計一個ETL過程，用于從源系統(tǒng)抽取薪資信息，包括EmployeeID、Salary和SalaryDate字段。五、數(shù)據(jù)建模與維度設(shè)計要求：請根據(jù)以下場景，完成數(shù)據(jù)建模與維度設(shè)計。1.設(shè)計一個數(shù)據(jù)模型，用于存儲員工信息，包括EmployeeID、Name、DepartmentID、Salary和HireDate字段。2.設(shè)計一個數(shù)據(jù)模型，用于存儲部門信息，包括DepartmentID、DepartmentName和ManagerID字段。3.設(shè)計一個數(shù)據(jù)模型，用于存儲薪資信息，包括EmployeeID、Salary、SalaryDate和SalaryType字段。4.設(shè)計一個數(shù)據(jù)模型，用于存儲員工績效信息，包括EmployeeID、PerformanceRating和RatingDate字段。5.設(shè)計一個數(shù)據(jù)模型，用于存儲員工培訓(xùn)信息，包括EmployeeID、TrainingName和TrainingDate字段。6.設(shè)計一個數(shù)據(jù)模型，用于存儲員工離職信息，包括EmployeeID、LeaveDate和Reason字段。六、數(shù)據(jù)倉庫查詢與優(yōu)化要求：請根據(jù)以下場景，完成數(shù)據(jù)倉庫查詢與優(yōu)化。1.編寫一個SQL查詢，用于查詢所有員工的姓名、部門和薪資。2.編寫一個SQL查詢，用于查詢每個部門的平均薪資。3.編寫一個SQL查詢，用于查詢過去一年內(nèi)離職的員工姓名和離職原因。4.編寫一個SQL查詢，用于查詢所有參加過培訓(xùn)的員工姓名和培訓(xùn)名稱。5.編寫一個SQL查詢，用于查詢過去三個月內(nèi)薪資上漲的員工姓名和薪資變動。6.編寫一個SQL查詢，用于查詢每個部門的員工數(shù)量。本次試卷答案如下：一、SQL基礎(chǔ)與數(shù)據(jù)查詢1.SQL語句：```sqlSELECTName,SalaryFROMEmployeesWHEREDepartment='IT';```解析思路：使用SELECT語句選擇Name和Salary字段，F(xiàn)ROM子句指定數(shù)據(jù)表為Employees，WHERE子句篩選部門為'IT'的記錄。2.SQL語句：```sqlSELECTName,Department,SalaryFROMEmployeesWHERESalary>(SELECTAVG(Salary)FROMEmployees);```解析思路：使用子查詢計算平均工資，然后在WHERE子句中比較每個員工的工資是否高于平均工資。3.SQL語句：```sqlSELECTName,DepartmentFROMEmployeesWHERENameLIKE'%a%';```解析思路：使用LIKE操作符和通配符'a%'來匹配包含字母'a'的姓名。4.SQL語句：```sqlSELECTName,SalaryFROMEmployeesWHEREDepartment='IT'ANDSalaryBETWEEN5000AND8000;```解析思路：使用AND操作符同時篩選部門和薪資范圍。5.SQL語句：```sqlSELECTName,Department,SalaryFROMEmployeesORDERBYSalaryDESCLIMIT5;```解析思路：使用ORDERBY子句對薪資進行降序排序，并使用LIMIT限制結(jié)果為前5條。6.SQL語句：```sqlSELECTName,DepartmentFROMEmployeesWHEREDepartment='IT'ORDepartment='Finance';```解析思路：使用OR操作符連接兩個條件，查詢部門為'IT'或'Finance'的員工。7.SQL語句：```sqlSELECTName,Department,SalaryFROMEmployeesORDERBYSalaryASCLIMIT5OFFSET5;```解析思路：使用OFFSET跳過前5條記錄，查詢薪資排名最后5的員工。8.SQL語句：```sqlSELECTName,DepartmentFROMEmployeesWHERENameNOTLIKE'%a%';```解析思路：使用NOTLIKE操作符和通配符'a%'來匹配不包含字母'a'的姓名。9.SQL語句：```sqlSELECTName,Department,SalaryFROMEmployeesWHERESalaryBETWEEN5000AND8000;```解析思路：與第4題類似，篩選薪資在5000到8000之間的員工。10.SQL語句：```sqlSELECTName,Department,SalaryFROMEmployeesWHEREDepartment='IT'ORDepartment='Finance'ANDSalary>(SELECTAVG(Salary)FROMEmployees);```解析思路：結(jié)合多個條件，查詢部門為'IT'或'Finance'且工資高于平均工資的員工。二、數(shù)據(jù)倉庫設(shè)計與數(shù)據(jù)建模1.數(shù)據(jù)倉庫模型設(shè)計（示例）：-Employees(EmployeeID,Name,DepartmentID,Salary,HireDate)-Departments(DepartmentID,DepartmentName,ManagerID)-Salaries(EmployeeID,Salary,SalaryDate,SalaryType)2.員工表設(shè)計（示例）：-EmployeeID(主鍵)-Name-DepartmentID(外鍵)-Salary-HireDate3.部門表設(shè)計（示例）：-DepartmentID(主鍵)-DepartmentName-ManagerID4.薪資表設(shè)計（示例）：-EmployeeID(外鍵)-Salary-SalaryDate-SalaryType5.員工薪資歷史表設(shè)計（示例）：-EmployeeID(外鍵)-Salary-SalaryDate-SalaryChange6.員工部門歷史表設(shè)計（示例）：-EmployeeID(外鍵)-DepartmentID(外鍵)-DepartmentName-ChangeDate7.員工晉升歷史表設(shè)計（示例）：-EmployeeID(外鍵)-Position-ChangeDate8.員工培訓(xùn)歷史表設(shè)計（示例）：-EmployeeID(外鍵)-TrainingName-TrainingDate9.員工績效歷史表設(shè)計（示例）：-EmployeeID(外鍵)-PerformanceRating-RatingDate10.員工離職歷史表設(shè)計（示例）：-EmployeeID(外鍵)-LeaveDate-Reason三、數(shù)據(jù)倉庫ETL過程1.ETL過程設(shè)計（示例）：-數(shù)據(jù)抽取：使用ETL工具從ERP系統(tǒng)抽取員工信息。-數(shù)據(jù)清洗：使用ETL工具去除重復(fù)記錄，修正錯誤數(shù)據(jù)，填補缺失值。-數(shù)據(jù)轉(zhuǎn)換：使用ETL工具統(tǒng)一日期格式，轉(zhuǎn)換薪資數(shù)據(jù)為月度薪資。-數(shù)據(jù)加載：使用ETL工具將清洗和轉(zhuǎn)換后的員工信息數(shù)據(jù)加載到數(shù)據(jù)倉庫的員工表中。2.ETL過程設(shè)計（示例）：-數(shù)據(jù)抽?。菏褂肊TL工具從ERP系統(tǒng)抽取員工信息。-數(shù)據(jù)清洗：使用ETL工具去除重復(fù)記錄，修正錯誤數(shù)據(jù)，填補缺失值。-數(shù)據(jù)轉(zhuǎn)換：使用ETL工具統(tǒng)一日期格式，轉(zhuǎn)換薪資數(shù)據(jù)為月度薪資。-數(shù)據(jù)加載：使用ETL工具將清洗和轉(zhuǎn)換后的員工信息數(shù)據(jù)加載到數(shù)據(jù)倉庫的員工表中。3.ETL過程設(shè)計（示例）：-數(shù)據(jù)抽?。菏褂肊TL工具從ERP系統(tǒng)抽取部門信息。-數(shù)據(jù)清洗：使用ETL工具去除重復(fù)記錄，修正錯誤數(shù)據(jù)，填補缺失值。-數(shù)據(jù)轉(zhuǎn)換：使用ETL工具統(tǒng)一日期格式，轉(zhuǎn)換薪資數(shù)據(jù)為月度薪資。-數(shù)據(jù)加載：使用ETL工具將清洗和轉(zhuǎn)換后的部門信息數(shù)據(jù)加載到數(shù)據(jù)倉庫的部門表中。4.ETL過程設(shè)計（示例）：-數(shù)據(jù)抽?。菏褂肊TL工具從ERP系統(tǒng)抽取薪資信息。-數(shù)據(jù)清洗：使用ETL工具去除重復(fù)記錄，修正錯誤數(shù)據(jù)，填補缺失值。-數(shù)據(jù)轉(zhuǎn)換：使用ETL工具統(tǒng)一日期格式，轉(zhuǎn)換薪資數(shù)據(jù)為月度薪資。-數(shù)據(jù)加載：使用ETL工具將清洗和轉(zhuǎn)換后的薪資信息數(shù)據(jù)加載到數(shù)據(jù)倉庫的薪資表中。四、數(shù)據(jù)建模與維度設(shè)計1.數(shù)據(jù)模型設(shè)計（示例）：-Employees(EmployeeID,Name,DepartmentID,Salary,HireDate)-Departments(DepartmentID,DepartmentName,ManagerID)2.部門表設(shè)計（示例）：-DepartmentID(主鍵)-DepartmentName-ManagerID3.薪資表設(shè)計（示例）：-EmployeeID(外鍵)-Salary-SalaryDate-SalaryType4.員工績效表設(shè)計（示例）：-EmployeeID(外鍵)-PerformanceRating-RatingDate5.員工培訓(xùn)表設(shè)計（示例）：-EmployeeID(外鍵)-TrainingName-TrainingDate6.員工離職表設(shè)計（示例）：-EmployeeID(外鍵)-LeaveDate-Reason五、數(shù)據(jù)倉庫查詢與優(yōu)化1.SQL查詢：```sqlSELECTName,Department,SalaryFROMEmployees;```解析思路：簡單的SELECT查詢，選擇員工姓名、部門和薪資。2.SQL查詢：```sqlSELECTDepartment,AVG(Salary)ASAverageSalaryFROMEmployeesGROUPBYDepartment;```解析思路：使用GROUPBY子句