Snowflake Ranking Function：資料函式庫排名技巧解析

Snowflake 提供強大的排名函式，對於資料分析至關重要，無論是銷售業績排行、產品評估或市場分析，都能派上用場。不同排名函式的特性決定了應用場景，row_number()、rank() 和 dense_rank() 各有千秋，需根據實際需求選擇。row_number() 函式會為每一列分配一個獨一無二的排名，即使數值相同，排名也不會重複，適用於需要嚴格排序的場景。rank() 函式則會將相同數值的列分配相同的排名，後續排名會跳號，適合允許並列排名的分析。dense_rank() 函式同樣處理並列排名，但後續排名會連續遞增，不會跳號，適用於需要連續排名的分析。除了全域排名，Snowflake 也提供 first_value() 和 last_value() 函式，方便找出特定群組中的最大值或最小值，例如找出每一年銷售額最高的產品。在使用排名函式時，partition by 子句可以將資料分割成不同的群組，order by 子句則可以指定排序規則，結合使用能更精確地控制排名結果。

駕馭資料的利器：使用 INFORMATION_SCHEMA 探索資料函式庫 Metadata

在資料倉管理中，Metadata（關於資料的資料）扮演著至關重要的角色。它不僅描述了資料的結構，還提供了關於資料的其他重要資訊，例如資料型別、長度、以及最後修改時間等。INFORMATION_SCHEMA 就像一個內建的資料字典，允許我們使用 SQL 查詢來探索這些 Metadata，從而更好地理解和管理我們的資料函式庫。

透過 SQL 挖掘 Metadata

INFORMATION_SCHEMA 提供了多個視窗 (Views)，每個視窗都包含了特定型別的 Metadata。以下是一些常用的視窗及其用途：

TABLES: 包含資料函式庫中所有表格的 Metadata，例如表格名稱、型別、以及建立時間等。
COLUMNS: 包含表格中所有欄位的 Metadata，例如欄位名稱、資料型別、長度、以及是否允許 NULL 值等。
VIEWS: 包含資料函式庫中所有視窗的 Metadata，例如視窗名稱、定義、以及建立時間等。

範例 1：查詢所有視窗的定義

有時候，我們可能會忘記某個視窗的具體定義。這時，可以使用以下 SQL 查詢來取得視窗的名稱和定義：

SELECT view_name, SUBSTRING(view_definition, 1, 100) AS first_100_chars
FROM views;

這個查詢會傳回每個視窗的名稱，以及視窗定義的前 100 個字元。

範例 2：探索表格欄位資訊

要了解 learning_sql.public Schema 中所有表格的欄位資訊，可以使用以下查詢：

SELECT
    table_name,
    column_name,
    CONCAT(
        data_type,
        CASE
            WHEN data_type = 'TEXT' THEN CONCAT('(', character_maximum_length, ')')
            WHEN data_type = 'NUMBER' THEN CONCAT('(', numeric_precision, ',', numeric_scale, ')')
            ELSE ''
        END
    ) AS column_def
FROM
    columns
WHERE
    table_schema = 'PUBLIC'
ORDER BY
    table_name,
    ordinal_position;

內容解密：

這個查詢會從 COLUMNS 視窗中檢索資料。
CONCAT 函式用於將資料型別和長度資訊合併成一個欄位定義字串。
CASE 陳述式用於根據資料型別選擇不同的長度資訊。
查詢結果會按照表格名稱和欄位順序排序。

這個查詢的結果會包含表格名稱、欄位名稱、以及欄位定義。例如，CUSTOMER 表格的 C_NAME 欄位，其定義為 TEXT(25)，表示該欄位可以儲存最多 25 個字元的文字。

Metadata 的應用場景

掌握瞭如何使用 SQL 查詢 Metadata 後，我們就可以利用這些資訊來解決各種實際問題。

Schema 探索

有時候，我們需要快速瞭解資料函式庫的結構，例如有哪些表格、每個表格有哪些欄位等。這時，可以使用 INFORMATION_SCHEMA 來進行 Schema 探索。

