如何將模組化應用於 SQL

三個層次的模組化
在 SQL 中，我們可以在 3 個不同級別應用模組化：

1. 在同一個 SQL 查詢中
2. 跨多個 SQL 查詢
3. 超越 SQL 查詢

級別 1 - 在同一個 SQL 查詢中
CTE 或公用表表示式是臨時檢視，其範圍僅限於當前查詢。它們不儲存在資料庫中；它們僅在查詢執行時存在，並且只能在該查詢中訪問。它們的行為類似於子查詢，但更易於理解和使用。

CTE 允許您將複雜的查詢分解為更簡單、更小的獨立模組。透過將它們連線在一起，我們可以解決任何複雜的查詢。

旁註： 儘管 CTE 自 1999 年以來一直是 SQL 標準定義的一部分，但資料庫供應商花了很多年才實現它們。某些版本的舊資料庫（如 8.0 之前的 MySQL、8.4 之前的 PostgreSQL、2005 之前的 SQL Server）不支援 CTE。所有現代雲倉庫供應商都支援它們。
視覺化 CTE 的最佳方法之一是透過 DAG（有向迴圈圖）。

以下是如何連結 CTE 以解決複雜查詢的一些示例。

這是第一張圖及其相應的程式碼。
在此示例中，每個 CTE 使用前一個 CTE 的結果來構建其結果集並進一步發展。

​

-- Define CTE 1
WITH cte1_name AS (
    SELECT col1
    FROM table1_name
),
-- Define CTE 2 by referring to CTE 1
cte2_name AS (
    SELECT col1
    FROM cte1_name
),
-- Define CTE 3 by referring to CTE 2
cte3_name AS (
    SELECT col1
    FROM cte2_name
),
-- Define CTE 4 by referring to CTE 3
cte4_name AS (
    SELECT col1
    FROM cte3_name
)
-- Main query
SELECT *
FROM cte4_name

​

-- Define CTE 1
WITH cte1_name AS (
    SELECT col1
    FROM table1_name
),
-- Define CTE 2
cte2_name AS (
    SELECT col1
    FROM table2_name
),
-- Define CTE 3 by referring to CTE 1 and 2
cte3_name AS (
    SELECT *
    FROM cte1_name AS cte1
    JOIN cte2_name AS cte2 
        ON cte1.col1 = cte2.col1
),
-- Define CTE 4 by referring to CTE 3
cte4_name AS (
    SELECT col1
    FROM cte3_name
)
-- Main query
SELECT *
FROM cte4_name

​

如您所見，您可以透過多種方式連結或堆疊 CTE 來解決複雜查詢。

級別 2- 跨多個查詢
當您發現自己在多個查詢中複製和貼上 CTE 時，是時候將它們重構為檢視、UDF 或儲存過程了。

-- Define CTE 1
WITH cte1_name AS (
    SELECT col1
    FROM table1_name
),
-- Define CTE 2 by referring to CTE 1
cte2_name AS (
    SELECT col1
    FROM cte1_name
),
-- Define CTE 3 by referring to CTE 1
cte3_name AS (
    SELECT col1
    FROM cte1_name
)
-- Define CTE 4 by referring to CTE 1
cte4_name AS (
    SELECT col1
    FROM cte1_name
),
-- Define CTE 5 by referring to CTE 4
cte5_name AS (
    SELECT col1
    FROM cte4_name
),
-- Define CTE 6 by referring to CTEs 2, 3 and 5
cte6_name AS (
    SELECT *
    FROM cte2_name cte2
        JOIN cte3_name cte3 ON cte2.column1 = cte3.column1
        JOIN cte5_name cte5 ON cte3.column1 = cte5.column1
)
-- Main query
SELECT *
FROM cte6_name

CREATE OR REPLACE VIEW <view_name> AS
    SELECT col1
    FROM table1
    WHERE col1 > x;

SELECT * FROM <view_name>

​

旁註： 透過組合檢視和 CTE，您可以在其他查​​詢中巢狀許多查詢。這不僅會對效能產生負面影響，而且某些資料庫對您可以擁有的巢狀級別有限制。

UDF
與檢視類似，您還可以將常用邏輯放入 UDF（使用者定義的函式）中。幾乎所有資料庫都允許您建立 UDF，但它們各自使用不同的程式語言來實現。
SQL Server 使用 T-SQL 建立函式。PostgreSQL 使用 PL/pgsql 或 Python（具有正確的副檔名）BigQuery 和 Snowflake 使用 Javascript、Python 等。

函式允許邏輯和變數的條件流，這使得複雜邏輯的實現變得容易。

UDF 可以返回單個標量值或表格。例如，單個標量值可用於透過正規表示式解析某些字串。
表值函式返回一個表而不是單個值。它們的行為與檢視完全一樣，但主要區別在於它們可以獲取輸入引數並基於該引數返回不同的表。很有用。

儲存過程
與表值函式一樣，儲存過程 (sprocs) 允許您將非常複雜的業務邏輯封裝在資料庫中。他們還返回表格作為輸出。
它們在事務系統中被大量使用以在資料庫內實現業務邏輯，但在資料處理中已經失寵。我不會在這裡介紹它們。

級別 3 - 超越 SQL 查詢

隨著dbt （資料構建工具）等工具的出現，您可以超越檢視和 CTE 的查詢，構建更復雜的 dag，將它們結合起來。
在 dbt 術語中，檢視和表被稱為模型。透過使用函式ref()dbt 可以引用以前構建的模型。
您還可以將 CTE 與模型混合搭配，以獲得所需的結果。

​

這是實踐中的樣子：
model_a.sql
select * from public.raw_data

model_b.sql
select * from {{ref('model_a')}}

如何將查詢分解為更簡單的模組
既然您已經瞭解了查詢組合背後的理論，那麼如何應用它呢？在編寫查詢時，您應用以下 3 條規則之一。它們同樣適用於所有 3 個級別。

規則 1：不要重複自己（又名 DRY 原則）
DRY 原則指出，如果您發現自己在查詢中多次複製貼上相同的程式碼塊，則應該將該程式碼放在 CTE 中並在需要的地方引用該 CTE。透過將重複的邏輯放入單獨的模組中，您可以做到輕鬆編寫、維護和除錯程式碼。

規則 2：使模組具有單一用途（也稱為 SRP 原則）
軟體工程中的單一職責原則 (SRP) 表示每個模組都應該是獨立的並且只有一個目的。該模組可以是 CTE、檢視或 dbt 模型。
透過自包含和單一職責，每個模型都可以獨立編寫、測試和除錯。Dbt 透過允許您建立宏或函式 (UDF) 來解除安裝責任，從而多次提高您的靈活性。

規則 3：將邏輯向上遊移動
當您發現自己在可能在其他地方使用的模型中實現非常具體的邏輯時，請儘快將該邏輯移至上游。

在 DAG 的世界裡，upstream 有著非常明確的含義。這意味著將潛在的通用邏輯移動到圖中較早的節點上，因為您永遠不知道哪些下游模型可能會使用它。

​

好的，這是足夠的理論。單擊此處檢視示例。