Snowflake 資料分組與聚合技巧精華

– 13 分鐘閱讀

Snowflake 對於處理大量資料的效率，仰賴於其強大的資料分組和聚合功能。當我們需要從不同角度分析資料，例如計算各個客戶的總消費金額或找出特定產品的銷售趨勢時，這些功能就變得至關重要。利用 GROUP BY 子句，我們可以將資料按照指定的欄位進行分組，再搭配聚合函式，例如 SUM()、COUNT()、AVG() 等，計算每個群組的統計資料。更進一步，HAVING 子句允許我們根據聚合結果篩選群組，例如找出消費金額超過特定門檻的客戶群。這些技術的組合，讓我們能夠從龐大的資料集中快速提取出關鍵資訊，例如高價值客戶或熱銷產品。此外，Snowflake 也提供了進階的聚合功能，例如 ROLLUP，可以自動產生多層次的彙總資料，例如各個產品類別的銷售額以及所有產品的總銷售額，省去手動計算的麻煩。

Snowflake 資料處理：分組與聚合的藝術

在資料函式庫的世界裡，資料通常以最低的粒度儲存，以滿足各種操作需求。以 Snowflake 為例，它儲存了客戶訂單的詳細資料，這些資料對於帳務處理和庫存管理等商業活動至關重要。然而，有時我們需要從更高的層次分析資料，例如按地區劃分的年度銷售額，這對於行銷和業務部門來說更有價值。玄貓將探討如何在 Snowflake 中對資料進行分組，以滿足各種業務需求。

行銷活動的資料分組應用：找出最佳客戶

假設行銷部門的副執行長希望針對最佳客戶推出一項特別促銷活動，提供下次訂單 25% 的折扣。符合資格的客戶必須消費超過 180 萬美元或下達 8 個或更多訂單。身為資料專家的你，需要找出哪些客戶符合這些條件。

Snowflake 範例資料函式庫包含 115,269 個訂單，若要逐一檢視這些資料是不切實際的。因此，你需要將訂單按照客戶分組，然後計算每個客戶的訂單數量和總消費金額。這可以透過 GROUP BY 子句實作，它能根據一個或多個欄位的共同值將資料列分組在一起。以下是為行銷副執行長準備資料的第一步：

SELECT o_custkey
FROM orders
GROUP BY o_custkey;

這個查詢傳回了 66,076 列，也就是 Orders 表格中不同的 custkey 值的數量。雖然這比檢視所有 115,000 個訂單有所改善，但效率仍然不夠高。下一步是計算每個客戶的訂單數量和總訂單金額。這可以透過聚合函式 SUM() 和 COUNT() 實作，這些函式將應用於每個群組中的所有資料列：

SELECT o_custkey,
       SUM(o_totalprice) AS total_sales,
       COUNT(*) AS number_of_orders
FROM orders
GROUP BY o_custkey;

total_sales 欄位加總了所有具有相同 custkey 值的資料列的 totalprice 欄位，而 number_of_orders 欄位則計算了具有相同 custkey 值的資料列數量。因此，我們知道 custkey 值為 77194 的客戶總共下了 3 個訂單，總金額超過 693,070 美元。這正是我們需要的資訊，但查詢仍然傳回過多的資料列，使其難以使用。

使用 HAVING 子句篩選分組後的資料

找出頂級客戶的最後一步是篩選掉總銷售額低於 1,800,000 美元或訂單數量少於 8 個的資料列。篩選通常在 WHERE 子句中完成，但這種篩選是在資料分組後進行的，因此需要使用 HAVING 子句。玄貓在過去為金融科技公司設計資料分析系統時，也經常使用這種方法來篩選高價值客戶。

SELECT o_custkey,
       SUM(o_totalprice) AS total_sales,
       COUNT(*) AS number_of_orders
FROM orders
GROUP BY o_custkey
HAVING SUM(o_totalprice) >= 1800000 OR COUNT(*) >= 8;

HAVING 子句允許我們在分組後對資料進行篩選，這對於分析聚合資料非常有用。透過這個查詢，我們可以快速找出符合促銷活動資格的頂級客戶。

資料型別轉換：CAST() 與 :: 的應用

