SQL 視窗函式深度解析與 dbt Cloud 資料轉換

SQL 視窗函式是一種強大的分析工具，能夠在不改變原有資料結構的情況下，進行分組計算、排名、滯後計算等操作。視窗函式根據分組和排序規則定義一個視窗，並在該視窗內執行計算。常見的視窗函式包括 ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE()、LAG()、LEAD()、SUM()、AVG()、MAX() 和 MIN() 等。它們可以應用於各種資料分析場景，例如計算移動平均值、累計總和、排名等。相較於傳統的分組聚合函式，視窗函式可以保留每行的詳細資訊，並在同一查詢中同時進行分組計算和單行計算，提高了查詢效率。然而，過度使用視窗函式也可能影響查詢效能，需要根據實際情況進行調整。 dbt（data build tool）則是一種流行的資料轉換和模型建立工具，簡化了資料倉儲的開發和維護。dbt Core 提供了基本的命令列工具，而 dbt Cloud 則提供了更便捷的網頁介面和協作功能。dbt 使用 SQL 語言定義資料模型，並支援版本控制、測試和佈署等功能。dbt 的核心概念包括模型、source、ref 等，可以幫助使用者構建清晰、可維護的資料管道。

視窗函式的種類

視窗函式有多種，包括：

ROW_NUMBER(): 將每一行賦予一個唯一的行號。
RANK(): 將每一行賦予一個排名。
DENSE_RANK(): 將每一行賦予一個密集排名。
NTILE(): 將每一行賦予一個分組號碼。
LAG(): 傳回前一行的值。
LEAD(): 傳回後一行的值。
SUM(): 傳回分組的總和。
AVG(): 傳回分組的平均值。
MAX(): 傳回分組的最大值。
MIN(): 傳回分組的最小值。

視窗函式的使用

視窗函式可以用來對資料進行分組和計算。以下是使用視窗函式的示例：

SELECT 
  id,
  name,
  ROW_NUMBER() OVER (ORDER BY id) AS row_num,
  RANK() OVER (ORDER BY id) AS rank,
  DENSE_RANK() OVER (ORDER BY id) AS dense_rank,
  NTILE(4) OVER (ORDER BY id) AS ntile
FROM 
  table_name;

這個示例使用視窗函式對資料進行分組和計算，包括賦予每一行一個唯一的行號、排名、密集排名和分組號碼。

視窗函式的優點

視窗函式有多個優點，包括：

可以對資料進行分組和計算。
可以對資料進行排序和排名。
可以對資料進行分組和計算。
可以傳回前一行和後一行的值。

視窗函式的缺點

視窗函式也有多個缺點，包括：

可能會對資料進行排序和排名，從而影響查詢效率。
可能會傳回大量的資料，從而影響查詢效率。
可能會對資料進行分組和計算，從而影響查詢效率。

視窗函式的應用

視窗函式（Window Function）是一種強大的工具，能夠讓我們對資料進行複雜的分析和計算。以下是視窗函式的基本語法：

<function_name> ([<arguments>])
OVER ( [PARTITION BY <part_expr>] [ORDER BY <order_expr>])

其中，PARTITION BY 用於指定資料的分割槽，ORDER BY 用於指定資料的排序。

視窗函式的種類

視窗函式可以分為兩種：排名相關函式（Rank-related functions）和視窗框架函式（Window frame functions）。

排名相關函式用於計算排名值，例如 RANK()、DENSE_RANK() 和 ROW_NUMBER()。這些函式可以用於計算資料的排名，例如計算學生成績的排名。

視窗框架函式用於計算視窗框架內的值，例如 SUM()、AVG() 和 MAX()。這些函式可以用於計算視窗框架內的資料的總和、平均值和最大值。

視窗框架的定義

視窗框架是視窗函式的核心概念。視窗框架是指視窗函式作用的資料範圍。視窗框架可以是固定或動態的。

固定視窗框架是指視窗函式作用的資料範圍是固定的，例如計算前 5 行的總和。

動態視窗框架是指視窗函式作用的資料範圍是動態的，例如計算當前行的前 5 行的總和。

Snowflake 中的視窗函式

Snowflake 是一種雲端資料倉儲平臺，支援視窗函式。以下是 Snowflake 中視窗函式的範例：

SELECT 
  O_ORDERKEY,
  O_CUSTKEY,
  O_ORDERDATE,
  O_TOTALPRICE,
  AVG(O_TOTALPRICE) OVER (PARTITION BY O_ORDERDATE) AS daily_avg,
  SUM(O_TOTALPRICE) OVER (PARTITION BY O_ORDERDATE) AS daily_total,
  SUM(O_TOTALPRICE) OVER (PARTITION BY MONTH(O_ORDERDATE)) AS monthly_total,
  O_TOTALPRICE / daily_avg * 100 AS avg_pct,
  O_TOTALPRICE / daily_total * 100 AS day_pct,
  O_TOTALPRICE / monthly_total * 100 AS month_pct
