返回文章列表
玄貓（BlackCat）

SQL分組彙總與子查詢進階用法

本文探討 SQL 分組彙總和子查詢的進階技巧，涵蓋 Snowflake 中 `GROUP BY ALL`、`ROLLUP` 和 `CUBE` 的使用，並比較子查詢與 JOIN 的效率差異，同時提供多種子查詢的應用場景和程式碼範例。

資料函式庫 SQL
SQL 分組 彙總 子查詢 Snowflake 資料函式庫查詢

在資料函式庫操作中，有效地運用分組彙總和子查詢能大幅提升查詢效率和資料分析能力。Snowflake 提供了 GROUP BY ALL 簡化分組操作，ROLLUP 和 CUBE 則能快速生成多維度彙總資料。子查詢雖然功能強大，但需注意效能問題，在特定場景下，使用 JOIN 語法可以獲得更佳的查詢效能。理解不同查詢方法的特性，才能根據實際需求選擇最合適的策略。

SQL 分組與彙總進階技巧

在 SQL 查詢中，分組與彙總是非常重要的功能，可以用來對資料進行匯總和分析。本文將介紹 Snowflake 中 GROUP BY 子句的進階用法，包括 GROUP BY ALL 和 ROLLUP 選項。

使用 GROUP BY ALL 簡化查詢

在 Snowflake 中，GROUP BY ALL 是一個方便的捷徑，可以自動將 SELECT 陳述式中非聚合函式的欄位加入 GROUP BY 子句中。

示例：使用 GROUP BY ALL

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 |

程式碼解說

此查詢使用了 GROUP BY ALL 自動將非聚合函式的欄位加入分組中。首先，從 orders 和 lineitem 表中選擇需要的欄位，並根據 o_orderdate 和 l_shipdate 計算出 months_to_ship。然後，使用 COUNT(*) 統計每個分組的資料筆數。最後，根據 year 和 months_to_ship 進行排序。

使用 ROLLUP 生成小計

ROLLUP 是 GROUP BY 的一個擴充套件，可以用來生成小計和總計。

示例：使用 ROLLUP

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 | | NULL | NULL | 13111 |

程式碼解說

此查詢使用了 ROLLUP 生成每個國家的客戶總數和所有國家的客戶總數。首先，從 customer 和 nation 表中選擇需要的欄位，並根據 n_name 和 c_mktsegment 分組統計客戶數量。然後，使用 ROLLUP 生成每個國家的客戶總數和小計。最後，根據 n_name 和 c_mktsegment進行排序。

使用 Plantuml 圖表展示查詢流程

圖表翻譯： 此圖表展示了查詢流程。首先，開始查詢並選擇需要的資料表。然後，連線相關的資料表並篩選需要的資料。接著，根據指定的欄位進行分組統計，並使用 ROLLUP 生成小計和總計。最後，排序結果並輸出。

資料分組與彙總分析

在進行資料分析時，我們經常需要對資料進行分組並計算各類別的彙總值。SQL 提供了多種方法來實作這一點，包括 ROLLUP 和 CUBE 運算元。

使用 CUBE 運算元進行多維度彙總

當我們需要對多個欄位進行分組彙總時，可以使用 CUBE 運算元。以下是一個例子：

SELECT 
    n.n_name, 
    c.c_mktsegment, 
    COUNT(*),
    GROUPING(n.n_name) AS name_sub,
    GROUPING(c.c_mktsegment) AS mktseg_sub
FROM 
    customer c 
INNER JOIN 
    nation n ON c.c_nationkey = n.n_nationkey
WHERE 
    n.n_regionkey = 1
GROUP BY 
    CUBE(c.c_mktsegment, n.n_name)
ORDER BY 
    1, 2;

內容解密：

  1. CUBE 運算元：用於對多個欄位進行分組彙總，產生所有可能的組合。
  2. GROUPING 函式：用於判斷某個欄位是否參與了彙總。若傳回 0，表示該欄位有值；若傳回 1，表示該欄位為 NULL，即該行為彙總行。
  3. COUNT(*)：計算每個分組中的資料行數。
  4. n.n_name 和 c.c_mktsegment：分別代表國家名稱和市場區隔。

篩選分組資料

在某些情況下，我們希望對分組後的資料進行篩選。例如，找出 1998 年訂單總金額超過 $700,000 的客戶。

首先，我們對每個客戶的訂單總金額進行計算：