舉例來說，如果我們想確保所有儲存人名的欄位都可以容納最多 50 個字元，可以使用以下查詢來找出所有名為 LNAME 或 LAST_NAME 的欄位：

SELECT table_name, data_type, character_maximum_length
FROM columns
WHERE table_schema = 'PUBLIC'
AND column_name IN ('LNAME', 'LAST_NAME');

追蹤 Schema 變更

INFORMATION_SCHEMA 還可以幫助我們追蹤 Schema 的變更。例如，我們可以查詢 TABLES 視窗中的 last_altered 欄位，以瞭解表格最後修改的時間：

SELECT table_name, last_altered
FROM tables
WHERE table_schema = 'PUBLIC'
ORDER BY 2 DESC;

這個查詢會傳回所有表格的名稱和最後修改時間，並按照時間降序排序。

`DESCRIBE` 指令：快速檢視錶格定義

除了使用 INFORMATION_SCHEMA，我們還可以使用 DESCRIBE 指令來快速檢視錶格的定義。例如，要檢視 employee 表格的定義，可以執行以下指令：

DESCRIBE employee;

這個指令會傳回表格中所有欄位的名稱、資料型別、以及是否允許 NULL 值等資訊。

玄貓（BlackCat）的經驗分享

玄貓（BlackCat）認為，INFORMATION_SCHEMA 是一個非常強大的工具，可以幫助我們更好地理解和管理資料函式庫。透過 SQL 查詢 Metadata，我們可以輕鬆地探索 Schema、追蹤變更、以及解決各種實際問題。當然，DESCRIBE 指令也是一個快速檢視錶格定義的好方法。

總而言之，INFORMATION_SCHEMA 是資料倉管理員和開發者不可或缺的利器。掌握了它的使用方法，就能夠更有效地駕馭資料，提升工作效率。

Snowflake Metadata 管理：利用 Information Schema 與 get_ddl() 函式

在資料倉管理中，追蹤變更和確保佈署的正確性至關重要。Snowflake 提供了 Information Schema 和 get_ddl() 函式，協助資料倉管理員執行這些任務。

佈署驗證：確保變更成功

資料函式庫變更通常在非高峰時段進行。完成佈署後，驗證所有變更已成功應用至關重要。

假設每月佈署包含變更 public.employee 表中 emp_name 欄位的大小：

ALTER TABLE public.employee MODIFY emp_name VARCHAR(30);

佈署完成後，可以執行以下查詢來尋找過去一小時內對表格所做的所有變更：

SELECT table_schema, table_name
FROM tables
WHERE last_altered > DATEADD(hour, -1, current_date);

如果佈署還包括新增或刪除表格或檢視中的欄位，則可以執行查詢來計算每個表格中的欄位數：

SELECT table_name, COUNT(*) num_columns
FROM columns
WHERE table_schema = 'PUBLIC'
GROUP BY table_name
ORDER BY 1;

在佈署前後執行此查詢，可以驗證欄位變更的數量是否正確。

生成管理指令碼：簡化重複任務

Information Schema 表格還可以用於生成 SQL 陳述式，然後可以執行這些陳述式。例如，假設團隊有一個由 QA 團隊使用的資料函式庫，需要定期使用一組測試資料進行更新。可以執行以下查詢來為給定結構描述中的每個表格生成一組刪除陳述式：

SELECT CONCAT('DELETE FROM ', table_name, ';') cmnd_str
FROM tables
WHERE table_schema = 'PUBLIC'
AND table_type = 'BASE TABLE';

為了方便複製/貼上查詢結果，可以透過在執行查詢之前設定 SnowSQL 中的 output_format 選項來移除結果集周圍的格式：

!set output_format=plain;
SELECT CONCAT('DELETE FROM ', table_name, ';') cmnd_str
FROM tables
WHERE table_schema = 'PUBLIC'
AND table_type = 'BASE TABLE';

將格式設定為 plain 會移除圍繞結果的 +、- 和 | 字元，從而可以乾淨地複製和貼上刪除陳述式。完成後，可以選擇將格式設定回預設值 (即 psql)，或設定為任何其他可用選項，例如 grid、html、json 或 csv。