FROM 
  "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."ORDERS"
QUALIFY 
  ROW_NUMBER() OVER (PARTITION BY O_ORDERDATE) <= 5
ORDER BY 
  O_ORDERDATE, O_TOTALPRICE DESC;

這個範例使用視窗函式計算每日的平均值、總和和百分比，同時也計算每月的總和和百分比。

SQL窗函式深度解析

窗函式（Window Function）是一種強大的SQL工具，允許您對資料進行複雜的分析和計算。窗函式可以用來計算移動平均值、排名、累積總和等等。

窗函式的種類

窗函式可以分為兩種：累積窗函式（Cumulative Window Function）和滑動窗函式（Sliding Window Function）。

累積窗函式

累積窗函式是一種窗函式，會根據目前的行資料，計算從視窗開始到目前行的所有資料的累積值。累積窗函式的語法如下：

{ROWS | RANGE} BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

或

{ROWS | RANGE} BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

其中，ROWS和RANGE是窗函式的兩種模式。ROWS模式根據行數計算累積值，而RANGE模式根據排序欄位的值計算累積值。

滑動窗函式

滑動窗函式是一種窗函式，會根據目前的行資料，計算從目前行開始，往前或往後一定行數的資料的累積值。滑動窗函式的語法如下：

ROWS BETWEEN <N> { PRECEDING | FOLLOWING } AND <M> { PRECEDING | FOLLOWING }

或

ROWS BETWEEN UNBOUNDED PRECEDING AND <M> { PRECEDING | FOLLOWING }

或

ROWS BETWEEN <N> { PRECEDING | FOLLOWING } AND UNBOUNDED FOLLOWING

其中，N和M是滑動窗函式的引數，代表往前或往後的行數。

ROWS/RANGE在累積窗函式中的差異

在累積窗函式中，ROWS和RANGE是兩種不同的模式。ROWS模式根據行數計算累積值，而RANGE模式根據排序欄位的值計算累積值。

ROWS模式：根據行數計算累積值，從視窗開始到目前行的所有資料的累積值。
RANGE模式：根據排序欄位的值計算累積值，從視窗開始到目前行的所有資料的累積值，且排序欄位的值相同的行會被視為同一組。

內容解密：

窗函式的語法和差異是瞭解窗函式的關鍵。累積窗函式和滑動窗函式的語法不同，需要根據不同的需求選擇不同的窗函式模式。ROWS和RANGE是窗函式的兩種模式，根據行數或排序欄位的值計算累積值。

圖表翻譯：

  graph LR
    A[窗函式] --> B[累積窗函式]
    A --> C[滑動窗函式]
    B --> D[ROWS模式]
    B --> E[RANGE模式]
    C --> F[ROWS BETWEEN]
    C --> G[ROWS BETWEEN UNBOUNDED PRECEDING AND]
    C --> H[ROWS BETWEEN AND UNBOUNDED FOLLOWING]

此圖表展示了窗函式的兩種模式：累積窗函式和滑動窗函式。累積窗函式又可以分為ROWS模式和RANGE模式，而滑動窗函式可以分為三種不同的語法。

版本控制系統與dbt Cloud工作流程

在開始使用dbt Cloud之前，我們需要了解版本控制系統（Version Control System, VCS）的基本概念。版本控制系統是一種軟體，能夠記錄和管理檔案的變化歷史，讓多個開發者可以合作開發軟體。

版本控制系統的功能

版本控制系統的主要功能包括：

儲存檔案的變化歷史
管理多個開發者的合作
提供檔案的版本管理

Git版本控制系統

Git是一種流行的版本控制系統，於2005年由Linus Torvalds建立。Git的主要特點包括：

分散式版本控制
快速的分支和合併
強大的版本管理

建立GitHub帳戶

要使用dbt Cloud，我們需要建立一個GitHub帳戶。以下是建立GitHub帳戶的步驟：

前往GitHub官網，點選「Sign up」按鈕。
填寫註冊表格，包括電子郵件地址、密碼和使用者名稱。
驗證電子郵件地址。
填寫個人資料資訊。

建立dbt Cloud帳戶

要使用dbt Cloud，我們需要建立一個dbt Cloud帳戶。以下是建立dbt Cloud帳戶的步驟：