在資料處理過程中，資料型別轉換是常見的需求。Snowflake 提供了多種方式來轉換資料型別，包括 CAST() 函式和 :: 運算元。

CAST() 函式

CAST() 函式用於將一個資料型別轉換為另一個資料型別。例如，將字串轉換為日期：

SELECT CAST('2023-09-23' AS DATE);

然而，如果字串格式不符合 Snowflake 的預期，CAST() 函式可能會丟擲錯誤。為了避免這種情況，可以使用 TRY_CAST() 函式，它在轉換失敗時會傳回 NULL：

SELECT TRY_CAST('09-23-2023' AS DATE);

CAST() 函式也可以用於將字串轉換為數字，或將實數轉換為整數：

SELECT CAST('123.456' AS NUMBER(6,3)),
       CAST(123.456 AS INTEGER);

:: 運算元

:: 運算元是 CAST() 函式的簡寫形式，可以更方便地進行資料型別轉換：

SELECT '09/23/2023'::DATE AS date_val,
       '23-SEP-2023'::TIMESTAMP AS tmstmp_val,
       '123.456'::NUMBER(6,3) AS num_val;

:: 運算元是轉換值的最簡單方式，但在轉換失敗時會丟擲錯誤，因為它沒有類別似 TRY_CAST() 的 “try” 版本。

客戶分群：利用 HAVING 篩選銷售資料

在資料分析中，我們經常需要從大量的資料中提取有價值的資訊。假設行銷部門想針對消費金額較高的顧客推出促銷活動，為了找出符合資格的客戶，最初可能會嘗試使用 WHERE 子句來過濾分組後的資料。然而，WHERE 子句是在分組之前進行篩選，無法直接應用於分組後的結果。

這時，HAVING 子句就派上用場了。HAVING 允許我們對 GROUP BY 分組後的資料進行條件篩選。以下面的 SQL 查詢為例，我們可以找出總銷售額達到一定金額或訂單數量超過一定值的客戶：

SELECT o_custkey,
       SUM(o_totalprice) AS total_sales,
       COUNT(*) AS number_of_orders
  FROM orders
 GROUP BY o_custkey
HAVING SUM(o_totalprice) >= 1800000
    OR COUNT(*) >= 8;

這個查詢會先將 orders 表格按照 o_custkey（客戶鍵）進行分組，然後計算每個客戶的總銷售額 (total_sales) 和訂單數量 (number_of_orders)。最後，HAVING 子句會篩選出總銷售額大於等於 1,800,000 或訂單數量大於等於 8 的客戶。

執行以上查詢後，我們就能得到符合行銷部門需求的客戶名單，總共有 16 位客戶符合資格。這個查詢範例展示了 GROUP BY 子句、SUM() 和 COUNT() 聚合函式以及 HAVING 子句的綜合應用，接下來將更深入地探討這些概念。

聚合函式：資料分析的利器

聚合函式是用於對一組資料執行特定操作的函式，例如計算數量、總和、平均值等。即使查詢中沒有 GROUP BY 子句，仍然存在一個隱含的分組，即整個結果集。

以下範例展示瞭如何在 Orders 表格的所有列上使用多個聚合函式：

SELECT COUNT(*) AS num_orders,
       MIN(o_totalprice) AS min_price,
       MAX(o_totalprice) AS max_price,
       AVG(o_totalprice) AS avg_price
  FROM orders;

這個查詢會計算訂單總數，以及所有訂單中的最低、最高和平均總價。

如果我們想將這個查詢擴充套件到按年份顯示相同的計算結果，可能會遇到問題。以下是一個錯誤的嘗試：

SELECT DATE_PART(year, o_orderdate) AS order_year,
       COUNT(*) AS num_orders,
       MIN(o_totalprice) AS min_price,
       MAX(o_totalprice) AS max_price,
       AVG(o_totalprice) AS avg_price
  FROM orders;

這個查詢會產生錯誤，因為 date_part() 不是聚合函式，不能直接應用於所有列。解決方法是使用 GROUP BY 子句，明確指定按照年份進行分組：

