SQL CTE與視窗函式應用及分散式處理

– 11 分鐘閱讀

SQL 中的 CTE 能有效分解複雜查詢，提升程式碼可讀性，並避免子查詢的冗餘。藉由 CTE 的模組化特性，開發者可以專注於特定任務，簡化查詢的理解和修改。視窗函式則提供更靈活的資料分析能力，允許在不合併資料列的情況下進行計算，例如排名、分組和計算執行總計。這兩種技術都能有效最佳化 SQL 查詢效能。分散式 SQL 的出現，結合了傳統關聯式資料函式庫的可靠性和雲端架構的可擴充套件性與容錯能力，解決了單點故障和資料函式庫瓶頸問題。Spark、Hadoop 和 Dask 等分散式處理框架與 SQL 的整合，讓使用者能以熟悉的 SQL 語法處理大規模資料，進行更有效率的資料分析。然而，在實際應用中，SQL 介面常與 Python 程式碼協同使用，以彌補 SQL 在資料轉換、DataFrame 載入和後處理等方面的不足。DuckDB 的出現，則提供了一個結合 pandas 和 SQL 優勢的解決方案，允許在 DataFrames 上直接執行 SQL 查詢，簡化了資料處理流程，並提升了查詢效率。

深入理解SQL中的CTE（Common Table Expressions）與視窗函式

在SQL查詢中，CTE（Common Table Expressions）和視窗函式是兩種強大的工具，能夠簡化複雜查詢、提高可讀性並最佳化效能。本文將探討這兩種技術的應用與優勢。

CTE的基本概念與應用

CTE是一種暫時性的結果集，可以在查詢中被參照多次。它類別似於衍生表（derived table），但提供了更好的可讀性和重用性。

簡單的CTE範例

WITH popular_books AS (
  SELECT title,
         author,
         rating
  FROM books
  WHERE rating >= 4.5
)
SELECT title,
       author
FROM popular_books
ORDER BY rating DESC;

內容解密：

  1. WITH popular_books AS (...) 定義了一個名為 popular_books 的CTE。
  2. CTE內部查詢選取了 books 表中評分大於或等於4.5的書籍標題、作者和評分。
  3. 主查詢從 popular_books 中選取標題和作者，並按評分降序排序。

CTE的優勢

  1. 提高可讀性：將複雜查詢分解為多個簡單的CTE，使查詢結構更清晰。
  2. 減少冗餘：避免重複相同的子查詢，提高維護性。
  3. 模組化設計：每個CTE可以專注於一個特定的任務，便於理解和修改。

範例：使用CTE最佳化查詢

WITH popular_books AS (
  SELECT book_id,
         title,
         author
  FROM books
  WHERE rating >= 4.6
),
best_sellers AS (
  SELECT pb.book_id,
         pb.title,
         pb.author,
         s.total_sales
  FROM popular_books pb
  JOIN sales s ON pb.book_id = s.book_id
  WHERE s.year = 2022
  ORDER BY s.total_sales DESC
  LIMIT 5
)
SELECT *
FROM best_sellers;

內容解密：

  1. popular_books CTE選取評分大於或等於4.6的書籍資訊。
  2. best_sellers CTE根據 popular_books，進一步篩選出2022年銷量前五的書籍。
  3. 主查詢簡單地選取 best_sellers 中的所有欄位。

使用CTE避免冗餘程式碼

當需要在查詢中多次參照相同的衍生表時，CTE可以避免重複定義。

WITH high_ratings AS (
  SELECT book_id,
         title,
         rating
  FROM books
  WHERE rating >= 4.5
),
high_sales AS (
  SELECT book_id,
         COUNT(book_id) AS nbr_sales
  FROM sales
  GROUP BY book_id
)
SELECT hr.title,
       hr.rating,
       hs.nbr_sales
FROM high_ratings hr
JOIN high_sales hs ON hr.book_id = hs.book_id;

內容解密：

  1. high_ratings CTE選取高評分書籍。
  2. high_sales CTE計算每本文的銷售次數。
  3. 主查詢結合兩者，提供高評分書籍的銷售資訊。

CTE作為臨時檢視

在某些情況下，建立永久檢視可能不必要或不可行。CTE可以作為臨時替代方案。

WITH filtered_books AS (
  SELECT title,
         author
  FROM books
  WHERE rating > 4.0
)
SELECT *
FROM filtered_books;

