2025年08月01日玄貓（BlackCat）

Snowflake視窗函式排名功能詳解

本文深入解析 Snowflake 中的視窗函式排名功能，包含 `row_number()`、`rank()`、`dense_rank()` 等，並搭配實際案例說明如何應用於銷售資料分析，例如計算季度銷售額、比較年度銷售資料、找出最佳和最差銷售季度等。此外，文章還介紹瞭如何使用 `qualify` 和 `top`

資料函式庫 SQL

Snowflake 視窗函式 SQL查詢排名函式資料分析

Snowflake 提供了強大的視窗函式，能有效處理資料排名與比較。不同於聚合函式，視窗函式可以保留個別資料列的資訊，同時進行群組計算。這在需要同時檢視個別值和群組統計資訊時非常有用，例如計算每個季度的銷售額在年度總銷售額中的佔比。row_number()、rank() 和 dense_rank() 分別針對不同的排名需求提供解決方案，row_number() 賦予唯一排名，rank() 和 dense_rank() 則處理並列情況，但 rank() 會留下排名間隙，dense_rank() 則不會。此外，first_value()、last_value() 和 nth_value() 可擷取特定排名資料，例如找出年度最佳和最差銷售季度。結合 PARTITION BY 子句，可以對資料進行分組排名，例如按年度比較季度銷售資料。最後，qualify 和 top 子句則能簡化排名查詢，提高程式碼可讀性和執行效率。

視窗函式中的排名功能解析

在資料分析與商業智慧的領域中，排名是一項重要的功能。無論是找出最佳產品、評估市場區域，還是簡單地對某些資料進行排序，排名功能都扮演著關鍵的角色。Snowflake 提供了多種排名函式，以滿足不同的需求。

排名函式的種類別

Snowflake 提供了三種主要的排名函式：row_number()、rank() 和 dense_rank()。這些函式的主要差異在於如何處理並列（ties）情況。

1. `row_number()` 函式

此函式為每一行分配一個唯一的排名，不考慮並列情況。它簡單地根據排序條件為每一行賦予一個不重複的編號。

2. `rank()` 函式

當出現並列情況時，rank() 函式會賦予相同的排名，並且在並列後留下間隙。例如，如果兩行並列第一名，則下一名的排名將是第三名。

3. `dense_rank()` 函式

與 rank() 類別似，dense_rank() 在並列時賦予相同的排名，但不會留下間隙。因此，如果兩行並列第一名，下一名的排名仍是第二名。

實際範例解析

假設我們要統計1996年每位客戶的訂單數量，並根據訂單數量進行排名。

select o_custkey, count(*) num_orders,
       row_number() over (order by count(*) desc) row_num_rnk,
       rank() over (order by count(*) desc) rank_rnk,
       dense_rank() over (order by count(*) desc) dns_rank_rnk
from orders o
where 1996 = date_part(year, o.o_orderdate)
group by o_custkey
having o_custkey in (43645,55120,71731,60250,55849,104692,20743,118636,4618,63620)
order by 2 desc;

結果分析

| O_CUSTKEY | NUM_ORDERS | ROW_NUM_RNK | RANK_RNK | DNS_RANK_RNK | |

|

-|

–|

|

| | 43645 | 5 | 1 | 1 | 1 | | 55120 | 4 | 2 | 2 | 2 | | 104692 | 4 | 3 | 2 | 2 | | … | … | … | … | … |

從結果中可以看到，三種排名函式在處理並列情況時的差異。row_number() 簡單地為每一行賦予一個唯一的編號；rank() 和 dense_rank() 在處理並列時賦予相同的排名，但 rank() 會在並列後留下間隙，而 dense_rank() 不會。

前/後 N 筆資料的取得

有時，我們感興趣的不是完整的排名列表，而是排名最前或最後的資料。對於這種需求，可以使用 first_value() 和 last_value() 函式。

程式碼重點整理

row_number()、rank() 和 dense_rank() 的差異：
- row_number()：分配唯一排名，無視並列。
- rank()：並列時賦予相同排名，並留下間隙。
- dense_rank()：並列時賦予相同排名，不留下間隙。

範例程式碼：

select o_custkey, count(*) num_orders,
       row_number() over (order by count(*) desc) row_num_rnk,
       rank() over (order by count(*) desc) rank_rnk,
       dense_rank() over (order by count(*) desc) dns_rank_rnk
from orders o
where 1996 = date_part(year, o.o_orderdate)
group by o_custkey
having o_custkey in (43645,55120,71731,60250,55849,104692,20743,118636,4618,63620)
order by 2 desc;

程式碼解密