SELECT DATE_PART(year, o_orderdate) AS order_year,
       COUNT(*) AS num_orders,
       MIN(o_totalprice) AS min_price,
       MAX(o_totalprice) AS max_price,
       AVG(o_totalprice) AS avg_price
  FROM orders
 GROUP BY DATE_PART(year, o_orderdate);

這個查詢會先按照訂單年份進行分組，然後計算每個年份的訂單數量、最低價格、最高價格和平均價格。

重點在於，當使用聚合函式時，如果沒有 GROUP BY 子句，則聚合函式會應用於結果集中的所有列。否則，需要使用 GROUP BY 子句，其中包含 SELECT 子句中除了聚合函式之外的所有欄位。

接下來，我們將介紹一些常用的聚合函式。

COUNT() 函式：統計資料列數

COUNT() 函式用於計算每個群組中的列數，這是非常常見的操作。COUNT() 函式有多種變體，其中一種是與 DISTINCT 運算元結合使用，以計算不同值的數量：

SELECT COUNT(*) AS total_orders,
       COUNT(DISTINCT o_custkey) AS num_customers,
       COUNT(DISTINCT DATE_PART(year, o_orderdate)) AS num_years
  FROM orders;

這個查詢會計算 Orders 表格中的總列數，以及 custkey 和 orderdate（年份部分）的不同值的數量。結果顯示，在七年期間，共有 66,076 位不同的客戶下了總共 115,269 筆訂單。

另一個有用的變體是 COUNT_IF() 函式，它會計算給定條件評估為 true 的列數。例如，若要計算 1992 年和 1995 年的訂單數量，可以使用以下查詢：

SELECT COUNT_IF(1992 = DATE_PART(year, o_orderdate)) num_1992,
       COUNT_IF(1995 = DATE_PART(year, o_orderdate)) num_1995
  FROM orders;

這個查詢會傳回兩欄，分別顯示 1992 年和 1995 年的訂單數量。

可惜的是，SUM()、MIN() 和 MAX() 函式沒有類別似的變體，允許包含或排除列。不過，正如在後面的章節中將看到的，還有其他方法可以將條件邏輯注入到聚合函式中。

MIN()、MAX()、AVG() 和 SUM() 函式：數值資料的分析

當對包含數值欄位的資料進行分組時，經常需要找出群組中的最大值或最小值、計算群組的平均值，或對群組中的所有列的值求和。MAX()、MIN()、AVG() 和 SUM() 聚合函式就是用於這些目的。MAX() 和 MIN() 也經常與日期欄位一起使用。

以下查詢會找出每年第一筆和最後一筆訂單的日期，以及每年的平均銷售額和總銷售額：

SELECT DATE_PART(year, o_orderdate) AS order_year,
       MIN(o_orderdate) AS first_order,
       MAX(o_orderdate) AS last_order,
       AVG(o_totalprice) AS avg_sales,
       SUM(o_totalprice) AS total_sales
  FROM orders
 GROUP BY DATE_PART(year, o_orderdate)
 ORDER BY order_year;

這個查詢首先按照訂單年份進行分組，然後計算每個年份的第一筆訂單日期、最後一筆訂單日期、平均銷售額和總銷售額。最後，結果會按照年份排序。

年度銷售資料分析：PostgreSQL 中的群組與彙總應用

在資料倉管理中，GROUP BY 子句與彙總函式是進行資料分析時不可或缺的工具。透過 GROUP BY，我們可以將資料表中的資料按照一個或多個欄位進行分組，然後使用彙總函式（例如 MIN(), MAX(), AVG(), SUM()）對每個群組的資料進行計算。玄貓將會展示如何運用這些功能，從訂單資料中提取有價值的資訊。

年度訂單分析

以下 SQL 查詢用於分析每年的訂單資料，包括最早和最晚的訂單日期、平均訂單價格以及總銷售額：

select date_part(year, o_orderdate) as year,
       min(o_orderdate) as first_order,
       max(o_orderdate) as last_order,
       avg(o_totalprice) as avg_price,
       sum(o_totalprice) as tot_sales
from orders
group by date_part(year, o_orderdate);