內容解密：

  1. filtered_books CTE篩選出評分大於4.0的書籍。
  2. 主查詢簡單地選取所有篩選後的結果。

CTE促程式式碼重用

當需要在查詢的不同部分執行相同的計算時，CTE可以定義一次並多次重用。

WITH total_sales AS (
  SELECT customer_id,
         SUM(sales_amount) AS total_amount
  FROM sales
  GROUP BY customer_id
)
SELECT ts.customer_id,
       ts.total_amount,
       AVG(total_amount) OVER () AS avg_amount
FROM total_sales ts;

內容解密：

  1. total_sales CTE計算每個客戶的總銷售額。
  2. 主查詢選取客戶ID、總銷售額，並計算所有客戶的平均銷售額。

視窗函式簡介

視窗函式是SQL中另一種強大的分析工具，用於對資料集的分割槽或視窗進行操作，提供更靈活的資料分析能力。

常見用途

  1. 排名結果：在給定的視窗內對結果進行排名。
  2. 存取其他行資料：在同一個視窗記憶體取其他行的資料，用於生成報告或比較相鄰行資料。

視窗函式在SQL分析中的應用

在SQL分析中，視窗函式提供了一種強大的工具，用於對資料進行複雜的計算和分析。與聚合函式不同，視窗函式可以在不將多行資料合併為單一行輸出的情況下，對資料進行計算。

視窗函式的基本語法

視窗函式的基本語法如範例3-44所示。它包括SELECT陳述式、視窗函式和OVER子句。OVER子句用於定義視窗函式的計算範圍。

SELECT column1,
       column2,
       ...,
       window_function() OVER (PARTITION BY column1, column2, ... ORDER BY column3, column4, ...)
FROM table_name;

內容解密：

  • SELECT陳述式指定要包含在結果集中的列。
  • 視窗函式用於對指定的列或列集進行計算或聚合操作。
  • OVER子句定義了視窗函式的計算範圍。
  • PARTITION BY子句根據一或多個列將行分成多個分割槽。
  • ORDER BY子句指定每個分割槽內的順序。

視窗函式的實際應用

視窗函式的一個實際應用是計算執行總計。範例3-45展示瞭如何使用ROW_NUMBER()函式為每本文分配一個行號，按出版年份排序。

SELECT book_id,
       book_title,
       publication_year,
       ROW_NUMBER() OVER (ORDER BY publication_year) AS running_count
FROM books;

內容解密：

  • ROW_NUMBER()函式為每行分配一個唯一的編號。
  • OVER (ORDER BY publication_year)指定了按出版年份排序。

各種視窗函式的型別

視窗函式有多種型別，包括聚合函式、排名函式和分析函式。表3-4總結了這些函式。

型別函式範例
聚合函式MAX(), MIN(), AVG(), SUM(), COUNT()對每個視窗內的資料進行聚合計算
排名函式ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), PERCENT_RANK(), CUME_DIST()對視窗內的行進行排名或分組
分析函式LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE()根據視窗內的資料計算值

視窗函式的具體範例

範例3-46展示瞭如何使用RANK()函式按出版年份對書籍進行排名。

SELECT book_id,
       book_title,
       publication_year,
       RANK() OVER (ORDER BY publication_year) AS rank
FROM books;

內容解密：

  • RANK()函式根據指定的條件對行進行排名。
  • 如果多行具有相同的publication_year，則它們將被賦予相同的排名，而下一個排名將被跳過。

範例3-47展示瞭如何使用NTILE()函式將資料分成三個組。

SELECT book_id,
       book_title,
       publication_year,
       NTILE(3) OVER (ORDER BY publication_year) AS running_ntile
FROM books;

內容解密：

  • NTILE(3)將資料分成三個組。
  • 每組內的資料是按出版年份排序的。

SQL 在分散式資料處理中的應用

在企業邁向雲端的過程中，它們面臨著一個共同的挑戰。現有的關係型資料函式庫是關鍵應用的基礎，但難以充分發揮雲端的潛力，且難以有效擴充套件。資料函式庫本身正逐漸成為一個瓶頸，阻礙了轉型的速度和效率。因此，企業正在尋找一種解決方案，將經過驗證的關係型資料儲存（如 Oracle、SQL Server、Postgres 和 MySQL）的可靠性與雲端的可擴充套件性和全球覆寫能力結合起來。