前往dbt Cloud官網，點選「Sign up」按鈕。
填寫註冊表格，包括電子郵件地址、密碼和使用者名稱。
驗證電子郵件地址。
填寫個人資料資訊。

設定dbt Cloud專案

要設定dbt Cloud專案，我們需要建立一個新的專案，並設定專案的基本資訊。以下是設定dbt Cloud專案的步驟：

登入dbt Cloud帳戶，點選「Create a new project」按鈕。
填寫專案基本資訊，包括專案名稱、描述和資料函式庫連線資訊。
設定專案的版本控制系統，包括Git儲存函式庫和分支。
設定專案的資料函式庫連線，包括資料函式庫型別、帳戶和密碼。

圖表翻譯：

  graph LR
    A[建立GitHub帳戶] --> B[建立dbt Cloud帳戶]
    B --> C[設定dbt Cloud專案]
    C --> D[設定版本控制系統]
    D --> E[設定資料函式庫連線]
    E --> F[完成設定]

此圖表顯示了建立GitHub帳戶、建立dbt Cloud帳戶、設定dbt Cloud專案、設定版本控制系統、設定資料函式庫連線和完成設定的流程。

使用 dbt 進行資料轉換和分析

dbt（data build tool）是一種流行的資料轉換和分析工具，提供了一種簡單且高效的方式來管理和分析資料。在本節中，我們將介紹如何使用 dbt 進行資料轉換和分析。

dbt Core 和 dbt Cloud

dbt 有兩種版本：dbt Core 和 dbt Cloud。dbt Core 是一種開源軟體，提供了基本的資料轉換和分析功能。dbt Cloud 是一種商業軟體，提供了更多的功能和工具，包括網頁介面、版本控制和合作功能。

dbt Core 工作流程

dbt Core 的工作流程通常如下：

建立一個新的分支並切換到該分支。
編輯和儲存 dbt 模型。
編譯和執行 dbt 模型。
測試 dbt 模型。
將變更提交到版本控制系統。

dbt Cloud 工作流程

dbt Cloud 的工作流程通常如下：

建立一個新的分支並切換到該分支。
編輯和儲存 dbt 模型。
測試 dbt 模型。
執行 dbt 模型。
將變更提交到版本控制系統。

使用 dbt 進行資料轉換和分析

使用 dbt 進行資料轉換和分析的步驟如下：

建立一個新的 dbt 專案。
編輯和儲存 dbt 模型。
編譯和執行 dbt 模型。
測試 dbt 模型。
將變更提交到版本控制系統。

內容解密：

-- 建立一個新的 dbt 專案
dbt init my_project

-- 編輯和儲存 dbt 模型
dbt edit my_model.sql

-- 編譯和執行 dbt 模型
dbt compile my_model.sql
dbt run my_model.sql

-- 測試 dbt 模型
dbt test my_model.sql

-- 將變更提交到版本控制系統
git add.
git commit -m "更新 dbt 模型"

圖表翻譯：

  graph LR
    A[建立 dbt 專案] --> B[編輯 dbt 模型]
    B --> C[編譯和執行 dbt 模型]
    C --> D[測試 dbt 模型]
    D --> E[提交變更到版本控制系統]

使用 dbt Cloud 進行資料分析和 ETL 工作流程

dbt Cloud 是一種根據網頁的工具，提供了簡單易用的介面來管理和執行 dbt 專案。以下是使用 dbt Cloud 進行資料分析和 ETL 工作流程的步驟：

步驟 1：選擇環境和分支

首先，需要選擇要使用的環境和分支。環境是指要執行 dbt 專案的環境，例如 development、testing 或 production。分支是指要使用的 Git 分支。

步驟 2：定義工作

接下來，需要定義工作（job）。工作是指要執行的 dbt 命令，例如 dbt run 或 dbt test。可以在工作中指定要執行的命令和引數。

步驟 3：執行工作

執行工作後，dbt Cloud 會自動執行指定的命令和引數。可以在實時檢視執行結果和日誌。

步驟 4：排程執行

如果需要定期執行工作，可以設定排程執行。dbt Cloud 支援 cron 排程語法，可以設定工作在特定時間或間隔執行。

步驟 5：發布結果

如果需要發布結果，可以設定發布選項。dbt Cloud 支援發布結果到不同的平臺，例如 GitHub 或 Slack。

使用 dbt Cloud IDE 進行 SQL 查詢和資料分析

dbt Cloud IDE 是一種根據網頁的 SQL 編輯器，提供了簡單易用的介面來進行 SQL 查詢和資料分析。以下是使用 dbt Cloud IDE 進行 SQL 查詢和資料分析的步驟：

