柚子快報(bào)邀請(qǐng)碼778899分享：大數(shù)據(jù) 離線數(shù)倉-數(shù)據(jù)倉庫系統(tǒng)

http://yzkb.51969.com/

1數(shù)據(jù)倉庫概述

數(shù)據(jù)倉庫核心架構(gòu)

ODS層（Operational Data Store）：運(yùn)營(yíng)數(shù)據(jù)存儲(chǔ)層，用于存儲(chǔ)來自操作型系統(tǒng)的原始數(shù)據(jù)，是數(shù)據(jù)倉庫中的第一層。 DWD層（Data Warehouse Detail）：數(shù)據(jù)倉庫細(xì)節(jié)層，用于存儲(chǔ)經(jīng)過清洗和加工的詳細(xì)數(shù)據(jù)，保留了原始數(shù)據(jù)的細(xì)節(jié)信息。 DWS層（Data Warehouse Summary）：數(shù)據(jù)倉庫匯總層，用于存儲(chǔ)經(jīng)過匯總和聚合的數(shù)據(jù)，通常用于報(bào)表和分析。 DIM層（Dimensional Data Warehouse）：維度數(shù)據(jù)倉庫層，用于存儲(chǔ)維度數(shù)據(jù)，如產(chǎn)品、用戶、時(shí)間等維度的信息。 ADS層（Application Data Store）：應(yīng)用數(shù)據(jù)存儲(chǔ)層，用于存儲(chǔ)與具體業(yè)務(wù)應(yīng)用相關(guān)的數(shù)據(jù)，通常是面向應(yīng)用的數(shù)據(jù)倉庫。 RDS層（Real-time Data Store）：實(shí)時(shí)數(shù)據(jù)存儲(chǔ)層，用于存儲(chǔ)實(shí)時(shí)生成的數(shù)據(jù)，支持實(shí)時(shí)數(shù)據(jù)分析和決策。 CDS層（Cold Data Store）：冷數(shù)據(jù)存儲(chǔ)層，用于存儲(chǔ)不經(jīng)常訪問的歷史數(shù)據(jù)，通常是低成本的存儲(chǔ)方式。

2數(shù)倉建模概述

2.1建模意義

數(shù)據(jù)模型就是數(shù)據(jù)組織和存儲(chǔ)方法，它強(qiáng)調(diào)從業(yè)務(wù)、數(shù)據(jù)存取和使用角度合理存儲(chǔ)數(shù)據(jù)。只有將數(shù)據(jù)有序的組織和存儲(chǔ)起來之后，數(shù)據(jù)才能得到高性能、低成本、高效率、高質(zhì)量的使用。

高性能：良好的數(shù)據(jù)模型能夠幫助我們快速查詢所需要的數(shù)據(jù)。低成本：良好的數(shù)據(jù)模型能減少重復(fù)計(jì)算，實(shí)現(xiàn)計(jì)算結(jié)果的復(fù)用，降低計(jì)算成本。高效率：良好的數(shù)據(jù)模型能極大的改善用戶使用數(shù)據(jù)的體驗(yàn)，提高使用數(shù)據(jù)的效率。高質(zhì)量：良好的數(shù)據(jù)模型能改善數(shù)據(jù)統(tǒng)計(jì)口徑的混亂，減少計(jì)算錯(cuò)誤的可能性。

2.2數(shù)據(jù)倉庫建模方法論

ER模型

從全企業(yè)的高度，用實(shí)體關(guān)系（EntityRelationship）模型來描述企業(yè)業(yè)務(wù)，并用規(guī)范化的方式表示出來，在范式理論上符合3NF。

1）實(shí)體關(guān)系模型

??????? 將復(fù)雜的數(shù)據(jù)抽象為兩個(gè)概念——實(shí)體和關(guān)系。實(shí)體表示一個(gè)對(duì)象，例如學(xué)生、班級(jí)，關(guān)系是指兩個(gè)實(shí)體之間的關(guān)系，例如學(xué)生和班級(jí)之間的從屬關(guān)系。

2）數(shù)據(jù)庫規(guī)范化

????????是使用一系列范式設(shè)計(jì)數(shù)據(jù)庫（通常是關(guān)系型數(shù)據(jù)庫）的過程，其目的是減少數(shù)據(jù)冗余，增強(qiáng)數(shù)據(jù)的一致性。

????????這一系列范式就是指在設(shè)計(jì)關(guān)系型數(shù)據(jù)庫時(shí)，需要遵從的不同的規(guī)范。關(guān)系型數(shù)據(jù)庫的范式一共有六種，分別是第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF）。遵循的范式級(jí)別越高，數(shù)據(jù)冗余性就越低。

3）三范式

??????? 1）函數(shù)依賴

完全函數(shù)依賴：通過AB能得出C，但AB單獨(dú)得不出C

部分函數(shù)依賴：通過AB能得出C，但通過A或B也能得出C

傳遞函數(shù)依賴：通過A得到B，通過B得到C，但C得不到A，即C傳遞依賴于A

??????? 2）第一范式1NF

?核心原則：屬性不可切割

3）第二范式2NF

核心原則：不能存在“部分函數(shù)依賴”

4）第三范式3NF

核心原則：不能存在傳遞函數(shù)依賴

ER模型結(jié)構(gòu)

出發(fā)點(diǎn)：整合數(shù)據(jù)，將數(shù)據(jù)進(jìn)行組合與合并減少數(shù)據(jù)的冗余性，但不適合直接用于分析統(tǒng)計(jì)

維度模型

將復(fù)雜的業(yè)務(wù)通過事實(shí)和維度兩個(gè)概念進(jìn)行呈現(xiàn)，事實(shí)通常對(duì)應(yīng)業(yè)務(wù)過程，而維度通常對(duì)應(yīng)業(yè)務(wù)發(fā)生時(shí)所處的環(huán)境

注：業(yè)務(wù)過程可以概括為一個(gè)個(gè)不可拆分的行為事件，例如電商交易中的下單，取消訂單，付款，退單等，都是業(yè)務(wù)過程。

下圖為一個(gè)典型的維度模型，其中位于中心的SalesOrder為事實(shí)表，其中保存的是下單這個(gè)業(yè)務(wù)過程的所有記錄。位于周圍每張表都是維度表，包括Date（日期），Customer（顧客），Product（產(chǎn)品），Location（地區(qū)）等，這些維度表就組成了每個(gè)訂單發(fā)生時(shí)所處的環(huán)境，即何人、何時(shí)、在何地下單了何種產(chǎn)品。從圖中可以看出，模型相對(duì)清晰、簡(jiǎn)潔。

維度建模以數(shù)據(jù)分析作為出發(fā)點(diǎn)，為數(shù)據(jù)分析服務(wù)，因此它關(guān)注的重點(diǎn)是用戶如何更快的完成需求分析以及如何實(shí)現(xiàn)較好的大規(guī)模復(fù)雜查詢的響應(yīng)性能。

3維度建模理論之事實(shí)表

3.1事實(shí)表概述

事實(shí)表作為數(shù)據(jù)倉庫維度建模的核心，緊緊圍繞著業(yè)務(wù)過程來設(shè)計(jì)。其包含與該業(yè)務(wù)過程有關(guān)的維度引用（維度表外鍵）以及該業(yè)務(wù)過程的度量（通常是可累加的數(shù)字類型字段）。

事實(shí)表特點(diǎn)

通常比較細(xì)長(zhǎng)，即列少行多，且行的增速快

事實(shí)表分類

事務(wù)事實(shí)表，周期快照事實(shí)表和累積快照事實(shí)表

3.2事務(wù)性事實(shí)表

概述

事務(wù)型事實(shí)表用來記錄各業(yè)務(wù)過程，它保存的是各業(yè)務(wù)過程的原子操作事件，即最細(xì)粒度的操作事件。粒度是指事實(shí)表中一行數(shù)據(jù)所表達(dá)的業(yè)務(wù)細(xì)節(jié)程度。

事務(wù)型事實(shí)表可用于分析與各業(yè)務(wù)過程相關(guān)的各項(xiàng)統(tǒng)計(jì)指標(biāo)，由于其保存了最細(xì)粒度的記錄，可以提供最大限度的靈活性，可以支持無法預(yù)期的各種細(xì)節(jié)層次的統(tǒng)計(jì)需求。

設(shè)計(jì)流程

設(shè)計(jì)事務(wù)事實(shí)表時(shí)一般可遵循以下四個(gè)步驟。

選擇業(yè)務(wù)過程→聲明粒度→確認(rèn)維度→確認(rèn)事實(shí)

1）選擇業(yè)務(wù)過程

在業(yè)務(wù)系統(tǒng)中，挑選我們感興趣的業(yè)務(wù)過程，業(yè)務(wù)過程可以概括為一個(gè)個(gè)不可拆分的行為事件，例如電商交易中的下單，取消訂單，付款，退單等，都是業(yè)務(wù)過程。通常情況下，一個(gè)業(yè)務(wù)過程對(duì)應(yīng)一張事務(wù)型事實(shí)表。

2）聲明粒度

業(yè)務(wù)過程確定后，需要為每個(gè)業(yè)務(wù)過程聲明粒度。即精確定義每張事務(wù)型事實(shí)表的每行數(shù)據(jù)表示什么，應(yīng)該盡可能選擇最細(xì)粒度，以此來響應(yīng)各種細(xì)節(jié)程度的需求。

典型的粒度聲明如下：

訂單事實(shí)表中一行數(shù)據(jù)表示的是一個(gè)訂單中的一個(gè)商品項(xiàng)。

3）確定維度

確定維度具體是指，確定與每張事務(wù)型事實(shí)表相關(guān)的維度有哪些。

確定維度時(shí)應(yīng)盡量多的選擇與業(yè)務(wù)過程相關(guān)的環(huán)境信息。因?yàn)榫S度的豐富程度就決定了維度模型能夠支持的指標(biāo)豐富程度。

4）確定事實(shí)

此處的“事實(shí)”一詞，指的是每個(gè)業(yè)務(wù)過程的度量值（通常是可累加的數(shù)字類型的值，例如：次數(shù)、個(gè)數(shù)、件數(shù)、金額等）。

經(jīng)過上述四個(gè)步驟，事務(wù)型事實(shí)表就基本設(shè)計(jì)完成了。第一步選擇業(yè)務(wù)過程可以確定有哪些事務(wù)型事實(shí)表，第二步可以確定每張事務(wù)型事實(shí)表的每行數(shù)據(jù)是什么，第三步可以確定每張事務(wù)型事實(shí)表的維度外鍵，第四步可以確定每張事務(wù)型事實(shí)表的度量值字段。

不足之處

事務(wù)型事實(shí)表可以保存所有業(yè)務(wù)過程的最細(xì)粒度的操作事件，故理論上其可以支撐與各業(yè)務(wù)過程相關(guān)的各種統(tǒng)計(jì)粒度的需求。但對(duì)于某些特定類型的需求，其邏輯可能會(huì)比較復(fù)雜，或者效率會(huì)比較低下。例如：

1）存量型指標(biāo)

例如商品庫存，賬戶余額等。此處以電商中的虛擬貨幣為例，虛擬貨幣業(yè)務(wù)包含的業(yè)務(wù)過程主要包括獲取貨幣和使用貨幣，兩個(gè)業(yè)務(wù)過程各自對(duì)應(yīng)一張事務(wù)型事實(shí)表，一張存儲(chǔ)所有的獲取貨幣的原子操作事件，另一張存儲(chǔ)所有使用貨幣的原子操作事件。

假定現(xiàn)有一個(gè)需求，要求統(tǒng)計(jì)截至當(dāng)日的各用戶虛擬貨幣余額。由于獲取貨幣和使用貨幣均會(huì)影響到余額，故需要對(duì)兩張事務(wù)型事實(shí)表進(jìn)行聚合，且需要區(qū)分兩者對(duì)余額的影響（加或減），另外需要對(duì)兩張表的全表數(shù)據(jù)聚合才能得到統(tǒng)計(jì)結(jié)果。

可以看到，不論是從邏輯上還是效率上考慮，這都不是一個(gè)好的方案。

2）多事務(wù)關(guān)聯(lián)統(tǒng)計(jì)

例如，現(xiàn)需要統(tǒng)計(jì)最近30天，用戶下單到支付的時(shí)間間隔的平均值。統(tǒng)計(jì)思路應(yīng)該是找到下單事務(wù)事實(shí)表和支付事務(wù)事實(shí)表，過濾出最近30天的記錄，然后按照訂單id對(duì)兩張事實(shí)表進(jìn)行關(guān)聯(lián)，之后用支付時(shí)間減去下單時(shí)間，然后再求平均值。

邏輯上雖然并不復(fù)雜，但是其效率較低，應(yīng)為下單事務(wù)事實(shí)表和支付事務(wù)事實(shí)表均為大表，大表join大表的操作應(yīng)盡量避免。

可以看到，在上述兩種場(chǎng)景下事務(wù)型事實(shí)表的表現(xiàn)并不理想。下面要介紹的另外兩種類型的事實(shí)表就是為了彌補(bǔ)事務(wù)型事實(shí)表的不足的。

3.3周期型快照事實(shí)表

概述

周期快照事實(shí)表以具有規(guī)律性的、可預(yù)見的時(shí)間間隔來記錄事實(shí)，主要用于分析一些存量型（例如商品庫存，賬戶余額）或者狀態(tài)型（空氣溫度，行駛速度）指標(biāo)。

對(duì)于商品庫存、賬戶余額這些存量型指標(biāo)，業(yè)務(wù)系統(tǒng)中通常就會(huì)計(jì)算并保存最新結(jié)果，所以定期同步一份全量數(shù)據(jù)到數(shù)據(jù)倉庫，構(gòu)建周期型快照事實(shí)表，就能輕松應(yīng)對(duì)此類統(tǒng)計(jì)需求，而無需再對(duì)事務(wù)型事實(shí)表中大量的歷史記錄進(jìn)行聚合了。

對(duì)于空氣溫度、行駛速度這些狀態(tài)型指標(biāo)，由于它們的值往往是連續(xù)的，我們無法捕獲其變動(dòng)的原子事務(wù)操作，所以無法使用事務(wù)型事實(shí)表統(tǒng)計(jì)此類需求。而只能定期對(duì)其進(jìn)行采樣，構(gòu)建周期型快照事實(shí)表。

設(shè)計(jì)流程

1）確定粒度

周期型快照事實(shí)表的粒度可由采樣周期和維度描述，故確定采樣周期和維度后即可確定粒度。

采樣周期通常選擇每日。

維度可根據(jù)統(tǒng)計(jì)指標(biāo)決定，例如指標(biāo)為統(tǒng)計(jì)每個(gè)倉庫中每種商品的庫存，則可確定維度為倉庫和商品。

確定完采樣周期和維度后，即可確定該表粒度為每日-倉庫-商品。

2）確認(rèn)事實(shí)

事實(shí)也可根據(jù)統(tǒng)計(jì)指標(biāo)決定，例如指標(biāo)為統(tǒng)計(jì)每個(gè)倉庫中每種商品的庫存，則事實(shí)為商品庫存。

事實(shí)類型

此處的事實(shí)類型是指度量值的類型，而非事實(shí)表的類型。事實(shí)（度量值）共分為三類，分別是可加事實(shí)，半可加事實(shí)和不可加事實(shí)。

1）可加事實(shí)

可加事實(shí)是指可以按照與事實(shí)表相關(guān)的所有維度進(jìn)行累加，例如事務(wù)型事實(shí)表中的事實(shí)。

2）半可加事實(shí)

半可加事實(shí)是指只能按照與事實(shí)表相關(guān)的一部分維度進(jìn)行累加，例如周期型快照事實(shí)表中的事實(shí)。以上述各倉庫中各商品的庫存每天快照事實(shí)表為例，這張表中的庫存事實(shí)可以按照倉庫或者商品維度進(jìn)行累加，但是不能按照時(shí)間維度進(jìn)行累加，因?yàn)閷⒚刻斓膸齑胬奂悠饋硎菦]有任何意義的。

3）不可加事實(shí)

不可加事實(shí)是指完全不具備可加性，例如比率型事實(shí)。不可加事實(shí)通常需要轉(zhuǎn)化為可加事實(shí)，例如比率可轉(zhuǎn)化為分子和分母。

3.4累積性快照事實(shí)表

概述

累計(jì)快照事實(shí)表是基于一個(gè)業(yè)務(wù)流程中的多個(gè)關(guān)鍵業(yè)務(wù)過程聯(lián)合處理而構(gòu)建的事實(shí)表，如交易流程中的下單、支付、發(fā)貨、確認(rèn)收貨業(yè)務(wù)過程。

累積型快照事實(shí)表通常具有多個(gè)日期字段，每個(gè)日期對(duì)應(yīng)業(yè)務(wù)流程中的一個(gè)關(guān)鍵業(yè)務(wù)過程（里程碑）。

累積型快照事實(shí)表主要用于分析業(yè)務(wù)過程（里程碑）之間的時(shí)間間隔等需求。例如前文提到的用戶下單到支付的平均時(shí)間間隔，使用累積型快照事實(shí)表進(jìn)行統(tǒng)計(jì)，就能避免兩個(gè)事務(wù)事實(shí)表的關(guān)聯(lián)操作，從而變得十分簡(jiǎn)單高效。

設(shè)計(jì)流程

累積型快照事實(shí)表的設(shè)計(jì)流程同事務(wù)型事實(shí)表類似，也可采用以下四個(gè)步驟，下面重點(diǎn)描述與事務(wù)型事實(shí)表的不同之處。

選擇業(yè)務(wù)過程→聲明粒度→確認(rèn)維度→確認(rèn)事實(shí)。

1）選擇業(yè)務(wù)過程

選擇一個(gè)業(yè)務(wù)流程中需要關(guān)聯(lián)分析的多個(gè)關(guān)鍵業(yè)務(wù)過程，多個(gè)業(yè)務(wù)過程對(duì)應(yīng)一張累積型快照事實(shí)表。

2）聲明粒度

精確定義每行數(shù)據(jù)表示的是什么，盡量選擇最小粒度。

3）確認(rèn)維度

選擇與各業(yè)務(wù)過程相關(guān)的維度，需要注意的是，每各業(yè)務(wù)過程均需要一個(gè)日期維度。

4）確認(rèn)事實(shí)

選擇各業(yè)務(wù)過程的度量值。

4維度建模理論之維度表

4.1概述

維度表是維度建模的基礎(chǔ)和靈魂。前文提到，事實(shí)表緊緊圍繞業(yè)務(wù)過程進(jìn)行設(shè)計(jì)，而維度表則圍繞業(yè)務(wù)過程所處的環(huán)境進(jìn)行設(shè)計(jì)。維度表主要包含一個(gè)主鍵和各種維度字段，維度字段稱為維度屬性。

4.2維度表設(shè)計(jì)步驟

1）確定維度（表）

在設(shè)計(jì)事實(shí)表時(shí)，已經(jīng)確定了與每個(gè)事實(shí)表相關(guān)的維度，理論上每個(gè)相關(guān)維度均需對(duì)應(yīng)一張維度表。需要注意到，可能存在多個(gè)事實(shí)表與同一個(gè)維度都相關(guān)的情況，這種情況需保證維度的唯一性，即只創(chuàng)建一張維度表。另外，如果某些維度表的維度屬性很少，例如只有一個(gè)**名稱，則可不創(chuàng)建該維度表，而把該表的維度屬性直接增加到與之相關(guān)的事實(shí)表中，這個(gè)操作稱為維度退化。

2）確定主維表和相關(guān)維表

此處的主維表和相關(guān)維表均指業(yè)務(wù)系統(tǒng)中與某維度相關(guān)的表。例如業(yè)務(wù)系統(tǒng)中與商品相關(guān)的表有sku_info，spu_info，base_trademark，base_category3，base_category2，base_category1等，其中sku_info就稱為商品維度的主維表，其余表稱為商品維度的相關(guān)維表。維度表的粒度通常與主維表相同。

3）確定維度屬性

確定維度屬性即確定維度表字段。維度屬性主要來自于業(yè)務(wù)系統(tǒng)中與該維度對(duì)應(yīng)的主維表和相關(guān)維表。維度屬性可直接從主維表或相關(guān)維表中選擇，也可通過進(jìn)一步加工得到。

確定維度屬性時(shí)，需要遵循以下要求：

（1）盡可能生成豐富的維度屬性

維度屬性是后續(xù)做分析統(tǒng)計(jì)時(shí)的查詢約束條件、分組字段的基本來源，是數(shù)據(jù)易用性的關(guān)鍵。維度屬性的豐富程度直接影響到數(shù)據(jù)模型能夠支持的指標(biāo)的豐富程度。

（2）盡量不使用編碼，而使用明確的文字說明，一般可以編碼和文字共存。

（3）盡量沉淀出通用的維度屬性

有些維度屬性的獲取需要進(jìn)行比較復(fù)雜的邏輯處理，例如需要通過多個(gè)字段拼接得到。為避免后續(xù)每次使用時(shí)的重復(fù)處理，可將這些維度屬性沉淀到維度表中。

4.3維度設(shè)計(jì)要點(diǎn)

規(guī)范化與反規(guī)范化