分散式 SQL 的優勢

為了滿足這些需求，一些公司轉向了 NoSQL 資料函式庫。雖然這些替代方案通常能夠滿足可擴充套件性要求，但它們往往不適合作為交易型資料函式庫。原因是它們最初的設計並未提供真正的從底層開始的一致性。雖然特定的 NoSQL 解決方案最近引入了處理某些型別挑戰的進展，但它們受到各種限制，最終無法為銀行或醫院等關鍵工作負載提供必要的隔離級別。

認識到傳統關係型資料函式庫和 NoSQL 儲存的不足之處，企業轉向了一種稱為分散式 SQL 的有前途的解決方案。這種創新方法將單一邏輯資料函式庫佈署在多個物理節點上，無論是在單一資料中心還是在多個資料中心之間。透過利用分散式架構的力量，分散式 SQL 結合了彈性可擴充套件性和堅不可摧的還原能力。

分散式 SQL 的主要優勢包括：

  • 無縫擴充套件：隨著資料量的增長和使用者需求的增加，組織可以輕鬆地向分散式佈署中新增額外的節點，從而允許資料函式庫水平擴充套件。這種彈性擴充套件確保了即使在重負載下，效能仍然保持最佳狀態，並消除了傳統關係型資料函式庫經常面臨的限制。

  • 無與倫比的還原能力：由於資料分佈在多個節點上，因此它本質上具有容錯能力。如果一個節點發生故障或變得不可用，系統可以自動將查詢轉發到剩餘的健康節點，從而確保對關鍵資料的不間斷存取。這種強大的還原能力大大降低了停機和資料丟失的風險，並提高了資料函式庫的整體可靠性。

  • 全球覆寫和資料可用性：組織可以在不同的地理區域佈署節點，以將它們戰略性地放置在靠近終端使用者的位置，從而減少延遲。這種地理分佈的方法確保了可以在全球範圍內快速存取資料，從而促進高效的資料傳遞，並使組織能夠服務全球使用者群。

SQL 與分散式資料處理的結合

本文的重點不在於實際的分散式處理引擎，也不是它們的工作原理；相反，我們僅涉及它們為我們提供的介面，以便與之互動。它們大多數最終都提供 API 或 SDK。然而，一些更側重於資料分析的引擎使用 SQL 作為介面語言。事實上，分散式處理和 SQL 已成為一種強大的組合，SQL 作為利用分散式計算能力的一種方便且熟悉的介面。

像 Spark、Hadoop 和 Dask 這樣的分散式處理框架為跨多台機器或叢集處理大規模資料提供了基礎設施。這些框架分佈工作負載並平行化計算，從而實作更快、更高效的資料處理。另一方面，SQL 提供了一種宣告式和直觀的方式來表達資料操作。使用者可以利用他們的 SQL 技能，透過將 SQL 整合為分散式處理的介面來利用分散式計算框架的能力。這種方法允許無縫的可擴充套件性、高效的資料處理，以及在龐大的資料集上處理複雜分析任務的能力，同時使用熟悉的 SQL 語法。

這種組合讓使用者能夠以直接和高效的方式執行先進的資料分析和處理任務。這種強大組合的例子包括 DuckDB、dbt 本身，甚至 FugueSQL。這些介面充當分散式計算引擎之上的層，允許使用者編寫 SQL 查詢，並利用他們對 SQL 語法和語義的熟悉程度。DuckDB 具體旨在實作 SQL 查詢的高效和可擴充套件執行，同時利用分散式計算的能力。它允許使用者制定他們的分析和資料處理任務。

使用視窗函式進行資料分析

最後，我們希望瞭解先前出版的書籍的 publication_year。為此，我們使用 LAG() 函式，如範例 3-48 所示。

範例 3-48. 視窗函式—LAG()

SELECT 
  book_id,
  book_title,
  publication_year,
  LAG(publication_year) OVER (ORDER BY publication_year) AS previous_year
FROM 
  books;

#### 內容解密：

  1. LAG() 函式的作用：LAG() 函式允許您存取視窗框架內的前一行資料。它根據 OVER 子句中指定的排序，檢索指定列的前一行的值。

  2. OVER (ORDER BY publication_year) 的作用：這指定了結果集的分割槽和排序。在這個例子中，我們根據 publication_year 對結果進行排序，因此 LAG() 將傳回前一本文的 publication_year。

  3. SELECT 陳述式的作用：選擇了 book_id、book_title、publication_year 以及前一本文的 publication_year（別名為 previous_year）。

  4. FROM books 的作用：指定了要查詢的表是 books。