利用 get_ddl() 函式：快速生成 DDL 陳述式

除了 information_schema 檢視 (由大多數其他關聯式資料函式庫實作，因此具有相當的可移植性) 之外，Snowflake 還提供了內建的 get_ddl() 函式，以協助執行資料倉管理任務。此函式可用於產生對建立表格、檢視或整個結構描述有用的陳述式。此外，該函式還可以產生用於建構預存程式、管道、序列和其他型別的 Snowflake 物件的陳述式。

例如，以下查詢產生可用於建立 public.employee 表格的陳述式 (輸出格式仍設定為 plain)：

SELECT get_ddl('table', 'public.employee');

產生的陳述式可用於在另一個結構描述或資料函式庫中建立 Employee 表格，例如用於測試的單獨資料函式庫。也可以透過 get_ddl() 產生檢視定義：

SELECT get_ddl('view', 'public.employee_vw');

還可以為整個結構描述產生一組指令碼：

SELECT get_ddl('schema', 'public');

玄貓（BlackCat）觀點

玄貓認為，Information Schema 和 get_ddl() 函式是 Snowflake 資料倉管理員的強大工具。它們提供了一種程式化的方式來查詢和操作 metadata，從而簡化了佈署驗證、指令碼生成和物件複製等任務。

儘管 Snowflake 的 Time Travel 功能不適用於檢視，但透過 Information Schema，資料倉管理員仍然可以有效地追蹤和管理資料函式庫變更，確保資料函式庫的穩定性和一致性。

總之，善用 Snowflake 提供的 metadata 管理工具，可以顯著提升資料倉管理的效率和可靠性。

使用 Snowflake 帳戶使用率視窗掌握資料函式庫物件的歷史與效能

在資料倉管理中，追蹤物件的變更歷史和效能表現至關重要。Snowflake 提供了 account_usage 視窗，讓管理者能夠深入瞭解資料函式庫物件的使用情況。然而，account_usage 存在一個缺點：資料延遲。在架構變更後，需要 45 分鐘到 3 小時才能在 account_usage 中反映出來。儘管如此，它仍然是一個寶貴的工具，可以幫助我們掌握資料函式庫的脈動。

檢視 Snowflake 資料函式庫物件的歷史變更

以下是一個查詢 account_usage.views 的範例，它會顯示過去六個月內建立的所有視窗：

SELECT table_name, table_owner, deleted
FROM snowflake.account_usage.views
ORDER BY 1, 4;

這個查詢的結果會顯示視窗被刪除和重新建立的歷史記錄。例如，order_calculation_vw 視窗在 2023 年 2 月 28 日被刪除，然後又被重新建立，這可能是為了新增欄位或修改視窗的底層查詢。

追蹤 Snowflake 資料函式庫物件過去 24 小時的使用情況

account_usage 也可以用來檢視過去 24 小時內的使用情況：

SELECT substr(query_text, 1, 40) partial_query,
       total_elapsed_time AS runtime,
       rows_produced AS num_rows
FROM snowflake.account_usage.query_history
WHERE start_time > current_date - 1
  AND query_text NOT LIKE 'SHOW%'
  AND rows_produced > 0;

這個查詢會擷取過去一天內從帳戶執行的所有 SQL 陳述式，顯示陳述式的前 40 個字元、經過的時間（毫秒）和傳回的列數。查詢 account_history.query_history 視窗是追蹤使用者社群活動、檢查效能問題和檢視哪些資料函式庫物件正在被使用的絕佳方式。

玄貓對於 Account_usage 的經驗分享

玄貓在過去的經驗中，發現 account_usage 雖然有延遲，但對於長期趨勢分析和資源使用最佳化非常有幫助。例如，透過定期分析 query_history，可以找出執行時間過長的查詢，進而最佳化 SQL 語法或調整資料函式庫索引，提升整體效能。