規(guī)范化是指使用一系列范式設(shè)計(jì)數(shù)據(jù)庫的過程，其目的是減少數(shù)據(jù)冗余，增強(qiáng)數(shù)據(jù)的一致性。通常情況下，規(guī)范化之后，一張表的字段會(huì)拆分到多張表。

反規(guī)范化是指將多張表的數(shù)據(jù)冗余到一張表，其目的是減少join操作，提高查詢性能。

在設(shè)計(jì)維度表時(shí)，如果對(duì)其進(jìn)行規(guī)范化，得到的維度模型稱為雪花模型，如果對(duì)其進(jìn)行反規(guī)范化，得到的模型稱為星型模型。

數(shù)據(jù)倉庫系統(tǒng)的主要目的是用于數(shù)據(jù)分析和統(tǒng)計(jì)，所以是否方便用戶進(jìn)行統(tǒng)計(jì)分析決定了模型的優(yōu)劣。采用雪花模型，用戶在統(tǒng)計(jì)分析的過程中需要大量的關(guān)聯(lián)操作，使用復(fù)雜度高，同時(shí)查詢性能很差，而采用星型模型，則方便、易用且性能好。所以出于易用性和性能的考慮，維度表一般是很不規(guī)范化的。

維度變化

維度屬性通常不是靜態(tài)的，而是會(huì)隨時(shí)間變化的，數(shù)據(jù)倉庫的一個(gè)重要特點(diǎn)就是反映歷史的變化，所以如何保存維度的歷史狀態(tài)是維度設(shè)計(jì)的重要工作之一。保存維度數(shù)據(jù)的歷史狀態(tài)，通常有以下兩種做法，分別是全量快照表和拉鏈表。

1）全量快照表

離線數(shù)據(jù)倉庫的計(jì)算周期通常為每天一次，所以可以每天保存一份全量的維度數(shù)據(jù)。這種方式的優(yōu)點(diǎn)和缺點(diǎn)都很明顯。

優(yōu)點(diǎn)是簡(jiǎn)單而有效，開發(fā)和維護(hù)成本低，且方便理解和使用。

缺點(diǎn)是浪費(fèi)存儲(chǔ)空間，尤其是當(dāng)數(shù)據(jù)的變化比例比較低時(shí)。

2）拉鏈表

拉鏈表的意義就在于能夠更加高效的保存維度信息的歷史狀態(tài)。

（1）什么是拉鏈表

（2）為什么要做拉鏈表

（3）如何使用拉鏈表

多值維度

如果事實(shí)表中一條記錄在某個(gè)維度表中有多條記錄與之對(duì)應(yīng)，稱為多值維度。例如，下單事實(shí)表中的一條記錄為一個(gè)訂單，一個(gè)訂單可能包含多個(gè)商品，所會(huì)商品維度表中就可能有多條數(shù)據(jù)與之對(duì)應(yīng)。

針對(duì)這種情況，通常采用以下兩種方案解決。

第一種：降低事實(shí)表的粒度，例如將訂單事實(shí)表的粒度由一個(gè)訂單降低為一個(gè)訂單中的一個(gè)商品項(xiàng)。

第二種：在事實(shí)表中采用多字段保存多個(gè)維度值，每個(gè)字段保存一個(gè)維度id。這種方案只適用于多值維度個(gè)數(shù)固定的情況。

建議盡量采用第一種方案解決多值維度問題。

多值屬性

維表中的某個(gè)屬性同時(shí)有多個(gè)值，稱之為“多值屬性”，例如商品維度的平臺(tái)屬性和銷售屬性，每個(gè)商品均有多個(gè)屬性值。

針對(duì)這種情況，通常有可以采用以下兩種方案。

第一種：將多值屬性放到一個(gè)字段，該字段內(nèi)容為key1:value1，key2:value2的形式，例如一個(gè)手機(jī)商品的平臺(tái)屬性值為“品牌：華為，系統(tǒng)：鴻蒙，CPU：麒麟990”。

第二種：將多值屬性放到多個(gè)字段，每個(gè)字段對(duì)應(yīng)一個(gè)屬性。這種方案只適用于多值屬性個(gè)數(shù)固定的情況。

5數(shù)據(jù)倉庫設(shè)計(jì)

5.1數(shù)據(jù)倉庫分層規(guī)劃

本項(xiàng)目分層規(guī)劃：

5.2數(shù)據(jù)倉庫構(gòu)建流程

完整流程如下：

數(shù)據(jù)調(diào)研

業(yè)務(wù)調(diào)研和需求分析，直接影響數(shù)倉質(zhì)量

1）業(yè)務(wù)調(diào)研

業(yè)務(wù)調(diào)研的主要目標(biāo)是熟悉業(yè)務(wù)流程、熟悉業(yè)務(wù)數(shù)據(jù)。

熟悉業(yè)務(wù)流程要求做到，明確每個(gè)業(yè)務(wù)的具體流程，需要將該業(yè)務(wù)所包含的每個(gè)業(yè)務(wù)過程一一列舉出來。

熟悉業(yè)務(wù)數(shù)據(jù)要求做到，將數(shù)據(jù)（包括埋點(diǎn)日志和業(yè)務(wù)數(shù)據(jù)表）與業(yè)務(wù)過程對(duì)應(yīng)起來，明確每個(gè)業(yè)務(wù)過程會(huì)對(duì)哪些表的數(shù)據(jù)產(chǎn)生影響，以及產(chǎn)生什么影響。產(chǎn)生的影響，需要具體到，是新增一條數(shù)據(jù)，還是修改一條數(shù)據(jù)，并且需要明確新增的內(nèi)容或者是修改的邏輯。

2）需求分析

典型的需求指標(biāo)如，最近一天各省份手機(jī)品類訂單總額。

分析需求時(shí)，需要明確需求所需的業(yè)務(wù)過程及維度，例如該需求所需的業(yè)務(wù)過程就是買家下單，所需的維度有日期，省份，商品品類。

3）總結(jié)

做完業(yè)務(wù)分析和需求分析之后，要保證每個(gè)需求都能找到與之對(duì)應(yīng)的業(yè)務(wù)過程及維度。若現(xiàn)有數(shù)據(jù)無法滿足需求，則需要和業(yè)務(wù)方進(jìn)行溝通，例如某個(gè)頁面需要新增某個(gè)行為的埋點(diǎn)。

明確數(shù)據(jù)域

便于數(shù)據(jù)的管理和應(yīng)用

通?？梢愿鶕?jù)業(yè)務(wù)過程或者部門進(jìn)行劃分，本項(xiàng)目根據(jù)業(yè)務(wù)過程進(jìn)行劃分，需要注意的是一個(gè)業(yè)務(wù)過程只能屬于一個(gè)數(shù)據(jù)域。

下面是本數(shù)倉項(xiàng)目所需的所有業(yè)務(wù)過程及數(shù)據(jù)域劃分詳情。

數(shù)據(jù)域 業(yè)務(wù)過程 交易域 加購、下單、取消訂單、支付成功、退單、退款成功 流量域 頁面瀏覽、啟動(dòng)應(yīng)用、動(dòng)作、曝光、錯(cuò)誤 用戶域 注冊(cè)、登錄 互動(dòng)域 收藏、評(píng)價(jià) 工具域 優(yōu)惠券領(lǐng)取、優(yōu)惠券使用（下單）、優(yōu)惠券使用（支付）

構(gòu)建業(yè)務(wù)總線矩陣

業(yè)務(wù)總線矩陣中包含維度模型所需的所有事實(shí)（業(yè)務(wù)過程）以及維度，以及各業(yè)務(wù)過程與各維度的關(guān)系。矩陣的行是一個(gè)個(gè)業(yè)務(wù)過程，矩陣的列是一個(gè)個(gè)的維度，行列的交點(diǎn)表示業(yè)務(wù)過程與維度的關(guān)系

按照事務(wù)型事實(shí)表的設(shè)計(jì)流程，選擇業(yè)務(wù)過程——聲明粒度——確認(rèn)維度——確認(rèn)事實(shí)，得到的最終的業(yè)務(wù)總線矩陣

后續(xù)的DWD層以及DIM層的搭建需參考業(yè)務(wù)總線矩陣。

明確統(tǒng)計(jì)指標(biāo)

具體的工作是，深入分析需求，構(gòu)建指標(biāo)體系。構(gòu)建指標(biāo)體系的主要意義就是指標(biāo)定義標(biāo)準(zhǔn)化。所有指標(biāo)的定義，都必須遵循同一套標(biāo)準(zhǔn)，這樣能有效的避免指標(biāo)定義存在歧義，指標(biāo)定義重復(fù)等問題。

1）指標(biāo)體系相關(guān)概念

（1）原子指標(biāo)

原子指標(biāo)基于某一業(yè)務(wù)過程的度量值，是業(yè)務(wù)定義中不可再拆解的指標(biāo)，原子指標(biāo)的核心功能就是對(duì)指標(biāo)的聚合邏輯進(jìn)行了定義。我們可以得出結(jié)論，原子指標(biāo)包含三要素，分別是業(yè)務(wù)過程、度量值和聚合邏輯。

例如訂單總額就是一個(gè)典型的原子指標(biāo)，其中的業(yè)務(wù)過程為用戶下單、度量值為訂單金額，聚合邏輯為sum()求和。需要注意的是原子指標(biāo)只是用來輔助定義指標(biāo)一個(gè)概念，通常不會(huì)對(duì)應(yīng)有實(shí)際統(tǒng)計(jì)需求與之對(duì)應(yīng)。

（2）派生指標(biāo)

派生指標(biāo)基于原子指標(biāo)，其與原子指標(biāo)的關(guān)系如下圖所示。

與原子指標(biāo)不同，派生指標(biāo)通常會(huì)對(duì)應(yīng)實(shí)際的統(tǒng)計(jì)需求。

（3）衍生指標(biāo)

衍生指標(biāo)是在一個(gè)或多個(gè)派生指標(biāo)的基礎(chǔ)上，通過各種邏輯運(yùn)算復(fù)合而成的。例如比率、比例等類型的指標(biāo)。衍生指標(biāo)也會(huì)對(duì)應(yīng)實(shí)際的統(tǒng)計(jì)需求。

2）指標(biāo)體系對(duì)于數(shù)倉建模的意義

通過上述兩個(gè)具體的案例可以看出，絕大多數(shù)的統(tǒng)計(jì)需求，都可以使用原子指標(biāo)、派生指標(biāo)以及衍生指標(biāo)這套標(biāo)準(zhǔn)去定義。同時(shí)能夠發(fā)現(xiàn)這些統(tǒng)計(jì)需求都直接的或間接的對(duì)應(yīng)一個(gè)或者是多個(gè)派生指標(biāo)。

當(dāng)統(tǒng)計(jì)需求足夠多時(shí)，必然會(huì)出現(xiàn)部分統(tǒng)計(jì)需求對(duì)應(yīng)的派生指標(biāo)相同的情況。這種情況下，我們就可以考慮將這些公共的派生指標(biāo)保存下來，這樣做的主要目的就是減少重復(fù)計(jì)算，提高數(shù)據(jù)的復(fù)用性。

這些公共的派生指標(biāo)統(tǒng)一保存在數(shù)據(jù)倉庫的DWS層。因此DWS層設(shè)計(jì)，就可以參考我們根據(jù)現(xiàn)有的統(tǒng)計(jì)需求整理出的派生指標(biāo)。

維度模型設(shè)計(jì)

維度模型的設(shè)計(jì)參照上述得到的業(yè)務(wù)總線矩陣即可。事實(shí)表存儲(chǔ)在DWD層，維度表存儲(chǔ)在DIM層。

匯總模型設(shè)計(jì)

匯總模型的設(shè)計(jì)參考上述整理出的指標(biāo)體系（主要是派生指標(biāo)）即可。匯總表與派生指標(biāo)的對(duì)應(yīng)關(guān)系是，一張匯總表通常包含業(yè)務(wù)過程相同、統(tǒng)計(jì)周期相同、統(tǒng)計(jì)粒度相同的多個(gè)派生指標(biāo)。請(qǐng)思考：匯總表與事實(shí)表的對(duì)應(yīng)關(guān)系是？

????????匯總表和事實(shí)表之間的對(duì)應(yīng)關(guān)系是根據(jù)業(yè)務(wù)需求和數(shù)據(jù)分析的目的來確定的，通過建立合適的匯總表，可以提高數(shù)據(jù)查詢和分析的效率，同時(shí)減少對(duì)事實(shí)表的直接查詢壓力。

6數(shù)倉環(huán)境準(zhǔn)備

6.1 數(shù)據(jù)倉庫運(yùn)行環(huán)境

Hive環(huán)境搭建

1）Hive引擎簡(jiǎn)介

Hive引擎包括：默認(rèn)MR、Tez、Spark。

Hive on Spark：Hive既作為存儲(chǔ)元數(shù)據(jù)又負(fù)責(zé)SQL的解析優(yōu)化，語法是HQL語法，執(zhí)行引擎變成了Spark，Spark負(fù)責(zé)采用RDD執(zhí)行。

Spark on Hive : Hive只作為存儲(chǔ)元數(shù)據(jù)，Spark負(fù)責(zé)SQL解析優(yōu)化，語法是Spark?SQL語法，Spark負(fù)責(zé)采用RDD執(zhí)行。

2）Hive?on?Spark配置

（1）兼容性說明

注意：官網(wǎng)下載的Hive3.1.3和Spark3.3.1默認(rèn)是不兼容的。因?yàn)镠ive3.1.3支持的Spark版本是2.3.0，所以需要我們重新編譯Hive3.1.3版本。

編譯步驟：官網(wǎng)下載Hive3.1.3源碼，修改pom文件中引用的Spark版本為3.3.1，如果編譯通過，直接打包獲取jar包。如果報(bào)錯(cuò)，就根據(jù)提示，修改相關(guān)方法，直到不報(bào)錯(cuò)，打包獲取jar包。

（2）在Hive所在節(jié)點(diǎn)部署Spark純凈版

（3）Hive on Spark測(cè)試

Yarn環(huán)境配置

1）增加ApplicationMaster資源比例

容量調(diào)度器對(duì)每個(gè)資源隊(duì)列中同時(shí)運(yùn)行的Application?Master占用的資源進(jìn)行了限制，該限制通過yarn.scheduler.capacity.maximum-am-resource-percent參數(shù)實(shí)現(xiàn)，其默認(rèn)值是0.1，表示每個(gè)資源隊(duì)列上Application?Master最多可使用的資源為該隊(duì)列總資源的10%，目的是防止大部分資源都被Application?Master占用，而導(dǎo)致Map/Reduce?Task無法執(zhí)行。

生產(chǎn)環(huán)境該參數(shù)可使用默認(rèn)值。但學(xué)習(xí)環(huán)境，集群資源總數(shù)很少，如果只分配10%的資源給Application?Master，則可能出現(xiàn)，同一時(shí)刻只能運(yùn)行一個(gè)Job的情況，因?yàn)橐粋€(gè)Application?Master使用的資源就可能已經(jīng)達(dá)到10%的上限了。故此處可將該值適當(dāng)調(diào)大。

具體操作 修改配置文件——分發(fā)——重啟

6.2 數(shù)據(jù)倉庫開發(fā)環(huán)境

數(shù)倉開發(fā)工具可選用DBeaver或者DataGrip。兩者都需要用到JDBC協(xié)議連接到Hive，故需要啟動(dòng)HiveServer2。

6.3 模擬數(shù)據(jù)準(zhǔn)備

通常企業(yè)在開始搭建數(shù)倉時(shí)，業(yè)務(wù)系統(tǒng)中會(huì)存在歷史數(shù)據(jù)，一般是業(yè)務(wù)數(shù)據(jù)庫存在歷史數(shù)據(jù)，而用戶行為日志無歷史數(shù)據(jù)。假定數(shù)倉上線的日期為2022-06-08，為模擬真實(shí)場(chǎng)景，需準(zhǔn)備以下數(shù)據(jù)。

注：在執(zhí)行以下操作之前，先將HDFS上/origin_data路徑下之前的數(shù)據(jù)刪除。

1）啟動(dòng)采集通道

命令如下。

[atguigu@hadoop102 ~]$ cluster.sh start

停止Maxwell。 ?

[atguigu@hadoop102 bin]$ mxw.sh stop

停止Maxwell

2）數(shù)據(jù)準(zhǔn)備

（1）生成模擬數(shù)據(jù)

（2）全量表同步

（3）增量表首日全量同步

????????① 清除Maxwell斷點(diǎn)記錄

????????由于Maxwell支持?jǐn)帱c(diǎn)續(xù)傳，而上述重新生成業(yè)務(wù)數(shù)據(jù)的過程，會(huì)產(chǎn)生大量的binlog操作日志，這些日志我們并不需要。故此處需清除Maxwell的斷點(diǎn)記錄，令其從binlog最新的位置開始采集。

????????清空Maxwell數(shù)據(jù)庫，相當(dāng)于初始化Maxwell。

7數(shù)倉開發(fā)之ODS層

ODS層的設(shè)計(jì)要點(diǎn)如下：

（1）ODS層的表結(jié)構(gòu)設(shè)計(jì)依托于從業(yè)務(wù)系統(tǒng)同步過來的數(shù)據(jù)結(jié)構(gòu)。

（2）ODS層要保存全部歷史數(shù)據(jù)，故其壓縮格式應(yīng)選擇壓縮比較高的，此處選擇gzip。

（3）ODS層表名的命名規(guī)范為：ods_表名_單分區(qū)增量全量標(biāo)識(shí)（inc/full）。

7.1日志表

1）建表語句

DROP TABLE IF EXISTS ods_log_inc;

CREATE EXTERNAL TABLE ods_log_inc

(

`common` STRUCT

ba :STRING,

ch :STRING,

is_new :STRING,

md :STRING,

mid :STRING,

os :STRING,

sid :STRING,

uid :STRING,

vc :STRING> COMMENT '公共信息',

`page` STRUCT

item :STRING,

item_type :STRING,

last_page_id :STRING,

page_id :STRING,

from_pos_id :STRING,

from_pos_seq :STRING,

refer_id :STRING> COMMENT '頁面信息',

`actions` ARRAY

item:STRING,

item_type:STRING,

ts:BIGINT>> COMMENT '動(dòng)作信息',

`displays` ARRAY

item :STRING,

item_type :STRING,

`pos_seq` :STRING,

pos_id :STRING>> COMMENT '曝光信息',

`start` STRUCT

first_open :BIGINT,

loading_time :BIGINT,

open_ad_id :BIGINT,

open_ad_ms :BIGINT,

open_ad_skip_ms :BIGINT> COMMENT '啟動(dòng)信息',

`err` STRUCT

msg:STRING> COMMENT '錯(cuò)誤信息',

`ts` BIGINT COMMENT '時(shí)間戳'

) COMMENT '活動(dòng)信息表'

PARTITIONED BY (`dt` STRING)

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'

LOCATION '/warehouse/gmall/ods/ods_log_inc/'

TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

2）數(shù)據(jù)裝載

load data inpath '/origin_data/gmall/log/topic_log/2022-06-08' into table ods_log_inc partition(dt='2022-06-08');

3）每日數(shù)據(jù)裝載腳本

（1）在hadoop102的/home/atguigu/bin目錄下創(chuàng)建hdfs_to_ods_log.sh

#!/bin/bash

# 定義變量方便修改

APP=gmall

# 如果是輸入的日期按照取輸入日期；如果沒輸入日期取當(dāng)前時(shí)間的前一天

if [ -n "$1" ] ;then

do_date=$1

else

do_date=`date -d "-1 day" +%F`

fi

echo ================== 日志日期為 $do_date ==================

sql="

load data inpath '/origin_data/$APP/log/topic_log/$do_date' into table ${APP}.ods_log_inc partition(dt='$do_date');

"

hive -e "$sql"

7.2 業(yè)務(wù)表

7.2.1 活動(dòng)信息表（全量表）ods_activity_info_full

DROP TABLE IF EXISTS ods_activity_info_full;

CREATE EXTERNAL TABLE ods_activity_info_full

(

`id` STRING COMMENT '活動(dòng)id',

`activity_name` STRING COMMENT '活動(dòng)名稱',

`activity_type` STRING COMMENT '活動(dòng)類型',

`activity_desc` STRING COMMENT '活動(dòng)描述',

`start_time` STRING COMMENT '開始時(shí)間',

`end_time` STRING COMMENT '結(jié)束時(shí)間',

`create_time` STRING COMMENT '創(chuàng)建時(shí)間',

`operate_time` STRING COMMENT '修改時(shí)間'

) COMMENT '活動(dòng)信息表'

PARTITIONED BY (`dt` STRING)

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

NULL DEFINED AS ''

LOCATION '/warehouse/gmall/ods/ods_activity_info_full/'

TBLPROPERTIES ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

7.2.2 活動(dòng)規(guī)則表（全量表）ods_activity_rule_full

7.2.3 一級(jí)品類表（全量表）ods_base_category1_full

7.2.4 二級(jí)品類表（全量表）ods_base_category2_full

7.2.5 三級(jí)品類表（全量表）ods_base_category3_full

7.2.6 編碼字典表（全量表）ods_base_dic_full

7.2.7 省份表（全量表）ods_base_province_full

7.2.8 地區(qū)表（全量表）ods_base_region_full

7.2.9 品牌表（全量表）ods_base_trademark_full

7.2.10 購物車表（全量表）ods_cart_info_full

