2025年05月17日玄貓（BlackCat）

SQL視窗函式進階應用與分析

本文探討 SQL 視窗函式的應用，包含計算滾動平均值、累積總和、比較相鄰時間段資料變化以及使用 Snowflake Scripting 語言建立預存程式和表格函式等進階技巧。文章涵蓋了多個實務案例，並以 Snowflake 資料函式庫為例，詳細說明如何使用 `lag()`、`lead()`、`listagg()`

資料函式庫 SQL

SQL 視窗函式資料分析 Snowflake 資料函式庫查詢 Snowflake Scripting

視窗函式在資料函式庫查詢中扮演著重要的角色，能夠在不影響資料排序的情況下進行各種計算和分析。常見的應用包括計算移動平均、累積總和、排名等。這些功能在商業分析、財務報表等領域都有廣泛的應用。例如，可以使用視窗函式計算每個季度的銷售額在年度總銷售額中的佔比，或者計算每個月的使用者留存率。除了這些基本應用外，視窗函式還可以結合其他 SQL 功能，例如聚合函式和子查詢，實作更複雜的資料分析需求。在實際應用中，需要根據具體的業務場景選擇合適的視窗函式和引數，才能最大程度地發揮其作用。

視窗函式的報告功能

視窗函式除了用於生成排名外，還常用於查詢異常值（如最小或最大值）或生成整個資料集的總和或平均值。對於這些型別的使用，您將使用聚合函式，如 min()、max() 和 sum()，但不是將它們與 group by 子句一起使用，而是將它們與 partition by 和/或 order by 子句配對使用。

總銷售額統計

首先，我們來看看一個查詢，該查詢計算亞洲地區所有國家的每年總銷售額：

SELECT 
    n.n_name, 
    date_part(year, o_orderdate) AS year,
    sum(o.o_totalprice) AS total_sales
FROM 
    region r
INNER JOIN 
    nation n ON r.r_regionkey = n.n_regionkey
INNER JOIN 
    customer c ON n.n_nationkey = c.c_nationkey
INNER JOIN 
    orders o ON o.o_custkey = c.c_custkey
WHERE 
    r.r_name = 'ASIA'
GROUP BY 
    n.n_name, date_part(year, o_orderdate)
ORDER BY 
    1, 2;

內容解密：

此查詢透過多表連線計算亞洲地區各國每年總銷售額。首先，region 表與 nation 表透過 r_regionkey 連線，篩選出亞洲地區的國家。然後，nation 表與 customer 表透過 n_nationkey 連線，取得這些國家的客戶資訊。最後，customer 表與 orders 表透過 c_custkey 連線，取得客戶的訂單資訊。查詢結果按國家和年份分組，並按國家和年份排序。

新增額外欄位

接下來，我們新增兩個額外的欄位：一個顯示每個國家在所有年份的總銷售額，另一個顯示每個年份所有國家的總銷售額。為此，我們使用兩個 sum() 函式，並結合 partition by 子句來生成適當的資料視窗：

SELECT 
    n.n_name, 
    date_part(year, o_orderdate) AS year,
    sum(o.o_totalprice) AS total_sales,
    sum(sum(o.o_totalprice)) OVER (PARTITION BY n.n_name) AS tot_cntry_sls,
    sum(sum(o.o_totalprice)) OVER (PARTITION BY date_part(year, o_orderdate)) AS tot_yrly_sls
FROM 
    region r
INNER JOIN 
    nation n ON r.r_regionkey = n.n_regionkey
INNER JOIN 
    customer c ON n.n_nationkey = c.c_nationkey
INNER JOIN 
    orders o ON o.o_custkey = c.c_custkey
WHERE 
    r.r_name = 'ASIA'
GROUP BY 
    n.n_name, date_part(year, o_orderdate)
ORDER BY 
    1, 2;

內容解密：