此外，account_usage 也可用於監控資料函式庫物件的變更。透過比較不同時間點的 views 和 tables 視窗，可以快速識別被刪除或修改的物件，及時採取應對措施，避免潛在的資料遺失或系統錯誤。

利用 Information_schema 檢索表名及其列數

除了 account_usage，information_schema 也是一個重要的 Metadata 來源。以下是如何使用 information_schema.tables 視窗檢索所有表名及其列數，並僅檢索超過 1000 列的表：

由於 Snowflake 沒有直接提供列數的欄位，你需要結合其他函式來估算或取得近似值。以下是一個可能的查詢範例，但請注意，這可能不是精確的列數，而是一個估計值：

SELECT 
    table_name,
    approximate_row_count
FROM 
    snowflake.account_usage.tables
WHERE 
    table_schema = 'YOUR_SCHEMA_NAME' -- 替換為你的 Schema 名稱
    AND table_catalog = 'YOUR_DATABASE_NAME' -- 替換為你的 Database 名稱
    AND approximate_row_count > 1000
ORDER BY 
    table_name;

程式碼解密

SELECT table_name, approximate_row_count: 選擇要顯示的欄位，包括表名和近似列數。
FROM snowflake.account_usage.tables: 從 Snowflake 的 account_usage.tables 系統視窗中檢索資料。
WHERE table_schema = 'YOUR_SCHEMA_NAME' AND table_catalog = 'YOUR_DATABASE_NAME': 替換 YOUR_SCHEMA_NAME 和 YOUR_DATABASE_NAME 為你要查詢的 Schema 和 Database 名稱，以篩選特定範圍的表。
AND approximate_row_count > 1000: 篩選出近似列數大於 1000 的表。
ORDER BY table_name: 按照表名進行排序，使結果更易於閱讀。

請注意：approximate_row_count 提供的數值是一個近似值，可能不完全準確。如果需要精確的列數，你可能需要執行 SELECT COUNT(*) 查詢，但這可能會消耗較多資源，特別是對於大型表。

找出被多次建立的視窗

以下是一個針對 account_usage.views 視窗的查詢範例，用於傳回任何被建立多次的視窗名稱：

SELECT table_name
FROM snowflake.account_usage.views
GROUP BY table_name
HAVING COUNT(*) > 1;

程式碼解密

SELECT table_name: 選擇要顯示的欄位，即表名（在這個上下文中，指的是視窗的名稱）。
FROM snowflake.account_usage.views: 從 Snowflake 的 account_usage.views 系統視窗中檢索資料。
GROUP BY table_name: 按照表名進行分組，這樣可以計算每個視窗名稱出現的次數。
HAVING COUNT(*) > 1: 使用 HAVING 子句篩選出那些出現次數大於 1 的表名，這表示這些視窗被建立了多次。

這個查詢透過將 account_usage.views 中的資料按照 table_name 進行分組，然後使用 COUNT(*) 函式計算每個 table_name 出現的次數。HAVING COUNT(*) > 1 確保只傳回那些出現次數大於 1 的 table_name，從而找出被建立多次的視窗。

透過這個查詢，可以快速識別出哪些視窗可能經歷了多次修改或重建，進而追蹤其變更歷史和原因。

銷售報表分析：多重資料視窗的應用

假設你需要產生一份報告，顯示從 1995 年到 1997 年，每年的每季總訂單金額。以下是一個可以產生所需結果的查詢範例：

PUBLIC>select date_part(year, o_orderdate) year,
date_part(quarter, o_orderdate) quarter,
sum(o_totalprice) 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;

執行上述查詢後，你會得到類別似以下的結果：

+
---
---
+
---
-
---
--+
---
-
---
-
---
---
+
| YEAR | QUARTER | TOT_SALES    |
|
---
---
+
---
-
---
--+
---
-
---
-
---
---
|
| 1995 | 1       | 828280426.28 |
| 1995 | 2       | 818992304.21 |
| 1995 | 3       | 845652776.68 |
| 1995 | 4       | 824596303.26 |
| 1996 | 1       | 805551195.59 |
| 1996 | 2       | 809903462.32 |
| 1996 | 3       | 841091513.43 |
| 1996 | 4       | 839827181.45 |
| 1997 | 1       | 793402839.95 |
| 1997 | 2       | 824211569.74 |
| 1997 | 3       | 824176170.61 |
| 1997 | 4       | 813296140.78 |
+
---
---
+
---
-
---
--+
---
-
---
-
---
---
+