此查詢首先篩選出1996年的訂單資料，接著根據客戶ID分組並計算每個客戶的訂單數量。然後，使用三種不同的排名函式對客戶進行排名，最後按照訂單數量降序排列結果。

row_number() 為每一行分配一個唯一的序號，無論是否有並列情況。
rank() 在出現並列時賦予相同的排名，並且在下一個排名中留下相應的間隙。
dense_rank() 同樣在並列時賦予相同的排名，但不會留下間隙。

這樣的查詢結果能夠清晰地展示不同排名函式之間的差異，以及如何根據實際需求選擇適當的函式。

視窗函式在銷售資料分析中的應用

在銷售資料分析中，我們經常需要計算不同季度之間的銷售差異，並找出最佳和最差銷售季度。本篇文章將介紹如何使用視窗函式來實作這一目標。

計算季度銷售總額

首先，我們需要計算每個季度的銷售總額。可以使用以下 SQL 查詢：

SELECT 
    date_part(year, o_orderdate) AS year,
    date_part(quarter, o_orderdate) AS qrter,
    SUM(o_totalprice) AS tot_sales
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) 計算每個季度的銷售總額。
WHERE 子句篩選出 1995 年至 1997 年之間的資料。
GROUP BY 子句按年份和季度分組資料。
ORDER BY 子句按年份和季度排序結果。

找出最佳和最差銷售季度

接下來，我們需要找出所有季度中的最佳和最差銷售季度。可以使用 first_value() 和 last_value() 函式來實作：

SELECT 
    date_part(year, o_orderdate) AS year,
    date_part(quarter, o_orderdate) AS qrter,
    SUM(o_totalprice) AS tot_sales,
    first_value(SUM(o_totalprice)) OVER (ORDER BY SUM(o_totalprice) DESC) AS top_sales,
    last_value(SUM(o_totalprice)) OVER (ORDER BY SUM(o_totalprice) DESC) AS btm_sales
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;

內容解密：

first_value(SUM(o_totalprice)) OVER (ORDER BY SUM(o_totalprice) DESC) 取得銷售總額最高的值。
last_value(SUM(o_totalprice)) OVER (ORDER BY SUM(o_totalprice) DESC) 取得銷售總額最低的值。
這兩個函式用於比較每個季度的銷售總額與最佳和最差銷售季度的總額。

計算季度銷售總額與最佳和最差銷售季度的百分比差異

有了最佳和最差銷售季度的總額後，我們可以計算每個季度的銷售總額與它們的百分比差異：

SELECT 
    date_part(year, o_orderdate) AS year,
    date_part(quarter, o_orderdate) AS qrter,
    SUM(o_totalprice) AS tot_sales,
    ROUND(SUM(o_totalprice) / first_value(SUM(o_totalprice)) OVER (ORDER BY SUM(o_totalprice) DESC) * 100, 1) AS pct_top_sales,
    ROUND(SUM(o_totalprice) / last_value(SUM(o_totalprice)) OVER (ORDER BY SUM(o_totalprice) DESC) * 100, 1) AS pct_btm_sales
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;

內容解密：

ROUND 函式用於四捨五入計算結果到小數點後一位。
pct_top_sales 和 pct_btm_sales 分別表示每個季度的銷售總額佔最佳和最差銷售季度總額的百分比。

按年度比較季度銷售資料

如果我們想按年度比較季度銷售資料，可以在 first_value() 和 last_value() 函式中新增 PARTITION BY 子句：

SELECT 
    date_part(year, o_orderdate) AS year,
    date_part(quarter, o_orderdate) AS qrter,
    SUM(o_totalprice) AS tot_sales,
    ROUND(SUM(o_totalprice) / first_value(SUM(o_totalprice)) OVER (PARTITION BY date_part(year, o_orderdate) ORDER BY SUM(o_totalprice) DESC) * 100, 1) AS pct_top_sales,
    ROUND(SUM(o_totalprice) / last_value(SUM(o_totalprice)) OVER (PARTITION BY date_part(year, o_orderdate) ORDER BY SUM(o_totalprice) DESC) * 100, 1) AS pct_btm_sales
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;

內容解密：

PARTITION BY date_part(year, o_orderdate) 將資料按年度分組，並在每個年度內進行比較。

使用 nth_value() 函式取得特定排名的季度

除了 first_value() 和 last_value()，還可以使用 nth_value() 函式來取得特定排名的季度資料：

SELECT 
    date_part(year, o_orderdate) AS year,
    date_part(quarter, o_orderdate) AS qrter,
    SUM(o_totalprice),
    first_value(date_part(quarter, o_orderdate)) OVER (PARTITION BY date_part(year, o_orderdate) ORDER BY SUM(o_totalprice) DESC) AS best_qtr,
    nth_value(date_part(quarter, o_orderdate), 2) OVER (PARTITION BY date_part(year, o_orderdate) ORDER BY SUM(o_totalprice) DESC) AS next_best_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;