7.2.11 優(yōu)惠券信息表（全量表）ods_coupon_info_full

7.2.12 商品平臺(tái)屬性表（全量表）ods_sku_attr_value_full

7.2.13 商品表（全量表）ods_sku_info_full

7.2.14 商品銷售屬性值表（全量表）ods_sku_sale_attr_value_full

7.2.15 SPU表（全量表）ods_spu_info_full

7.2.16 營(yíng)銷坑位表（全量表）ods_promotion_pos_full

7.2.17 營(yíng)銷渠道表（全量表）ods_promotion_refer_ful

7.2.18 購物車表（增量表）ods_cart_info_inc

7.2.19 評(píng)論表（增量表）ods_comment_info_inc

7.2.20 優(yōu)惠券領(lǐng)用表（增量表）ods_coupon_use_inc

7.2.21 收藏表（增量表）ods_favor_info_inc

7.2.22 訂單明細(xì)表（增量表）ods_order_detail_inc

7.2.23 訂單明細(xì)活動(dòng)關(guān)聯(lián)表（增量表）ods_order_detail_activity_inc

7.2.24 訂單明細(xì)優(yōu)惠券關(guān)聯(lián)表（增量表）ods_order_detail_coupon_inc

7.2.25 訂單表（增量表）ods_order_info_inc

7.2.26 退單表（增量表）ods_order_refund_info_inc

7.2.27 訂單狀態(tài)流水表（增量表）ods_order_status_log_inc

7.2.28 支付表（增量表）ods_payment_info_inc

7.2.29 退款表（增量表）ods_refund_payment_inc

7.2.30 用戶表（增量表）ods_user_info_inc

7.2.31 數(shù)據(jù)裝載腳本

1）在hadoop102的/home/atguigu/bin目錄下創(chuàng)建hdfs_to_ods_db.sh

#!/bin/bash

APP=gmall

if [ -n "$2" ] ;then

do_date=$2

else

do_date=`date -d '-1 day' +%F`

fi

load_data(){

sql=""

for i in $*; do

#判斷路徑是否存在

hadoop fs -test -e /origin_data/$APP/db/${i:4}/$do_date

#路徑存在方可裝載數(shù)據(jù)

if [[ $? = 0 ]]; then

sql=$sql"load data inpath '/origin_data/$APP/db/${i:4}/$do_date' OVERWRITE into table ${APP}.$i partition(dt='$do_date');"

fi

done

hive -e "$sql"

}

case $1 in

"ods_activity_info_full")

load_data "ods_activity_info_full"

;;

"ods_activity_rule_full")

load_data "ods_activity_rule_full"

;;

"ods_base_category1_full")

load_data "ods_base_category1_full"

;;

"ods_base_category2_full")

load_data "ods_base_category2_full"

;;

"ods_base_category3_full")

load_data "ods_base_category3_full"

;;

"ods_base_dic_full")

load_data "ods_base_dic_full"

;;

"ods_base_province_full")

load_data "ods_base_province_full"

;;

"ods_base_region_full")

load_data "ods_base_region_full"

;;

"ods_base_trademark_full")

load_data "ods_base_trademark_full"

;;

"ods_cart_info_full")

load_data "ods_cart_info_full"

;;

"ods_coupon_info_full")

load_data "ods_coupon_info_full"

;;

"ods_sku_attr_value_full")

load_data "ods_sku_attr_value_full"

;;

"ods_sku_info_full")

load_data "ods_sku_info_full"

;;

"ods_sku_sale_attr_value_full")

load_data "ods_sku_sale_attr_value_full"

;;

"ods_spu_info_full")

load_data "ods_spu_info_full"

;;

"ods_promotion_pos_full")

load_data "ods_promotion_pos_full"

;;

"ods_promotion_refer_full")

load_data "ods_promotion_refer_full"

;;

"ods_cart_info_inc")

load_data "ods_cart_info_inc"

;;

"ods_comment_info_inc")

load_data "ods_comment_info_inc"

;;

"ods_coupon_use_inc")

load_data "ods_coupon_use_inc"

;;

"ods_favor_info_inc")

load_data "ods_favor_info_inc"

;;

"ods_order_detail_inc")

load_data "ods_order_detail_inc"

;;

"ods_order_detail_activity_inc")

load_data "ods_order_detail_activity_inc"

;;

"ods_order_detail_coupon_inc")

load_data "ods_order_detail_coupon_inc"

;;

"ods_order_info_inc")

load_data "ods_order_info_inc"

;;

"ods_order_refund_info_inc")

load_data "ods_order_refund_info_inc"

;;

"ods_order_status_log_inc")

load_data "ods_order_status_log_inc"

;;

"ods_payment_info_inc")

load_data "ods_payment_info_inc"

;;

"ods_refund_payment_inc")

load_data "ods_refund_payment_inc"

;;

"ods_user_info_inc")

load_data "ods_user_info_inc"

;;

"all")

load_data "ods_activity_info_full" "ods_activity_rule_full" "ods_base_category1_full" "ods_base_category2_full" "ods_base_category3_full" "ods_base_dic_full" "ods_base_province_full" "ods_base_region_full" "ods_base_trademark_full" "ods_cart_info_full" "ods_coupon_info_full" "ods_sku_attr_value_full" "ods_sku_info_full" "ods_sku_sale_attr_value_full" "ods_spu_info_full" "ods_promotion_pos_full" "ods_promotion_refer_full" "ods_cart_info_inc" "ods_comment_info_inc" "ods_coupon_use_inc" "ods_favor_info_inc" "ods_order_detail_inc" "ods_order_detail_activity_inc" "ods_order_detail_coupon_inc" "ods_order_info_inc" "ods_order_refund_info_inc" "ods_order_status_log_inc" "ods_payment_info_inc" "ods_refund_payment_inc" "ods_user_info_inc"

;;

esac

8數(shù)倉開發(fā)之DIM層

DIM層設(shè)計(jì)要點(diǎn)：

（1）DIM層的設(shè)計(jì)依據(jù)是維度建模理論，該層存儲(chǔ)維度模型的維度表。

（2）DIM層的數(shù)據(jù)存儲(chǔ)格式為orc列式存儲(chǔ)+snappy壓縮。

（3）DIM層表名的命名規(guī)范為dim_表名_全量表或者拉鏈表標(biāo)識(shí)（full/zip）。

8.1 商品維度表dim_sku_full

1）建表語句

DROP TABLE IF EXISTS dim_sku_full;

CREATE EXTERNAL TABLE dim_sku_full

(

`id` STRING COMMENT 'SKU_ID',

`price` DECIMAL(16, 2) COMMENT '商品價(jià)格',

`sku_name` STRING COMMENT '商品名稱',

`sku_desc` STRING COMMENT '商品描述',

`weight` DECIMAL(16, 2) COMMENT '重量',

`is_sale` BOOLEAN COMMENT '是否在售',

`spu_id` STRING COMMENT 'SPU編號(hào)',

`spu_name` STRING COMMENT 'SPU名稱',

`category3_id` STRING COMMENT '三級(jí)品類ID',

`category3_name` STRING COMMENT '三級(jí)品類名稱',

`category2_id` STRING COMMENT '二級(jí)品類id',

`category2_name` STRING COMMENT '二級(jí)品類名稱',

`category1_id` STRING COMMENT '一級(jí)品類ID',

`category1_name` STRING COMMENT '一級(jí)品類名稱',

`tm_id` STRING COMMENT '品牌ID',

`tm_name` STRING COMMENT '品牌名稱',

`sku_attr_values` ARRAY

value_id :STRING,

attr_name :STRING,

value_name:STRING>> COMMENT '平臺(tái)屬性',

`sku_sale_attr_values` ARRAY

sale_attr_value_id :STRING,

sale_attr_name :STRING,

sale_attr_value_name:STRING>> COMMENT '銷售屬性',

`create_time` STRING COMMENT '創(chuàng)建時(shí)間'

) COMMENT '商品維度表'

PARTITIONED BY (`dt` STRING)

STORED AS ORC

LOCATION '/warehouse/gmall/dim/dim_sku_full/'

TBLPROPERTIES ('orc.compress' = 'snappy');

2）數(shù)據(jù)裝載

我們知道，ODS層表的數(shù)據(jù)來源于原始業(yè)務(wù)表，只是剔除了某些不需要的字段。因此在考慮DIM和DWD層表的數(shù)據(jù)裝載時(shí)，對(duì)原始表和ODS層表分析是一樣的。本項(xiàng)目可能會(huì)基于業(yè)務(wù)數(shù)據(jù)庫的原表分析，特此說明。下文不再贅述。

（1）思路分析

① 分區(qū)規(guī)劃

為了避免下游查詢時(shí)執(zhí)行全表掃描，按天分區(qū)，分區(qū)字段取值為當(dāng)天日期。

② 數(shù)據(jù)流向

商品維度相關(guān)的原始表都做了全量采集，我們只需要獲取ODS層數(shù)據(jù)源表當(dāng)日分區(qū)的數(shù)據(jù)，寫回當(dāng)日分區(qū)即可。

③ 數(shù)據(jù)裝載

商品維度相關(guān)的業(yè)務(wù)表有八張：sku_info，spu_info，base_trademark，base_category1_info，

base_category2_info，base_category3_info，sku_attr_value，sku_sale_attr_value。這些表都做了全量采集，在ODS層有相應(yīng)的原始表與之對(duì)應(yīng)?；诰S度建模理論，我們要確定主維表，將商品維度相關(guān)的原始表關(guān)聯(lián)起來。維度表的粒度與主維表保持一致，后者的主鍵就是維度表的唯一標(biāo)識(shí)。在下游的DWS層或ADS層，我們會(huì)用事實(shí)表去關(guān)聯(lián)維度表，補(bǔ)充維度信息，即維度關(guān)聯(lián)。顯然，如果事實(shí)表中保存了維度表的唯一標(biāo)識(shí)，維度關(guān)聯(lián)更加易于實(shí)現(xiàn)，如交易域下單事務(wù)事實(shí)表保留了sku_id，如果以sku_info作為主維表，可以很方便地通過sku_id完成關(guān)聯(lián)。因此，通常要保證主維表的主鍵字段存在于絕大多數(shù)相關(guān)事實(shí)表中。此外，在數(shù)據(jù)倉庫中，維度表的粒度越細(xì)，保留的信息越多，下游可以做的分析就越豐富。

綜上，選擇ods_sku_info_full作為商品維度主表。

（2）執(zhí)行步驟

① 通過CTE表達(dá)式定義臨時(shí)表

CTE 是 Common Table Expressions 的縮寫，是 SQL 中一種用于創(chuàng)建臨時(shí)結(jié)果集的語法結(jié)構(gòu)。CTE 提供了一種更清晰、更易讀的方式來組織復(fù)雜的 SQL 查詢語句，使得查詢邏輯更加模塊化和可維護(hù)。

CTE 主要用于兩個(gè)方面：

遞歸查詢：CTE 可以用于執(zhí)行遞歸查詢，即在一個(gè)查詢中引用自身，以處理具有層次結(jié)構(gòu)的數(shù)據(jù)。 復(fù)雜查詢：CTE 可以用于將復(fù)雜的查詢分解為多個(gè)步驟，并將每個(gè)步驟的結(jié)果存儲(chǔ)在臨時(shí)表中，以便后續(xù)查詢引用。

CTE 通常使用 WITH 關(guān)鍵字定義，并且可以在同一個(gè)查詢中定義多個(gè) CTE。每個(gè) CTE 可以在后續(xù)的查詢語句中被引用。

sku子查詢：從ods_sku_info_full表中選取當(dāng)日（'2022-06-08'）數(shù)據(jù)，篩選每個(gè)SKU的id、價(jià)格、名稱、描述、重量、是否上架、所屬的SPU的id、所屬的第三級(jí)品類的id、所屬的品牌的id、創(chuàng)建時(shí)間等字段。spu子查詢：從ods_spu_info_full表中選取當(dāng)日（'2022-06-08'）數(shù)據(jù)，篩選每個(gè)SPU的id和名稱。c3子查詢：從ods_base_category3_full表中選取當(dāng)日（'2022-06-08'）數(shù)據(jù)，篩選每個(gè)第三級(jí)品類的id、名稱和所屬的第二級(jí)品類的id。c2子查詢：從ods_base_category2_full表中選取當(dāng)日（'2022-06-08'）數(shù)據(jù)，篩選每個(gè)第二級(jí)品類的id、名稱和所屬的第一級(jí)品類的id。c1子查詢：從ods_base_category1_full表中選取當(dāng)日（'2022-06-08'）數(shù)據(jù)，篩選每個(gè)第一級(jí)品類的id和名稱。tm子查詢：從ods_base_trademark_full表中選取當(dāng)日（'2022-06-08'）數(shù)據(jù)，篩選每個(gè)品牌的id和名稱。attr子查詢：從ods_sku_attr_value_full表中選取當(dāng)日（'2022-06-08'）數(shù)據(jù)，篩選每個(gè)SKU的屬性及其取值，使用collect_set函數(shù)對(duì)相同的SKU的屬性進(jìn)行了合并，生成了一個(gè)名為attrs的數(shù)組。sale_attr子查詢：從ods_sku_sale_attr_value_full表中選取當(dāng)日（'2022-06-08'）數(shù)據(jù)，篩選每個(gè)SKU的銷售屬性及其取值，使用collect_set函數(shù)對(duì)相同的SKU的銷售屬性進(jìn)行了合并，生成了一個(gè)名為sale_attrs的數(shù)組。

② 最后，以sku子查詢?yōu)橹鞅恚瑢藗€(gè)臨時(shí)表關(guān)聯(lián)起來，關(guān)聯(lián)字段及條件如下。

與spu子查詢通過spu_id等值連接與c3子查詢通過category3_id關(guān)聯(lián)與c2子查詢通過category2_id關(guān)聯(lián)與c1子查詢通過category1_id關(guān)聯(lián)與tm子查詢通過tm_id關(guān)聯(lián)與attr子查詢通過sku_id關(guān)聯(lián)與sale_attr子查詢通過sku_id關(guān)聯(lián)

其中，與spu、c3、c2、c1、tm的關(guān)聯(lián)使用left?join和join是等價(jià)的，full?[outer] join和right?join等價(jià)的，因?yàn)閟ku一定存在對(duì)應(yīng)的spu、一級(jí)、二級(jí)、三級(jí)品類和品牌，主表中不存在不滿足關(guān)聯(lián)條件的數(shù)據(jù)。而這些從表中不滿足關(guān)聯(lián)條件的數(shù)據(jù)是無用的，因此應(yīng)選擇left?join或join。此處選擇left?join，這是因?yàn)?，?shù)據(jù)的數(shù)據(jù)經(jīng)歷多個(gè)環(huán)節(jié)的傳遞很可能丟失，如果使用join，當(dāng)從表數(shù)據(jù)丟失時(shí)，與之匹配的主表數(shù)據(jù)會(huì)被舍棄，導(dǎo)致關(guān)聯(lián)結(jié)果的整條數(shù)據(jù)丟失。而使用left?join，則關(guān)聯(lián)結(jié)果中整條數(shù)據(jù)得以保留，只是取自從表的字段為null，可以減少數(shù)據(jù)丟失帶來的損失，且便于發(fā)現(xiàn)和定位問題。因而，left?join和join等價(jià)時(shí)，選用left?join。下文同理，不再贅述。

接下來關(guān)聯(lián)最后兩個(gè)子查詢，sku可能不存在屬性或銷售屬性，在這兩個(gè)子查詢中未必有滿足關(guān)聯(lián)條件的數(shù)據(jù)，此時(shí)主表數(shù)據(jù)需要保留，因此要選用left?join或full?join，而這兩個(gè)子查詢中不滿足關(guān)聯(lián)條件的數(shù)據(jù)不應(yīng)保留，只能選擇left?join。

最后選取所需字段，寫入dim_sku_full表的當(dāng)日分區(qū)。

（3）圖解

1分區(qū)規(guī)劃

2數(shù)據(jù)流向

3數(shù)據(jù)裝載

代碼實(shí)現(xiàn)

collect_set 函數(shù)用于聚合操作，它將一個(gè)列中的所有不同的值收集到一個(gè)集合中，并且去重。collect_set 函數(shù)通常用于對(duì)某列中的數(shù)據(jù)進(jìn)行去重后的統(tǒng)計(jì)或者分析。

8.2 優(yōu)惠券維度表dim_coupon_full

只展示與前不同點(diǎn)

③ 數(shù)據(jù)裝載

我們只會(huì)用到字典表的編碼和名稱兩個(gè)字段，單獨(dú)建表意義不大，應(yīng)做維度退化。優(yōu)惠券相關(guān)的原始業(yè)務(wù)表只有coupon_info，不需要確定主維表和相關(guān)維表。因此，只須關(guān)聯(lián)ods_coupon_info_full與ods_base_dic_full。

（2）執(zhí)行步驟

① 從ods_coupon_info_full表中篩選2022-06-08分區(qū)的數(shù)據(jù)，選取需要的字段，如優(yōu)惠券名稱、優(yōu)惠券類型、使用條件等。

② 將①中獲取的數(shù)據(jù)與ods_base_dic_full關(guān)聯(lián)獲取優(yōu)惠券類型名稱和優(yōu)惠范圍類型名稱。關(guān)聯(lián)字段分別為優(yōu)惠券類型和優(yōu)惠范圍類型，這兩種編碼在字典表中一定有對(duì)應(yīng)記錄，而字典表中其它編碼對(duì)應(yīng)記錄對(duì)我們是沒有意義的，因此以ods_coupon_info_full作為主表，應(yīng)使用內(nèi)連接或左外連接。

③ 通過case?when… then… end語法根據(jù)不同的優(yōu)惠券類型，生成不同的優(yōu)惠規(guī)則字符串，記為benefit_rule字段。

④ 最終，將查詢結(jié)果覆蓋寫入dim_coupon_full的2022-06-08分區(qū)。

8.3 活動(dòng)維度表dim_activity_full

③ 數(shù)據(jù)裝載

活動(dòng)相關(guān)的原始業(yè)務(wù)表有activity_rule、activity_info，此外，為了獲取活動(dòng)類型名稱，還需要關(guān)聯(lián)字典表。activity_rule中記錄了活動(dòng)的規(guī)則描述，activity_info記錄了活動(dòng)描述，用戶下單時(shí)，每條明細(xì)記錄都可能參與活動(dòng)，order_detail_activity（訂單活動(dòng)關(guān)聯(lián)表）記錄了這些信息，該表中記錄的是每個(gè)SKU具體參與了那次活動(dòng)，滿足了該活動(dòng)的哪條規(guī)則，因此，要讓事實(shí)表與活動(dòng)維度進(jìn)行關(guān)聯(lián)，活動(dòng)維度表的粒度應(yīng)細(xì)化至活動(dòng)規(guī)則粒度。綜上，以activity_rule作為主表。

（2）執(zhí)行步驟

① 從ods_activity_rule_full表中篩選2022-06-08分區(qū)的數(shù)據(jù)，并選取需要的字段，子查詢記為rule。

② 從ods_activity_info_full 表中篩選2022-06-08分區(qū)的數(shù)據(jù)，并選取需要的字段，子查詢記為info。

③?篩選字典表2022-06-08分區(qū)的數(shù)據(jù)。

④ 以rule作為主表，通過activity_id關(guān)聯(lián)info。活動(dòng)規(guī)則和活動(dòng)信息的每一條數(shù)據(jù)都可以在另一張表中找到對(duì)應(yīng)記錄，因此inner join、left?join、right?join、full?outer?join都是等價(jià)的，任選一種即可。此處選擇left?join。

⑤ 與8.2同理，關(guān)聯(lián)字段表獲取編碼名稱字段選擇left?join。

⑥ 篩選字段，寫入dim_activity_full的2022-06-08分區(qū)

8.4 地區(qū)維度表dim_province_full

③ 數(shù)據(jù)裝載

原始業(yè)務(wù)數(shù)據(jù)庫中與地區(qū)相關(guān)的表有base_province和base_region，二者通過region_id產(chǎn)生聯(lián)系。下單等業(yè)務(wù)過程相關(guān)的表中都通過province_id字段與地區(qū)維度產(chǎn)生關(guān)聯(lián)，顯然base_province應(yīng)為主維表。

（2）執(zhí)行步驟

① 篩選ods_base_province_full表2022-06-08分區(qū)的數(shù)據(jù)，選取所須字段，子查詢記為province。

② 篩選ods_base_region_full表2022-06-08分區(qū)的數(shù)據(jù)，選取所須字段，子查詢記為region。

③ 通過region_id將province和region關(guān)聯(lián)起來。兩張表不存在不滿足關(guān)聯(lián)條件的數(shù)據(jù)，inner join、left?join、right?join、full?outer?join都是一樣的，此處選用left join。

④ 選取所須字段，寫入dim_province_full表的2022-06-08分區(qū)。

8.5 營(yíng)銷坑位維度表dim_promotion_pos_full

③ 數(shù)據(jù)裝載

原始業(yè)務(wù)數(shù)據(jù)庫中只有promotion_pos表與營(yíng)銷坑位維度相關(guān)，從ods_promotion_pos_full表中篩選2022-06-08分區(qū)的數(shù)據(jù)，選擇所須字段寫入dim_promotion_pos_full表的2022-06-08分區(qū)即可。