除了每季的銷售總額，這份報表還應該顯示該季度佔全年銷售額的百分比（同一年所有四個季度）。在學習本章之前，你可能會想將先前的結果載入到 Excel 中，或編寫 Python 指令碼來產生額外的欄位。但實際上，你可以透過定義多個資料視窗來輕鬆產生這些結果，每個視窗包含同一年份的資料列。以下是如何實作的：

PUBLIC>select date_part(year, o_orderdate) year,
date_part(quarter, o_orderdate) qrter,
sum(o_totalprice) tot_sales,
sum(sum(o_totalprice))
over (partition by
date_part(year, o_orderdate)) tot_yrly_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;

上述查詢會產生如下的結果：

+
---
---
+
---
-
---
+
---
-
---
-
---
---
+
---
-
---
-
---
-
---
-+
| YEAR | QRTER | TOT_SALES    | TOT_YRLY_SALES |
|
---
---
+
---
-
---
+
---
-
---
-
---
---
+
---
-
---
-
---
-
---
-|
| 1995 | 1     | 828280426.28 | 3317521810.43 |
| 1995 | 2     | 818992304.21 | 3317521810.43 |
| 1995 | 3     | 845652776.68 | 3317521810.43 |
| 1995 | 4     | 824596303.26 | 3317521810.43 |
| 1996 | 1     | 805551195.59 | 3296373352.79 |
| 1996 | 2     | 809903462.32 | 3296373352.79 |
| 1996 | 3     | 841091513.43 | 3296373352.79 |
| 1996 | 4     | 839827181.45 | 3296373352.79 |
| 1997 | 1     | 793402839.95 | 3255086721.08 |
| 1997 | 2     | 824211569.74 | 3255086721.08 |
| 1997 | 3     | 824176170.61 | 3255086721.08 |
| 1997 | 4     | 813296140.78 | 3255086721.08 |
+
---
---
+
---
-
---
+
---
-
---
-
---
---
+
---
-
---
-
---
-
---
-+

tot_yrly_sales 欄位使用 partition by 子句來定義一個資料視窗，包含同一年份的所有資料列。因此，這裡定義了 3 個資料視窗，分別對應 1995 年、1996 年和 1997 年。每年的總銷售額是透過「加總總額」產生的，這就是為什麼你會看到 sum() 函式被使用了兩次：

sum(sum(o_totalprice))

第一次加總產生了每個季度的總銷售額，而外層的加總則產生了同一個資料視窗（年份）中所有季度的總銷售額。現在你已經有了年度總額，你可以使用視窗函式作為分母來計算百分比，從而完成查詢：

PUBLIC>select date_part(year, o_orderdate) year,
date_part(quarter, o_orderdate) qrter,
sum(o_totalprice) tot_sales,
round(sum(o_totalprice) /
sum(sum(o_totalprice))
over (partition by date_part(year, o_orderdate))
* 100, 1) pct_of_yrly_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;

上述查詢會產生如下的結果：

+
---
---
+
---
-
---
+
---
-
---
-
---
---
+
---
-
---
-
---
-
---
-
---
+
| YEAR | QRTER | TOT_SALES    | PCT_OF_YRLY_SALES |
|
---
---
+
---
-
---
+
---
-
---
-
---
---
+
---
-
---
-
---
-
---
-
---
|
| 1995 | 1     | 828280426.28 | 25.0              |
| 1995 | 2     | 818992304.21 | 24.7              |
| 1995 | 3     | 845652776.68 | 25.5              |
| 1995 | 4     | 824596303.26 | 24.9              |
| 1996 | 1     | 805551195.59 | 24.4              |
| 1996 | 2     | 809903462.32 | 24.6              |
| 1996 | 3     | 841091513.43 | 25.5              |
| 1996 | 4     | 839827181.45 | 25.5              |
| 1997 | 1     | 793402839.95 | 24.4              |
| 1997 | 2     | 824211569.74 | 25.3              |
| 1997 | 3     | 824176170.61 | 25.3              |
| 1997 | 4     | 813296140.78 | 25.0              |
+
---
---
+
---
-
---
+
---
-
---
-
---
---
+
---
-
---
-
---
-
---
-
---
+