SELECT 
    o_custkey, 
    SUM(o_totalprice) AS total_price
FROM 
    orders
WHERE 
    date_part('year', o_orderdate) = 1998
GROUP BY 
    o_custkey
ORDER BY 
    1;

內容解密：

  1. SUM(o_totalprice)：計算每個客戶的訂單總金額。
  2. date_part('year', o_orderdate)：提取訂單日期的年份部分，用於篩選特定年份的資料。
  3. GROUP BY o_custkey：按客戶 ID 分組。

接著，我們需要對這些結果進行篩選，但不能直接在 WHERE 子句中加入篩選條件，因為 WHERE 子句是在分組前進行篩選的。正確的做法是使用 HAVING 子句：

SELECT 
    o_custkey, 
    SUM(o_totalprice) AS total_price
FROM 
    orders
WHERE 
    date_part('year', o_orderdate) = 1998
GROUP BY 
    o_custkey
HAVING 
    SUM(o_totalprice) >= 700000;

內容解密：

  1. HAVING 子句：用於對分組後的結果進行篩選。
  2. SUM(o_totalprice) >= 700000：篩選出訂單總金額大於或等於 $700,000 的客戶。

第8章：子查詢（Subqueries）

子查詢是一種強大的工具，可以用於SELECT、UPDATE、INSERT、DELETE和MERGE陳述式中。本章將探討不同型別的子查詢，以及它們與SQL陳述式的互動方式。

子查詢的定義

子查詢是一個包含在另一個SQL陳述式中的查詢（以下稱為包含陳述式或包含查詢）。子查詢總是被括號包圍，並且通常在包含陳述式之前執行。與任何查詢一樣，子查詢傳回一個結果集，該結果集可以由單行或多行、單列或多列組成。子查詢傳回的結果集型別決定了包含陳述式可以使用哪些運算元與子查詢傳回的資料進行互動。

簡單的子查詢範例

SELECT n_nationkey, n_name 
FROM nation
WHERE n_regionkey = 
    (SELECT r_regionkey 
     FROM region 
     WHERE r_name = 'ASIA');

內容解密：

  1. 主查詢從nation表中檢索資料。
  2. 子查詢(SELECT r_regionkey FROM region WHERE r_name = 'ASIA')傳回region表中r_name為’ASIA’的r_regionkey值。
  3. 主查詢使用子查詢傳回的r_regionkey值來過濾nation表中的資料，選取n_regionkey相等的行。
  4. 最終結果顯示了屬於’ASIA’地區的國家的n_nationkey和n_name。

子查詢的型別與應用

根據傳回結果的不同，子查詢可以分為多種型別，並用於不同的SQL陳述式中。

1. 單行子查詢

傳回單行單列的結果，可以用於比較運算。

2. 多行子查詢

傳回多行的結果，可以與IN、ANY、ALL等運算元結合使用。

3. 相關子查詢

子查詢的執行依賴於外部查詢的資料，每次外部查詢處理一行資料時，子查詢都會被重新執行一次。

子查詢的使用場景

  1. 資料過濾：在WHERE子句中使用子查詢來過濾資料。
  2. 資料計算：在SELECT子句中使用子查詢進行複雜的計算。
  3. 資料插入/更新/刪除：在INSERT、UPDATE、DELETE陳述式中使用子查詢來確定操作的資料範圍。

練習題

  1. 編寫一個查詢，使用子查詢找出屬於’ASIA’地區的所有國家。
  2. 修改上述查詢，使用連線（JOIN）代替子查詢，並比較兩者的執行效率。
  3. 使用子查詢找出訂單數量最多的客戶。

SQL 子查詢詳解

SQL 子查詢是一種強大的工具，能夠讓我們在查詢中巢狀另一個查詢，以實作更複雜的資料檢索和操作。本文將探討 SQL 子查詢的型別、使用方法和相關運算子。

子查詢的基本概念

子查詢是一個巢狀在另一個查詢中的查詢陳述式。它可以獨立執行，也可以與外部查詢相互關聯。子查詢的主要作用是提供一個臨時的結果集，供外部查詢使用。

示例：使用子查詢檢索特定區域的國家資訊

SELECT n.n_nationkey, n.n_name
FROM nation n
WHERE n.n_regionkey = 
    (SELECT r.r_regionkey FROM region r WHERE r.r_name = 'ASIA');