8.6 營(yíng)銷渠道維度表dim_promotion_refer_full

③ 數(shù)據(jù)裝載

原始業(yè)務(wù)數(shù)據(jù)庫中只有promotion_refer表與營(yíng)銷渠道維度相關(guān)，從ods_promotion_refer_full表中篩選2022-06-08分區(qū)的數(shù)據(jù)，選取所須字段寫入dim_promotion_refer_full表的2022-06-08分區(qū)即可。

8.7 日期維度表dim_date

通常情況下，時(shí)間維度表的數(shù)據(jù)并不是來自于業(yè)務(wù)系統(tǒng)，而是手動(dòng)寫入，并且由于時(shí)間維度表數(shù)據(jù)的可預(yù)見性，無須每日導(dǎo)入，一般可一次性導(dǎo)入一年的數(shù)據(jù)。

（1）創(chuàng)建臨時(shí)表

DROP TABLE IF EXISTS tmp_dim_date_info;

CREATE EXTERNAL TABLE tmp_dim_date_info (

`date_id` STRING COMMENT '日',

`week_id` STRING COMMENT '周ID',

`week_day` STRING COMMENT '周幾',

`day` STRING COMMENT '每月的第幾天',

`month` STRING COMMENT '第幾月',

`quarter` STRING COMMENT '第幾季度',

`year` STRING COMMENT '年',

`is_workday` STRING COMMENT '是否是工作日',

`holiday_id` STRING COMMENT '節(jié)假日'

) COMMENT '時(shí)間維度表'

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

LOCATION '/warehouse/gmall/tmp/tmp_dim_date_info/';

（2）將數(shù)據(jù)文件上傳到HFDS上臨時(shí)表路徑/warehouse/gmall/tmp/tmp_dim_date_info

（3）執(zhí)行以下語句將其導(dǎo)入時(shí)間維度表

insert overwrite table dim_date select * from tmp_dim_date_info;

（4）檢查數(shù)據(jù)是否導(dǎo)入成功

select * from dim_date;

8.8 用戶維度表dim_user_zip

2）數(shù)據(jù)裝載

（1）思路分析

① 分區(qū)規(guī)劃

拉鏈表的分區(qū)有兩類：9999-12-31分區(qū)和普通日期分區(qū)。前者保存最新的維度數(shù)據(jù)，后者保存有效期截至分區(qū)日期的數(shù)據(jù)。

② 數(shù)據(jù)流向

a）首日

業(yè)務(wù)數(shù)據(jù)庫的user_info表中記錄了全量最新的用戶數(shù)據(jù)，全部進(jìn)入9999-12-31分區(qū)。

b）每日

從數(shù)倉上線次日開始，采集user_info的變更數(shù)據(jù)，通常業(yè)務(wù)數(shù)據(jù)庫的數(shù)據(jù)不會(huì)被刪除，因而用戶信息的變更只有新增和修改兩類。新增的用戶信息進(jìn)入9999-12-31分區(qū)，修改的用戶信息覆蓋9999-12-31分區(qū)的記錄，并將歷史數(shù)據(jù)寫入前一分區(qū)（如某用戶信息在2022-06-09發(fā)生變化，當(dāng)日的歷史用戶信息有效期截至2022-06-08，進(jìn)入2022-06-08分區(qū)）。

③ 數(shù)據(jù)裝載

首日數(shù)據(jù)裝載：篩選所須字段，對(duì)敏感信息加密脫敏，寫入9999-12-31分區(qū)即可。

每日數(shù)據(jù)裝載較為復(fù)雜。首先要考慮到，用戶的數(shù)據(jù)可能在一天內(nèi)多次變化，而拉鏈表中對(duì)于同一個(gè)用戶每天至多只會(huì)維護(hù)一條數(shù)據(jù)，因此只須保留同一用戶每天的最后一次更改。此處不需要區(qū)分新增和修改操作，我們只要獲取同一用戶當(dāng)天最晚的一次操作就可以獲取其最新狀態(tài)。

接下來，要將當(dāng)日發(fā)生變更的用戶信息與歷史所有用戶的最新信息（拉鏈表9999-12-31分區(qū)的數(shù)據(jù)）合并起來。最后，在9999-12-31分區(qū)保留每個(gè)用戶最新的狀態(tài)，并將過期數(shù)據(jù)寫入當(dāng)日分區(qū)。

（2）知識(shí)儲(chǔ)備

①?regexp：用法：A?regexp?B，AB均為字符串，A是待匹配的字符串，B是正則表達(dá)式。若A、B其中之一為null則返回false。若A中的任意子串可以匹配B則返回true，否則返回false。如果要讓整個(gè)A串與B匹配才返回true，需要在B中通過^和$限定A開頭和結(jié)尾的字符。

② rlike：等價(jià)于regexp。

（3）執(zhí)行步驟

① 首日裝載

a）篩選ods_user_info_inc表當(dāng)日分區(qū)的數(shù)據(jù)，對(duì)用戶姓名、電話號(hào)碼、郵箱地址做脫敏處理。其中電話號(hào)碼、郵箱地址在加密前要校驗(yàn)格式是否合法，若不合法則返回null。用戶的姓名可能被用于姓氏相關(guān)的統(tǒng)計(jì)，保留姓氏，手機(jī)號(hào)可能用于運(yùn)營(yíng)商相關(guān)統(tǒng)計(jì)，保留前三位，郵箱可能用于郵箱類型相關(guān)統(tǒng)計(jì)，保留@及之后的內(nèi)容。

b）定義兩個(gè)字段：start_date和end_date。

start_date：用戶信息的生效起始日期，首日裝載時(shí)所有數(shù)據(jù)的start_date均為數(shù)倉上線起始日期。end_date：用戶信息的過期日期。首日裝載時(shí)所有用戶信息均為當(dāng)日最新，過期時(shí)間取極大值：9999-12-31。

c）篩選統(tǒng)計(jì)所須的其它字段，寫入9999-12-31分區(qū)。

② 每日裝載

a）t1子查詢

篩選ods_user_info_inc表當(dāng)日分區(qū)的數(shù)據(jù)，開窗，按照用戶ID分區(qū)，數(shù)據(jù)變更時(shí)間降序排列，調(diào)用row_number()函數(shù)編號(hào)，記為rn。顯然，同一用戶當(dāng)天最晚的操作記錄rn為1。

b）t2子查詢

從t1子查詢中篩選rn為1的數(shù)據(jù)，獲取當(dāng)日信息發(fā)生新增或變更的所有用戶最新的狀態(tài)，字段處理與首日相同。此時(shí)獲得的子查詢數(shù)據(jù)結(jié)構(gòu)與dim_user_zip表相同，記為tmp。

本項(xiàng)目約定的數(shù)倉上線首日為2022-06-08，假設(shè)統(tǒng)計(jì)日期為2022-06-11，此時(shí)過期分區(qū)2022-06-08、2022-06-09、2022-06-10分別存儲(chǔ)了當(dāng)日過期的用戶數(shù)據(jù)，2022-06-11新增和變化的用戶數(shù)據(jù)不會(huì)影響到上述三個(gè)分區(qū)已過期的數(shù)據(jù)，只會(huì)對(duì)9999-12-31分區(qū)保存的用戶最新狀態(tài)產(chǎn)生影響。因此，我們只須關(guān)心9999-12-31分區(qū)即可。篩選該分區(qū)數(shù)據(jù)，與tmp子查詢union，即t2子查詢。

c）t3子查詢

t2子查詢包含了昨日和當(dāng)日最新的用戶數(shù)據(jù)，它們將進(jìn)入兩個(gè)分區(qū)：i）所有用戶的最新信息進(jìn)入9999-12-31分區(qū)；ii）當(dāng)日發(fā)生變更因而過期的用戶歷史信息進(jìn)入昨日分區(qū)，如統(tǒng)計(jì)日期為2022-06-09，則當(dāng)天過期的用戶數(shù)據(jù)有效日期截至2022-06-08，進(jìn)入2022-06-08分區(qū)。

那么，如何確定數(shù)據(jù)所屬分區(qū)？根據(jù)數(shù)據(jù)變更情況，可以將用戶分為三類，如下。

昨日存在且當(dāng)日信息未變更的用戶。

當(dāng)日新增的用戶。

當(dāng)日信息發(fā)生變更的用戶。

前兩類用戶只對(duì)應(yīng)一條數(shù)據(jù)，這條數(shù)據(jù)就是用戶的最新信息，寫入9999-12-31分區(qū)即可。第三種用戶對(duì)應(yīng)兩條數(shù)據(jù)，其中，當(dāng)日變更數(shù)據(jù)為最新的用戶信息，應(yīng)進(jìn)入9999-12-31分區(qū)，而昨日9999-12-31分區(qū)的數(shù)據(jù)已過期，應(yīng)進(jìn)入昨日分區(qū)。

考慮下一個(gè)問題，如何實(shí)現(xiàn)數(shù)據(jù)分流？執(zhí)行如下操作：開窗，按照用戶ID分區(qū)，start_date降序排列，調(diào)用row_number()編號(hào)。我們會(huì)發(fā)現(xiàn)，t2子查詢中每個(gè)用戶最多只有兩條數(shù)據(jù)，編號(hào)不超過2，因?yàn)閷?duì)于每一用戶當(dāng)日的變更操作只取最新的一條，9999-12-31分區(qū)也只會(huì)對(duì)每個(gè)用戶保留一條數(shù)據(jù)。編號(hào)為1的是用戶的最新信息，編號(hào)為2的是用戶的過期數(shù)據(jù)。將前者寫入9999-12-31分區(qū)，后者寫入昨日分區(qū)即可。

最后，所有數(shù)據(jù)的start_date均為其新增或變化的日期。初始的end_date均為9999-12-31。過期數(shù)據(jù)的end_date變更為昨日，未過期數(shù)據(jù)的end_date仍為9999-12-31。不難發(fā)現(xiàn)，每條數(shù)據(jù)的end_date與dt一致。end_date或dt的計(jì)算邏輯有兩種：

如果編號(hào)為1，記為9999-12-31，否則記為昨日。如果編號(hào)為2，記為昨日，否則為9999-12-31。

任選一種計(jì)算邏輯，按照dt字段動(dòng)態(tài)分區(qū)將數(shù)據(jù)寫入所屬分區(qū)。

圖解

1分區(qū)規(guī)劃

2數(shù)據(jù)流向

3數(shù)據(jù)裝載

首日裝載和每日裝載腳本分別寫

9 數(shù)倉開發(fā)之DWD層

DWD層設(shè)計(jì)要點(diǎn)：

（1）DWD層的設(shè)計(jì)依據(jù)是維度建模理論，該層存儲(chǔ)維度模型的事實(shí)表。

（2）DWD層的數(shù)據(jù)存儲(chǔ)格式為orc列式存儲(chǔ)+snappy壓縮。

（3）DWD層表名的命名規(guī)范為dwd_數(shù)據(jù)域_表名_單分區(qū)增量全量標(biāo)識(shí)（inc/full）

9.1 交易域加購事務(wù)事實(shí)表dwd_trade_cart_add_inc

1）建表語句

DROP TABLE IF EXISTS dwd_trade_cart_add_inc;

CREATE EXTERNAL TABLE dwd_trade_cart_add_inc

(

`id` STRING COMMENT '編號(hào)',

`user_id` STRING COMMENT '用戶ID',

`sku_id` STRING COMMENT 'SKU_ID',

`date_id` STRING COMMENT '日期ID',

`create_time` STRING COMMENT '加購時(shí)間',

`sku_num` BIGINT COMMENT '加購物車件數(shù)'

) COMMENT '交易域加購事務(wù)事實(shí)表'

PARTITIONED BY (`dt` STRING)

STORED AS ORC

LOCATION '/warehouse/gmall/dwd/dwd_trade_cart_add_inc/'

TBLPROPERTIES ('orc.compress' = 'snappy');

2）數(shù)據(jù)裝載

1）思路分析

① 分區(qū)規(guī)劃

為了避免全表掃描，事務(wù)事實(shí)表也要按天分區(qū)，數(shù)據(jù)應(yīng)進(jìn)入業(yè)務(wù)過程發(fā)生日期對(duì)應(yīng)的分區(qū)。如2022-06-08發(fā)生的加購操作進(jìn)入2022-06-08分區(qū)。

② 數(shù)據(jù)流向

事務(wù)事實(shí)表的數(shù)據(jù)源表都做了增量采集，數(shù)倉上線首日?qǐng)?zhí)行一次全表掃描獲取歷史全量數(shù)據(jù)，從第二日開始只采集每日變更數(shù)據(jù)。事實(shí)表上游ODS層表的首日分區(qū)可能包含多天的業(yè)務(wù)操作，這些數(shù)據(jù)應(yīng)進(jìn)入事實(shí)表的不同分區(qū)，第二日及之后的分區(qū)只包含當(dāng)日的業(yè)務(wù)操作，應(yīng)進(jìn)入事實(shí)表的當(dāng)日分區(qū)。

③ 數(shù)據(jù)裝載

首日和每日裝載數(shù)據(jù)集和數(shù)據(jù)流向的不同，導(dǎo)致了二者處理邏輯的差異，需要分開處理。加購操作發(fā)生時(shí)，只會(huì)導(dǎo)致業(yè)務(wù)庫cart_info表的數(shù)據(jù)發(fā)生變化，因而本節(jié)事實(shí)表的數(shù)據(jù)來源于cart_info的變更記錄，應(yīng)從ods_cart_info_inc表中讀取數(shù)據(jù)。

a）首日裝載

獲取ods_cart_add_inc表首日分區(qū)的數(shù)據(jù)，寫入加購操作發(fā)生日期對(duì)應(yīng)的分區(qū)即可。

b）每日裝載

獲取ods_cart_add_inc當(dāng)日分區(qū)的數(shù)據(jù)，寫入事實(shí)表當(dāng)日分區(qū)。

（2）知識(shí)儲(chǔ)備

from_utc_timestamp({any primitive type} ts, string timezone)：將UTC時(shí)間戳ts轉(zhuǎn)化為給定時(shí)區(qū)的timestamp類型數(shù)據(jù)，展示為YYYY-MM-DD HH:MM:SS.fffffffff格式的日期字符串。ts可以是任意原生類型，包括timestamp/date，tinyint/smallint/init/bigint，float/double和decimal。如果ts是小數(shù)會(huì)被視為秒級(jí)時(shí)間戳，如果是整數(shù)會(huì)被視為毫秒時(shí)間戳。timezone是用于指明時(shí)區(qū)的字符串，可以用IANA時(shí)區(qū)數(shù)據(jù)中的時(shí)區(qū)名稱或時(shí)區(qū)偏移量表示，如東八區(qū)可以用GMT+8或Asia/Shanghai表示。

（3）執(zhí)行步驟

① 首日裝載

從ods_cart_info_inc中篩選2022-06-08分區(qū)的數(shù)據(jù)。首日?qǐng)?zhí)行maxwell-bootstrap命令做全表掃描，將掃描到的數(shù)據(jù)交給Maxwell進(jìn)程封裝為JSON字符串，這些數(shù)據(jù)的操作類型分為bootstrap-start、bootstrap-insert、bootstrap-complete三類，第一類和第三類分別標(biāo)記了全表掃描任務(wù)的開始和結(jié)束，只有操作類型為第二類的JSON包含了統(tǒng)計(jì)所須的數(shù)據(jù)，?

接下來，如何從ods_cart_info_inc中提取加購操作？加購操作發(fā)生時(shí)，若加購表沒有該用戶對(duì)該sku的加購記錄，就會(huì)新增一條數(shù)據(jù)，create_time為加購時(shí)間；否則修改數(shù)據(jù)，將sku_num更改為加購之后的值，此時(shí)update_time為加購時(shí)間。業(yè)務(wù)數(shù)據(jù)庫沒有記錄cart_info的變更操作，首日?qǐng)?zhí)行全表掃描只能獲取第一種加購記錄，我們認(rèn)為每條記錄都對(duì)應(yīng)一次加購操作，create_time為加購時(shí)間，sku_num為加購商品數(shù)。這樣做存在誤差，不可避免。

最后，篩選所須字段，基于create_time計(jì)算加購日期，作為分區(qū)字段，通過動(dòng)態(tài)分區(qū)將數(shù)據(jù)寫入加購日期對(duì)應(yīng)分區(qū)即可。

② 每日裝載

每日裝載相對(duì)簡(jiǎn)單。只需要獲取ods_cart_info_inc當(dāng)日分區(qū)的數(shù)據(jù)處理后寫入事實(shí)表當(dāng)日分區(qū)即可。需要注意的是，從第二日開始我們可以采集到兩類加購操作，如下。

操作類型為insert，加購時(shí)間為create_time。操作類型為update且sku_num大于變更前的sku_num。當(dāng)前的sku_num存儲(chǔ)在data字段下，變更前的sku_num存儲(chǔ)在old字段下。加購時(shí)間為update_time。

圖解

分區(qū)規(guī)劃

數(shù)據(jù)流向

9.2 交易域下單事務(wù)事實(shí)表dwd_trade_order_detail_inc

③ 數(shù)據(jù)裝載

用戶執(zhí)行一次下單操作時(shí)，業(yè)務(wù)庫的order_info表會(huì)新增一條數(shù)據(jù)，order_detail表新增一至多條數(shù)據(jù)，如果參加了活動(dòng)，order_detail_activity新增一至多條數(shù)據(jù)，如果使用了優(yōu)惠券，order_detail_coupon新增一至多條數(shù)據(jù)。因此，本節(jié)的事實(shí)數(shù)據(jù)來源于ods_order_detail_inc、ods_order_info_inc、ods_order_detail_activity_inc、ods_order_detail_coupon_inc。下單業(yè)務(wù)過程的最細(xì)粒度是一個(gè)用戶對(duì)一個(gè)sku的下單記錄，order_detail的粒度與之相同，因而將ods_order_detail_inc作為主表關(guān)聯(lián)其余三張表，與各從表的關(guān)聯(lián)方式及關(guān)聯(lián)字段如下。

ods_order_info_inc：關(guān)聯(lián)字段為order_id。訂單明細(xì)記錄一定有對(duì)應(yīng)的訂單記錄，反之亦然，因此join、left?join、right?join和full?join都是等價(jià)的，此處選擇left?join。ods_order_detail_activity_inc：關(guān)聯(lián)字段為order_detail_id。下單未必參與活動(dòng)，因此主表數(shù)據(jù)在該表可能并沒有滿足關(guān)聯(lián)條件的數(shù)據(jù)，要保留主表獨(dú)有數(shù)據(jù)，只能用left join或full?join。該表中并不存在不滿足關(guān)聯(lián)條件的數(shù)據(jù)，因?yàn)橛唵蚊骷?xì)活動(dòng)關(guān)聯(lián)表的order_detail_id必然不為null，因而left?join等價(jià)于full?join，此處選擇left join。ods_order_detail_coupon_inc：關(guān)聯(lián)字段為order_detail_id。關(guān)聯(lián)方式的分析與ods_order_detail_activity_inc同理，選擇left?join，不再贅述。

a）首日裝載

篩選上文提到的四張ODS表首日分區(qū)數(shù)據(jù)，關(guān)聯(lián)起來，根據(jù)下單日期動(dòng)態(tài)分區(qū)寫入交易域下單事務(wù)事實(shí)表。

b）每日裝載

篩選上述四張ODS表每日分區(qū)數(shù)據(jù)，關(guān)聯(lián)后寫入當(dāng)日分區(qū)。

（2）執(zhí)行步驟

① 首日裝載

a）子查詢

篩選上述ODS層表首日分區(qū)的數(shù)據(jù)，其中，四張下單事實(shí)相關(guān)的表都做了增量采集，首日要做全量同步，僅保留操作類型為bootstrap-insert類型的數(shù)據(jù)，不需要額外的過濾條件。從這些ODS層表中選擇需要的字段，獲得四個(gè)子查詢：od、oi、act和cou，分別對(duì)應(yīng)ods_order_detail_inc、ods_order_info_inc、ods_order_detail_activity_inc和ods_order_detail_coupon_inc。

b）按照上文提到的關(guān)聯(lián)方式和條件將a）中得到的子查詢關(guān)聯(lián)起來，取自ods_order_detail_inc的create_time字段即下單時(shí)間，格式化為日期字符串，作為分區(qū)字段dt，篩選所須字段，通過dt動(dòng)態(tài)分區(qū)寫入dwd_trade_order_detail_inc。

② 每日裝載

篩選上述ODS層表首日分區(qū)的數(shù)據(jù)。通常業(yè)務(wù)數(shù)據(jù)庫的原始表不會(huì)執(zhí)行刪除操作，增量表采集到的數(shù)據(jù)操作只有兩種類型insert和update。由上文分析可知，上述四張表只有insert操作類型的數(shù)據(jù)與下單業(yè)務(wù)過程相關(guān)，因此僅保留這些表操作類型為insert的數(shù)據(jù)?；谶@些表獲得子查詢（處理邏輯與首日相同），關(guān)聯(lián)在一起。需要注意的是，每日裝載無須動(dòng)態(tài)分區(qū)，將數(shù)據(jù)寫入當(dāng)日分區(qū)即可。

9.3 交易域支付成功事務(wù)事實(shí)表dwd_trade_pay_detail_suc_inc