程式碼解密

  1. date_part(year, o_orderdate) as year: 提取 o_orderdate 欄位中的年份，並將其命名為 year。
  2. min(o_orderdate) as first_order: 計算每年最早的訂單日期，命名為 first_order。
  3. max(o_orderdate) as last_order: 計算每年最晚的訂單日期，命名為 last_order。
  4. avg(o_totalprice) as avg_price: 計算每年訂單的平均價格，命名為 avg_price。
  5. sum(o_totalprice) as tot_sales: 計算每年的總銷售額，命名為 tot_sales。
  6. from orders: 指定從 orders 資料表查詢資料。
  7. group by date_part(year, o_orderdate): 按照年份對資料進行分組。

這個查詢的結果會顯示每年的關鍵銷售資料，例如：

+------+-------------+------------+-------------+---------------+
| YEAR | FIRST_ORDER | LAST_ORDER | AVG_PRICE | TOT_SALES       |
+------+-------------+------------+-------------+---------------+
| 1997 | 1997-01-01  | 1997-12-31 | 186987.9780 | 3255086721.08 |
| 1998 | 1998-01-01  | 1998-08-02 | 188929.9753 | 1925196448.52 |
| 1995 | 1995-01-01  | 1995-12-31 | 188100.1196 | 3317521810.43 |
| 1994 | 1994-01-01  | 1994-12-31 | 187566.4450 | 3278473892.67 |
| 1992 | 1992-01-01  | 1992-12-31 | 189062.8792 | 3309734764.39 |
| 1993 | 1993-01-01  | 1993-12-31 | 188041.4138 | 3270416270.14 |
| 1996 | 1996-01-01  | 1996-12-31 | 186689.3216 | 3296373352.79 |
+------+-------------+------------+-------------+---------------+

使用 listagg() 函式

listagg() 函式在需要將多行資料合併成單一欄位時非常有用，尤其是在產生 XML 或 JSON 檔案時。這個函式可以將一個群組內的多個值連線成一個以特定分隔符號分隔的字串。

以下範例展示如何使用 listagg() 函式列出每個地區的所有國家：

select r.r_name,
       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;

程式碼解密

  1. r.r_name: 選擇地區名稱。
  2. listagg(n.n_name, ','): 使用 listagg() 函式將每個地區的國家名稱連線成一個字串，使用逗號作為分隔符號。
  3. within group (order by n.n_name): 指定國家名稱在字串中按照字母順序排列。
  4. from region r inner join nation n on r.r_regionkey = n.n_regionkey: 將 region 資料表和 nation 資料表進行內部連線，根據 r_regionkey 欄位。
  5. group by r.r_name: 按照地區名稱對結果進行分組。

這個查詢的結果會顯示每個地區及其所包含的國家列表：

+-------------+----------------------------------------------+
| R_NAME      | NATION_LIST                                  |
+-------------+----------------------------------------------+
| AFRICA      | ALGERIA,ETHIOPIA,KENYA,MOROCCO,MOZAMBIQUE    |
| AMERICA     | ARGENTINA,BRAZIL,CANADA,PERU,UNITED STATES    |
| MIDDLE EAST | EGYPT,IRAN,IRAQ,JORDAN,SAUDI ARABIA          |
| ASIA        | CHINA,INDIA,INDONESIA,JAPAN,VIETNAM          |
| EUROPE      | FRANCE,GERMANY,ROMANIA,RUSSIA,UNITED KINGDOM |
+-------------+----------------------------------------------+

多欄位分組

GROUP BY 子句不僅可以按照單一欄位分組，還可以同時按照多個欄位進行分組，以產生更細緻的資料分析結果。

以下範例展示如何按照國家和市場區隔計算客戶數量：

select n.n_name, c.c_mktsegment, count(*)
from customer c inner join nation n
on c.c_nationkey = n.n_nationkey
where n.n_regionkey = 1
group by n.n_name, c.c_mktsegment
order by 1, 2;

程式碼解密

  1. n.n_name: 選擇國家名稱。
  2. c.c_mktsegment: 選擇市場區隔。
  3. count(*): 計算每個國家和市場區隔的客戶數量。
  4. from customer c inner join nation n on c.c_nationkey = n.n_nationkey: 將 customer 資料表和 nation 資料表進行內部連線，根據 c_nationkey 欄位。
  5. where n.n_regionkey = 1: 篩選出屬於美洲地區的國家。
  6. group by n.n_name, c.c_mktsegment: 按照國家和市場區隔對結果進行分組。
  7. order by 1, 2: 按照國家名稱和市場區隔的順序對結果進行排序。