內容解密：

  1. 外部查詢從 nation 表中檢索國家的鍵值和名稱。
  2. 子查詢首先執行，傳回 ‘ASIA’ 區域的 r_regionkey 值。
  3. 外部查詢使用子查詢的結果來過濾 nation 表中的資料。

子查詢與 JOIN 的比較

許多情況下，子查詢可以被 JOIN 替代，反之亦然。例如，上述查詢也可以使用 INNER JOIN 實作：

SELECT n.n_nationkey, n.n_name
FROM nation n 
INNER JOIN region r 
ON n.n_regionkey = r.r_regionkey
WHERE r.r_name = 'ASIA';

內容解密：

  1. 使用 INNER JOIN 將 nation 和 region 表連線起來。
  2. 連線條件是 n_regionkey 和 r_regionkey 的相等關係。
  3. WHERE 子句過濾出 ‘ASIA’ 區域的資料。

子查詢型別

SQL 子查詢主要分為兩種型別：無關子查詢和相關子查詢。

無關子查詢（Uncorrelated Subqueries）

無關子查詢可以獨立於外部查詢執行，不依賴外部查詢的任何資料。

SELECT n_nationkey, n_name 
FROM nation
WHERE n_regionkey <> 
    (SELECT r_regionkey FROM region WHERE r_name = 'ASIA');

內容解密：

  1. 子查詢傳回 ‘ASIA’ 區域的 r_regionkey。
  2. 外部查詢傳回不在 ‘ASIA’ 區域的國家資訊。

多行單列子查詢

當子查詢傳回多行資料時，我們需要使用特定的運算子來處理這些資料。

使用 IN 運算子

SELECT n_nationkey, n_name 
FROM nation
WHERE n_regionkey IN 
    (SELECT r_regionkey FROM region WHERE r_name <> 'ASIA');

內容解密：

  1. 子查詢傳回所有非 ‘ASIA’ 區域的 r_regionkey。
  2. 外部查詢傳回屬於這些區域的國家資訊。

使用 NOT IN 運算子

SELECT n_nationkey, n_name 
FROM nation
WHERE n_regionkey NOT IN 
    (SELECT r_regionkey FROM region WHERE r_name IN ('AMERICA', 'EUROPE'));

內容解密：

  1. 子查詢傳回 ‘AMERICA’ 和 ‘EUROPE’ 區域的 r_regionkey。
  2. 外部查詢傳回不屬於這兩個區域的國家資訊。

使用 ALL 和 ANY 運算子

ALL 運算子

SELECT o_custkey, COUNT(*) AS num_orders
FROM orders
WHERE 1996 = date_part(year, o_orderdate)
GROUP BY o_custkey
HAVING COUNT(*) > ALL 
    (SELECT COUNT(*) 
     FROM orders 
     WHERE 1997 = date_part(year, o_orderdate) 
     GROUP BY o_custkey);

內容解密：

  1. 子查詢計算 1997 年每位客戶的訂單數量。
  2. 外部查詢找出 1996 年訂單數量超過 1997 年任何客戶訂單數量的客戶。

ANY 運算子

SELECT date_part(year, o_orderdate), MAX(o_totalprice)
FROM orders
WHERE 1997 <> date_part(year, o_orderdate)
GROUP BY date_part(year, o_orderdate)
ORDER BY 1;

這裡展示瞭如何找出除1997年外每年訂單的最大總價。

子查詢型別示意圖

@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle

title SQL分組彙總與子查詢進階用法

package "資料庫架構" {
    package "應用層" {
        component [連線池] as pool
        component [ORM 框架] as orm
    }

    package "資料庫引擎" {
        component [查詢解析器] as parser
        component [優化器] as optimizer
        component [執行引擎] as executor
    }

    package "儲存層" {
        database [主資料庫] as master
        database [讀取副本] as replica
        database [快取層] as cache
    }
}

pool --> orm : 管理連線
orm --> parser : SQL 查詢
parser --> optimizer : 解析樹
optimizer --> executor : 執行計畫
executor --> master : 寫入操作
executor --> replica : 讀取操作
cache --> executor : 快取命中

master --> replica : 資料同步

note right of cache
  Redis/Memcached
  減少資料庫負載
end note

@enduml

圖表翻譯： 此圖示展示了SQL子查詢的主要分類別及其子型別。無關子查詢可以進一步分為單行單列和多行單列子查詢。多行單列子查詢可以使用IN、ALL和ANY等運算子進行處理。