2025年大數(shù)據(jù)分析師職業(yè)技能測(cè)試卷：數(shù)據(jù)倉(cāng)庫(kù)設(shè)計(jì)與實(shí)現(xiàn)試題解析

2025年大數(shù)據(jù)分析師職業(yè)技能測(cè)試卷：數(shù)據(jù)倉(cāng)庫(kù)設(shè)計(jì)與實(shí)現(xiàn)試題解析考試時(shí)間：______分鐘總分：______分姓名：______一、SQL語(yǔ)句編寫(xiě)與應(yīng)用要求：請(qǐng)根據(jù)以下場(chǎng)景，編寫(xiě)相應(yīng)的SQL語(yǔ)句，并解釋其作用。1.假設(shè)有一個(gè)名為“Employees”的表，包含以下字段：EmployeeID（員工ID）、Name（姓名）、DepartmentID（部門(mén)ID）、Salary（薪資）。請(qǐng)編寫(xiě)SQL語(yǔ)句，查詢所有薪資大于10000的員工姓名和部門(mén)ID。2.假設(shè)有一個(gè)名為“Orders”的表，包含以下字段：OrderID（訂單ID）、CustomerID（客戶ID）、OrderDate（訂單日期）、TotalAmount（訂單總額）。請(qǐng)編寫(xiě)SQL語(yǔ)句，查詢所有訂單日期在2022年1月1日到2022年12月31日之間的訂單ID和客戶ID。3.假設(shè)有一個(gè)名為“Products”的表，包含以下字段：ProductID（產(chǎn)品ID）、ProductName（產(chǎn)品名稱）、CategoryID（類別ID）、Price（價(jià)格）。請(qǐng)編寫(xiě)SQL語(yǔ)句，查詢所有類別ID為1的產(chǎn)品名稱和價(jià)格。4.假設(shè)有一個(gè)名為“Customers”的表，包含以下字段：CustomerID（客戶ID）、CustomerName（客戶姓名）、Email（郵箱）、Phone（電話）。請(qǐng)編寫(xiě)SQL語(yǔ)句，查詢所有郵箱中包含“@”的客戶姓名和郵箱。5.假設(shè)有一個(gè)名為“Departments”的表，包含以下字段：DepartmentID（部門(mén)ID）、DepartmentName（部門(mén)名稱）、ManagerID（經(jīng)理ID）。請(qǐng)編寫(xiě)SQL語(yǔ)句，查詢所有部門(mén)名稱為“Sales”的部門(mén)ID和經(jīng)理ID。6.假設(shè)有一個(gè)名為“OrderDetails”的表，包含以下字段：OrderDetailID（訂單明細(xì)ID）、OrderID（訂單ID）、ProductID（產(chǎn)品ID）、Quantity（數(shù)量）。請(qǐng)編寫(xiě)SQL語(yǔ)句，查詢所有訂單ID為1的訂單明細(xì)中的產(chǎn)品名稱和數(shù)量。7.假設(shè)有一個(gè)名為“Employees”的表，包含以下字段：EmployeeID（員工ID）、Name（姓名）、DepartmentID（部門(mén)ID）、Salary（薪資）。請(qǐng)編寫(xiě)SQL語(yǔ)句，查詢所有薪資在5000到10000之間的員工姓名和薪資。8.假設(shè)有一個(gè)名為“Orders”的表，包含以下字段：OrderID（訂單ID）、CustomerID（客戶ID）、OrderDate（訂單日期）、TotalAmount（訂單總額）。請(qǐng)編寫(xiě)SQL語(yǔ)句，查詢所有訂單總額大于1000的訂單ID和客戶ID。9.假設(shè)有一個(gè)名為“Products”的表，包含以下字段：ProductID（產(chǎn)品ID）、ProductName（產(chǎn)品名稱）、CategoryID（類別ID）、Price（價(jià)格）。請(qǐng)編寫(xiě)SQL語(yǔ)句，查詢所有類別ID為2的產(chǎn)品名稱和價(jià)格。10.假設(shè)有一個(gè)名為“Customers”的表，包含以下字段：CustomerID（客戶ID）、CustomerName（客戶姓名）、Email（郵箱）、Phone（電話）。請(qǐng)編寫(xiě)SQL語(yǔ)句，查詢所有電話號(hào)碼以“1”開(kāi)頭的客戶姓名和電話。二、數(shù)據(jù)倉(cāng)庫(kù)設(shè)計(jì)要求：請(qǐng)根據(jù)以下場(chǎng)景，設(shè)計(jì)一個(gè)數(shù)據(jù)倉(cāng)庫(kù)模型，并解釋其作用。1.假設(shè)有一個(gè)名為“Sales”的源系統(tǒng)，包含以下表：Customers（客戶信息）、Orders（訂單信息）、OrderDetails（訂單明細(xì)信息）、Products（產(chǎn)品信息）。請(qǐng)?jiān)O(shè)計(jì)一個(gè)數(shù)據(jù)倉(cāng)庫(kù)模型，包括事實(shí)表和維度表，并解釋其作用。2.假設(shè)有一個(gè)名為“HR”的源系統(tǒng)，包含以下表：Employees（員工信息）、Departments（部門(mén)信息）、Positions（職位信息）。請(qǐng)?jiān)O(shè)計(jì)一個(gè)數(shù)據(jù)倉(cāng)庫(kù)模型，包括事實(shí)表和維度表，并解釋其作用。3.假設(shè)有一個(gè)名為“Finance”的源系統(tǒng)，包含以下表：Invoices（發(fā)票信息）、Transactions（交易信息）、Accounts（賬戶信息）。請(qǐng)?jiān)O(shè)計(jì)一個(gè)數(shù)據(jù)倉(cāng)庫(kù)模型，包括事實(shí)表和維度表，并解釋其作用。4.假設(shè)有一個(gè)名為“Marketing”的源系統(tǒng)，包含以下表：Campaigns（營(yíng)銷(xiāo)活動(dòng)信息）、Respondents（參與人員信息）、Results（結(jié)果信息）。請(qǐng)?jiān)O(shè)計(jì)一個(gè)數(shù)據(jù)倉(cāng)庫(kù)模型，包括事實(shí)表和維度表，并解釋其作用。5.假設(shè)有一個(gè)名為“SupplyChain”的源系統(tǒng)，包含以下表：Suppliers（供應(yīng)商信息）、Purchases（采購(gòu)信息）、Inventory（庫(kù)存信息）。請(qǐng)?jiān)O(shè)計(jì)一個(gè)數(shù)據(jù)倉(cāng)庫(kù)模型，包括事實(shí)表和維度表，并解釋其作用。6.假設(shè)有一個(gè)名為“Education”的源系統(tǒng)，包含以下表：Students（學(xué)生信息）、Courses（課程信息）、Grades（成績(jī)信息）。請(qǐng)?jiān)O(shè)計(jì)一個(gè)數(shù)據(jù)倉(cāng)庫(kù)模型，包括事實(shí)表和維度表，并解釋其作用。7.假設(shè)有一個(gè)名為“Healthcare”的源系統(tǒng)，包含以下表：Patients（患者信息）、Appointments（預(yù)約信息）、Tests（檢查信息）。請(qǐng)?jiān)O(shè)計(jì)一個(gè)數(shù)據(jù)倉(cāng)庫(kù)模型，包括事實(shí)表和維度表，并解釋其作用。8.假設(shè)有一個(gè)名為“E-commerce”的源系統(tǒng)，包含以下表：Users（用戶信息）、Orders（訂單信息）、Products（產(chǎn)品信息）、Reviews（評(píng)論信息）。請(qǐng)?jiān)O(shè)計(jì)一個(gè)數(shù)據(jù)倉(cāng)庫(kù)模型，包括事實(shí)表和維度表，并解釋其作用。9.假設(shè)有一個(gè)名為“Transportation”的源系統(tǒng)，包含以下表：Vehicles（車(chē)輛信息）、Trips（行程信息）、Passengers（乘客信息）。請(qǐng)?jiān)O(shè)計(jì)一個(gè)數(shù)據(jù)倉(cāng)庫(kù)模型，包括事實(shí)表和維度表，并解釋其作用。10.假設(shè)有一個(gè)名為“RealEstate”的源系統(tǒng)，包含以下表：Properties（房產(chǎn)信息）、Transactions（交易信息）、Agents（經(jīng)紀(jì)人信息）。請(qǐng)?jiān)O(shè)計(jì)一個(gè)數(shù)據(jù)倉(cāng)庫(kù)模型，包括事實(shí)表和維度表，并解釋其作用。四、數(shù)據(jù)倉(cāng)庫(kù)建模與ETL過(guò)程要求：根據(jù)以下數(shù)據(jù)倉(cāng)庫(kù)設(shè)計(jì)和ETL過(guò)程的要求，完成以下任務(wù)。1.設(shè)計(jì)一個(gè)數(shù)據(jù)倉(cāng)庫(kù)模型，包含以下源系統(tǒng)表和所需的事實(shí)表及維度表：-源系統(tǒng)表：Orders（訂單）、Customers（客戶）、Products（產(chǎn)品）-事實(shí)表：SalesFacts-維度表：TimeDimension（時(shí)間）、CustomerDimension（客戶）、ProductDimension（產(chǎn)品）2.編寫(xiě)ETL過(guò)程中的數(shù)據(jù)清洗步驟，包括：-檢查并修正數(shù)據(jù)類型錯(cuò)誤-處理缺失值-檢測(cè)并刪除重復(fù)記錄-標(biāo)準(zhǔn)化數(shù)據(jù)格式3.編寫(xiě)ETL過(guò)程中的數(shù)據(jù)轉(zhuǎn)換步驟，包括：-將訂單日期從YYYY-MM-DD轉(zhuǎn)換為日期時(shí)間格式-計(jì)算每個(gè)訂單的折扣后金額-創(chuàng)建訂單日期的年、季度、月份維度4.編寫(xiě)ETL過(guò)程中的數(shù)據(jù)加載步驟，包括：-將清洗和轉(zhuǎn)換后的數(shù)據(jù)加載到數(shù)據(jù)倉(cāng)庫(kù)的SalesFacts事實(shí)表中-確保數(shù)據(jù)的完整性-創(chuàng)建索引以優(yōu)化查詢性能5.設(shè)計(jì)一個(gè)查詢，用于分析客戶的購(gòu)買(mǎi)行為，包括以下字段：-客戶姓名-購(gòu)買(mǎi)的產(chǎn)品類別-購(gòu)買(mǎi)次數(shù)-平均訂單金額-最后一次購(gòu)買(mǎi)日期6.編寫(xiě)一個(gè)SQL查詢，用于計(jì)算每個(gè)產(chǎn)品類別在過(guò)去一年的總銷(xiāo)售額和訂單數(shù)量。查詢結(jié)果應(yīng)包含以下字段：-產(chǎn)品類別ID-產(chǎn)品類別名稱-總銷(xiāo)售額-訂單數(shù)量本次試卷答案如下：一、SQL語(yǔ)句編寫(xiě)與應(yīng)用1.SQL語(yǔ)句：```sqlSELECTName,DepartmentIDFROMEmployeesWHERESalary>10000;```解析思路：通過(guò)WHERE子句篩選出薪資大于10000的員工，SELECT子句指定返回員工的姓名和部門(mén)ID。2.SQL語(yǔ)句：```sqlSELECTOrderID,CustomerIDFROMOrdersWHEREOrderDateBETWEEN'2022-01-01'AND'2022-12-31';```解析思路：使用BETWEEN運(yùn)算符篩選出訂單日期在指定日期范圍內(nèi)的記錄，SELECT子句指定返回訂單ID和客戶ID。3.SQL語(yǔ)句：```sqlSELECTProductName,PriceFROMProductsWHERECategoryID=1;```解析思路：通過(guò)WHERE子句篩選出類別ID為1的產(chǎn)品，SELECT子句指定返回產(chǎn)品的名稱和價(jià)格。4.SQL語(yǔ)句：```sqlSELECTCustomerName,EmailFROMCustomersWHEREEmailLIKE'%@%';```解析思路：使用LIKE運(yùn)算符和通配符%篩選出郵箱中包含的客戶，SELECT子句指定返回客戶的姓名和郵箱。5.SQL語(yǔ)句：```sqlSELECTDepartmentID,ManagerIDFROMDepartmentsWHEREDepartmentName='Sales';```解析思路：通過(guò)WHERE子句篩選出部門(mén)名稱為Sales的部門(mén)，SELECT子句指定返回部門(mén)的ID和經(jīng)理ID。6.SQL語(yǔ)句：```sqlSELECTProductName,QuantityFROMOrderDetailsWHEREOrderID=1;```解析思路：通過(guò)WHERE子句篩選出訂單ID為1的訂單明細(xì)，SELECT子句指定返回產(chǎn)品的名稱和數(shù)量。7.SQL語(yǔ)句：```sqlSELECTName,SalaryFROMEmployeesWHERESalaryBETWEEN5000AND10000;```解析思路：通過(guò)WHERE子句篩選出薪資在5000到10000之間的員工，SELECT子句指定返回員工的姓名和薪資。8.SQL語(yǔ)句：```sqlSELECTOrderID,CustomerIDFROMOrdersWHERETotalAmount>1000;```解析思路：通過(guò)WHERE子句篩選出訂單總額大于1000的訂單，SELECT子句指定返回訂單ID和客戶ID。9.SQL語(yǔ)句：```sqlSELECTProductName,PriceFROMProductsWHERECategoryID=2;```解析思路：通過(guò)WHERE子句篩選出類別ID為2的產(chǎn)品，SELECT子句指定返回產(chǎn)品的名稱和價(jià)格。10.SQL語(yǔ)句：```sqlSELECTCustomerName,PhoneFROMCustomersWHEREPhoneLIKE'1%';```解析思路：使用LIKE運(yùn)算符和通配符1%篩選出電話號(hào)碼以1開(kāi)頭的客戶，SELECT子句指定返回客戶的姓名和電話。二、數(shù)據(jù)倉(cāng)庫(kù)設(shè)計(jì)1.數(shù)據(jù)倉(cāng)庫(kù)模型設(shè)計(jì)：-事實(shí)表：SalesFacts-OrderID-CustomerID-ProductID-OrderDate-TotalAmount-維度表：-TimeDimension-OrderDate-Year-Quarter-Month-CustomerDimension-CustomerID-CustomerName-Email-Phone-ProductDimension-ProductID-ProductName-CategoryID-Price2.數(shù)據(jù)清洗步驟：-檢查數(shù)據(jù)類型：確保每個(gè)字段的值符合預(yù)期數(shù)據(jù)類型，如日期格式正確，數(shù)值字段無(wú)非法字符等。-處理缺失值：對(duì)于缺失值，可以選擇填充默認(rèn)值、刪除記錄或使用其他統(tǒng)計(jì)方法填充。-刪除重復(fù)記錄：識(shí)別并刪除重復(fù)的記錄，確保數(shù)據(jù)唯一性。-標(biāo)準(zhǔn)化數(shù)據(jù)格式：對(duì)于日期、貨幣等格式不一致的數(shù)據(jù)，進(jìn)行統(tǒng)一格式轉(zhuǎn)換。3.數(shù)據(jù)轉(zhuǎn)換步驟：-訂單日期轉(zhuǎn)換：將訂單日期從YYYY-MM-DD格式轉(zhuǎn)換為YYYY-MM-DDHH:MM:SS格式。-訂單金額計(jì)算：計(jì)算每個(gè)訂單的折扣后金額，可能涉及價(jià)格乘以折扣系數(shù)等操作。-創(chuàng)建維度數(shù)據(jù)：根據(jù)訂單日期創(chuàng)建年、季度、月份等時(shí)間維度數(shù)據(jù)。4.數(shù)據(jù)加載步驟：-加載事實(shí)表數(shù)據(jù)：將清洗和轉(zhuǎn)換后的數(shù)據(jù)加載到SalesFacts事實(shí)表中。-確保數(shù)據(jù)完整性：通過(guò)主鍵和外鍵約束確保數(shù)據(jù)的一致性和完整性。-創(chuàng)建索引：為常用查詢字段創(chuàng)建索引，如OrderID、CustomerID等，以提高查詢性能。五、數(shù)據(jù)倉(cāng)庫(kù)建模與ETL過(guò)程1.數(shù)據(jù)倉(cāng)庫(kù)模型設(shè)計(jì)：-事實(shí)表：SalesFacts-OrderID-CustomerID-ProductID-OrderDate-TotalAmount-維度表：-TimeDimension-OrderDate-Year-Quarter-Month-CustomerDimension-CustomerID-CustomerName-Email-Phone-ProductDimension-ProductID-ProductName-CategoryID-Price2.數(shù)據(jù)清洗步驟：-檢查數(shù)據(jù)類型：確保每個(gè)字段的值符合預(yù)期數(shù)據(jù)類型，如日期格式正確，數(shù)值字段無(wú)非法字符等。-處理缺失值：對(duì)于缺失值，可以選擇填充默認(rèn)值、刪除記錄或使用其他統(tǒng)計(jì)方法填充。-刪除重復(fù)記錄：識(shí)別并刪除重復(fù)的記錄，確保數(shù)據(jù)唯一性。-標(biāo)準(zhǔn)化數(shù)據(jù)格式：對(duì)于日期、貨幣等格式不一致的數(shù)據(jù)，進(jìn)行統(tǒng)一格式轉(zhuǎn)換。3.數(shù)據(jù)轉(zhuǎn)換步驟：-訂單日期轉(zhuǎn)換：將訂單日期從YYYY-MM