步驟 1：開啟 IDE

首先，需要開啟 dbt Cloud IDE。可以在 dbt Cloud 的導航欄中找到 IDE 的入口。

步驟 2：編寫 SQL 查詢

接下來，需要編寫 SQL 查詢。可以使用 dbt Cloud IDE 的 SQL 編輯器來編寫查詢。

步驟 3：執行查詢

執行查詢後，dbt Cloud IDE 會自動執行查詢和顯示結果。

步驟 4：分析結果

可以在 dbt Cloud IDE 中分析查詢結果。提供了各種工具和功能來幫助分析結果，例如篩選、排序和分組。

介紹 source 和 ref 函式

dbt 提供了兩個重要的函式：source 和 ref。source 函式用於定義資料來源，ref 函式用於參照其他模型。

source 函式

source 函式用於定義資料來源。例如：

{{ source('my_table') }}

這個函式會定義一個名為 my_table 的資料來源。

ref 函式

ref 函式用於參照其他模型。例如：

{{ ref('my_model') }}

這個函式會參照一個名為 my_model 的模型。

資料倉儲建模工具dbt的專案結構和模型分析

dbt是一種流行的資料倉儲建模工具，允許使用者使用SQL語言定義和管理資料倉儲中的資料模型。在本節中，我們將探討dbt專案的結構和模型的分析。

dbt專案結構

一個典型的dbt專案包含以下幾個主要檔案和目錄：

dbt_project.yml：這是dbt專案的主組態檔，定義了專案的設定和依賴關係。
.gitignore：這是一個git特有的檔案，列出了不需要版本控制的資源，例如dbt_packages、target和logs目錄。
models目錄：這個目錄包含了dbt模型的SQL檔案，每個檔案代表了一個資料模型。
models/example目錄：這個目錄包含了示例模型的SQL檔案和組態檔。

模型分析

讓我們分析一下my_first_dbt_model.sql和my_second_dbt_model.sql這兩個模型。

my_first_dbt_model

這個模型的原始碼如下：

{{ config(materialized='table') }}

with source_data as (
  select 1 as id
  union all
  select null as id
)

select *
from source_data

這個模型使用了兩個基本的查詢來生成兩行資料，一行的ID值為1，另一行的ID值為null。然後，模型使用CTE（Common Table Expression）來定義一個名為source_data的臨時結果集。最後，模型選擇所有欄位從source_data臨時結果集。

值得注意的是，第一行的{{ config(materialized='table') }}是一個內聯組態，指示dbt將這個模型的結果物化為一個表格。如果您預覽這個模型，您將看到查詢的結果，但不會在您的資料函式庫中生成任何東西。要影響您的資料函式庫，您需要使用dbt run或dbt build命令執行模型。

my_second_dbt_model

這個模型的原始碼如下：

{{ config(materialized='table') }}

with source_data as (
  select * from {{ ref('my_first_dbt_model') }}
)

select *
from source_data

這個模型使用了ref函式來參照前面的my_first_dbt_model模型。然後，模型選擇所有欄位從source_data臨時結果集。

線性圖

如果您開啟其中一個模型並點選編輯面板下方的Lineage標籤，您將看到線性圖，它顯示了兩個模型之間的依賴關係。

分析 my_second_dbt_model

第二個模型的原始碼如下：

SELECT *
FROM {{ ref('my_first_dbt_model') }}
WHERE id = 1

如果您在使用 dbt run 命令之前預覽此模型，您將會遇到錯誤，因為您參照的第一個模型尚不存在於您的資料函式庫中。很可能，具有您選擇的schema名稱的schema也不存在。

我們將在下一節中使用 dbt run 命令，因此目前我們只需注意，這個模型只從第一個模型中選擇所有資料，該模型簡單地被參照為 my_first_dbt_model。