此查詢在前一個查詢的基礎上，增加了兩個新的欄位：tot_cntry_sls 和 tot_yrly_sls。tot_cntry_sls 顯示每個國家在所有年份的總銷售額，透過 sum(sum(o.o_totalprice)) OVER (PARTITION BY n.n_name) 實作。tot_yrly_sls 顯示每個年份所有國家的總銷售額，透過 sum(sum(o.o_totalprice)) OVER (PARTITION BY date_part(year, o_orderdate)) 實作。這兩個欄位利用了視窗函式的特性，對資料進行了進一步的匯總和分析。

比較與分析

您可以使用這些額外的欄位來計算每個國家或每年的銷售額百分比。同時，也可以比較平均值或最大值：

SELECT 
    n.n_name, 
    date_part(year, o_orderdate) AS year,
    sum(o.o_totalprice) AS total_sales,
    max(sum(o.o_totalprice)) OVER (PARTITION BY n.n_name) AS max_cntry_sls,
    avg(round(sum(o.o_totalprice))) OVER (PARTITION BY date_part(year, o_orderdate)) AS avg_yrly_sls
FROM 
    region r
INNER JOIN 
    nation n ON r.r_regionkey = n.n_regionkey
INNER JOIN 
    customer c ON n.n_nationkey = c.c_nationkey
INNER JOIN 
    orders o ON o.o_custkey = c.c_custkey
WHERE 
    r.r_name = 'ASIA'
GROUP BY 
    n.n_name, date_part(year, o_orderdate)
ORDER BY 
    1, 2;

內容解密：

此查詢進一步擴充套件了分析功能，透過計算每個國家的最大銷售額和每年的平均銷售額。max(sum(o.o_totalprice)) OVER (PARTITION BY n.n_name) 用於找出每個國家的最大年銷售額，而 avg(round(sum(o.o_totalprice))) OVER (PARTITION BY date_part(year, o_orderdate)) 則計算每年的平均銷售額（先四捨五入到整數）。這有助於深入瞭解各國和各年的銷售表現。

資料視窗函式的深入解析

在資料分析與處理的過程中，視窗函式（Window Functions）提供了一種強大的工具，能夠對資料進行複雜的運算與分析。其中，根據資料值的相似性或位置關係來定義資料視窗，是兩種常見的應用場景。本文將重點探討如何使用 ROWS 子句來定義根據位置的資料視窗，並介紹其在實際查詢中的應用。

根據位置的資料視窗

在某些情況下，我們需要根據資料行的位置而非具體值來建立資料視窗。例如，計算滾動平均值（Rolling Average）或累積總和（Running Total）時，就需要定義包含特定範圍內資料行的視窗。為此，SQL 提供了 ROWS 子句，讓我們能夠根據資料行的順序來指定視窗的範圍。

滾動平均值的計算

假設我們有一組按年份和季度排序的銷售資料，希望計算每季度的三個月滾動平均值。這需要為每個季度建立一個包含前一季度、當前季度和下一季度的資料視窗。以下是一個具體的 SQL 查詢範例：