內容解密：

nth_value(date_part(quarter, o_orderdate), 2) 用於取得第二佳的季度。

視窗函式與排名功能在資料分析中的應用

在資料分析領域中，視窗函式（Window Functions）提供了一種強大的工具，能夠在不改變原始資料排序的情況下進行計算與分析。本文將探討視窗函式中的排名功能及其在實際查詢中的應用。

排名功能的基礎

排名功能允許我們根據特定的條件對資料進行排序和排名。常見的排名函式包括 rank()、dense_rank()、row_number()、first_value() 和 nth_value() 等。這些函式能夠幫助我們快速找出資料中的前幾名或特定排名的資料。

使用 `first_value()` 和 `nth_value()` 進行資料比較

以下是一個使用 first_value() 和 nth_value() 的範例查詢，用於找出每年的最佳季度和次佳季度銷售額：

SELECT 
    date_part(year, o_orderdate) AS year,
    date_part(quarter, o_orderdate) AS qrter,
    SUM(o_totalprice),
    first_value(date_part(quarter, o_orderdate)) OVER (
        PARTITION BY date_part(year, o_orderdate)
        ORDER BY SUM(o_totalprice) DESC
    ) AS best_qtr,
    nth_value(date_part(quarter, o_orderdate), 2) FROM LAST OVER (
        PARTITION BY date_part(year, o_orderdate)
        ORDER BY SUM(o_totalprice) DESC
    ) AS next_worst_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)：計算每個季度的總銷售額。
first_value()：根據每個年份的分割槽，找出銷售額最高的季度。
nth_value()：同樣根據每個年份的分割槽，找出銷售額第二高的季度。
OVER (PARTITION BY ... ORDER BY ...)：定義視窗函式的分割和排序規則。

使用 `qualify` 子句最佳化排名查詢

在 Snowflake 中，qualify 子句允許我們直接在查詢中對視窗函式的結果進行過濾，避免了使用子查詢的繁瑣。以下是一個使用 qualify 的範例，用於找出供應商數量排名前五的國家：

SELECT 
    n.n_name AS name, 
    COUNT(*) AS num_suppliers,
    rank() OVER (ORDER BY COUNT(*) DESC) AS rnk
FROM 
    supplier s
INNER JOIN 
    nation n ON n.n_nationkey = s.s_nationkey
GROUP BY 
    n.n_name
QUALIFY 
    rnk <= 5;

內容解密：

rank() OVER (ORDER BY COUNT(*) DESC)：根據供應商數量對國家進行排名。
QUALIFY rnk <= 5：直接篩選出排名前五的國家。

使用 `top` 子句進行簡化查詢

另一種簡化查詢的方法是使用 top 子句，它能夠直接傳回指定數量的頂部資料行。以下是一個範例：

SELECT TOP 5
    n.n_name AS name, 
    COUNT(*) AS num_suppliers,
    rank() OVER (ORDER BY COUNT(*) DESC) AS rnk
FROM 
    supplier s
INNER JOIN 
    nation n ON n.n_nationkey = s.s_nationkey
GROUP BY 
    n.n_name
ORDER BY 
    3;

內容解密：

SELECT TOP 5：傳回前五筆資料。
ORDER BY 3：根據第三欄位（即 rnk）進行排序，確保結果是按排名順序排列。

視窗函式中的排名功能解析

排名函式的種類別

1. row_number() 函式

2. rank() 函式

3. dense_rank() 函式

實際範例解析

結果分析

| O_CUSTKEY | NUM_ORDERS | ROW_NUM_RNK | RANK_RNK | DNS_RANK_RNK | |

|

-|

–|

|

前/後 N 筆資料的取得

程式碼重點整理

程式碼解密

視窗函式在銷售資料分析中的應用

計算季度銷售總額

內容解密：

找出最佳和最差銷售季度

內容解密：

計算季度銷售總額與最佳和最差銷售季度的百分比差異

內容解密：

按年度比較季度銷售資料

內容解密：

使用 nth_value() 函式取得特定排名的季度

內容解密：

視窗函式與排名功能在資料分析中的應用

排名功能的基礎

使用 first_value() 和 nth_value() 進行資料比較

內容解密：

使用 qualify 子句最佳化排名查詢

內容解密：

使用 top 子句進行簡化查詢

內容解密：

1. `row_number()` 函式

2. `rank()` 函式

3. `dense_rank()` 函式

使用 `first_value()` 和 `nth_value()` 進行資料比較

使用 `qualify` 子句最佳化排名查詢

使用 `top` 子句進行簡化查詢