③ 數(shù)據(jù)裝載

支付成功操作發(fā)生時(shí)，原始業(yè)務(wù)庫的order_info表和payment_info表會(huì)發(fā)生變化，因此，本節(jié)需要獲取ods_order_info_inc和ods_payment_info_inc的數(shù)據(jù)。此外，我們還需要訂單明細(xì)等下單業(yè)務(wù)過程相關(guān)的表。支付成功業(yè)務(wù)過程最細(xì)粒度為一個(gè)用戶對(duì)一個(gè)sku（商品項(xiàng)）的支付成功操作，payment_info和order_info的粒度只能具體到訂單，要拿到原子操作，就必須關(guān)聯(lián)訂單明細(xì)表，要知道活動(dòng)和優(yōu)惠券參與情況，還要關(guān)聯(lián)訂單明細(xì)活動(dòng)關(guān)聯(lián)和訂單明細(xì)優(yōu)惠券關(guān)聯(lián)表。最后，我們只會(huì)用到字典表的編碼和名稱兩個(gè)字段，單獨(dú)建維度表意義不大。因此做維度退化，將編碼名稱退化到事實(shí)表中。此處關(guān)聯(lián)字典表將支付類型名稱退化到事實(shí)表中。

找到這些表對(duì)應(yīng)的ODS層表，將它們關(guān)聯(lián)起來，主表應(yīng)為粒度最細(xì)的ods_order_detail_inc，與9.2相比，本節(jié)新增了ods_payment_info_inc、ods_base_dic（用于退化支付類型名稱字段），其他表關(guān)聯(lián)方式和關(guān)聯(lián)條件與9.2相同，不再贅述。

ods_payment_info_inc：通過order_id關(guān)聯(lián)。訂單明細(xì)表中有很多未支付成功的數(shù)據(jù)，應(yīng)舍棄，關(guān)聯(lián)方式應(yīng)為join或right?join。我們會(huì)從ods支付表中篩選支付成功的數(shù)據(jù)（下文詳述），這部分?jǐn)?shù)據(jù)一定有對(duì)應(yīng)的訂單明細(xì)，因而join等價(jià)于right?join，此處選擇join。ods_base_dic_full：通過主表的payment_type和字段表的dic_code字段關(guān)聯(lián)，補(bǔ)全支付類型名稱字段。字典表中有很多不滿足關(guān)聯(lián)條件的數(shù)據(jù)，即與支付類型無關(guān)的編碼，這部分?jǐn)?shù)據(jù)與本節(jié)無關(guān)，應(yīng)舍棄。此外，主表payment_type的取值一定是字典表dic_code的子集，left?join等價(jià)于join，選擇left?join（參見上文）。考慮一個(gè)問題：發(fā)生在2022-06-05的支付成功操作應(yīng)與同一天的字典表數(shù)據(jù)關(guān)聯(lián)，也就是說關(guān)聯(lián)時(shí)還應(yīng)限制主從表的dt字段相同。為什么此處沒有這個(gè)條件？這是因?yàn)闃I(yè)務(wù)數(shù)據(jù)庫沒有記錄歷史數(shù)據(jù)的功能，我們無法獲取首日之前的字典表數(shù)據(jù)，只能退而求其次，全部與字典表的最新狀態(tài)（當(dāng)日全量采集的字典表數(shù)據(jù)）關(guān)聯(lián)。因此，這里是存在一定誤差的。

最后，選擇所須字段，寫入對(duì)應(yīng)分區(qū)即可。

a）首日裝載

篩選ODS表首日數(shù)據(jù)，關(guān)聯(lián)后根據(jù)支付成功日期動(dòng)態(tài)分區(qū)寫入本節(jié)事實(shí)表。

b）每日裝載

篩選ODS表每日數(shù)據(jù)，關(guān)聯(lián)后寫入本節(jié)事實(shí)表當(dāng)日分區(qū)。

（2）執(zhí)行步驟

① 首日裝載

a）子查詢

支付成功業(yè)務(wù)過程發(fā)生時(shí)，payment_info表數(shù)據(jù)會(huì)發(fā)生變化，payment_status字段的值由1601變更為1602。首日全量同步掃描全表，ods_payment_info_inc的首日分區(qū)沒有記錄變更操作，不能通過上述條件篩選。實(shí)際上，payment_status變更為1602之后數(shù)據(jù)就不會(huì)再修改了，只要篩選滿足這一條件的數(shù)據(jù)即可。該條數(shù)據(jù)的update_time或callback_time（回調(diào)時(shí)間）即支付成功時(shí)間，本項(xiàng)目選用callback_time作為支付成功時(shí)間。此外，還要限定操作類型為bootstrap-insert，分區(qū)為首日，獲取的子查詢記為pi。

用于退化支付類型名稱的子查詢記為pay_dic，支付類型編碼的父級(jí)編碼為11，根據(jù)該條件從字典表首日分區(qū)篩選與支付類型相關(guān)的記錄即可。

其余子查詢與9.2相同。

b）關(guān)聯(lián)

按照上文所述關(guān)聯(lián)，選取所須字段即可。取自ods_payment_info_inc的callback_time即支付成功時(shí)間，格式化為支付成功日期，作為分區(qū)字段，動(dòng)態(tài)分區(qū)寫入本節(jié)事實(shí)表即可。

② 每日裝載

a）子查詢

每日同步可以采集到增量表的數(shù)據(jù)變化，ods_payment_info_inc的過濾條件更改：操作類型為update、payment_status為1602，且更改字段包含了payment_status（判斷old字段下是否包含該字段即可）。實(shí)際上，第三個(gè)條件是可以省略的，因?yàn)閜ayment_status變更為1602之后該條數(shù)據(jù)就不會(huì)再發(fā)生改變了，所以若操作類型為update，且變更后的payment_status為1602，payment_status字段一定發(fā)生了更改。通過上述條件，篩選當(dāng)日分區(qū)的數(shù)據(jù)，選擇所須字段即可得到pi子查詢。

字典表子查詢處理邏輯與首日相同。

其余四張表都是下單業(yè)務(wù)過程相關(guān)的表，過濾條件相同。通常，用戶下單后必須在30分鐘內(nèi)支付成功，否則訂單會(huì)被取消，因此支付成功和下單業(yè)務(wù)過程發(fā)生的時(shí)間差在0到 30分鐘之間。當(dāng)日的支付成功操作需要關(guān)聯(lián)的訂單明細(xì)數(shù)據(jù)可能屬于昨日分區(qū)，因此我們要篩選當(dāng)日和昨日兩個(gè)分區(qū)的數(shù)據(jù)。操作類型為insert或bootstrap-insert，這是因?yàn)樽蛉湛赡転閿?shù)倉上線首日，后者的操作類型均為bootstrap-insert。這兩個(gè)條件要同時(shí)滿足。再限定分區(qū)為當(dāng)日，即可獲得四個(gè)下單相關(guān)的子查詢。

b）關(guān)聯(lián)

將a）中得到的六個(gè)子查詢關(guān)聯(lián)起來，關(guān)聯(lián)方式及條件與首日裝載相同，寫入當(dāng)日分區(qū)即可。

9.4?交易域購物車周期快照事實(shí)表dwd_trade_cart_full

1）思路分析

① 分區(qū)規(guī)劃

周期快照事實(shí)表記錄原始業(yè)務(wù)表每日的全量狀態(tài)，每日一分區(qū)。

② 數(shù)據(jù)流向

數(shù)據(jù)從ods_cart_info_full每日分區(qū)進(jìn)入dwd_trade_cart_full的當(dāng)日分區(qū)。

③ 數(shù)據(jù)裝載

從ods_cart_info_full當(dāng)日分區(qū)篩選未下單的數(shù)據(jù)（is_ordered為0，已下單的數(shù)據(jù)打標(biāo)記，該字段為1，不應(yīng)計(jì)入存量統(tǒng)計(jì)），選取所須字段寫入當(dāng)日分區(qū)即可。

9.5 交易域交易流程累積快照事實(shí)表dwd_trade_trade_flow_acc

2）數(shù)據(jù)裝載

（1）思路分析

累積快照事實(shí)表與拉鏈表的處理邏輯非常相似，可以互為參考。

① 分區(qū)規(guī)劃

累積快照事實(shí)表是為了處理涉及同一業(yè)務(wù)流程多個(gè)業(yè)務(wù)過程的指標(biāo)而設(shè)計(jì)的。它的分區(qū)規(guī)劃與拉鏈表完全相同，其中，9999-12-31分區(qū)存儲(chǔ)的是業(yè)務(wù)流程尚未結(jié)束的所有數(shù)據(jù)，普通分區(qū)存儲(chǔ)的是業(yè)務(wù)流程在當(dāng)日結(jié)束的數(shù)據(jù)。

② 數(shù)據(jù)流向

a）首日裝載

與拉鏈表不同，累積快照事實(shí)表的首日裝載也需要?jiǎng)討B(tài)分區(qū)。因?yàn)闅v史數(shù)據(jù)中一定會(huì)有業(yè)務(wù)流程已結(jié)束的數(shù)據(jù)，這部分?jǐn)?shù)據(jù)應(yīng)進(jìn)入流程結(jié)束當(dāng)日分區(qū)，也會(huì)有未完成的數(shù)據(jù)，進(jìn)入9999-12-31分區(qū)。

b）每日裝載

業(yè)務(wù)流程在當(dāng)日結(jié)束的數(shù)據(jù)進(jìn)入當(dāng)日分區(qū)，未完成的數(shù)據(jù)進(jìn)入9999-12-31分區(qū)。

③ 數(shù)據(jù)裝載

本節(jié)僅篩選交易流程的三個(gè)關(guān)鍵結(jié)點(diǎn)：下單、支付、確認(rèn)收貨。下單時(shí)間通過order_info的create_time字段獲取，支付操作通過payment_info的callback_time字段獲取，參考上文，不再贅述。order_status_log記錄了訂單表狀態(tài)的變更明細(xì)，確認(rèn)收貨時(shí)該表會(huì)插入一條狀態(tài)為1006的數(shù)據(jù)，create_time即確認(rèn)收貨時(shí)間。

下單、支付、確認(rèn)收貨是按照時(shí)間順序依次發(fā)生的，因此，在任意時(shí)刻下單數(shù)據(jù)中order_id的集合包含已支付order_id的集合，后者又包含已確認(rèn)收貨order_id的集合，顯然，應(yīng)將取自order_info表的子查詢作為主表，通過order_id字段left?join支付成功數(shù)據(jù)，通過order_id字段left?join確認(rèn)收貨數(shù)據(jù)。

最后，選擇所須字段寫入對(duì)應(yīng)分區(qū)即可。

a）首日裝載

篩選ODS層相關(guān)表首日分區(qū)數(shù)據(jù)，關(guān)聯(lián)后寫入所屬分區(qū)。確認(rèn)收貨時(shí)間即業(yè)務(wù)流程結(jié)束時(shí)間，若該字段不為null則說明業(yè)務(wù)流程已完成，進(jìn)入收貨日期對(duì)應(yīng)分區(qū)，否則進(jìn)入9999-12-31分區(qū)。

b）每日裝載

篩選ODS層相關(guān)表每日分區(qū)數(shù)據(jù)，與9999-12-31分區(qū)數(shù)據(jù)union、關(guān)聯(lián)后（下文詳述）寫入所屬分區(qū)。

（2）執(zhí)行步驟

① 首日裝載

a）子查詢

篩選上述ods_order_info_inc、ods_payment_info_inc、ods_order_status_log_inc表首日分區(qū)操作類型為bootstrap-insert類型的數(shù)據(jù)。其中，ods_payment_info_inc還要限定payment_status為1602（支付成功狀態(tài)），ods_order_status_log_inc要限定order_status為1006（已完成，即確認(rèn)收貨）。按照前文所述關(guān)聯(lián)。

b）關(guān)聯(lián)

篩選所須字段，處理時(shí)間字段。其中，下單時(shí)間為取自訂單表的create_time，支付成功時(shí)間為取自支付表的callback_time，確認(rèn)收貨時(shí)間取自訂單流水表的create_time。如果確認(rèn)收貨時(shí)間不為null，說明業(yè)務(wù)流程結(jié)束，將其格式化為yyyy-MM-dd格式，作為分區(qū)字段dt的值，否則業(yè)務(wù)流程未結(jié)束，dt取值為9999-12-31。按照dt動(dòng)態(tài)分區(qū)寫入本節(jié)事實(shí)表。

② 每日裝載

a）子查詢

與首日裝載相比篩選條件有所不同，如下。

ods_order_info_inc：篩選首日分區(qū)操作類型為insert的數(shù)據(jù)。ods_payment_info_inc：篩選首日分區(qū)操作類型為update、payment_status為1602且變更字段包含了payment_status（該條件可省略，參見9.3節(jié)說明）的數(shù)據(jù)。所得子查詢記為pi。ods_order_status_log_inc：篩選首日分區(qū)操作類型為insert，且order_status為1006的數(shù)據(jù)。所得子查詢記為log。

與首日裝載不同，每日裝載要考慮前日的9999-12-31分區(qū)，這部分?jǐn)?shù)據(jù)并沒有完成業(yè)務(wù)流程，仍可能發(fā)生變更。我們需要將該分區(qū)數(shù)據(jù)與上述三個(gè)業(yè)務(wù)過程當(dāng)日新增的記錄合并，有兩種實(shí)現(xiàn)思路，如下。

i）第一步，根據(jù)上文所述篩選條件將下單、支付、確認(rèn)收貨業(yè)務(wù)過程當(dāng)日數(shù)據(jù)分別作為子查詢，關(guān)聯(lián)起來，關(guān)聯(lián)方式及條件、字段處理邏輯與首日相同。

第二步，篩選9999-12-31分區(qū)數(shù)據(jù)，該分區(qū)數(shù)據(jù)分為兩種：已下單未支付，已支付未收貨（已收貨則業(yè)務(wù)流程結(jié)束，進(jìn)入收貨當(dāng)日分區(qū)）。第一類記錄和當(dāng)日新增的下單記錄處理邏輯完全相同，與pi、log子查詢left?join即可。第二類記錄已支付，因而在pi子查詢中一定不會(huì)有對(duì)應(yīng)記錄，left?join關(guān)聯(lián)log即可。根據(jù)上述分析，似乎我們需要將9999-12-31分區(qū)的數(shù)據(jù)分成兩部分處理，比較麻煩。實(shí)際上，這兩類數(shù)據(jù)的處理完全可以統(tǒng)一，對(duì)于第二類數(shù)據(jù)，pi表中一定不存在滿足關(guān)聯(lián)條件的記錄，left?join pi得到的子查詢與關(guān)聯(lián)前的主表完全一致，因此對(duì)于這部分?jǐn)?shù)據(jù)，分別與pi、log關(guān)聯(lián)的結(jié)果和僅關(guān)聯(lián)log的結(jié)果是一樣的。綜上，我們只需要把第一步子查詢的主表替換為9999-12-31分區(qū)的數(shù)據(jù)即可。

第一步和第二步獲得的子查詢數(shù)據(jù)結(jié)構(gòu)完全相同，union在一起，通過dt字段動(dòng)態(tài)分區(qū)寫入本節(jié)事實(shí)表即可。

ii）觀察方法i）的實(shí)現(xiàn)步驟，可以發(fā)現(xiàn)當(dāng)日新增下單記錄和昨日未完成數(shù)據(jù)（9999-12-31分區(qū)數(shù)據(jù)）處理邏輯完全一致，可以用union運(yùn)算符將二者合并起來，作為oi子查詢，而后與pi及l(fā)og通過left?join關(guān)聯(lián)即可。需要注意的是，待union的兩部分子查詢數(shù)據(jù)結(jié)構(gòu)不同，新增的下單記錄缺少五個(gè)字段，需要用null值或0.0填充，處理如下。

payment_date_id：在當(dāng)日新增下單記錄子查詢中補(bǔ)null。對(duì)于9999-12-31分區(qū)未完成的歷史數(shù)據(jù)（以下簡(jiǎn)稱歷史數(shù)據(jù)），若前日已支付，則oi子查詢中的支付日期不為null，直接獲取，否則可能在當(dāng)日支付，也可能不支付，無論哪種情況此時(shí)獲取pi子查詢中的支付日期即可。對(duì)于當(dāng)日新增的下單記錄，oi子查詢中的支付日期為null，最終的支付日期與pi子查詢中的支付日期保持一致。綜上，關(guān)聯(lián)后取數(shù)邏輯為nvl(oi.payment_date_id,pi.payment_date_id)。payment_time：在當(dāng)日新增下單記錄子查詢中補(bǔ)null，關(guān)聯(lián)后取數(shù)邏輯為nvl(oi.payment_time,pi.payment_time)。思路同上。finish_date_id：在當(dāng)日新增下單記錄子查詢中補(bǔ)null，關(guān)聯(lián)后取數(shù)邏輯為nvl(oi.finish_date_id,log.finish_date_id)。與支付日期同理。finish_time：在當(dāng)日新增下單記錄子查詢中補(bǔ)null，關(guān)聯(lián)后取數(shù)邏輯為nvl(oi.finish_time,log.finish_time)。與支付時(shí)間同理。payment_amount：在當(dāng)日新增下單記錄子查詢中補(bǔ)0.0。對(duì)于9999-12-31分區(qū)未完成的歷史數(shù)據(jù)（以下簡(jiǎn)稱歷史數(shù)據(jù)），若前日已支付，則oi子查詢中的支付金額就是我們要的值，直接獲取。否則可能在當(dāng)日支付，也可能不支付，對(duì)于前者，獲取pi子查詢中的支付金額即可，對(duì)于后者，pi子查詢中的支付金額為null，應(yīng)置為0.0，因次要在獲取pi.payment_amount后做空值處理，若為null則置為0.0。對(duì)于當(dāng)日新增的下單記錄，oi子查詢中的支付金額為0.0，支付金額應(yīng)取自pi子查詢的payment_amount字段，若該字段不為null則直接獲取，否則做空值處理，同上。綜上，關(guān)聯(lián)后取數(shù)邏輯為nvl(if(oi.payment_amount = 0.0, pi.payment_amount, oi.payment_amount), 0.0)。

最后，篩選所須字段，生成分區(qū)字段，寫入事實(shí)表。分區(qū)字段取數(shù)邏輯如下。

oi子查詢的兩類數(shù)據(jù)的完成日期一定為null：第一類數(shù)據(jù)位于9999-12-31分區(qū)，業(yè)務(wù)流程未完成，確認(rèn)收貨日期一定為null，第二類數(shù)據(jù)的完成日期是我們補(bǔ)充的null值。因而只需要判斷l(xiāng)og子查詢中的完成日期即可。若log. finish_date_id不為null，說明流程在當(dāng)日結(jié)束，數(shù)據(jù)寫入當(dāng)日分區(qū)，否則流程未完成，寫入9999-12-31分區(qū)。綜上，分區(qū)字段計(jì)算邏輯為：nvl(log.finish_date_id,'9999-12-31')。

本節(jié)選用方法ii）完成每日裝載。

③ 特殊情況說明

實(shí)際上，上述交易流程并不是線性的，從下單到確認(rèn)收貨的任何一步都可能取消訂單，確認(rèn)收貨和取消訂單都可以終結(jié)交易流程。對(duì)于這類非線性過程的處理，通常有兩種處理方式，如下。

a）將確認(rèn)收貨和取消訂單都作為業(yè)務(wù)流程結(jié)束的標(biāo)志，將數(shù)據(jù)寫入業(yè)務(wù)流程結(jié)束日期對(duì)應(yīng)的分區(qū)。

b）如果取消訂單，將數(shù)據(jù)永久地保留在9999-12-31分區(qū)，未達(dá)到的業(yè)務(wù)過程對(duì)應(yīng)里程碑字段置空。

兩種方式均可，本節(jié)選擇的是Plan?B。生產(chǎn)環(huán)境如何處理需要綜合考慮下游需求、企業(yè)硬件資源等因素，選取合適的方案。

9.6?工具域優(yōu)惠券使用(支付)事務(wù)事實(shí)表dwd_tool_coupon_used_inc

③ 數(shù)據(jù)裝載

用戶使用優(yōu)惠券支付時(shí)，業(yè)務(wù)數(shù)據(jù)庫的coupon_use表的used_time會(huì)由null值變?yōu)橹Ц稌r(shí)間，即優(yōu)惠券使用（支付）時(shí)間。根據(jù)這個(gè)條件，我們可以篩選目標(biāo)數(shù)據(jù)，將數(shù)據(jù)寫入業(yè)務(wù)過程發(fā)生日期對(duì)應(yīng)分區(qū)。

（2）執(zhí)行步驟

① 首日裝載

從ods_coupon_use_inc表篩選首日分區(qū)操作類型為bootstrap-insert，且date.used_time不為null的數(shù)據(jù)，將used_time格式化為yyyy-MM-dd格式的日期字符串，生成date_id和dt字段。篩選所須字段，根據(jù)dt字段動(dòng)態(tài)分區(qū)將數(shù)據(jù)寫入事實(shí)表。

② 每日裝載

