數(shù)據(jù)倉庫工程師技術(shù)能力考核題庫含SQLETL數(shù)據(jù)建模一、選擇題（共5題，每題2分）1.在數(shù)據(jù)倉庫建模中，星型模型的主要優(yōu)點是什么？A.提高數(shù)據(jù)查詢效率B.簡化數(shù)據(jù)血緣關(guān)系C.減少數(shù)據(jù)冗余D.以上都是2.ETL過程中，數(shù)據(jù)清洗的典型步驟不包括以下哪項？A.去重B.缺失值填充C.數(shù)據(jù)類型轉(zhuǎn)換D.數(shù)據(jù)加密3.在數(shù)據(jù)倉庫中，維度表通常具有以下特征：A.擁有大量行和少量列B.擁有少量行和大量列C.列數(shù)遠多于行數(shù)D.以上都不對4.Snowflake模型相較于星型模型的復雜性體現(xiàn)在：A.維度表進一步規(guī)范化B.事實表進一步規(guī)范化C.提高了數(shù)據(jù)一致性D.減少了數(shù)據(jù)冗余5.在SQLETL中，以下哪個函數(shù)用于提取字符串的子串？A.`LEFT()`B.`RIGHT()`C.`SUBSTRING()`D.以上都是二、判斷題（共5題，每題2分）1.數(shù)據(jù)倉庫中的數(shù)據(jù)更新通常采用增量加載方式。（正確/錯誤）2.維度表的主鍵通常是代理鍵（SurrogateKey）。（正確/錯誤）3.數(shù)據(jù)倉庫中的事實表通常包含大量維度屬性。（正確/錯誤）4.ETL過程中的數(shù)據(jù)轉(zhuǎn)換包括數(shù)據(jù)清洗、數(shù)據(jù)整合和數(shù)據(jù)標準化。（正確/錯誤）5.Snowflake模型可以顯著提高查詢性能，但會增加數(shù)據(jù)加載復雜度。（正確/錯誤）三、簡答題（共3題，每題5分）1.簡述數(shù)據(jù)倉庫與操作型數(shù)據(jù)庫的主要區(qū)別。2.解釋什么是數(shù)據(jù)血緣，并說明其在數(shù)據(jù)倉庫中的重要性。3.描述ETL過程中數(shù)據(jù)轉(zhuǎn)換的常見類型及其作用。四、SQL編程題（共3題，每題10分）1.假設(shè)有一個事實表`sales`（`sale_id`,`product_id`,`store_id`,`sale_date`,`quantity`,`price`），和一個維度表`products`（`product_id`,`product_name`,`category`）。請編寫SQL查詢，統(tǒng)計每個產(chǎn)品類別的總銷售額（`quantityprice`）。2.在數(shù)據(jù)清洗過程中，需要處理缺失值。假設(shè)有一個表`customers`（`customer_id`,`name`,`email`），其中`email`列存在缺失值。請編寫SQL語句，將缺失的`email`填充為`'unknown'`。3.假設(shè)有一個表`sales_log`（`log_id`,`sale_id`,`log_time`），其中`log_time`的格式為`YYYY-MM-DDHH:MI:SS`。請編寫SQL查詢，統(tǒng)計每天的銷售日志數(shù)量。五、數(shù)據(jù)建模題（共2題，每題15分）1.設(shè)計一個星型模型，用于支持一家電商公司的銷售數(shù)據(jù)分析。假設(shè)包含以下實體：-產(chǎn)品（產(chǎn)品ID、產(chǎn)品名稱、分類）-客戶（客戶ID、姓名、城市）-訂單（訂單ID、客戶ID、訂單日期、訂單金額）-產(chǎn)品分類（分類ID、分類名稱）請繪制星型模型圖，并說明事實表和維度表的設(shè)計。2.某銀行需要構(gòu)建一個數(shù)據(jù)倉庫，支持風險管理和客戶分析。假設(shè)包含以下數(shù)據(jù)源：-客戶表（客戶ID、姓名、年齡、城市）-交易表（交易ID、客戶ID、交易金額、交易時間）-存款表（存款I(lǐng)D、客戶ID、存款金額、存款日期）請設(shè)計一個Snowflake模型，并說明其優(yōu)缺點。答案與解析一、選擇題答案與解析1.D.以上都是解析：星型模型的優(yōu)點包括提高查詢效率（通過事實表和維度表的關(guān)聯(lián)）、簡化數(shù)據(jù)血緣關(guān)系（結(jié)構(gòu)清晰）、減少數(shù)據(jù)冗余（維度表共享）。因此，選項D正確。2.D.數(shù)據(jù)加密解析：數(shù)據(jù)清洗通常包括去重、缺失值填充、數(shù)據(jù)類型轉(zhuǎn)換等，而數(shù)據(jù)加密屬于數(shù)據(jù)安全范疇，不屬于清洗步驟。3.A.擁有大量行和少量列解析：維度表通常包含描述業(yè)務場景的屬性，行數(shù)較多（如客戶、產(chǎn)品等），列數(shù)較少（屬性有限）。4.A.維度表進一步規(guī)范化解析：Snowflake模型將維度表進一步分解，如將“產(chǎn)品分類”拆分為“分類ID”和“分類名稱”兩張表，增加了復雜性但減少了冗余。5.D.以上都是解析：`LEFT()`、`RIGHT()`、`SUBSTRING()`都是SQL中提取子串的函數(shù)，因此選項D正確。二、判斷題答案與解析1.正確解析：數(shù)據(jù)倉庫通常采用增量加載，只處理新增或變化的數(shù)據(jù)，提高加載效率。2.正確解析：維度表使用代理鍵（SurrogateKey）可以避免業(yè)務鍵變化帶來的影響，提高數(shù)據(jù)一致性。3.錯誤解析：事實表通常包含大量度量值（如銷售數(shù)量、金額等），維度屬性較少。4.正確解析：ETL中的數(shù)據(jù)轉(zhuǎn)換包括清洗、整合（關(guān)聯(lián)、合并）、標準化（格式統(tǒng)一）等。5.正確解析：Snowflake模型通過規(guī)范化維度表減少了冗余，但查詢時需要更多表連接，增加了復雜度。三、簡答題答案與解析1.數(shù)據(jù)倉庫與操作型數(shù)據(jù)庫的主要區(qū)別：-目的不同：操作型數(shù)據(jù)庫支持日常交易處理，數(shù)據(jù)倉庫支持分析決策。-數(shù)據(jù)結(jié)構(gòu)不同：操作型數(shù)據(jù)庫為事務優(yōu)化（行式存儲），數(shù)據(jù)倉庫為查詢優(yōu)化（列式存儲或星型/雪花模型）。-數(shù)據(jù)更新方式不同：操作型數(shù)據(jù)庫實時更新，數(shù)據(jù)倉庫定期加載（增量或全量）。-數(shù)據(jù)粒度不同：操作型數(shù)據(jù)庫細粒度（每筆交易），數(shù)據(jù)倉庫粗粒度（匯總或聚合）。2.數(shù)據(jù)血緣解釋及重要性：解釋：數(shù)據(jù)血緣指數(shù)據(jù)從源頭經(jīng)過ETL過程到目標表的流轉(zhuǎn)路徑，包括數(shù)據(jù)來源、處理步驟、轉(zhuǎn)換規(guī)則等。重要性：支持數(shù)據(jù)溯源（定位問題數(shù)據(jù)）、影響分析（變更影響評估）、合規(guī)審計（滿足監(jiān)管要求）。3.ETL數(shù)據(jù)轉(zhuǎn)換類型及作用：-數(shù)據(jù)清洗：去除錯誤、缺失、重復數(shù)據(jù)，提高數(shù)據(jù)質(zhì)量。-數(shù)據(jù)整合：合并來自不同源的數(shù)據(jù)，形成統(tǒng)一視圖。-數(shù)據(jù)轉(zhuǎn)換：格式統(tǒng)一（如日期、數(shù)字類型轉(zhuǎn)換）、計算衍生字段（如銷售額=數(shù)量價格）。-數(shù)據(jù)加載：將處理后的數(shù)據(jù)寫入數(shù)據(jù)倉庫。四、SQL編程題答案與解析1.SQL查詢統(tǒng)計產(chǎn)品類別總銷售額：sqlSELECTp.category,SUM(s.quantitys.price)AStotal_salesFROMsalessJOINproductspONduct_id=duct_idGROUPBYp.category;2.SQL填充缺失的email：sqlUPDATEcustomersSETemail='unknown'WHEREemailISNULLORemail='';3.SQL統(tǒng)計每日銷售日志數(shù)量：sqlSELECTDATE(log_time)ASsale_date,COUNT()ASlog_countFROMsales_logGROUPBYDATE(log_time);五、數(shù)據(jù)建模題答案與解析1.電商公司星型模型設(shè)計：-事實表：`sales_facts`（`sale_id`,`product_id`,`customer_id`,`store_id`,`sale_date`,`order_amount`）-維度表：-`products_dim`（`product_id`,`product_name`,`category_id`）-`customers_dim`（`customer_id`,`name`,`city`）-`stores_dim`（`store_id`,`store_name`,`location`）-`time_dim`（`sale_date`,`day`,`month`,`year`）-`categories_dim`（`category_id`,`category_name`）圖示：事實表位于中心，維度表向外輻射。2.銀行Snowflake模型設(shè)計：-事實表：`transactions_facts`（`transaction_id`,`customer_id`,`amount`,`transaction_time`）-維度表：-`customers_dim`（`customer_id`,`name`,`age`,`city`）-`transactions_dim`（`transaction_id`,`transaction_time`）-`deposits_fa