資料分割與排序：視窗函式的關鍵

在先前的章節中，你已經看過如何使用 partition by 子句來定義資料視窗的範例。對於某些視窗函式（例如 sum() 和 avg()），只需要定義資料視窗即可。然而，對於其他型別的視窗函式，則需要對每個視窗內的資料列進行排序，這就需要使用 order by 子句。

舉例來說，假設你需要為一年中的每個季度分配一個排名，以顯示哪個季度的總銷售額最高（排名 = 1），哪個季度的銷售額次高（排名 = 2），以此類別推。為了在資料視窗內分配排名，你需要描述如何對資料列進行排序，如下面的查詢所示：

PUBIC>select date_part(year, o_orderdate) year,
date_part(quarter, o_orderdate) qrter,
sum(o_totalprice) tot_sales,
rank()
over (partition by date_part(year, o_orderdate)
order by sum(o_totalprice) desc) qtr_rank_per_year
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;

上述查詢會產生如下的結果：

+
---
---
+
---
-
---
+
---
-
---
-
---
---
+
---
-
---
-
---
-
---
-
---
+
| YEAR | QRTER | TOT_SALES    | QTR_RANK_PER_YEAR |
|
---
---
+
---
-
---
+
---
-
---
-
---
---
+
---
-
---
-
---
-
---
-
---
|
| 1995 | 1     | 828280426.28 | 2                 |
| 1995 | 2     | 818992304.21 | 4                 |
| 1995 | 3     | 845652776.68 | 1                 |
| 1995 | 4     | 824596303.26 | 3                 |
| 1996 | 1     | 805551195.59 | 4                 |
| 1996 | 2     | 809903462.32 | 3                 |
| 1996 | 3     | 841091513.43 | 1                 |
| 1996 | 4     | 839827181.45 | 2                 |
| 1997 | 1     | 793402839.95 | 4                 |
| 1997 | 2     | 824211569.74 | 1                 |
| 1997 | 3     | 824176170.61 | 2                 |
| 1997 | 4     | 813296140.78 | 3                 |
+
---
---
+
---
-
---
+
---
-
---
-
---
---
+
---
-
---
-
---
-
---
-
---
+

這個範例同時使用了 partition by 和 order by 子句來定義資料視窗，並描述每個視窗內的排序方式。然而，如果你想要在整個結果集中產生排名，你可以省略 partition by 子句：

PUBLIC>select date_part(year, o_orderdate) year,
date_part(quarter, o_orderdate) qrter,
sum(o_totalprice) tot_sales,

Snowflake Ranking Function：掌握資料排名的藝術

在資料分析的世界裡，排名是一項至關重要的技術。無論是找出最佳銷售員、評估產品表現，還是分析市場區域，排名都能幫助我們做出更明智的決策。Snowflake 提供了多種排名函式，讓資料分析師能夠靈活地處理各種排名需求。

排名函式：各有千秋

Snowflake 主要提供三種排名函式：row_number()、rank() 和 dense_rank()。它們的主要區別在於如何處理並列的情況。

| 排名函式 | 描述 | | :

– | :

| | row_number() | 為每一列分配唯一的排名，不考慮並列情況，排名連續不間斷。 | | rank() | 並列的列給予相同排名，排名會出現跳號。 | | dense_rank() | 並列的列給予相同排名，但排名是連續的，不會出現跳號。 |

為了更清楚地理解它們的差異，讓玄貓用一個實際的例子來說明。假設我們想找出 1996 年訂單數量最多的前幾名客戶。首先，我們可以使用以下 SQL 查詢來計算每位客戶的訂單數量：