從ods_coupon_use_inc表篩選每日分區(qū)操作類型為update，且old字段下的keys包含了used_time的數(shù)據(jù)（更改了used_time字段）。該條件等價(jià)于date.used_time不為null，因?yàn)橛脩羰褂脙?yōu)惠券支付后，coupon_use表中的對(duì)應(yīng)記錄就不會(huì)再發(fā)生改變，只要used_time字段不為null，且操作類型為update，則used_time字段一定發(fā)生了更改。

最后，將used_time格式化為yyyy-MM-dd格式的日期字符串，生成date_id字段。篩選所須字段，將數(shù)據(jù)寫入事實(shí)表當(dāng)日分區(qū)。

9.7?互動(dòng)域收藏商品事務(wù)事實(shí)表dwd_interaction_favor_add_inc

③ 數(shù)據(jù)裝載

用戶收藏商品時(shí)，業(yè)務(wù)數(shù)據(jù)庫的favor_info表會(huì)新增一條收藏記錄，create_time即收藏操作發(fā)生時(shí)間。

（2）執(zhí)行步驟

① 首日裝載

從ods_favor_info_inc表篩選首日分區(qū)操作類型為bootstrap-insert的數(shù)據(jù)，將create_time格式化為yyyy-MM-dd格式的日期字符串，生成date_id和dt字段。篩選所須字段，根據(jù)dt字段動(dòng)態(tài)分區(qū)將數(shù)據(jù)寫入事實(shí)表。

② 每日裝載

從ods_?favor_info_inc表篩選每日分區(qū)操作類型為insert的數(shù)據(jù)，將used_time格式化為yyyy-MM-dd格式的日期字符串，生成date_id字段。篩選所須字段，將數(shù)據(jù)寫入事實(shí)表當(dāng)日分區(qū)。

9.8?流量域頁面瀏覽事務(wù)事實(shí)表dwd_traffic_page_view_inc

（1）思路分析

① 分區(qū)規(guī)劃

按天分區(qū)，數(shù)據(jù)進(jìn)入頁面日志生成時(shí)間對(duì)應(yīng)分區(qū)。

② 數(shù)據(jù)流向

埋點(diǎn)服務(wù)通常是在企業(yè)成立大數(shù)據(jù)部門之后開始的，因而數(shù)倉上線時(shí)，日志一般沒有歷史數(shù)據(jù)，每日生成的頁面日志數(shù)據(jù)進(jìn)入當(dāng)日分區(qū)。

③ 數(shù)據(jù)裝載

因?yàn)闆]有歷史數(shù)據(jù)，因而日志數(shù)據(jù)的首日和每日處理邏輯相同，不作區(qū)分。只有頁面日志的page字段不為null，據(jù)此篩選頁面日志。

（2）執(zhí)行步驟

篩選ods_log_inc當(dāng)日分區(qū)的頁面日志數(shù)據(jù)，將時(shí)間戳字段轉(zhuǎn)換為東八區(qū)的日期格式化字符串，補(bǔ)充時(shí)間字段，選取所須字段寫入事實(shí)表當(dāng)日分區(qū)。

9.9 用戶域用戶注冊(cè)事務(wù)事實(shí)表dwd_user_register_inc

② 數(shù)據(jù)流向

a）首日裝載

本節(jié)部分?jǐn)?shù)據(jù)來源于業(yè)務(wù)系統(tǒng)，部分?jǐn)?shù)據(jù)來源于日志。日志是沒有歷史數(shù)據(jù)的，數(shù)倉上線首日，獲取來自業(yè)務(wù)系統(tǒng)的所有注冊(cè)記錄與當(dāng)日的注冊(cè)日志關(guān)聯(lián)以補(bǔ)充維度信息，只有注冊(cè)時(shí)間在當(dāng)日注冊(cè)記錄存在對(duì)應(yīng)的日志數(shù)據(jù)，維度字段不為null，其余注冊(cè)記錄的維度字段均為null。關(guān)聯(lián)后的數(shù)據(jù)根據(jù)注冊(cè)日期進(jìn)入注冊(cè)當(dāng)日分區(qū)。

b）每日裝載

獲取來源于業(yè)務(wù)系統(tǒng)的當(dāng)日注冊(cè)記錄，與當(dāng)日注冊(cè)日志關(guān)聯(lián)后寫入當(dāng)日分區(qū)即可。

③ 數(shù)據(jù)裝載

用戶注冊(cè)時(shí)，業(yè)務(wù)數(shù)據(jù)庫的user_info表會(huì)新增一條數(shù)據(jù)，create_time即注冊(cè)時(shí)間。同時(shí)，會(huì)生成uid不為null的注冊(cè)頁面日志。除uid外，日志的common字段還記錄了其它維度信息，如品牌、渠道、省份、操作系統(tǒng)等，我們可以通過用戶ID將業(yè)務(wù)庫的注冊(cè)數(shù)據(jù)和日志關(guān)聯(lián)起來，補(bǔ)充注冊(cè)時(shí)的相關(guān)維度信息。

a）首日裝載

篩選ods_user_info_inc首日分區(qū)的數(shù)據(jù)，及ods_log_inc首日分區(qū)的注冊(cè)頁面日志，通過user_id將二者關(guān)聯(lián)起來。要注意，注冊(cè)日期在首日之前的注冊(cè)記錄無法拿到頁面數(shù)據(jù)，對(duì)于這部分?jǐn)?shù)據(jù)，取自日志的維度字段只能置空。

b）每日裝載

篩選ods_user_info_inc每日分區(qū)的數(shù)據(jù)，ods_log_inc每日分區(qū)的注冊(cè)頁面日志，通過user_id關(guān)聯(lián)。日志在傳輸過程中可能丟失，因此取自日志的維度字段也可能為空。

（2）執(zhí)行步驟

① 首日裝載

a）子查詢

篩選ods_user_info_inc首日分區(qū)操作類型為bootstrap-insert的數(shù)據(jù)，作為ui子查詢。篩選ods_log_inc首日分區(qū)頁面ID為register，且uid不為null的數(shù)據(jù)，作為log子查詢。

b）上文提到，注冊(cè)操作可能并沒有對(duì)應(yīng)的頁面日志，因此以u(píng)i為主表，left?join關(guān)聯(lián)log，篩選所須字段，根據(jù)用戶表的創(chuàng)建時(shí)間生成yyyy-MM-dd格式的日期字符串，作為分區(qū)字段dt，根據(jù)dt動(dòng)態(tài)分區(qū)將數(shù)據(jù)寫入注冊(cè)當(dāng)日分區(qū)。

② 每日裝載

a）子查詢

篩選ods_user_info_inc每日分區(qū)操作類型為insert的數(shù)據(jù)，作為ui子查詢。篩選ods_log_inc每日分區(qū)頁面ID為register，且uid不為null的數(shù)據(jù)，作為log子查詢。

b）上文提到，注冊(cè)操作對(duì)應(yīng)的頁面日志可能丟失，因此以u(píng)i為主表，left?join關(guān)聯(lián)log，篩選所須字段，將數(shù)據(jù)寫入當(dāng)日分區(qū)。

9.10?用戶域用戶登錄事務(wù)事實(shí)表dwd_user_login_inc

2）數(shù)據(jù)裝載

（1）思路分析

① 分區(qū)規(guī)劃

登錄操作取自日志。一次會(huì)話是指從打開App到關(guān)閉App的一次完整業(yè)務(wù)流程。會(huì)話內(nèi)第一個(gè)uid不為null的頁面就對(duì)應(yīng)一次登錄操作。分區(qū)規(guī)劃同9.8。

?

（2）執(zhí)行步驟

① 篩選當(dāng)日分區(qū)page字段不為null的數(shù)據(jù)，且user_id不為null的數(shù)據(jù)，獲取用戶處于登錄狀態(tài)的頁面日志。開窗，按照會(huì)話ID（common.sid）分區(qū)，ts升序排列，調(diào)用row_number()為每條數(shù)據(jù)編號(hào)，記為rn，選取ts、user_id及其它感興趣的字段，記為t1子查詢。

③ 從t1子查詢中篩選rn為1的數(shù)據(jù)，即每個(gè)會(huì)話首個(gè)user_id不為null的頁面瀏覽記錄，對(duì)應(yīng)登錄操作，選取所須字段，記為t2子查詢。

④ 根據(jù)t2子查詢中的ts字段生成登錄日期和登錄時(shí)間字段，與其它感興趣的字段一并寫入當(dāng)日分區(qū)。

9.11 數(shù)據(jù)裝載腳本

首日每日分別寫腳本

10數(shù)倉開發(fā)之DWS層

設(shè)計(jì)要點(diǎn)：

（1）DWS層的設(shè)計(jì)參考指標(biāo)體系。

（2）DWS層的數(shù)據(jù)存儲(chǔ)格式為orc列式存儲(chǔ)+snappy壓縮。

（3）DWS層表名的命名規(guī)范為dws_數(shù)據(jù)域_統(tǒng)計(jì)粒度_業(yè)務(wù)過程_統(tǒng)計(jì)周期（1d/nd/td）。

注：1d表示最近1日，nd表示最近n日，td表示歷史至今。

10.1 最近1日匯總表

10.1.1 交易域用戶商品粒度訂單最近1日匯總表dws_trade_user_sku_order_1d

2）數(shù)據(jù)裝載

（1）思路分析

① 分區(qū)規(guī)劃

按天分區(qū)，避免全表掃描。

② 數(shù)據(jù)流向

1日匯總表需要對(duì)歷史數(shù)據(jù)做匯總，首日裝載數(shù)據(jù)取自事實(shí)表所有分區(qū)和維度表當(dāng)日分區(qū)（維度表沒有歷史數(shù)據(jù)），匯總后進(jìn)入下單日期對(duì)應(yīng)分區(qū)，每日裝載數(shù)據(jù)取自事實(shí)表和維度表當(dāng)日分區(qū)，匯總后寫入當(dāng)日分區(qū)。

③ 數(shù)據(jù)裝載

本節(jié)須匯總各用戶各sku最近1日的下單次數(shù)、下單件數(shù)、下單原始金額、活動(dòng)優(yōu)惠金額、優(yōu)惠券優(yōu)惠金額、最終金額，匯總后要補(bǔ)充sku的品類、品牌等維度信息。事實(shí)數(shù)據(jù)取自dwd_trade_order_detail_inc，維度數(shù)據(jù)取自dim_sku_full。

a）首日裝載

需要對(duì)歷史全量數(shù)據(jù)做統(tǒng)計(jì)，匯總截止到首日每一天的統(tǒng)計(jì)指標(biāo)。下文同理，不再贅述。

b）每日裝載

匯總當(dāng)日數(shù)據(jù)，計(jì)算最近1日的統(tǒng)計(jì)指標(biāo)即可。下文同理，不再贅述。

（2）執(zhí)行步驟

① 首日裝載

a）od子查詢

從數(shù)據(jù)源事實(shí)表首日分區(qū)取數(shù)，按照dt、user_id、sku_id分組聚合，統(tǒng)計(jì)所求指標(biāo)。

b）sku子查詢

篩選dim_sku_full首日分區(qū)數(shù)據(jù)即可。

c）關(guān)聯(lián)

od子查詢記錄了最關(guān)鍵的數(shù)據(jù)（統(tǒng)計(jì)結(jié)果），將之作為主表，關(guān)聯(lián)sku的目的是為了補(bǔ)全維度信息，顯然要通過sku_id字段關(guān)聯(lián)。理論上，訂單表中的sku_id在從表中一定有記錄，因此left?join和join是等價(jià)的，full?join和right?join是等價(jià)的，而從表中無法與主表關(guān)聯(lián)的數(shù)據(jù)沒有意義，因此選擇left?join或join，此處選用left?join。

最后，選取所須字段，按照od的dt字段動(dòng)態(tài)分區(qū)寫入本節(jié)匯總表即可。

② 每日裝載

與首日相比只有三處不同，如下。

a）od子查詢只取當(dāng)日數(shù)據(jù)，分組字段中沒有dt。

b）sku子查詢篩選當(dāng)日數(shù)據(jù)。

c）寫入?yún)R總表當(dāng)日分區(qū)。

b）每日裝載

與首日裝載邏輯基本一致，區(qū)別在于od子查詢只取dwd_trade_order_detail_inc當(dāng)日分區(qū)的數(shù)據(jù)

10.1.2 交易域用戶粒度訂單最近1日匯總表dws_trade_user_order_1d

10.1.3 交易域用戶粒度加購最近1日匯總表dws_trade_user_cart_add_1d

10.1.4 交易域用戶粒度支付最近1日匯總表dws_trade_user_payment_1d

10.1.5 交易域省份粒度訂單最近1日匯總表dws_trade_province_order_1d

10.1.6 工具域用戶優(yōu)惠券粒度優(yōu)惠券使用(支付)最近1日匯總表dws_tool_user_coupon_coupon_used_1d

10.1.7 互動(dòng)域商品粒度收藏商品最近1日匯總表dws_interaction_sku_favor_add_1d

10.1.8 流量域會(huì)話粒度頁面瀏覽最近1日匯總表dws_traffic_session_page_view_1d

10.1.9 流量域訪客頁面粒度頁面瀏覽最近1日匯總表dws_traffic_page_visitor_page_view_1d

10.1.10 數(shù)據(jù)裝載腳本

首日每日分別裝載

10.2 最近n日匯總表

10.2.1 交易域用戶商品粒度訂單最近n日匯總表dws_trade_user_sku_order_nd

2）數(shù)據(jù)裝載

（1）思路分析

① 分區(qū)規(guī)劃

通常，我們不會(huì)做歷史某天最近n日的匯總統(tǒng)計(jì)，因此，這些表的分區(qū)是從首日開始的，每日分區(qū)記錄當(dāng)日的匯總結(jié)果，此外，最近n日匯總表數(shù)據(jù)裝載選取的數(shù)據(jù)源時(shí)間范圍相同，處理邏輯相同，不區(qū)分首日裝載和每日裝載。下文同理，不再贅述。

② 數(shù)據(jù)流向

n日匯總表的數(shù)據(jù)可以取自事實(shí)表，也可以取自統(tǒng)計(jì)粒度相同而統(tǒng)計(jì)周期不同的1日匯總表（前提是存在對(duì)應(yīng)的1日匯總表），顯然應(yīng)選擇后者，復(fù)用1日匯總表的統(tǒng)計(jì)結(jié)果，減少重復(fù)計(jì)算。

本節(jié)匯總表存在對(duì)應(yīng)的1日匯總表，篩選后者最近n日分的數(shù)據(jù)匯總后寫入n日匯總表當(dāng)日分區(qū)。

③ 數(shù)據(jù)裝載

????????本節(jié)匯總最近7日和最近30日各用戶各商品下單次數(shù)、件數(shù)、原始金額、活動(dòng)優(yōu)惠金額、優(yōu)惠券優(yōu)惠金額和最終金額。事實(shí)數(shù)據(jù)取自dwd_trade_order_detail_inc，維度數(shù)據(jù)取自dim_sku_full。我們知道，匯總表的構(gòu)建是基于指標(biāo)體系完成的，業(yè)務(wù)過程、統(tǒng)計(jì)周期、統(tǒng)計(jì)粒度相同的指標(biāo)會(huì)由同一張匯總表計(jì)算得到。業(yè)務(wù)過程相同則數(shù)據(jù)源表相同，統(tǒng)計(jì)周期相同篩選數(shù)據(jù)的時(shí)間范圍相同，統(tǒng)計(jì)粒度相同則統(tǒng)計(jì)時(shí)的分組字段相同，剩余的指標(biāo)構(gòu)成組分：度量、聚合邏輯、業(yè)務(wù)限定（通過聚合函數(shù)加條件判斷結(jié)合使用實(shí)現(xiàn)，如sum(if())）均可通過不同的字段實(shí)現(xiàn)。綜上，業(yè)務(wù)過程、統(tǒng)計(jì)周期和統(tǒng)計(jì)粒度相同的指標(biāo)，統(tǒng)計(jì)時(shí)SQL的數(shù)據(jù)源表（from）、過濾條件（where?dt?xxx）和分組字段（group?by?xxx）相同，可以通過一個(gè)SQL子查詢完成統(tǒng)計(jì)而不需要關(guān)聯(lián)其它的事實(shí)數(shù)據(jù)，清晰明了。

本節(jié)將最近7日和最近30日的指標(biāo)放在了一張表中，違反了上述規(guī)范，為什么這么做？此處主要是考慮到這兩種指標(biāo)除統(tǒng)計(jì)周期外全部相同，計(jì)算邏輯一致，為了減少匯總表的數(shù)量，便于管理，就將它們放在了一張表中。

有兩種方式將兩類指標(biāo)整合到同一張表中：通過一個(gè)字段標(biāo)記統(tǒng)計(jì)周期，或者為每個(gè)統(tǒng)計(jì)周期生成一份指標(biāo)字段。此處選擇后者。

生產(chǎn)環(huán)境可能執(zhí)行補(bǔ)數(shù)操作（重跑歷史某天的任務(wù)），因此需要實(shí)現(xiàn)冪等寫入，即多次執(zhí)行裝載語句，表中數(shù)據(jù)和執(zhí)行一次的結(jié)果是一樣的，否則補(bǔ)數(shù)可能會(huì)導(dǎo)致數(shù)據(jù)重復(fù)。本項(xiàng)目通過insert?overwrite語法實(shí)現(xiàn)這樣的效果。n日匯總表的數(shù)據(jù)通過每日裝載寫入當(dāng)日分區(qū)，如果使用了兩個(gè)及以上的裝載語句，后面的語句會(huì)覆蓋前面的寫入，最終只會(huì)保留最后一個(gè)SQL的執(zhí)行結(jié)果，和執(zhí)行一個(gè)裝載語句效果是一樣的。

因此，我們需要將兩類指標(biāo)的處理合并到一個(gè)裝載語句中，可以用兩個(gè)子查詢分別統(tǒng)計(jì)最近7日和最近30日的指標(biāo)，再通過唯一鍵user_id?+ sku_id將二者關(guān)聯(lián)起來。也可以在一個(gè)子查詢中完成所有指標(biāo)的計(jì)算，思路如下：篩選最近30日的事實(shí)數(shù)據(jù)，運(yùn)用聚合函數(shù)+條件判斷（如sum(if())）通過對(duì)分區(qū)字段dt的判斷，限制數(shù)據(jù)范圍在最近7日內(nèi)可得最近7日的指標(biāo)，去掉條件判斷可得最近30日的指標(biāo)。此處選擇第二種思路。

（2）執(zhí)行步驟

篩選最近30日dws_trade_user_sku_order_1d表所有分區(qū)的數(shù)據(jù)，按照user_id，sku_id及其余的維度字段分組聚合，通過sum(if())組合調(diào)用統(tǒng)計(jì)最近7日的指標(biāo)，通過sum()統(tǒng)計(jì)最近30日指標(biāo)，寫入n日匯總表當(dāng)日分區(qū)。

10.2.2 交易域省份粒度訂單最近n日匯總表dws_trade_province_order_nd

10.2.3 數(shù)據(jù)裝載腳本

無需區(qū)分首日、每日數(shù)據(jù)裝載腳本

10.3 歷史至今匯總表

10.3.1?交易域用戶粒度訂單歷史至今匯總表dws_trade_user_order_td

（1）思路分析

① 分區(qū)規(guī)劃

歷史至今匯總表按天分區(qū)，每日分區(qū)記錄截止當(dāng)日的統(tǒng)計(jì)結(jié)果，分區(qū)從數(shù)倉上線首日開始。

② 數(shù)據(jù)流向

基于截止當(dāng)日的所有業(yè)務(wù)數(shù)據(jù)和當(dāng)日維度數(shù)據(jù)匯總統(tǒng)計(jì)，統(tǒng)計(jì)結(jié)果寫入當(dāng)日分區(qū)。

③ 數(shù)據(jù)裝載

歷史至今匯總表的事實(shí)數(shù)據(jù)可以取自事實(shí)表，也可以取自統(tǒng)計(jì)粒度、業(yè)務(wù)過程相同的最近1日匯總表。顯然應(yīng)選擇后者，減少重復(fù)計(jì)算。

歷史至今匯總表首日裝載和每日裝載選取的數(shù)據(jù)范圍相同（均為歷史至今），我們可以運(yùn)用相同的計(jì)算邏輯處理，但是每日裝載可以依賴于前一天的匯總結(jié)果，這樣只需要對(duì)當(dāng)前表前日分區(qū)數(shù)據(jù)和當(dāng)日新增事實(shí)數(shù)據(jù)做計(jì)算，而不必加載歷史全量的事實(shí)數(shù)據(jù)，節(jié)省大量資源。這樣一來，首日裝載和每日裝載的計(jì)算邏輯就不同了，需要分別處理。

本節(jié)須統(tǒng)計(jì)各用戶歷史至今首次下單日期、歷史至今末次下單日期、歷史至今下單次數(shù)、歷史至今購買商品件數(shù)、歷史至今下單原始金額、歷史至今下單活動(dòng)優(yōu)惠金額、歷史至今下單優(yōu)惠券優(yōu)惠金額、歷史至今下單最終金額，寫入當(dāng)日分區(qū)。

（2）執(zhí)行步驟

① 首日裝載

讀取dws_trade_user_order_1d所有分區(qū)的數(shù)據(jù)，按照usre_id分組聚合，統(tǒng)計(jì)上述指標(biāo)，寫入首日分區(qū)。

