以下是資料倉庫常見算法整理，希望對你工作有所幫助，請大家點贊，輕按兩下"曉彬聊資料"關注謝謝！
曉彬聊資料
本公衆号主要關注：傳統關系型資料庫、國産資料庫、大資料、資料分析、BI、人工智能、PYTHON等技術開發與交流
7篇原創内容
公衆号
**一、ETL定義 **
ETL是将業務系統的資料經過抽取、清洗轉換之後加載到資料倉庫的過程，目的是将企業中的分散、零亂、标準不統一的資料整合到一起，為企業的決策提供分析依據。
二、ETL算法導圖
這8種ETL算法，其中主要分成4大類，增量累加、拉鍊算法是更符合資料倉庫曆史資料追蹤的算法，但現實中基于業務及性能考慮，往往存在全删全插、增量累全算法的資料表應用。
四、算法詳解
1、全删全插算法
用DML語句中的Delete/Insert實作邏輯，主要應用在維表、參數表、主檔表加載上，即适合源表是全量資料表，該資料表業務邏輯隻需儲存目前最新全量資料，不需跟蹤過往曆史資訊。
SQL代碼模型：
--步驟1. 清空目标表
TRUNCATE TABLE <目标表名>;
--步驟2. 全量插入
INSERT INTO <目标表名> (字段1，***)
SELECT 字段***
FROM <源表>
***JOIN <關聯資料>
WHERE ***;
2、增量累全算法
用Upsert實作邏輯，主要應用在參數表、主檔表加載上，即源表可以是增量或全量資料表，目标表始終最新最全記錄。
--步驟1. 生成加工源表 Create temp Table <臨時表> ***;
INSERT INTO <臨時表> (字段***)
WHERE ***；
-- 步驟2. 可利用Merge Into實作累全，目前也可以采用分步Delete/Insert或Update/Insert操作
Merge INTO <目标表> As T1 (字段***)
Using <臨時表> as S1
on (PK)
when Matched then
update set Colx = S1.Colx ***
when Not Matched then
INSERT (字段***) values (字段*** );
3、增量累加
用Append實作邏輯，主要應用在流水表加載上，即每日産生的流水、事件資料，追加到目标表中保留全曆史資料。流水表、快照表、統計分析表等均是通過該邏輯實作。
--步驟1.插入目标表
INSERT INTO <目标表> (字段1***)
4、全曆史拉鍊算法
拉連結清單定義：是一張至少存在PK字段、跟蹤變化的字段、開鍊日期、閉鍊日期組成的資料倉庫ETL資料表。
拉連結清單優勢：根據開鍊、閉鍊日期可以快速提取對應日期有效資料，對于跟蹤源系統非事件流水類表資料，拉鍊算法發揮越大作用，源業務系統通常每日變化資料有限，通過拉鍊加工可以大大降低每日打快照帶來的空間開銷，且不損失資料變化曆史。
全曆史拉連結清單算法實作邏輯：提取目前有效記錄-提取當日源系統最新資料-根據PK字段比對目前有效記錄與最新源表，更新目标表目前有效記錄，進行閉鍊操作-根據全字段比對最新源表與目前有效記錄，插入目标表。
--步驟1. 提取目前有效記錄
Insert into <臨時表-開鍊-pre> (不含開閉鍊字段***)
Select 不含開閉鍊字段***
From <目标表>
Where 結束日期 =date'<最大日期>';
--步驟2. 提取當日源系統最新資料
<源表臨時表-cur>
-- 步驟3 今天全部開鍊的資料，即包含今天全新插入、資料發生變化的記錄
Insert Into <臨時表-增量-ins>
From <源表臨時表-cur>
where (不含開閉鍊字段***) not in
(Select 不含開閉鍊字段***
From <臨時表-開鍊-pre>
);
-- 4 今天需要閉鍊的資料，即今天發生變化的記錄
Insert into <臨時表-增量-upd>
Select 不含開閉鍊字段***,開始時間
From <臨時表-開鍊-pre>
From <臨時表-開鍊-cur>
);
--步驟5 更新閉鍊資料，即曆史記錄閉鍊（删除-插入替代更新）
DELETE FROM <目标表>
WHERE (PK***) IN
(Select PK*** From <臨時表-增量-upd>)
AND 結束日期=date'<最大日期>';
INSERT INTO <目标表>
(不含開閉鍊字段***,開始時間,結束日期)
Select 不含開閉鍊字段***,開始時間,date'<資料日期>'
From <臨時表-增量-upd>;
-- 6 插入開鍊資料，即當日新增記錄
(不含開閉鍊字段***,開始時間,結束日期)
Select 不含開閉鍊字段***,date'<資料日期>',date'<最大日期>'
From <臨時表-增量-ins>;
5、增量拉鍊算法
算法實作邏輯是提取上日開鍊資料-PK相同變化記錄，關閉舊記錄鍊，開啟新記錄鍊-PK不同，源表存在，新增開鍊記錄。增量拉鍊，目的是追蹤資料增量變化曆史，根據PK比對新拉一條開鍊資料。
-- 步驟1. 提取目前有效記錄
--步驟2. 提取當日源系統增量記錄
-- 步驟3. 提取當日源系統新增記錄
Insert into <臨時表-增量-ins>
From <臨時表-開鍊-cur>
where (PK) not in
(select PK from <臨時表-開鍊-pre>);
--步驟4. 提取當日源系統曆史變化記錄
inner join <臨時表-開鍊-pre>
on (PK 等值)
where (變化字段 非等值);
--步驟5. 更新曆史變化記錄，關閉曆史舊鍊，開啟新鍊
update <目标表> AS T1
SET <變化字段 S1指派>,結束日期 = date'<資料日期>'
FROM <臨時表-增量-upd> AS S1
WHERE ( <PK 等值> )
AND T1.結束日期 =date'<最大日期>'
(不含開閉鍊字段***,開始時間,結束日期)
SELECT 不含開閉鍊字段***,date'<資料日期>',date'<最大日期>'
FROM <臨時表-增量-upd>;
--步驟6. 插入全新開鍊資料
(不含開閉鍊字段***,開始時間,結束日期)
FROM <臨時表-增量-ins>;
6、增删拉鍊算法
算法實作邏輯是，提取上日開鍊資料-提取源表非删除記錄-PK相同變化記錄，關閉舊記錄鍊，開啟新記錄鍊-PK比對，源表存在，新增開鍊記錄-提取源表删除記錄-PK比對，舊開鍊記錄存在，關閉舊記錄鍊。
-- 步驟1. 清理目标表
TRUNCATE TABLE <目标表>;
-- 步驟2. 全量插入
INSERT INTO <目标表> (字段***)
7、全量增删拉鍊算法
算法實作邏輯是提取上日開鍊資料-提取源表非删除記錄_PK相同變化記錄，關閉舊記錄鍊，開啟新記錄鍊-PK比對，源表存在，新增開鍊記錄-提取源表删除記錄-PK比對，舊開鍊記錄存在，關閉舊記錄鍊-PK比對，提取舊開鍊存在但源表不存在記錄，關閉舊記錄鍊；主要是利用業務字段跟蹤全量資料中包含删除的變化曆史。
8、自拉鍊算法
根據源表業務日期字段，和目标表開鍊、閉鍊日期比對，首尾相接，拉出全曆史拉鍊，主要将流水表資料轉化成拉連結清單資料。
--步驟1. 清理目标表