select o_custkey, count(*) num_orders
from orders o
where 1996 = date_part(year, o.o_orderdate)
group by o_custkey
order by 2 desc
limit 10;

執行結果如下：

+
---
-
---
-
---
+
---
-
---
-
---
-+
| O_CUSTKEY | NUM_ORDERS |
|
---
-
---
-
---
+
---
-
---
-
---
-|
| 43645     | 5          |
| 55120     | 4          |
| 71731     | 4          |
| 60250     | 4          |
| 55849     | 4          |
| 104692    | 4          |
| 20743     | 3          |
| 118636    | 3          |
| 4618      | 3          |
| 63620     | 3          |
+
---
-
---
-
---
+
---
-
---
-
---
-+

從結果中可以看到，客戶 43645 有 5 張訂單，排名第一。接下來有五位客戶的訂單數量都是 4 張，然後有四位客戶的訂單數量是 3 張。現在，讓我們使用三種不同的排名函式來看看結果有什麼不同：

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            |
| 60250     | 4          | 4           | 2        | 2            |
| 55849     | 4          | 5           | 2        | 2            |
| 71731     | 4          | 6           | 2        | 2            |
| 20743     | 3          | 7           | 7        | 3            |
| 4618      | 3          | 8           | 7        | 3            |
| 63620     | 3          | 9           | 7        | 3            |
| 118636    | 3          | 10          | 7        | 3            |
+
---
-
---
-
---
+
---
-
---
-
---
-+
---
-
---
-
---
--+
---
-
---
---
+
---
-
---
-
---
---
+

從結果中可以清楚地看到三種排名函式的差異：

row_number() 為每一列分配了唯一的排名，即使訂單數量相同，排名也不會重複。
rank() 在訂單數量為 4 的客戶那裡出現了並列，因此排名從 2 直接跳到 7。
dense_rank() 也處理了並列的情況，但排名是連續的，沒有跳號。

Top N 排名的另一種選擇

除了為整個結果集分配排名外，有時我們可能只對前幾名或後幾名的資料感興趣。這時候，可以使用 first_value() 和 last_value() 函式來取得排名最高的或最低的列的值。

讓玄貓回顧一下之前使用過的查詢，該查詢計算了 1995 年至 1997 年每季的總銷售額：

select date_part(year, o_orderdate) year,
       date_part(quarter, o_orderdate) qrter,
       sum(o_totalprice) 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;

這個查詢會傳回一個包含年份、季度和總銷售額的結果集。如果我們想找出銷售額最高的季度，可以使用 first_value() 函式。

玄貓 BlackCat

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

Snowflake Ranking Function：資料函式庫排名技巧解析

駕馭資料的利器：使用 INFORMATION_SCHEMA 探索資料函式庫 Metadata

透過 SQL 挖掘 Metadata

Metadata 的應用場景

Schema 探索

追蹤 Schema 變更

DESCRIBE 指令：快速檢視錶格定義

玄貓（BlackCat）的經驗分享

Snowflake Metadata 管理：利用 Information Schema 與 get_ddl() 函式

佈署驗證：確保變更成功

生成管理指令碼：簡化重複任務

利用 get_ddl() 函式：快速生成 DDL 陳述式

玄貓（BlackCat）觀點

使用 Snowflake 帳戶使用率視窗掌握資料函式庫物件的歷史與效能

檢視 Snowflake 資料函式庫物件的歷史變更

追蹤 Snowflake 資料函式庫物件過去 24 小時的使用情況

玄貓對於 Account_usage 的經驗分享

利用 Information_schema 檢索表名及其列數

找出被多次建立的視窗

銷售報表分析：多重資料視窗的應用

資料分割與排序：視窗函式的關鍵

Snowflake Ranking Function：掌握資料排名的藝術

排名函式：各有千秋

| 排名函式 | 描述 | | :

– | :

Top N 排名的另一種選擇

玄貓 BlackCat

`DESCRIBE` 指令：快速檢視錶格定義