SELECT 
    date_part(year, o_orderdate) AS year,
    date_part(quarter, o_orderdate) AS quarter,
    sum(o_totalprice) AS total_sales,
    avg(sum(o_totalprice)) OVER (
        ORDER BY date_part(year, o_orderdate), 
                 date_part(quarter, o_orderdate)
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS rolling_avg
FROM orders
WHERE date_part(year, o_orderdate) BETWEEN 1995 AND 1997
GROUP BY date_part(year, o_orderdate), date_part(quarter, o_orderdate)
ORDER BY 1, 2;

內容解密：

date_part(year, o_orderdate) 和 date_part(quarter, o_orderdate)：用於從訂單日期中提取年份和季度。
sum(o_totalprice)：計算每個季度的總銷售額。
avg(sum(o_totalprice)) OVER (...)：計算滾動平均值。
- ORDER BY 子句確保資料按年份和季度排序。
- ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING：定義視窗範圍為當前行的前一行、當前行和下一行。
WHERE 和 GROUP BY 子句：限制資料範圍並按年份和季度進行分組。

累積總和的計算

另一個常見的需求是計算累積總和（Running Total），即從第一筆資料到當前資料的累積和。以下是一個 SQL 查詢範例，展示如何使用 ROWS UNBOUNDED PRECEDING 來實作這一功能：

SELECT 
    date_part(year, o_orderdate) AS year,
    date_part(quarter, o_orderdate) AS quarter,
    sum(o_totalprice) AS total_sales,
    sum(sum(o_totalprice)) OVER (
        ORDER BY date_part(year, o_orderdate), 
                 date_part(quarter, o_orderdate)
        ROWS UNBOUNDED PRECEDING
    ) AS running_total
FROM orders
WHERE date_part(year, o_orderdate) BETWEEN 1995 AND 1997
GROUP BY date_part(year, o_orderdate), date_part(quarter, o_orderdate)
ORDER BY 1, 2;

內容解密：

sum(sum(o_totalprice)) OVER (...)：計算累積總和。
- ORDER BY 子句確保資料按年份和季度排序。
- ROWS UNBOUNDED PRECEDING：定義視窗範圍從第一行開始到當前行結束。
其他部分與前一個查詢類別似，用於提取年份、季度和計算總銷售額。

視窗函式的進階應用

視窗函式是資料函式庫查詢中非常強大的工具，能夠在不改變原始資料順序的前提下，進行諸如排名、匯總和計算移動平均等操作。本篇文章將探討 Snowflake 中視窗函式的各種應用，特別是在資料分析和報表生成中的實務案例。

使用 `lag()` 和 `lead()` 函式

在許多商業分析場景中，我們需要比較相鄰時間段的資料變化，例如計算季度之間的銷售額變化率。這時，lag() 和 lead() 函式就變得非常有用。

`lag()` 函式範例

SELECT 
    date_part(year, o_orderdate) AS year,
    date_part(quarter, o_orderdate) AS qrter,
    sum(o_totalprice) AS total_sales,
    lag(sum(o_totalprice), 1) OVER (
        ORDER BY date_part(year, o_orderdate), 
                 date_part(quarter, o_orderdate)
    ) AS prior_qtr
FROM orders
WHERE date_part(year, o_orderdate) BETWEEN 1995 AND 1997
GROUP BY date_part(year, o_orderdate), date_part(quarter, o_orderdate)
ORDER BY 1, 2;

內容解密：

date_part(year, o_orderdate) 和 date_part(quarter, o_orderdate) 用於提取訂單日期的年份和季度。
sum(o_totalprice) 計算每個季度的總銷售額。
lag(sum(o_totalprice), 1) 取得前一季度的總銷售額。
OVER 子句定義了資料視窗的排序規則。

結果展示：

| YEAR | QRTER | TOTAL_SALES | PRIOR_QTR | |

|

| | 1995 | 1 | 828280426.28 | NULL | | 1995 | 2 | 818992304.21 | 828280426.28 | | … | … | … | … |

`lead()` 函式範例

SELECT 
    date_part(year, o_orderdate) AS year,
    date_part(quarter, o_orderdate) AS qrter,
    sum(o_totalprice) AS total_sales,
    lead(sum(o_totalprice), 1) OVER (
        ORDER BY date_part(year, o_orderdate), 
                 date_part(quarter, o_orderdate)
    ) AS next_qtr
FROM orders
WHERE date_part(year, o_orderdate) BETWEEN 1995 AND 1997
GROUP BY date_part(year, o_orderdate), date_part(quarter, o_orderdate)
ORDER BY 1, 2;

內容解密：

lead(sum(o_totalprice), 1) 取得下一季度的總銷售額。
其他部分與 lag() 範例類別似。

其他視窗函式

Snowflake 提供了多種視窗函式，用於統計分析和資料處理。例如，listagg() 可以將多行資料聚合成一個以特定分隔符隔開的字串。

`listagg()` 範例

SELECT 
    r.r_name AS region,
    listagg(n.n_name, ',') WITHIN GROUP (ORDER BY n.n_name) AS nation_list
FROM region r
INNER JOIN nation n ON r.r_regionkey = n.n_regionkey
GROUP BY r.r_name
ORDER BY 1;

內容解密：

listagg(n.n_name, ',') 將每個區域內的國家名稱聚合成一個以逗號分隔的字串。
WITHIN GROUP (ORDER BY n.n_name) 定義了國家名稱的排序規則。

結果展示：

| REGION | NATION_LIST | |

–|

Snowflake Scripting 語言入門

Snowflake Scripting 是 Snowflake 提供的一種程式語言，允許使用者在資料函式庫中執行複雜的邏輯操作。本章將介紹 Snowflake Scripting 的基礎知識以及如何使用它來建立預存程式和表格函式。

Snowflake Scripting 的背景

Snowflake Scripting 是一種程式語言，旨在彌補 SQL 語言的非程式性限制。SQL 語言只能定義所需的結果，而不能指定如何獲得這些結果。Snowflake Scripting 允許使用者編寫程式來執行複雜的邏輯操作，例如變數定義、條件邏輯、迴圈和例外處理。

Snowflake Scripting 的基本結構

Snowflake Scripting 的基本結構稱為區塊（Block），它由以下幾個部分組成：

區塊結構

部分	功能
DECLARE	可選，用於宣告變數
BEGIN	包含 SQL 和 Scripting 命令
EXCEPTION	可選，用於處理例外
END	區塊的結尾

只有 BEGIN 和 END 是必要的，DECLARE 和 EXCEPTION 部分是可選的。

簡單的匿名區塊範例

以下是一個簡單的匿名區塊範例：

BEGIN
    SELECT 'Welcome to Snowflake Scripting';
END;

這個範例執行一個簡單的查詢，但不傳回任何結果。

使用變數和傳回值的匿名區塊

以下是一個使用變數和傳回值的匿名區塊範例：

DECLARE
    v_string VARCHAR(50);
BEGIN
    SELECT 'Welcome to Snowflake Scripting' INTO v_string;
    RETURN v_string;
END;

內容解密：

DECLARE 部分宣告了一個名為 v_string 的變數，型別為 VARCHAR(50)。
BEGIN 部分執行了一個查詢，將結果存入 v_string 變數中。
RETURN 陳述式傳回 v_string 變數的值。

例外處理

如果變數的長度不足以容納查詢結果，將會引發例外。以下是一個引發例外的範例：

DECLARE
    v_string VARCHAR(10);
BEGIN
    SELECT 'Welcome to Snowflake Scripting' INTO v_string;
    RETURN v_string;
END;

內容解密：

由於 v_string 的長度只有 10，不足以容納查詢結果，因此引發了 expression_error 例外。

新增例外處理

以下是一個新增例外處理的範例：

DECLARE
    v_string VARCHAR(10);
BEGIN
    SELECT 'Welcome to Snowflake Scripting' INTO v_string;
    RETURN v_string;
EXCEPTION
    WHEN expression_error THEN
        RETURN 'Error: ' || SQLERRM;
END;

內容解密：

EXCEPTION 部分捕捉了 expression_error 例外，並傳回錯誤訊息。

使用 Snowsight 執行 Snowflake Scripting

Snowflake 提供了一個根據瀏覽器的圖形介面 Snowsight，用於執行 Snowflake Scripting。使用者可以在 Snowsight 中編寫和執行 Snowflake Scripting 程式。

視窗函式的報告功能

總銷售額統計

內容解密：

新增額外欄位

內容解密：

比較與分析

內容解密：

資料視窗函式的深入解析

根據位置的資料視窗

滾動平均值的計算

內容解密：

累積總和的計算

內容解密：

視窗函式的進階應用

使用 lag() 和 lead() 函式

lag() 函式範例

內容解密：

結果展示：

| YEAR | QRTER | TOTAL_SALES | PRIOR_QTR | |

|

|

|

lead() 函式範例

內容解密：

其他視窗函式

listagg() 範例

內容解密：

結果展示：

| REGION | NATION_LIST | |

–|

Snowflake Scripting 語言入門

Snowflake Scripting 的背景

Snowflake Scripting 的基本結構

區塊結構

簡單的匿名區塊範例

使用變數和傳回值的匿名區塊

內容解密：

例外處理

內容解密：

新增例外處理

內容解密：

使用 Snowsight 執行 Snowflake Scripting

使用 `lag()` 和 `lead()` 函式

`lag()` 函式範例

`lead()` 函式範例

`listagg()` 範例