這個查詢可以用來比較每本文與其前一本文的出版年份，從而進行一些有用的分析，例如計算出版年份間隔等。

SQL 與 Python 的協同應用

在分散式資料處理的領域中，SQL 介面為使用者提供了一種便捷的方式來查詢和分析資料。分散式處理引擎負責在多個叢集上平行執行任務。然而，儘管 SQL 介面存在，它們經常與 Python 程式碼結合使用。即使在 Spark 檔案中，Python 程式碼仍然是執行各種任務的必要條件，例如資料轉換、DataFrame 載入以及執行 SQL 查詢後的後處理。

為何需要 Python 程式碼？

Example 3-49：一個基本的 SQL 查詢

SELECT Title,
       UnitsSold
FROM Sales
WHERE Publisher = 'O''Reilly'
ORDER BY UnitsSold DESC
LIMIT 5

此時，SQL 查詢為我們提供了所需的彙總結果。然而，如果我們想要進行額外的資料操作或將結果與外部系統整合，通常需要藉助 Python 或其他程式語言。

結合 SQL 與 Python 的優勢

例如，我們可以將彙總結果與儲存在另一個資料集中的客戶人口統計資料合併，以獲得更深入的洞察。這種操作通常需要編寫 Python 程式碼來執行資料合併和後處理步驟。此外，如果我們打算視覺化結果或將其匯出到其他格式，Python 程式碼也是必要的。

一個常見的使用案例是將資料暴露為 API，而 SQL 並不提供此功能。Example 3-50 展示瞭如何結合 SQL 與 Python 來實作端對端的流程。

Example 3-50：一個基本的 FastAPI 應用

from fastapi import FastAPI
import duckdb

app = FastAPI()

@app.get("/top_books")
def get_top_books():
    # 建立與 DuckDB 資料函式庫的連線
    conn = duckdb.connect()
    # 執行 SQL 查詢
    query = '''
        SELECT Title, UnitsSold
        FROM sales
        WHERE Publisher = "TechBooks"
        ORDER BY UnitsSold DESC
        LIMIT 5
    '''
    result = conn.execute(query)
    # 將查詢結果轉換為字典列表
    books = []
    for row in result:
        book = {
            "title": row[0],
            "units_sold": row[1]
        }
        books.append(book)
    # 以 JSON 格式傳回結果
    return {"top_books": books}

內容解密：

  1. 建立 FastAPI 應用：匯入 FastAPI 並建立一個應使用案例項 app。
  2. 定義路由：使用 @app.get("/top_books") 定義了一個 GET 請求的路由 /top_books。
  3. 處理查詢結果：將查詢結果轉換為字典列表，每個字典代表一本文及其銷售單位。
  4. 傳回 JSON 結果：將結果包裝在字典中，以 JSON 格式傳回。

資料處理的新選擇：DuckDB

在資料處理領域，pandas 是資料科學家熟悉的強大工具。然而，當面對超出記憶體限制的大型資料集或複雜的資料處理操作時，使用 SQL 可能是一個更好的解決方案。這就是 DuckDB 發揮作用的地方。

DuckDB 結合了 pandas 和 SQL 的優勢，提供了一個快速且高效的 SQL 查詢執行引擎，能夠處理大型資料集上的複雜查詢。它與 pandas DataFrames 無縫整合，允許直接在 DataFrames 上執行查詢，而無需頻繁的資料傳輸。

安裝 DuckDB

安裝 DuckDB 的過程非常簡單，可以透過 pip 包管理器完成，如 Example 3-51 所示。

Example 3-51：安裝 DuckDB

pip install duckdb

內容解密：

  1. 使用 pip 安裝：透過執行 pip install duckdb 命令來安裝 DuckDB。
  2. 無外部依賴：DuckDB 是一個輕量級的資料函式庫引擎，無需外部依賴即可在主機程式中執行。

透過結合 SQL 和 Python，我們可以利用 SQL 的便捷性和 Python 的靈活性，實作更高效的資料處理和分析工作流程。隨著 DuckDB 等工具的發展，未來將有更多可能性等待探索。

玄貓 BlackCat

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。