這個查詢的結果會顯示每個國家在不同市場區隔中的客戶數量：

+---------------+--------------+----------+
| N_NAME        | C_MKTSEGMENT | COUNT(*) |
+---------------+--------------+----------+
| ARGENTINA     | AUTOMOBILE   | 521      |
| ARGENTINA     | BUILDING     | 580      |
| ARGENTINA     | FURNITURE    | 488      |
| ARGENTINA     | HOUSEHOLD    | 516      |
| ARGENTINA     | MACHINERY    | 533      |
| BRAZIL        | AUTOMOBILE   | 503      |
| BRAZIL        | BUILDING     | 551      |
| BRAZIL        | FURNITURE    | 492      |
| BRAZIL        | HOUSEHOLD    | 521      |
| BRAZIL        | MACHINERY    | 547      |
| CANADA        | AUTOMOBILE   | 499      |
| CANADA        | BUILDING     | 555      |
| CANADA        | FURNITURE    | 511      |
| CANADA        | HOUSEHOLD    | 544      |
| CANADA        | MACHINERY    | 522      |
| PERU          | AUTOMOBILE   | 560      |
| PERU          | BUILDING     | 541      |
| PERU          | FURNITURE    | 516      |
| PERU          | HOUSEHOLD    | 538      |
| PERU          | MACHINERY    | 470      |
| UNITED STATES | AUTOMOBILE   | 514      |
| UNITED STATES | BUILDING     | 516      |
| UNITED STATES | FURNITURE    | 522      |
| UNITED STATES | HOUSEHOLD    | 532      |
| UNITED STATES | MACHINERY    | 519      |
+---------------+--------------+----------+

使用表示式分組

除了使用欄位進行分組外，GROUP BY 子句還可以使用表示式。這使得我們可以根據計算結果進行分組，從而實作更靈活的資料分析。

以下範例展示如何按照訂單年份和發貨所需月份數計算訂單數量：

select date_part(year, o.o_orderdate) as year,
       datediff(month, o.o_orderdate, l.l_shipdate) as months_to_ship,
       count(*)
from orders o inner join lineitem l
on o.o_orderkey = l.l_orderkey
where o.o_orderdate >= '01-JAN-1997'::date
group by date_part(year, o.o_orderdate),
         datediff(month, o.o_orderdate, l.l_shipdate)
order by 1, 2;

程式碼解密

  1. date_part(year, o.o_orderdate) as year: 提取 o_orderdate 欄位中的年份，並將其命名為 year。
  2. datediff(month, o.o_orderdate, l.l_shipdate) as months_to_ship: 計算訂單日期和發貨日期之間的月份差，並將其命名為 months_to_ship。
  3. count(*): 計算每個年份和月份差的訂單數量。
  4. from orders o inner join lineitem l on o.o_orderkey = l.l_orderkey: 將 orders 資料表和 lineitem 資料表進行內部連線，根據 o_orderkey 欄位。
  5. where o.o_orderdate >= '01-JAN-1997'::date: 篩選出 1997 年 1 月 1 日之後的訂單。
  6. group by date_part(year, o.o_orderdate), datediff(month, o.o_orderdate, l.l_shipdate): 按照年份和月份差對結果進行分組。
  7. order by 1, 2: 按照年份和月份差的順序對結果進行排序。

透過這些範例，玄貓展示瞭如何在 PostgreSQL 中使用 GROUP BY 子句和彙總函式進行多樣化的資料分析。這些技術不僅可以幫助我們從資料中提取有價值的資訊，還可以支援更深入的業務決策。


玄貓將帶領大家深入 Snowflake 的分組與聚合功能，探索如何更有效地處理和分析資料。

### `GROUP BY ALL`：簡化你的分組語法

Snowflake 持續進化，許多伺服器功能都新增了特性，包括 SQL 實作。`group by all` 選項就是一個不錯的捷徑，可以在使用表示式分組資料時簡化語法。

以下範例展示瞭如何使用 `group by all`：

