2025年大數據分析師職業技能測試卷：數據倉庫數據抽取與清洗實戰案例實戰試題考試時間：______分鐘總分：______分姓名：______一、數據倉庫數據抽取要求：請根據以下數據源，完成數據的抽取任務。1.請將以下CSV文件中的數據抽取到Excel表中：-文件名：customer_data.csv-數據內容：id,customer_name,age,city,country-請抽取id、customer_name、age、city、country五列數據。2.請將以下JSON文件中的數據抽取到MySQL數據庫中，創建一個名為customer的表，包含id、customer_name、age、city、country五個字段，并將數據插入到表中：-文件名：customer_data.json-數據內容：[{"id":1,"customer_name":"JohnDoe","age":30,"city":"NewYork","country":"USA"},{"id":2,"customer_name":"JaneSmith","age":25,"city":"London","country":"UK"}]3.請將以下XML文件中的數據抽取到PostgreSQL數據庫中，創建一個名為customer的表，包含id、customer_name、age、city、country五個字段，并將數據插入到表中：-文件名：customer_data.xml-數據內容：<customers><customer><id>1</id><customer_name>JohnDoe</customer_name><age>30</age><city>NewYork</city><country>USA</country></customer><customer><id>2</id><customer_name>JaneSmith</customer_name><age>25</age><city>London</city><country>UK</country></customer></customers>二、數據清洗要求：請對以下數據進行清洗，確保數據的準確性。1.請將以下文本數據中的空格、特殊字符和重復字符進行清洗，只保留數字和字母：-原始數據："Hello!@#1234567&*()890"2.請將以下CSV文件中的數據清洗，去除重復行，并只保留年齡大于20歲且城市為"Beijing"的數據：-文件名：customer_data.csv-數據內容：id,customer_name,age,city,country-數據示例：1,JohnDoe,30,Beijing,USA2,JaneSmith,25,NewYork,UK3,MichaelJohnson,22,Beijing,China4,LindaWang,28,Beijing,China3.請將以下JSON文件中的數據清洗，刪除包含空值或null字段的記錄，并確保年齡字段為整數：-文件名：customer_data.json-數據內容：[{"id":1,"customer_name":"JohnDoe","age":"30","city":"NewYork","country":"USA"},{"id":2,"customer_name":"JaneSmith","age":null,"city":"London","country":"UK"},{"id":3,"customer_name":"MichaelJohnson","age":"22","city":"Beijing","country":"China"}]三、數據轉換要求：請對以下數據進行轉換，滿足以下要求。1.請將以下文本數據中的數字轉換為整數類型：-原始數據："Hello!@#1234567&*()890"2.請將以下CSV文件中的日期字段轉換為YYYY-MM-DD格式：-文件名：order_data.csv-數據內容：order_id,customer_id,order_date,amount-數據示例：1,1,2021-10-01,1002,2,2021-10-02,2003,3,2021/10/03,3003.請將以下JSON文件中的布爾值字段轉換為0和1的整數類型：-文件名：user_data.json-數據內容：[{"id":1,"user_name":"JohnDoe","is_active":true},{"id":2,"user_name":"JaneSmith","is_active":false}]四、數據歸一化要求：請對以下數據集進行歸一化處理，確保每個字段的數值范圍在0到1之間。1.請將以下數據集中的年齡字段進行歸一化處理：-原始數據：-customer_id:1,customer_name:"Alice",age:25-customer_id:2,customer_name:"Bob",age:35-customer_id:3,customer_name:"Charlie",age:452.請將以下數據集中的收入字段進行歸一化處理，假設最低收入為$10,000，最高收入為$100,000：-原始數據：-customer_id:1,customer_name:"David",income:$50,000-customer_id:2,customer_name:"Eve",income:$75,000-customer_id:3,customer_name:"Frank",income:$25,000五、數據去重要求：請對以下數據集進行去重處理，去除重復的記錄。1.請去除以下數據集中的重復顧客記錄：-原始數據：-customer_id:1,customer_name:"Grace",email:"grace@"-customer_id:2,customer_name:"Heidi",email:"heidi@"-customer_id:1,customer_name:"Grace",email:"grace@"-customer_id:3,customer_name:"Ivy",email:"ivy@"2.請去除以下數據集中的重復訂單記錄，假設訂單ID是唯一的：-原始數據：-order_id:1001,product_id:101,quantity:2-order_id:1002,product_id:102,quantity:1-order_id:1001,product_id:101,quantity:2-order_id:1003,product_id:103,quantity:3六、數據聚合要求：請對以下數據集進行聚合操作，計算每個城市顧客的平均年齡和收入總和。1.請計算以下數據集中每個城市的顧客平均年齡：-原始數據：-customer_id:1,customer_name:"John",age:30,city:"NewYork"-customer_id:2,customer_name:"Paul",age:22,city:"LosAngeles"-customer_id:3,customer_name:"Tom",age:40,city:"NewYork"-customer_id:4,customer_name:"Ray",age:28,city:"LosAngeles"2.請計算以下數據集中每個城市的顧客收入總和：-原始數據：-customer_id:1,customer_name:"John",age:30,city:"NewYork",income:$50,000-customer_id:2,customer_name:"Paul",age:22,city:"LosAngeles",income:$60,000-customer_id:3,customer_name:"Tom",age:40,city:"NewYork",income:$70,000-customer_id:4,customer_name:"Ray",age:28,city:"LosAngeles",income:$80,000本次試卷答案如下：一、數據倉庫數據抽取1.答案：-id,customer_name,age,city,country-1,JohnDoe,30,NewYork,USA-2,JaneSmith,25,London,UK-3,MichaelJohnson,22,Beijing,China解析思路：-使用CSV文件處理工具，如Excel或Python的pandas庫，讀取CSV文件。-選擇所需的列：id,customer_name,age,city,country。-將選定的列數據輸出到新的Excel表中。2.答案：-創建customer表：-CREATETABLEcustomer(idINTPRIMARYKEY,customer_nameVARCHAR(255),ageINT,cityVARCHAR(255),countryVARCHAR(255));-插入數據：-INSERTINTOcustomer(id,customer_name,age,city,country)VALUES(1,'JohnDoe',30,'NewYork','USA');-INSERTINTOcustomer(id,customer_name,age,city,country)VALUES(2,'JaneSmith',25,'London','UK');解析思路：-使用SQL語句創建一個名為customer的表，包含id、customer_name、age、city、country字段。-使用INSERT語句將JSON文件中的數據插入到customer表中。3.答案：-創建customer表：-CREATETABLEcustomer(idINTPRIMARYKEY,customer_nameVARCHAR(255),ageINT,cityVARCHAR(255),countryVARCHAR(255));-插入數據：-INSERTINTOcustomer(id,customer_name,age,city,country)VALUES(1,'JohnDoe',30,'NewYork','USA');-INSERTINTOcustomer(id,customer_name,age,city,country)VALUES(2,'JaneSmith',25,'London','UK');解析思路：-使用SQL語句創建一個名為customer的表，包含id、customer_name、age、city、country字段。-使用INSERT語句將XML文件中的數據解析并插入到customer表中。二、數據清洗1.答案：-"1234567890"解析思路：-使用正則表達式或字符串處理函數，從原始數據中提取數字和字母。-移除所有非數字和非字母字符。2.答案：-id,customer_name,age,city,country-1,JohnDoe,30,Beijing,USA-3,MichaelJohnson,22,Beijing,China解析思路：-使用CSV文件處理工具，如Excel或Python的pandas庫，讀取CSV文件。-使用drop_duplicates()函數去除重復行。-使用條件篩選，只保留年齡大于20歲且城市為"Beijing"的