② 每日裝載

每日裝載需要將dws_trade_user_order_td前日分區(qū)的數(shù)據(jù)和dws_trade_user_order_1d當(dāng)日分區(qū)的數(shù)據(jù)合并起來，即相同user_id的數(shù)據(jù)合并為一條，通過關(guān)聯(lián)實(shí)現(xiàn)。

顯然，關(guān)聯(lián)字段為user_id，考慮關(guān)聯(lián)方式。根據(jù)下單情況可以將用戶分為三類：當(dāng)日之前下過單且當(dāng)日沒有下單的用戶，當(dāng)日下單且當(dāng)日之前沒有下過單的用戶，當(dāng)日及之前都下過單的用戶。如果要將上文提到的兩部分?jǐn)?shù)據(jù)關(guān)聯(lián)起來，這三類用戶對(duì)應(yīng)主表中不滿足關(guān)聯(lián)條件的數(shù)據(jù)，從表中不滿足關(guān)聯(lián)條件的數(shù)據(jù)以及兩張表中滿足關(guān)聯(lián)條件的數(shù)據(jù)，它們都是需要保留的，因而用full?outer?join關(guān)聯(lián)。

a）old子查詢

讀取本節(jié)匯總表昨日分區(qū)的數(shù)據(jù)，記為old子查詢。

b）new子查詢

讀取dws_trade_user_order_1d當(dāng)日分區(qū)的數(shù)據(jù)，記為new子查詢。

c）裝載

通過user_id字段full?outer?join關(guān)聯(lián)兩個(gè)子查詢。字段處理如下。

user_id：兩張表的user_id至少有一個(gè)不為null，均不為null時(shí)取誰都一樣，因此調(diào)用nvl()當(dāng)其中一個(gè)表的user_id為null時(shí)取另一張表的user_id即可。order_date_first：若old.user_id不為null，說明單日之前有過下單記錄，首次下單日期應(yīng)為old.order_date_first。否則new.user_id必不為null，當(dāng)日必有下單記錄，為首次下單日期。order_date_last：若new.user_id不為null，說明當(dāng)日有下單記錄，末次下單日期為當(dāng)日，否則old.order_date_last不為null，當(dāng)日未下單，末次下單日期與昨日保持一致。order_count_td等度量字段：度量字段的處理邏輯相同，若為null則賦默認(rèn)值0，對(duì)nvl()處理后的兩表度量字段求和即可。

最后將數(shù)據(jù)寫入每日分區(qū)。

10.3.2?用戶域用戶粒度登錄歷史至今匯總表dws_user_user_login_td

（1）思路分析

① 分區(qū)規(guī)劃

同10.3.1。

② 數(shù)據(jù)流向

首日裝載獲取用戶登錄事實(shí)表首日分區(qū)的數(shù)據(jù)（該表沒有歷史分區(qū)，因此數(shù)倉上線首日該表只有一個(gè)分區(qū)），與用戶拉鏈表9999-12-31分區(qū)的用戶數(shù)據(jù)合并后寫入本節(jié)匯總表首日分區(qū)。每日裝載獲取匯總表前日分區(qū)和用戶登錄事實(shí)表當(dāng)日分區(qū)的數(shù)據(jù)，合并后寫入?yún)R總表當(dāng)日分區(qū)。

③ 數(shù)據(jù)裝載

本節(jié)需要統(tǒng)計(jì)各用戶歷史至今首次、末次登錄日期和累計(jì)登錄次數(shù)。登錄數(shù)據(jù)存儲(chǔ)在dwd_traffic_user_login表中，該表數(shù)據(jù)取自日志，數(shù)據(jù)不全。用戶注冊(cè)成功后自動(dòng)登錄，因此，注冊(cè)日期與首日登錄日期相同。對(duì)于登錄事實(shí)表中沒有記錄的用戶，將注冊(cè)日期作為末次登錄日期，歷史至今登錄次數(shù)記為1。

（2）執(zhí)行步驟

① 首日裝載

我們需要獲取所有用戶的注冊(cè)日期和首日所有用戶的登錄記錄。注冊(cè)日期可以從dim_user_zip或dwd_user_register_inc獲取，此處選擇用戶拉鏈表。

a）u子查詢

從dim_user_zip的9999-12-31分區(qū)獲取全量最新的用戶信息，篩選user_id，create_time字段（注冊(cè)時(shí)間）。

b）l子查詢

從dwd_user_login_inc首日分區(qū)取數(shù)（首日該表只有一個(gè)分區(qū)，不需要篩選條件），按照用戶分組聚合，統(tǒng)計(jì)行數(shù)即用戶登錄次數(shù)，dt最大值作為末次登錄日期（即當(dāng)日，沒有按照dt分組，要篩選該字段必須聚合）。

c）裝載

按照user_id關(guān)聯(lián)u和l子查詢。前者包含了全量的用戶信息，應(yīng)作為主表，關(guān)聯(lián)方式為left?join（分析過程與上文同理）。關(guān)聯(lián)后字段處理如下。

user_id：選擇u.id作為user_id。login_date_last：若右表的末次登錄日期不為null說明當(dāng)日登錄，末次登錄日期因?yàn)楫?dāng)日，否則格式化create_time為yyyy-MM-dd格式的字符串，將其作為末次登錄日期。login_date_first：格式化create_time為yyyy-MM-dd格式，作為首次登錄日期。login_count_td：如果取自l子查詢的登錄次數(shù)不為null則將其作為歷史至今登錄日期，否則記為0。

寫入當(dāng)日分區(qū)即可。

② 每日裝載

首日分區(qū)記錄了歷史全量的用戶登錄信息，不必從dim_user_zip或dwd_user_register_inc取數(shù)，只須匯總dws_user_user_login_td昨日分區(qū)和dwd_user_login當(dāng)日的登錄記錄即可。實(shí)現(xiàn)思路與10.3.1類似，不再贅述。

10.3.3 數(shù)據(jù)裝載腳本

首日每日分別編寫

11 數(shù)倉開發(fā)之ADS層

11.1 流量主題

11.1.1 各渠道流量統(tǒng)計(jì)ads_traffic_stats_by_channel

需求說明如下

統(tǒng)計(jì)周期 統(tǒng)計(jì)粒度 指標(biāo) 說明 最近1/7/30日 渠道 訪客數(shù) 統(tǒng)計(jì)訪問人數(shù) 最近1/7/30日 渠道 會(huì)話平均停留時(shí)長(zhǎng) 統(tǒng)計(jì)會(huì)話平均停留時(shí)長(zhǎng) 最近1/7/30日 渠道 會(huì)話平均瀏覽頁面數(shù) 統(tǒng)計(jì)會(huì)話平均瀏覽頁面數(shù) 最近1/7/30日 渠道 會(huì)話總數(shù) 統(tǒng)計(jì)會(huì)話總數(shù) 最近1/7/30日 渠道 跳出率 只有一個(gè)頁面的會(huì)話的比例

2）數(shù)據(jù)裝載

（1）思路分析

在dws_traffic_session_page_view_1d中，我們對(duì)每個(gè)會(huì)話的頁面訪問情況做了匯總，本節(jié)基于該表統(tǒng)計(jì)。

ADS層報(bào)表數(shù)據(jù)量不大，分區(qū)意義不大，通過dt字段區(qū)分統(tǒng)計(jì)日期即可。每日裝載將歷史報(bào)表數(shù)據(jù)取出，與當(dāng)日統(tǒng)計(jì)結(jié)果union后寫回該表即可。要注意，為了避免數(shù)據(jù)重復(fù)，此處要用union去重。

（2）執(zhí)行步驟

① 數(shù)據(jù)膨脹

我們將統(tǒng)計(jì)周期為最近1/7/30日的指標(biāo)放到了同一張表中。慣用的實(shí)現(xiàn)思路是用三個(gè)子查詢分別匯總最近1/7/30日的數(shù)據(jù)然后union起來，這樣相同的SQL邏輯需要編寫三次，略顯冗長(zhǎng)，我們可以通過側(cè)寫 + 炸裂的方式精簡(jiǎn)。

這一步運(yùn)用lateral?view語法結(jié)合explode(array(1,7,30))將數(shù)據(jù)膨脹為三倍，補(bǔ)充recent_days字段，用于標(biāo)識(shí)這三份數(shù)據(jù)，區(qū)分統(tǒng)計(jì)周期。然后在where子句中通過date_add()函數(shù)針對(duì)不同的recent_days過濾相應(yīng)時(shí)間范圍的數(shù)據(jù)。

LATERAL VIEW 是 HiveQL 中的一種語法，用于將一個(gè)表的列轉(zhuǎn)換為多行。它通常與 explode() 函數(shù)一起使用，用于將一個(gè)數(shù)組列拆分成多行。

② 聚合

按照recent_days和渠道分組聚合，字段處理邏輯如下。

dt：統(tǒng)計(jì)日期，當(dāng)日。recent_days：取自分組字段，標(biāo)識(shí)統(tǒng)計(jì)周期。channel：取自分組字段，唯一標(biāo)識(shí)渠道。uv_count：訪客數(shù)，統(tǒng)計(jì)mid的數(shù)量，需要去重。強(qiáng)轉(zhuǎn)為bigint類型。avg_duration_sec：平均停留還長(zhǎng)，dws_traffic_session_page_view_1d表的一行對(duì)應(yīng)一個(gè)會(huì)話，調(diào)用avg()統(tǒng)計(jì)during_time_1d即得各渠道下會(huì)話平均停留時(shí)長(zhǎng)，此時(shí)時(shí)間為毫秒數(shù)，除以1000轉(zhuǎn)為秒，強(qiáng)轉(zhuǎn)為bigint類型。avg_page_count：平均頁面瀏覽數(shù)，與avg_duration_sec同理，求page_count_1d均值即可，強(qiáng)轉(zhuǎn)為bigint類型。sv_count：會(huì)話數(shù)，一行對(duì)應(yīng)一個(gè)會(huì)話，行數(shù)即會(huì)話數(shù)，強(qiáng)轉(zhuǎn)為bigint類型。bounce_rate：跳出率，跳出會(huì)話數(shù)除以會(huì)話總數(shù)。跳出會(huì)話是只有一個(gè)頁面的會(huì)話，統(tǒng)計(jì)page_count_1d為1的會(huì)話數(shù)即可。統(tǒng)計(jì)結(jié)果強(qiáng)轉(zhuǎn)為decimal(16, 2)類型。

與報(bào)表歷史數(shù)據(jù)union后寫回即可。下文同理，不再贅述。

11.1.2 路徑分析ads_page_path

用戶路徑分析，顧名思義，就是指用戶在APP或網(wǎng)站中的訪問路徑。為了衡量網(wǎng)站優(yōu)化的效果或營(yíng)銷推廣的效果，以及了解用戶行為偏好，時(shí)常要對(duì)訪問路徑進(jìn)行分析。

用戶訪問路徑的可視化通常使用?；鶊D。如下圖所示，該圖可真實(shí)還原用戶的訪問路徑，包括頁面跳轉(zhuǎn)和頁面訪問次序。

桑基圖需要我們提供每種頁面跳轉(zhuǎn)的次數(shù)，每個(gè)跳轉(zhuǎn)由source/target表示，source指跳轉(zhuǎn)起始頁面，target表示跳轉(zhuǎn)終到頁面。

2）數(shù)據(jù)裝載

（1）思路分析

將同一會(huì)話的頁面瀏覽記錄按照日志生成時(shí)間升序排列，每個(gè)頁面都可以作為source，排在source之后的就是與之對(duì)應(yīng)的target。每次頁面瀏覽都對(duì)應(yīng)一次跳轉(zhuǎn)，只要確定了source和target，統(tǒng)計(jì)頁面瀏覽次數(shù)即可得所有組合的跳轉(zhuǎn)次數(shù)。

（2）執(zhí)行步驟

頁面跳轉(zhuǎn)信息隱含在頁面瀏覽記錄中，本節(jié)數(shù)據(jù)源為dwd_traffic_page_view_inc。

①?t1子查詢

統(tǒng)計(jì)下頁ID：開窗，按照會(huì)話ID分區(qū)，頁面訪問時(shí)間升序排列，調(diào)用lead()函數(shù)取下一行的page_id，取不到記為null，可得下頁ID，記為next_page_id。

統(tǒng)計(jì)當(dāng)前頁在所屬會(huì)話中的序號(hào)：如果按照跳轉(zhuǎn)時(shí)間對(duì)同一會(huì)話中的頁面升序排列，相同的頁面跳轉(zhuǎn)組合在各自會(huì)話中的序號(hào)可能不同，不應(yīng)混為一談。開窗，按照session_id分區(qū)，訪問時(shí)間升序排列，調(diào)用row_number()對(duì)當(dāng)前記錄編號(hào)，記為rn。

② t2子查詢

拼接page_id和rn獲得source，拼接next_page_id和rn+1獲得target（目標(biāo)頁的編號(hào)應(yīng)比當(dāng)前頁大1）。

③ 統(tǒng)計(jì)

按照source和target分組聚合，統(tǒng)計(jì)行數(shù)即每種頁面組合的跳轉(zhuǎn)次數(shù)。要注意，當(dāng)source頁為會(huì)話末頁時(shí)，target為null。ADS層表的數(shù)據(jù)最終要導(dǎo)入MySQL報(bào)表，我們會(huì)以source和target唯一標(biāo)識(shí)報(bào)表的一條數(shù)據(jù)，將其作為聯(lián)合主鍵，而MySQL中主鍵字段不可為null，因而對(duì)target做處理，為null時(shí)轉(zhuǎn)換為字符串'null'。

11.2 用戶主題

11.2.1 用戶變動(dòng)統(tǒng)計(jì)ads_user_change

該需求包括兩個(gè)指標(biāo)，分別為流失用戶數(shù)和回流用戶數(shù)，以下為對(duì)兩個(gè)指標(biāo)的解釋說明。

指標(biāo) 說明 流失用戶數(shù) 之前活躍過的用戶，最近一段時(shí)間未活躍，就稱為流失用戶。此處要求統(tǒng)計(jì)7日前（只包含7日前當(dāng)天）活躍，但最近7日未活躍的用戶總數(shù)。 回流用戶數(shù) 之前的活躍用戶，一段時(shí)間未活躍（流失），今日又活躍了，就稱為回流用戶。此處要求統(tǒng)計(jì)回流用戶總數(shù)。

2）數(shù)據(jù)裝載

（1）思路分析

① 流失用戶數(shù)計(jì)算思路

末次登錄日期至少比當(dāng)日早七天即為流失用戶，過濾條件為login_last_dt<=date_add('cur_date', -7)，然而，此處只考慮當(dāng)日流失的用戶，如果用<=篩選數(shù)據(jù)，那么昨日及之前流失的用戶也會(huì)被統(tǒng)計(jì)在內(nèi)，因此這里應(yīng)該用=，即過濾條件為login_last_dt=date_add('cur_date',-7)。

② 回流用戶數(shù)計(jì)算思路

當(dāng)日回流用戶是指流失之后，當(dāng)日登錄的用戶。由此可得兩個(gè)過濾條件：當(dāng)日登錄且昨日為流失用戶。流失用戶的判定上文已作詳解，那么，如何判定當(dāng)日登錄？最容易想到的辦法是根據(jù)登錄事實(shí)表記錄獲得當(dāng)日登錄過的user_id集合，對(duì)于每個(gè)用戶判定其user_id是否在該集合中，但這樣太過浪費(fèi)性能，不取。實(shí)際上，用戶在當(dāng)日登錄等價(jià)于其末次登錄日期為當(dāng)日，二者互為充要條件。因此，滿足條件一的數(shù)據(jù)可以通過限定當(dāng)日的login_last_dt為'cur_date'篩選。將昨日分區(qū)的login_last_dt命名為login_last_previous，則滿足條件二的數(shù)據(jù)可以限定login_last_previous<=date_add('cur_date', -8)篩選。這兩個(gè)過濾條件可以合并為login_last_previous<=date_add(login_last_dt, -8)。

上述條件等價(jià)于datediff(login_last_dt,login_last_previous)>=8。

（2）執(zhí)行步驟

①?t1子查詢

篩選dws_user_user_login_td當(dāng)日分區(qū)的數(shù)據(jù)，獲取各用戶歷史至今末次登錄日期。經(jīng)過上述分析，我們可以通過對(duì)比日期差來篩選回流數(shù)據(jù)，此處不需要額外的過濾條件，然而，如果只是通過dt篩選，t1子查詢會(huì)對(duì)每個(gè)用戶保留一條數(shù)據(jù)，只有當(dāng)日登錄過的有可能是回流用戶，可以通過login_date_last = '2022-06-08'篩選這部分?jǐn)?shù)據(jù)，這樣可以極大削減t1子查詢的數(shù)據(jù)量，節(jié)省資源。

② t2子查詢

篩選dws_user_user_login_td昨日分區(qū)的數(shù)據(jù)，獲取各用戶昨日末次登錄日期。

③ 統(tǒng)計(jì)

根據(jù)user_id將t1、t2關(guān)聯(lián)起來。t2的user_id集合是t1中user_id的超集，多出的那部分user_id對(duì)應(yīng)當(dāng)日新增用戶。我們要統(tǒng)計(jì)回流用戶數(shù)不需要考慮新增用戶，因此，使用join關(guān)聯(lián)即可。根據(jù)datediff(login_last_dt,login_last_previous)>=8篩選回流記錄，統(tǒng)計(jì)行數(shù)，即得回流用戶數(shù)。

11.2.2 用戶留存率ads_user_retention

留存分析一般包含新增留存和活躍留存分析。

新增留存分析是分析某天的新增用戶中，有多少人有后續(xù)的活躍行為?；钴S留存分析是分析某天的活躍用戶中，有多少人有后續(xù)的活躍行為。

留存分析是衡量產(chǎn)品對(duì)用戶價(jià)值高低的重要指標(biāo)。

此處要求統(tǒng)計(jì)新增留存率，新增留存率具體是指留存用戶數(shù)與新增用戶數(shù)的比值，例如2022-06-08新增100個(gè)用戶，1日之后（2022-06-09）這100人中有80個(gè)人活躍了，那2022-06-08的1日留存數(shù)則為80，2022-06-08的1日留存率則為80%。

要求統(tǒng)計(jì)每天的1至7日留存率，如下圖所示。

（1）思路分析

我們無法統(tǒng)計(jì)注冊(cè)日期在當(dāng)日的1-7日留存率，因?yàn)楫?dāng)日拿不到未來7天的登錄數(shù)據(jù)，因此統(tǒng)計(jì)時(shí)限定登錄日期為當(dāng)日，統(tǒng)計(jì)當(dāng)日前7-1天的1-7日留存率。

留存率涉及兩個(gè)業(yè)務(wù)過程，用戶注冊(cè)和用戶登錄。上文提到，當(dāng)日登錄等價(jià)于末次登錄日期為當(dāng)日。我們需要獲取用戶注冊(cè)日期和用戶末次登錄日期。但實(shí)際上，在當(dāng)前的業(yè)務(wù)場(chǎng)景下，注冊(cè)后自動(dòng)跳轉(zhuǎn)至登錄頁面，可以認(rèn)為注冊(cè)日期等價(jià)于首次登錄日期。我們?cè)谏衔囊呀?jīng)統(tǒng)計(jì)了歷史至今首次和末次登錄日期，從dws_user_user_login_td表中獲取即可。

（2）執(zhí)行步驟

從dws_user_user_login_td中篩選首次登錄日期在過去七日范圍內(nèi)的數(shù)據(jù)，按照login_date_first分組，統(tǒng)計(jì)各組行數(shù)即得新增用戶數(shù)，組合調(diào)用sum(if())統(tǒng)計(jì)當(dāng)日登錄的用戶即得對(duì)應(yīng)天數(shù)的留存用戶數(shù)。二者作比即得留存率。

11.2.3 用戶新增活躍統(tǒng)計(jì)ads_user_stats

需求說明如下

統(tǒng)計(jì)周期 指標(biāo) 指標(biāo)說明 最近1、7、30日 新增用戶數(shù) 略 最近1、7、30日 活躍用戶數(shù) 略

2）數(shù)據(jù)裝載

（1）思路分析

本需求對(duì)活躍的定義是限定時(shí)間范圍內(nèi)有登錄記錄，可以通過限制末次登錄日期在最近1/7/30日篩選活躍用戶。注冊(cè)日期等價(jià)于首次登錄日期，可以根據(jù)首次登錄日期篩選新增用戶。

（2）執(zhí)行步驟

從dws_user_user_login_td表中篩選當(dāng)日數(shù)據(jù)，為精簡(jiǎn)SQL，通過側(cè)寫+炸裂，補(bǔ)充recent_days字段，將數(shù)據(jù)炸裂為三份，recent_days字段取值分別為1、7、30，標(biāo)識(shí)統(tǒng)計(jì)周期。我們只需要末次登錄日期或注冊(cè)日期在date_add(cur_date, -recent_days)（下文以threshold代指）及之后的數(shù)據(jù)，過濾條件為login_date_last >=?threshold?or login_date_first >= threshold。而注冊(cè)日期必定小于等于末次登錄日期，只要login_date_last >=?threshold成立，login_date_first >=threshold必定成立，因而過濾條件可以精簡(jiǎn)為login_date_last?>= threshold。