```sql
PUBLIC> select date_part(year, o.o_orderdate) as year,
datediff(month, o.o_orderdate,
l.l_shipdate) as months_to_ship,
count(*)
from orders o inner join lineitem l
on o.o_orderkey = l.l_orderkey
where o.o_orderdate >= '01-JAN-1997'::date
group by all
order by 1,2;

結果：

+------+----------------+----------+
| YEAR | MONTHS_TO_SHIP | COUNT(*) |
|------+----------------+----------|
| 1997 | 0 | 2195 |
| 1997 | 1 | 4601 |
| 1997 | 2 | 4644 |
| 1997 | 3 | 4429 |
| 1997 | 4 | 2245 |
| 1997 | 5 | 2 |
| 1998 | 0 | 1295 |
| 1998 | 1 | 2602 |
| 1998 | 2 | 2628 |
| 1998 | 3 | 2724 |
| 1998 | 4 | 1356 |
| 1998 | 5 | 1 |
+------+----------------+----------+

all 關鍵字代表 select 陳述式中所有不是聚合函式（例如 sum() 和 count()）的內容。如果你的查詢根據複雜的表示式（例如函式呼叫或 case 表示式）進行分組，使用 group by all 可以減少大量的輸入。玄貓認為這在處理複雜的資料轉換時特別有用，減少了重複輸入欄位的錯誤。

ROLLUP：生成多維度彙總

在多欄位分組的例子中，我們展示瞭如何計算每個國家和市場區隔的客戶數量。

PUBLIC> select n.n_name, c.c_mktsegment, count(*)
from customer c inner join nation n
on c.c_nationkey = n.n_nationkey
where n.n_regionkey = 1
group by n.n_name, c.c_mktsegment
order by 1,2;

結果：

+---------------+--------------+----------+
| N_NAME | C_MKTSEGMENT | COUNT(*) |
|---------------+--------------+----------|
| ARGENTINA | AUTOMOBILE | 521 |
| ARGENTINA | BUILDING | 580 |
| ARGENTINA | FURNITURE | 488 |
| ARGENTINA | HOUSEHOLD | 516 |
| ARGENTINA | MACHINERY | 533 |
| BRAZIL | AUTOMOBILE | 503 |
| BRAZIL | BUILDING | 551 |
| BRAZIL | FURNITURE | 492 |
| BRAZIL | HOUSEHOLD | 521 |
| BRAZIL | MACHINERY | 547 |
| CANADA | AUTOMOBILE | 499 |
| CANADA | BUILDING | 555 |
| CANADA | FURNITURE | 511 |
| CANADA | HOUSEHOLD | 544 |
| CANADA | MACHINERY | 522 |
| PERU | AUTOMOBILE | 560 |
| PERU | BUILDING | 541 |
| PERU | FURNITURE | 516 |
| PERU | HOUSEHOLD | 538 |
| PERU | MACHINERY | 470 |
| UNITED STATES | AUTOMOBILE | 514 |
| UNITED STATES | BUILDING | 516 |
| UNITED STATES | FURNITURE | 522 |
| UNITED STATES | HOUSEHOLD | 532 |
| UNITED STATES | MACHINERY | 519 |
+---------------+--------------+----------+

如果除了每個國家和市場區隔的計數外，你還想知道每個國家在所有市場區隔的總計數，可以使用 group by 子句的 rollup 選項：

PUBLIC> select n.n_name, c.c_mktsegment, count(*)
from customer c inner join nation n
on c.c_nationkey = n.n_nationkey
where n.n_regionkey = 1
group by rollup(n.n_name, c.c_mktsegment)
order by 1,2;

結果：

+---------------+--------------+----------+
| N_NAME | C_MKTSEGMENT | COUNT(*) |
|---------------+--------------+----------|
| ARGENTINA | AUTOMOBILE | 521 |
| ARGENTINA | BUILDING | 580 |
| ARGENTINA | FURNITURE | 488 |
| ARGENTINA | HOUSEHOLD | 516 |
| ARGENTINA | MACHINERY | 533 |
| ARGENTINA | NULL | 2638 |
| BRAZIL | AUTOMOBILE | 503 |
| BRAZIL | BUILDING | 551 |
| BRAZIL | FURNITURE | 492 |
| BRAZIL | HOUSEHOLD | 521 |
| BRAZIL | MACHINERY | 547 |
| BRAZIL | NULL | 2614 |
| CANADA | AUTOMOBILE | 499 |
| CANADA | BUILDING | 555 |
| CANADA | FURNITURE | 511 |
| CANADA | HOUSEHOLD | 544 |
| CANADA | MACHINERY | 522 |
| CANADA | NULL | 2631 |
| PERU | AUTOMOBILE | 560 |
| PERU | BUILDING | 541 |
| PERU | FURNITURE | 516 |
| PERU | HOUSEHOLD | 538 |
| PERU | MACHINERY | 470 |
| PERU | NULL | 2625 |
| UNITED STATES | AUTOMOBILE | 514 |
| UNITED STATES | BUILDING | 516 |
| UNITED STATES | FURNITURE | 522 |
| UNITED STATES | HOUSEHOLD | 532 |
| UNITED STATES | MACHINERY | 519 |
| UNITED STATES | NULL | 2603 |
| NULL | NULL | 13111 |
+---------------+--------------+----------+

rollup 選項生成了六個額外的列：每個國家一個，以及一個顯示整個結果集中總列數的最終列。額外的列在被匯總的列中顯示 null。例如，第六列的第一個欄位值為 Argentina，第二個欄位值為 null，第三個欄位值為 2,638，這表示阿根廷所有市場區隔的客戶總數為 2,638。最後一列的第一個和第二個欄位值為 null，第三個欄位值為 13,111，這表示美洲地區所有國家的客戶總數為 13,111。

玄貓認為，ROLLUP 功能在需要生成多層次小計報表時非常有用，例如銷售資料分析或財務報表。

如果你還想要每個市場區隔的小計，一個方法是使用 rollup，但切換欄位的順序：

PUBLIC> select n.n_name, c.c_mktsegment, count(*)
from customer c inner join nation n
on c.c_nationkey = n.n_nationkey
where n.n_regionkey = 1
group by rollup(c.c_mktsegment, n.n_name)
order by 1,2;

結果：

+---------------+--------------+----------+
| N_NAME | C_MKTSEGMENT | COUNT(*) |
|---------------+--------------+----------|
| ARGENTINA | AUTOMOBILE | 521 |
| ARGENTINA | BUILDING | 580 |
| ARGENTINA | FURNITURE | 488 |
| ARGENTINA | HOUSEHOLD | 516 |
| ARGENTINA | MACHINERY | 533 |
| BRAZIL | AUTOMOBILE | 503 |
| BRAZIL | BUILDING | 551 |
| BRAZIL | FURNITURE | 492 |
| BRAZIL | HOUSEHOLD | 521 |
| BRAZIL | MACHINERY | 547 |
| CANADA | AUTOMOBILE | 499 |
| CANADA | BUILDING | 555 |
| CANADA | FURNITURE | 511 |
| CANADA | HOUSEHOLD | 544 |
| CANADA | MACHINERY | 522 |
| PERU | AUTOMOBILE | 560 |
| PERU | BUILDING | 541 |
| PERU | FURNITURE | 516 |
| PERU | HOUSEHOLD | 538 |
| PERU | MACHINERY | 470 |
| UNITED STATES | AUTOMOBILE | 514 |
| UNITED STATES | BUILDING | 516 |
| UNITED STATES | FURNITURE | 522 |
| UNITED STATES | HOUSEHOLD | 532 |
| UNITED STATES | MACHINERY | 519 |
| NULL | AUTOMOBILE | 2597 |
| NULL | BUILDING | 2743 |
| NULL | FURNITURE | 2529 |
| NULL | HOUSEHOLD | 2651 |
| NULL | MACHINERY | 2591 |
| NULL | NULL | 13111 |
+---------------+--------------+----------+

玄貓認為，掌握 ROLLUP 的使用方式，可以更靈活地產生各種維度的彙總資料，滿足不同的分析需求。

GROUP BY ALL 簡化語法，ROLLUP 則提供多維度彙總功能。善用這些工具，能更有效地分析 Snowflake 中的資料，挖掘出更多有價值的資訊。

玄貓

玄貓

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