在我們的模型中， { 和 } 這兩個花括號符號用於開始和結束 Jinja 程式碼塊。稍後我們將更詳細地研究 Jinja。目前，請注意雙花括號 {{ 用於列印 Jinja 表示式的值。

我們預期 {{ ref('my_first_dbt_model') }} 這行將列印預出正確的 SQL 名稱，以參照包含第一個模型的表或檢視。

分析 schema.yml

此組態檔的第一行如下：

version: 2

models:
  - name: my_first_dbt_model
    description: "A starter dbt model"
    columns:
      - name: id
        description: "The primary key for this table"
    tests:
      - unique

這個組態檔定義了模型的版本、名稱、描述、欄位和測試。其中，my_first_dbt_model 是模型的名稱，description 是模型的描述，columns 定義了模型中的欄位，包括欄位名稱和描述。tests 部分定義了模型的測試，包括唯一性測試。

內容解密：

上述模型和組態檔案的原始碼和設定，將會在後續的章節中進行更詳細的解釋和分析。特別是，Jinja 的使用和模型的參照機制將會被深入探討。

圖表翻譯：

以下是使用 Mermaid 圖表來描述模型之間的關係：

  graph LR
    A[my_first_dbt_model] --> B[my_second_dbt_model]
    B --> C[資料函式庫]
    C --> D[SQL 名稱]
    D --> E[參照]
    E --> F[模型]

這個圖表展示了 my_first_dbt_model 和 my_second_dbt_model 之間的關係，以及它們與資料函式庫和 SQL 名稱的關係。

使用 dbt 進行資料轉換和模型建立

dbt（data build tool）是一種強大的資料轉換和模型建立工具，允許使用者定義和管理複雜的資料轉換和模型建立流程。以下是使用 dbt 進行資料轉換和模型建立的步驟：

步驟 1：建立 dbt 專案

首先，需要建立一個 dbt 專案。這可以透過建立一個新資料夾並在其中建立一個 dbt_project.yml 檔案來完成。

步驟 2：定義模型

接下來，需要定義模型。模型是 dbt 中的一個基本概念，代表了一個資料轉換或模型建立流程。可以使用 dbt 命令來建立一個新模型。

步驟 3：組態模型

組態模型需要定義模型的輸入和輸出資料源、資料轉換和模型建立流程等。可以使用 dbt 命令來組態模型。

步驟 4：執行模型

執行模型需要使用 dbt run 命令。這個命令會將模型編譯成 SQL 程式碼並執行它。

步驟 5：測試模型

測試模型需要使用 dbt test 命令。這個命令會執行模型的測試案例並報告測試結果。

步驟 6：修復錯誤

如果模型執行或測試出現錯誤，需要修復錯誤。可以使用 dbt 命令來修復錯誤。

步驟 7：佈署模型

佈署模型需要使用 dbt deploy 命令。這個命令會將模型佈署到生產環境。

使用 dbt 進行資料轉換和模型建立的優點

使用 dbt 進行資料轉換和模型建立有以下優點：

簡單易用：dbt 提供了一個簡單易用的介面，允許使用者輕鬆定義和管理複雜的資料轉換和模型建立流程。
高效：dbt 可以高效地執行資料轉換和模型建立流程，減少了資料處理的時間和成本。
可擴充套件：dbt 支援大規模的資料轉換和模型建立，允許使用者輕鬆處理大量的資料。
安全：dbt 提供了一個安全的環境，允許使用者保護其資料和模型。

資料模型的重要性

資料模型是資料工程的基礎，為資料提供了清晰的背景和意義。它是描述資料的結構和關係的過程，幫助我們瞭解資料的意義和價值。

資料模型的型別

資料模型可以分為三種型別：概念模型、邏輯模型和物理模型。

概念模型：是最抽象的模型，定義了專案的範圍和內容。
邏輯模型：提供了更詳細的資訊，定義了資料的結構和關係。
物理模型：是最詳細的模型，描述了資料在儲存系統中的儲存方式。

實體關係模型（E-R模型）

E-R模型是一種常用的資料模型，描述了實體、屬性和關係之間的關係。它由三個主要部分組成：

實體：是指我們想要分析的物件，例如客戶、訂單等。
屬性：是指實體的特徵，例如客戶的姓名、地址等。
關係：是指實體之間的關係，例如客戶和訂單之間的關係。

資料模型的重要性

資料模型對於資料工程專案的成功至關重要。它可以幫助我們：

瞭解資料的意義：資料模型可以幫助我們瞭解資料的背景和意義。
改善資料品質：資料模型可以幫助我們確保資料的品質和一致性。
提高資料的可重用性：資料模型可以幫助我們提高資料的可重用性和分享性。

從技術架構視角來看，SQL 視窗函式為資料分析提供了強大的工具，能夠在不改變原有資料結構的基礎上進行複雜的計算和分析。本文深入探討了視窗函式的種類、語法、ROWS/RANGE 模式差異以及在 Snowflake 中的應用，並結合 dbt 工具的專案結構、模型分析、工作流程和資料轉換等方面，展現了現代資料工程的最佳實踐。然而，視窗函式的效能取決於資料量和查詢複雜度，過於複雜的視窗函式可能會影響查詢效率。對於追求極致效能的場景，需要仔細評估視窗函式的資源消耗。玄貓認為，熟練掌握視窗函式和 dbt 等現代資料工具，能有效提升資料分析效率，是資料工程師的必備技能。

玄貓

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