接下來，按照recent_days分組聚合通過sum(if())統(tǒng)計(jì)滿足login_date_first >= threshold條件的行數(shù)，即新增用戶數(shù)，統(tǒng)計(jì)各組行數(shù)即得活躍用戶數(shù)，將當(dāng)天日期作為dt字段的值，完成統(tǒng)計(jì)。

11.2.4 用戶行為漏斗分析ads_user_action

漏斗分析是一個(gè)數(shù)據(jù)分析模型，它能夠科學(xué)反映一個(gè)業(yè)務(wù)流程從起點(diǎn)到終點(diǎn)各階段用戶轉(zhuǎn)化情況。由于其能將各階段環(huán)節(jié)都展示出來，故哪個(gè)階段存在問題，就能一目了然。

該需求要求統(tǒng)計(jì)一個(gè)完整的購物流程各個(gè)階段的人數(shù)，具體說明如下：

統(tǒng)計(jì)周期 指標(biāo) 說明 最近1?日 首頁瀏覽人數(shù) 略 最近1?日 商品詳情頁瀏覽人數(shù) 略 最近1?日 加購人數(shù) 略 最近1?日 下單人數(shù) 略 最近1?日 支付人數(shù) 支付成功人數(shù)

2）數(shù)據(jù)裝載

（1）思路分析

漏斗分析由五個(gè)派生指標(biāo)構(gòu)成，從各自的匯總表取數(shù)統(tǒng)計(jì)，整合后寫入報(bào)表即可。

（2）執(zhí)行步驟

① page子查詢

dws_traffic_page_visitor_page_view_1d一行對(duì)應(yīng)當(dāng)日一名訪客對(duì)一個(gè)頁面的瀏覽情況，按照page_id分組聚合統(tǒng)計(jì)行數(shù)即得當(dāng)日各頁面的瀏覽人數(shù)。從該表當(dāng)日分區(qū)篩選page_id為home或good_detail的數(shù)據(jù)，通過sum(if())分別統(tǒng)計(jì)page_id為home和good_detail的行數(shù)即得當(dāng)日首頁瀏覽人數(shù)和當(dāng)日商品詳情頁瀏覽人數(shù)。補(bǔ)充recent_days，賦值為1，下同。

② cart子查詢

dws_trade_user_cart_add_1d一行對(duì)應(yīng)當(dāng)日一名用戶的加購情況，行數(shù)即當(dāng)日加購用戶數(shù)。

③ ord子查詢

dws_trade_user_order_1d一行對(duì)應(yīng)當(dāng)日一名用戶的下單情況，行數(shù)即當(dāng)日下單用戶數(shù)。

④ pay子查詢

dws_trade_user_payment_1d一行對(duì)應(yīng)當(dāng)日一名用戶的支付情況，行數(shù)即當(dāng)日支付用戶數(shù)。

⑤ 統(tǒng)計(jì)

通過recent_days將上述四個(gè)子查詢關(guān)聯(lián)起來，所有子查詢的recent_days均相同，內(nèi)連接即可。統(tǒng)計(jì)完成。

11.2.5 新增下單用戶統(tǒng)計(jì)ads_new_order_user_stats

需求說明如下。

統(tǒng)計(jì)周期 指標(biāo) 說明 最近1、7、30日 新增下單人數(shù) 略

2）數(shù)據(jù)裝載

（1）思路分析

首次下單日期在指定時(shí)間范圍內(nèi)即相應(yīng)的新增下單用戶。從dws_trade_user_order_td獲取各用戶的首次下單日期。

（2）執(zhí)行步驟

通過側(cè)寫+炸裂的方式將dws_trade_user_order_td的數(shù)據(jù)擴(kuò)充為三倍，補(bǔ)充recent_days字段，取值為1、7、30。通過order_date_first>=date_add('$do_date',-recent_days+1)篩選數(shù)據(jù)，按照recent_days分組聚合，行數(shù)即相應(yīng)時(shí)間范圍的新增下單人數(shù)。

11.2.6 最近7日內(nèi)連續(xù)3日下單用戶數(shù)ads_order_continuously_user_count

2）數(shù)據(jù)裝載

（1）思路分析

連續(xù)三日下單的記錄有這樣的特點(diǎn)：按照日期升序排列，那么一定存在某一個(gè)日期與它后兩行的日期相差兩天。據(jù)此可以篩選連續(xù)下單用戶數(shù)。

（2）執(zhí)行步驟

我們要獲取最近7日內(nèi)用戶的下單日期，數(shù)據(jù)取自dws_trade_user_order_1d，篩選最近7日分區(qū)的數(shù)據(jù)即可。開窗，按照用戶分區(qū)，dt（下單日期）升序排列，調(diào)用lead()函數(shù)獲取后兩行的dt取值，而后用datadiff計(jì)算與dt的天數(shù)差，記為diff，上文提到，如果是連續(xù)三日下單的記錄，一定存在某條數(shù)據(jù)的diff為2。篩選diff=2的數(shù)據(jù)，即可獲得所有滿足條件的用戶，如果連續(xù)下單的天數(shù)為4天及以上，那么同一用戶滿足條件的數(shù)據(jù)就不止一條，需要去重，因此調(diào)用count(distinct user_id)完成統(tǒng)計(jì)。

LEAD() 函數(shù)是 SQL 中的一種窗口函數(shù)，用于獲取當(dāng)前行之后的指定行的數(shù)據(jù)。它常用于在結(jié)果集中獲取當(dāng)前行后面的行的數(shù)據(jù)，例如獲取下一個(gè)時(shí)間點(diǎn)的數(shù)據(jù)，或者計(jì)算相鄰行之間的差值等。

LEAD(expression, offset, default_value) OVER (partition_clause ORDER BY order_clause)

select * from ads_order_continuously_user_count

union

select

'2022-06-08',

7,

count(distinct(user_id))

from

(

select

user_id,

datediff(lead(dt,2,'9999-12-31') over(partition by user_id order by dt),dt) diff

from dws_trade_user_order_1d

where dt>=date_add('2022-06-08',-6)

)t1

where diff=2;

11.3 商品主題

1.3.1 最近30日各品牌復(fù)購率ads_repeat_purchase_by_tm

需求說明如下。

統(tǒng)計(jì)周期 統(tǒng)計(jì)粒度 指標(biāo) 說明 最近30日 品牌 復(fù)購率 重復(fù)購買人數(shù)占購買人數(shù)比例

2）數(shù)據(jù)裝載

（1）思路分析

復(fù)購率可以基于支付或下單記錄統(tǒng)計(jì)，具體如何選擇需要考慮具體的業(yè)務(wù)情況和數(shù)據(jù)特點(diǎn)。

如果希望更加準(zhǔn)確地反映用戶的購買行為，并且排除訂單取消和退款等因素的影響，那么以支付為基準(zhǔn)統(tǒng)計(jì)復(fù)購率是一個(gè)更好的選擇。這樣能夠更加準(zhǔn)確地反映用戶的實(shí)際購買行為。

如果希望更加全面地了解用戶的購買情況，以下單為基準(zhǔn)更加合理。這樣可以獲得更全面、準(zhǔn)確、詳細(xì)的客戶購買行為數(shù)據(jù)，包括客戶的購買意向、購買渠道等，這些數(shù)據(jù)可以用于更深入的分析和挖掘，為企業(yè)提供更多有價(jià)值的商業(yè)洞察。

綜上所述，以支付為基準(zhǔn)或者以訂單為基準(zhǔn)統(tǒng)計(jì)復(fù)購率都有其優(yōu)缺點(diǎn)，應(yīng)根據(jù)實(shí)際情況選擇合適的方法。在實(shí)際操作中，也可以采用多種統(tǒng)計(jì)方式綜合分析，以得到更加全面和準(zhǔn)確的結(jié)果。

此處選擇以下單為基準(zhǔn)統(tǒng)計(jì)復(fù)購率。

最近30日各品牌復(fù)購率是最近30日內(nèi)各品牌至少下單兩次的用戶占所有下單人數(shù)的比例。統(tǒng)計(jì)最近30日內(nèi)各用戶對(duì)各品牌的下單次數(shù)，在此基礎(chǔ)上，按照tm_id分組聚合，統(tǒng)計(jì)各品牌下單至少兩次和至少一次的用戶數(shù)，作比即得復(fù)購率。

（2）執(zhí)行步驟

① t1子查詢

篩選dws_trade_user_sku_order_nd當(dāng)日分區(qū)的數(shù)據(jù)，按照user_id，tm_id，tm_name分組聚合，對(duì)order_count_30d求和，統(tǒng)計(jì)最近30日各用戶各品牌下單次數(shù)。

② 統(tǒng)計(jì)

按照tm_id，tm_name分組聚合，調(diào)用sum(if(order_count>=2,1,0))統(tǒng)計(jì)下單至少兩次的用戶數(shù)，sum(if(order_count>=1,1,0))統(tǒng)計(jì)下單至少一次的用戶數(shù)，作比即得復(fù)購率，統(tǒng)計(jì)完成。實(shí)際上，order_count一定大于0，因?yàn)槿绻麤]有下單，t1表中就不會(huì)有對(duì)應(yīng)記錄，因此，行數(shù)即為下單用戶數(shù)。

11.3.2 各品牌商品下單統(tǒng)計(jì)

需求說明如下

統(tǒng)計(jì)周期 統(tǒng)計(jì)粒度 指標(biāo) 說明 最近1、7、30日 品牌 下單數(shù) 略

（2）執(zhí)行步驟

① 最近1日指標(biāo)

從dws_trade_user_sku_order_1d篩選當(dāng)然數(shù)據(jù)，按照tm_id，tm_name分組，對(duì)order_count_1d求和統(tǒng)計(jì)訂單數(shù)，去重對(duì)user_id計(jì)數(shù)統(tǒng)計(jì)下單用戶數(shù)。

② 最近n日指標(biāo)

通過側(cè)寫+炸裂方式的方式將dws_trade_user_sku_order_nd數(shù)據(jù)膨脹為兩份，補(bǔ)充字段recent_days標(biāo)識(shí)這兩部分?jǐn)?shù)據(jù)。篩選當(dāng)日分區(qū)數(shù)據(jù)，通過case?when語法根據(jù)recent_days的不同取值獲取最近n日各用戶各sku的下單次數(shù)，記為order_count。篩選recent_days、user_id、tm_id、tm_name、order_count字段，作為t1子查詢。

在t1的基礎(chǔ)上按照tm_id，tm_name分組聚合，統(tǒng)計(jì)下單數(shù)次數(shù)和下單用戶數(shù)即可。

③ 統(tǒng)計(jì)

合并①、②的統(tǒng)計(jì)結(jié)果，兩部分查詢結(jié)果的recent_days一定不同，不存在重復(fù)數(shù)據(jù)，去重?zé)o意義，因此通過union?all合并，作為odr子查詢。篩選需要的字段，補(bǔ)充dt（當(dāng)天日期），完成統(tǒng)計(jì)。

11.3.3 各品類商品下單統(tǒng)計(jì)

需求說明如下

統(tǒng)計(jì)周期 統(tǒng)計(jì)粒度 指標(biāo) 說明 最近1、7、30日 品類 下單數(shù) 略 最近1、7、30日 品類 下單人數(shù) 略

11.3.4 各品類商品購物車存量Top3

（1）思路分析

本節(jié)需求是典型的分組TopN，統(tǒng)計(jì)當(dāng)日各品類商品購物車存量Top3。

（2）執(zhí)行步驟

① cart子查詢

要對(duì)各品類商品購物車存量做排名，首先要獲取各商品的購物車存量。購物車相關(guān)數(shù)據(jù)存儲(chǔ)在業(yè)務(wù)數(shù)據(jù)庫的cart_info表中，這是一張事實(shí)相關(guān)的表，通常做增量采集，如果基于該表的增量數(shù)據(jù)統(tǒng)計(jì)，先要將cart_info表的變更數(shù)據(jù)和首日全量數(shù)據(jù)累加得出當(dāng)日各用戶各商品的購物車存量，才能進(jìn)行后續(xù)統(tǒng)計(jì)。cart_info的變化對(duì)應(yīng)兩個(gè)業(yè)務(wù)過程：加購和減購操作，在DWD會(huì)對(duì)應(yīng)兩個(gè)事實(shí)表，缺一不可，這種統(tǒng)計(jì)方式非常繁瑣的。為了簡(jiǎn)化指標(biāo)計(jì)算，我們對(duì)cart_info做了全量同步，并在DWD層維護(hù)了一張周期快照事實(shí)表dwd_trade_cart_full。這樣一來，可以通過該表直接獲取各用戶各商品的存量信息，分組聚合獲取各品類商品購物車存量，分組排名取前三即可完成統(tǒng)計(jì)。

② sku子查詢

篩選dim_sku_full當(dāng)日分區(qū)的數(shù)據(jù)，獲取sku的詳細(xì)信息。

③ t1子查詢

以sku_id作為關(guān)聯(lián)字段，將cart子查詢和sku子查詢關(guān)聯(lián)起來，關(guān)聯(lián)方式為left?join（分析過程參見上文）。開窗，按照品類分區(qū)，購物車存量降序排列，調(diào)用rank()函數(shù)為商品排名，記為rk。篩選其他感興趣的字段，記為t1子查詢。

④ 統(tǒng)計(jì)

篩選t1子查詢中rk<=3的數(shù)據(jù)，完成統(tǒng)計(jì)。

11.3.5 各品牌商品收藏次數(shù)Top3

2）數(shù)據(jù)裝載

（1）思路分析

統(tǒng)計(jì)當(dāng)日各品牌商品收藏次數(shù)Top3。

（2）執(zhí)行步驟

① t1子查詢

從dws_interaction_sku_favor_add_1d篩選當(dāng)日數(shù)據(jù)，開窗，按照tm_id分區(qū)，按照當(dāng)日收藏次數(shù)降序排列，調(diào)用rank()排名，記為rk。

② 統(tǒng)計(jì)

篩選rk<=3的數(shù)據(jù)，完成統(tǒng)計(jì)。

11.4 交易主題

11.4.1 下單到支付時(shí)間間隔平均值

2）數(shù)據(jù)裝載

（1）思路分析

本節(jié)需求涉及了電商交易流程的下單和支付業(yè)務(wù)過程。要統(tǒng)計(jì)下單到支付的時(shí)間間隔，最簡(jiǎn)單的思路是獲取當(dāng)日的所有支付記錄和最近2日的所有下單記錄（通常下單和支付的時(shí)間間隔在30min以內(nèi)，如果下單時(shí)間在前一天的24點(diǎn)附近，支付操作很可能發(fā)生在第二天，因此和當(dāng)日支付記錄對(duì)應(yīng)的下單記錄可能屬于前一天分區(qū)，因獲取最近2日的數(shù)據(jù)），按照order_id關(guān)聯(lián)，用支付時(shí)間減去下單時(shí)間，求其均值即可。

上述思路計(jì)算邏輯非常簡(jiǎn)單，但通常不會(huì)這么做。下單和支付是電商系統(tǒng)的核心業(yè)務(wù)，每日會(huì)有大量的記錄，大表join是我們應(yīng)極力避免的。它會(huì)帶來極大的資源開銷，一旦發(fā)生數(shù)據(jù)傾斜，甚至有可能執(zhí)行失敗。為了從根本上避免大表join，在DWD層構(gòu)建了累積快照事實(shí)表，該表記錄了一個(gè)業(yè)務(wù)流程多個(gè)業(yè)務(wù)過程的關(guān)鍵時(shí)間節(jié)點(diǎn)（里程碑），省去關(guān)聯(lián)操作。

（2）執(zhí)行步驟

dwd_trade_trade_flow_acc記錄了下單、支付、確認(rèn)收貨三個(gè)業(yè)務(wù)過程，當(dāng)確認(rèn)收貨后認(rèn)為業(yè)務(wù)流程結(jié)束，數(shù)據(jù)進(jìn)入確認(rèn)收貨日期對(duì)應(yīng)分區(qū)。當(dāng)日支付的記錄有兩種去向，如果確認(rèn)收貨則進(jìn)入當(dāng)日分區(qū)，否則保存在9999-12-31分區(qū)。篩選這兩個(gè)分區(qū)支付日期為當(dāng)日的數(shù)據(jù)，統(tǒng)計(jì)其下單到支付的平均時(shí)間間隔即可。

11.4.2 各省份交易統(tǒng)計(jì)

11.5 優(yōu)惠券主題

11.5.1?優(yōu)惠券使用統(tǒng)計(jì)

11.6 數(shù)據(jù)裝載腳本

12 報(bào)表數(shù)據(jù)導(dǎo)出

為方便報(bào)表應(yīng)用使用數(shù)據(jù)，需將ads各指標(biāo)的統(tǒng)計(jì)結(jié)果導(dǎo)出到MySQL數(shù)據(jù)庫中。

12.1 MySQL建庫建表

12.1.1 創(chuàng)建數(shù)據(jù)庫

12.1.2 結(jié)合ADS統(tǒng)計(jì)數(shù)據(jù)分別創(chuàng)建表

12.2 數(shù)據(jù)導(dǎo)出

數(shù)據(jù)導(dǎo)出工具選用DataX，選用HDFSReader和MySQLWriter。

12.2.1 編寫DataX配置文件

{

"job": {

"content": [

{

"reader": {

"name": "hdfsreader",

"parameter": {

"column": [

"*"

],

"defaultFS": "hdfs://hadoop102:8020",

"encoding": "UTF-8",

"fieldDelimiter": "\t",

"fileType": "text",

"nullFormat": "\\N",

"path": "${exportdir}"

}

},

"writer": {

"name": "mysqlwriter",

"parameter": {

"column": [

"dt",

"recent_days",

"channel",

"uv_count",

"avg_duration_sec",

"avg_page_count",

"sv_count",

"bounce_rate"

],

"connection": [

{

"jdbcUrl": "jdbc:mysql://hadoop102:3306/gmall_report?useUnicode=true&characterEncoding=utf-8&allowPublicKeyRetrieval=true",

"table": [

"ads_traffic_stats_by_channel"

]

}

],

"password": "000000",

"username": "root",

"writeMode": "replace"

}

}

}

],

"setting": {

"errorLimit": {

"percentage": 0.02,

"record": 0

},

"speed": {

"channel": 3

}

}

}

}

12.2.2 DataX配置文件生成腳本

使用采集部分提到的生成器生成DataX配置文件。

1）修改/opt/module/gen_datax_config/configuration.properties文件

mysql.username=root

mysql.password=000000

mysql.host=hadoop102

mysql.port=3306

mysql.database.import=gmall

# 從HDFS導(dǎo)出進(jìn)入的 MySQL 數(shù)據(jù)庫名稱

mysql.database.export=gmall_report

mysql.tables.import=activity_info,activity_rule,base_trademark,cart_info,base_category1,base_category2,base_category3,coupon_info,sku_attr_value,sku_sale_attr_value,base_dic,sku_info,base_province,spu_info,base_region,promotion_pos,promotion_refer

# MySQL 庫中需要導(dǎo)出的表，空串表示導(dǎo)出庫的所有表

mysql.tables.export=

is.seperated.tables=0

hdfs.uri=hdfs://hadoop102:8020

import_out_dir=/opt/module/datax/job/import

# DataX 導(dǎo)出配置文件存放路徑

export_out_dir=/opt/module/datax/job/export

2）執(zhí)行配置文件生成器

3）查看配置文件存放路徑

12.2.3 測(cè)試生成的DataX配置文件

12.2.4 編寫每日導(dǎo)出腳本

13 數(shù)據(jù)倉庫工作流調(diào)度

13.1 調(diào)度工具部署DolphinScheduler

13.2 新數(shù)據(jù)生成

1）啟動(dòng)采集通道，包括Kafka、Flume等

（1）啟動(dòng)Zookeeper

[atguigu@hadoop102 ~]$ zk.sh start

（2）啟動(dòng)Kafka

[atguigu@hadoop102 ~]$ kf.sh start

（3）啟動(dòng)Flume

[atguigu@hadoop102 ~]$ f1.sh start

[atguigu@hadoop102 ~]$ f2.sh start

[atguigu@hadoop102 ~]$ f3.sh start

2）修改日志模擬器配置文件

修改hadoop102的/opt/module/applog/application.yml文件，修改mock.date參數(shù)如下

mock.date: "2022-06-09"

3）修改/opt/module/maxwell/config.properties文件

[atguigu@hadoop102 maxwell]$ vim /opt/module/maxwell/config.properties

4）修改mock_date參數(shù)設(shè)置如下

mock_date=2022-06-09

5）啟動(dòng)Maxwell

[atguigu@hadoop102 ~]$ mxw.sh start

6）執(zhí)行日志生成腳本

[atguigu@hadoop102 ~]$ lg.sh

7）觀察HDFS上是否有2022-06-09的日志數(shù)據(jù)和增量表數(shù)據(jù)生成

13.3 工作流調(diào)度實(shí)操

13.3.1 DolphinScheduler集群模式

13.3.2 DolphinScheduler單機(jī)模式

針對(duì)虛擬機(jī)內(nèi)存不足情況使用

柚子快報(bào)邀請(qǐng)碼778899分享：大數(shù)據(jù) 離線數(shù)倉-數(shù)據(jù)倉庫系統(tǒng)

http://yzkb.51969.com/

精彩文章