Snowflake SQL 資料處理：字串、數值與時間操作技巧

– 12 分鐘閱讀

在資料函式庫系統中，資料的處理與轉換是資料分析的根本。Snowflake 作為現代雲端資料倉儲，提供強大的 SQL 語法和函式，讓資料工程師能更有效率地處理不同型別的資料。本文將聚焦於 Snowflake SQL 中的字串、數值和時間資料的處理技巧，並以實務案例說明如何應用這些技巧。字串處理涵蓋連線、大小寫轉換、子字串擷取和取代等常用操作，搭配 concat()、upper()、lower()、substring() 和 replace() 等函式，讓字串處理更靈活。數值處理則包含絕對值、四捨五入、向上取整、向下取整和隨機數產生等，並透過 abs()、round()、floor()、ceil() 和 rand() 等函式示範如何進行精確的數值計算。此外，文章也涵蓋時間資料的處理，包含取得目前日期時間、日期時間格式轉換、日期時間截斷和日期時間加減等操作，並以 current_date()、current_timestamp()、to_date()、to_timestamp()、date_trunc() 和 dateadd() 等函式輔助說明，讓讀者能輕鬆掌握時間資料的處理技巧。

6. 資料的產生、轉換與操作

在資料函式庫的世界裡，資料就像是積木，我們需要各種工具和技巧來創造、改造和運用這些積木。本章將探討如何在 Snowflake 中進行資料的產生、轉換和操作，涵蓋字串、數值和時間等不同資料型別。

6.1 字串處理：從入門到精通

字串是資料中常見的一種形式，無論是姓名、地址還是產品描述，都離不開字串的處理。

6.1.1 字串的產生與操作

在 Snowflake 中，你可以使用各種函式來產生和操作字串，例如：

  • concat(): 將多個字串連線成一個。
  • upper()/lower(): 將字串轉換為大寫或小寫。
  • substring(): 提取字串的一部分。
  • replace(): 替換字串中的部分內容。
-- 字串連線
SELECT concat('玄貓', '技術', '筆記') AS full_string;

-- 轉換為大寫
SELECT upper('BlackCat') AS uppercase_string;

-- 提取子字串
SELECT substring('Snowflake', 1, 4) AS substring_result;

-- 字串替換
SELECT replace('Hello World', 'World', 'Snowflake') AS replaced_string;

內容解密

  • concat('玄貓', '技術', '筆記'): 這個範例展示瞭如何使用 concat() 函式將三個字串 '玄貓'、'技術' 和 '筆記' 連線在一起，形成一個完整的字串 '玄貓技術筆記'。
  • upper('BlackCat'): 這個範例展示瞭如何使用 upper() 函式將字串 'BlackCat' 轉換為大寫形式 'BLACKCAT'。
  • substring('Snowflake', 1, 4): 這個範例展示瞭如何使用 substring() 函式從字串 'Snowflake' 中提取子字串。引數 1 表示起始位置（從 1 開始），4 表示提取的長度。結果是 'Snow'。
  • replace('Hello World', 'World', 'Snowflake'): 這個範例展示瞭如何使用 replace() 函式將字串 'Hello World' 中的 'World' 替換為 'Snowflake'。結果是 'Hello Snowflake'。
6.1.2 字串的搜尋與提取

有時候，我們需要從字串中找到特定的內容，這時可以使用以下函式：

  • like: 模糊比對字串。
  • regexp_like(): 使用正規表示式比對字串。
  • regexp_extract(): 使用正規表示式提取字串。
-- 模糊比對
SELECT * FROM employees WHERE name LIKE '玄貓%';

-- 正規表示式比對
SELECT * FROM products WHERE description regexp_like '.*(AI|人工智慧).*';

-- 正規表示式提取
SELECT regexp_extract('name:玄貓,age:30', 'name:(.*?),', 1) AS extracted_name;

內容解密

  • SELECT * FROM employees WHERE name LIKE '玄貓%';: 這個範例展示瞭如何使用 LIKE 運算元進行模糊比對。它會選取 employees 表中所有 name 欄位以 '玄貓' 開頭的記錄。% 是一個萬用字元，表示零個或多個字元。
  • SELECT * FROM products WHERE description regexp_like '.*(AI|人工智慧).*';: 這個範例展示瞭如何使用 regexp_like 函式進行正規表示式比對。它會選取 products 表中所有 description 欄位包含 'AI' 或 '人工智慧' 的記錄。.* 表示零個或多個任意字元，| 表示或。
  • SELECT regexp_extract('name:玄貓,age:30', 'name:(.*?),', 1) AS extracted_name;: 這個範例展示瞭如何使用 regexp_extract 函式從字串中提取符合正規表示式的部分。它會從字串 'name:玄貓,age:30' 中提取 'name:' 和下一個逗號之間的內容。正規表示式 'name:(.*?),' 中的 (.*?) 表示提取的內容，? 表示非貪婪比對。第三個引數 1 表示提取第一個捕捉組的內容。結果是 '玄貓'。

6.2 數值資料處理：精準與效率

數值資料在資料分析中扮演著重要的角色，從銷售額到庫存量，都需要精確的數值計算。

6.2.1 常用數值函式

Snowflake 提供了豐富的數值函式，例如：

  • abs(): 絕對值。
  • round(): 四捨五入。
  • floor()/ceil(): 向下或向上取整。
  • rand(): 產生隨機數。
-- 絕對值
SELECT abs(-10) AS absolute_value;

-- 四捨五入
SELECT round(3.14159, 2) AS rounded_value;

-- 向下取整
SELECT floor(3.9) AS floor_value;

-- 產生隨機數
SELECT rand() AS random_number;

內容解密

  • SELECT abs(-10) AS absolute_value;: 這個範例展示瞭如何使用 abs() 函式計算 -10 的絕對值。結果是 10。
  • SELECT round(3.14159, 2) AS rounded_value;: 這個範例展示瞭如何使用 round() 函式對 3.14159 進行四捨五入，保留兩位小數。結果是 3.14。
  • SELECT floor(3.9) AS floor_value;: 這個範例展示瞭如何使用 floor() 函式對 3.9 向下取整。結果是 3。
  • SELECT rand() AS random_number;: 這個範例展示瞭如何使用 rand() 函式生成一個 0 到 1 之間的隨機數。每次執行的結果都會不同。
6.2.2 數值轉換

有時候，我們需要將數值資料轉換為不同的格式，例如將字串轉換為數值，可以使用 to_number() 函式。

-- 字串轉換為數值
SELECT to_number('123.45') AS numeric_value;

內容解密

  • SELECT to_number('123.45') AS numeric_value;: 這個範例展示瞭如何使用 to_number() 函式將字串 '123.45' 轉換為數值型別。結果是 123.45。
6.2.3 產生連續數字

在某些情況下，我們需要產生一系列連續的數字，例如用於測試或模擬資料。Snowflake 可以透過結合 row_number() 視窗函式和 union all 來實作。

-- 產生 1 到 10 的連續數字
WITH RECURSIVE numbers AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT n FROM numbers;

內容解密

這個範例使用遞迴 CTE（Common Table Expression）來生成 1 到 10 的連續數字。

  • WITH RECURSIVE numbers AS (...): 定義一個名為 numbers 的遞迴 CTE。
  • SELECT 1 AS n: 初始查詢，產生第一個數字 1。
  • UNION ALL: 將初始查詢和遞迴查詢的結果合併。
  • SELECT n + 1 FROM numbers WHERE n < 10: 遞迴查詢，每次將上一個數字加 1，直到 n 等於 10。
  • SELECT n FROM numbers: 從 CTE 中選取所有生成的數字。

6.3 時間資料處理：掌握時間的藝術

時間資料在商業分析中至關重要，例如分析銷售趨勢、追蹤使用者行為等。

6.3.1 時間資料的產生

Snowflake 提供了多種方式來產生時間資料：

  • current_date(): 取得目前日期。
  • current_timestamp(): 取得目前時間戳記。
  • date()/to_date(): 將字串轉換為日期。
  • timestamp()/to_timestamp(): 將字串轉換為時間戳記。
-- 取得目前日期
SELECT current_date() AS today;

-- 取得目前時間戳記
SELECT current_timestamp() AS now;

-- 字串轉換為日期
SELECT to_date('2024-01-01', 'YYYY-MM-DD') AS date_value;

-- 字串轉換為時間戳記
SELECT to_timestamp('2024-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS') AS timestamp_value;

內容解密

  • SELECT current_date() AS today;: 這個範例展示瞭如何使用 current_date() 函式取得目前日期。結果會是執行查詢時的日期，例如 '2024-07-28'。
  • SELECT current_timestamp() AS now;: 這個範例展示瞭如何使用 current_timestamp() 函式取得目前時間戳記。結果會是執行查詢時的日期和時間，例如 '2024-07-28 10:30:00.123'。
  • SELECT to_date('2024-01-01', 'YYYY-MM-DD') AS date_value;: 這個範例展示瞭如何使用 to_date() 函式將字串 '2024-01-01' 轉換為日期型別。第二個引數 'YYYY-MM-DD' 是日期格式。結果是 '2024-01-01'。
  • SELECT to_timestamp('2024-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS') AS timestamp_value;: 這個範例展示瞭如何使用 to_timestamp() 函式將字串 '2024-01-01 12:00:00' 轉換為時間戳記型別。第二個引數 'YYYY-MM-DD HH24:MI:SS' 是時間戳記格式。結果是 '2024-01-01 12:00:00.000'。
6.3.2 時間資料的操作

Snowflake 提供了豐富的函式來操作時間資料，例如：

  • date_trunc(): 截斷日期到指定的精確度（例如年、月、日）。
  • date_part(): 提取日期的部分資訊（例如年、月、日）。
  • dateadd()/date_sub(): 在日期上增加或減少指定的時間間隔。
-- 截斷日期到月份
SELECT date_trunc('month', current_date()) AS month_start;

-- 提取年份
SELECT date_part('year', current_date()) AS current_year;

-- 增加一天
SELECT dateadd('day', 1, current_date()) AS tomorrow;

-- 減少一週
SELECT dateadd('week', -1, current_date()) AS last_week;

內容解密

  • SELECT date_trunc('month', current_date()) AS month_start;: 這個範例展示瞭如何使用 date_trunc() 函式將目前日期截斷到月份的開始。例如，如果今天是 '2024-07-28'，結果會是 '2024-07-01'。
  • SELECT date_part('year', current_date()) AS current_year;: 這個範例展示瞭如何使用 date_part() 函式從目前日期中提取年份。結果會是 '2024'。
  • SELECT dateadd('day', 1, current_date()) AS tomorrow;: 這個範例展示瞭如何使用 dateadd() 函式在目前日期上增加一天。例如，如果今天是 '2024-07-28'，結果會是 '2024-07-29'。
  • SELECT dateadd('week', -1, current_date()) AS last_week;: 這個範例展示瞭如何使用 dateadd() 函式在目前日期上減少一週。例如，如果今天是 '2024-07-28'，結果會是 '2024-07-21'。
6.3.3 時間資料的轉換

有時候，我們需要在不同的時間格式之間進行轉換，例如將日期轉換為字串，可以使用 date_format() 函式。

-- 日期轉換為字串
SELECT date_format(current_date(), 'YYYY/MM/DD') AS formatted_date;

內容解密

  • SELECT date_format(current_date(), 'YYYY/MM/DD') AS formatted_date;: 這個範例展示瞭如何使用 date_format() 函式將目前日期格式化為字串。'YYYY/MM/DD' 是目標格式。例如，如果今天是 '2024-07-28'，結果會是 '2024/07/28'。

透過掌握這些資料產生、轉換和操作的技巧，玄貓可以更有效地處理 Snowflake 中的各種資料，為後續的分析和應用打下堅實的基礎。

7. 分組與聚合

在資料分析中，我們常常需要將資料分組，然後對每組資料進行統計分析。本章將探討 Snowflake 中的分組與聚合功能，幫助你從資料中提取有價值的資訊。

7.1 分組概念

分組是指將資料按照一個或多個欄位的值進行分類別，將具有相同值的記錄放在同一組。例如，我們可以按照產品類別將銷售資料分組，然後計算每個類別的總銷售額。

7.2 聚合函式

聚合函式是用於對分組後的資料進行計算的函式，例如：

  • count(): 計算組中的記錄數。
  • min()/max(): 取得組中的最小值或最大值。
  • avg(): 計算組中的平均值。
  • sum(): 計算組中的總和。
  • listagg(): 將組中的多個值合併為一個字串。
-- 計算每個產品類別的總銷售額
SELECT
    category,
    sum(sales) AS total_sales
FROM
    sales_data
GROUP BY
    category;

-- 計算每個產品類別的平均價格
SELECT
    category,
    avg(price) AS average_price
FROM
    products
GROUP BY
    category;

-- 將每個訂單的產品名稱合併為一個字串
SELECT
    order_id,
    listagg(product_name, ', ') WITHIN GROUP (ORDER BY product_name) AS product_list
FROM
    order_items
GROUP BY
    order_id;

內容解密

  • SELECT category, sum(sales) AS total_sales FROM sales_data GROUP BY category;: 這個範例展示瞭如何使用 GROUP BY 子句將 sales_data 表中的資料按照 category 欄位分組，然後使用 sum() 函式計算每個類別的總銷售額。結果會包含每個類別的名稱和對應的總銷售額。
  • SELECT category, avg(price) AS average_price FROM products GROUP BY category;: 這個範例展示瞭如何使用 GROUP BY 子句將 products 表中的資料按照 category 欄位分組，然後使用 avg() 函式計算每個類別的平均價格。結果會包含每個類別的名稱和對應的平均價格。
  • SELECT order_id, listagg(product_name, ', ') WITHIN GROUP (ORDER BY product_name) AS product_list FROM order_items GROUP BY order_id;: 這個範例展示瞭如何使用 listagg() 函式將 order_items 表中每個訂單的產品名稱合併為一個字串。WITHIN GROUP (ORDER BY product_name) 子句指定了合併的順序。結果會包含每個訂單的 ID 和對應的產品列表字串。

7.3 產生分組

7.3.1 多欄位分組

除了按照單一欄位分組，我們還可以按照多個欄位進行分組，例如按照產品類別和地區將銷售資料分組。

-- 計算每個產品類別在每個地區的總銷售額
SELECT
    category,
    region,
    sum(sales) AS total_sales
FROM
    sales_data
GROUP BY
    category,
    region;

內容解密

  • SELECT category, region, sum(sales) AS total_sales FROM sales_data GROUP BY category, region;: 這個範例展示瞭如何使用 GROUP BY 子句將 sales_data 表中的資料按照 category 和 region 欄位分組，然後使用 sum() 函式計算每個類別在每個地區的總銷售額。結果會包含每個類別、地區和對應的總銷售額。
7.3.2 使用表示式分組

分組的欄位也可以是表示式，例如按照年份和月份將銷售資料分組。

-- 計算每個月的總銷售額
SELECT
    date_trunc('month', sale_date) AS sale_month,
    sum(sales) AS total_sales
FROM
    sales_data
GROUP BY
    sale_month;

內容解密

  • SELECT date_trunc('month', sale_date) AS sale_month, sum(sales) AS total_sales FROM sales_data GROUP BY sale_month;: 這個範例展示瞭如何使用 date_trunc() 函式將 sale_date 欄位截斷到月份的開始，然後使用 GROUP BY 子句將 sales_data 表中的資料按照月份分組，並使用 sum() 函式計算每個月的總銷售額。結果會包含每個月的開始日期和對應的總銷售額。

7.4 產生 Rollups

Rollup 是一種特殊的group by，可以產生多個層級的聚合結果，例如同時計算總銷售額、每個類別的銷售額以及每個地區的銷售額。

-- 使用 ROLLUP 計算總銷售額、每個類別的銷售額以及每個地區的銷售額
SELECT
    category,
    region,
    sum(sales) AS total_sales
FROM
    sales_data
GROUP BY ROLLUP (category, region);

內容解密

  • SELECT category, region, sum(sales) AS total_sales FROM sales_data GROUP BY ROLLUP (category, region);: 這個範例展示瞭如何使用 ROLLUP 子句同時計算總銷售額、每個類別的銷售額以及每個地區的銷售額。結果會包含以下幾種情況：
    • category 和 region 都有值：表示該類別在該地區的銷售額。
    • category 有值，region 為 NULL：表示該類別的總銷售額。
    • category 為 NULL，region 也為 NULL：表示總銷售額。

7.5 過濾分組後的資料

有時候，我們需要對分組後的資料進行過濾，只保留符合特定條件的組。這可以使用 HAVING 子句來實作。

-- 選取總銷售額超過 100 萬的產品類別
SELECT
    category,
    sum(sales) AS total_sales
FROM
    sales_data
GROUP BY
    category
HAVING
    sum(sales) > 1000000;

內容解密

  • SELECT category, sum(sales) AS total_sales FROM sales_data GROUP BY category HAVING sum(sales) > 1000000;: 這個範例展示瞭如何使用 HAVING 子句過濾分組後的資料。它會選取 sales_data 表中總銷售額超過 100 萬的產品類別。

7.6 Snowsight 中的過濾

Snowsight 是 Snowflake 的 Web UI，也提供了過濾分組後資料的功能。玄貓可以在 Snowsight 中直接設定過濾條件，方便地檢視符合特定條件的組。

透過本章的學習，玄貓可以靈活運用 Snowflake 的分組與聚合功能，從大量的資料中提取有價值的資訊，為商業決策提供有力的支援。

8. 子查詢

子查詢是指巢狀在其他 SQL 查詢中的查詢。它們是一種強大的工具，可以用於從多個表中提取資料、過濾資料以及執行更複雜的查詢。

8.1 子查詢的定義

子查詢本質上是一個SELECT陳述式，它被包含在另一個查詢陳述式中。子查詢可以出現在主查詢的SELECT、FROM、WHERE和HAVING子句中。

8.2 子查詢的型別

子查詢主要分為以下幾種型別：

  • 非關聯子查詢（Uncorrelated Subqueries）: 子查詢獨立執行，不依賴於外部查詢。
  • 關聯子查詢（Correlated Subqueries）: 子查詢的執行依賴於外部查詢的資料。
  • 作為資料來源的子查詢: 子查詢的結果作為一個臨時表，供外部查詢使用。

8.3 非關聯子查詢

非關聯子查詢是最簡單的子查詢型別。它們獨立執行，並將結果傳回給外部查詢。

-- 找出所有薪水高於平均薪水的員工
SELECT
    employee_name,
    salary
FROM
    employees
WHERE
    salary > (SELECT AVG(salary) FROM employees);

內容解密

  • SELECT employee_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);: 這個範例展示瞭如何使用非關聯子查詢找出所有薪水高於平均薪水的員工。子查詢 (SELECT AVG(salary) FROM employees) 計算所有員工的平均薪水，然後外部查詢選取薪水高於這個平均值的員工。

8.4 關聯子查詢

關聯子查詢的執行依賴於外部查詢的資料。對於外部查詢的每一行，關聯子查詢都會執行一次。

-- 找出每個部門薪水最高的員工
SELECT
    employee_name,
    salary,
    department_id
FROM
    employees AS e1
WHERE
    salary = (
        SELECT
            MAX(salary)
        FROM
            employees AS e2
        WHERE
            e1.department_id = e2.department_id
    );

內容解密

  • SELECT employee_name, salary, department_id FROM employees AS e1 WHERE salary = (SELECT MAX(salary) FROM employees AS e2 WHERE e1.department_id = e2.department_id);: 這個範例展示瞭如何使用關聯子查詢找出每個部門薪水最高的員工。對於 employees 表中的每一行（別名為 e1），子查詢 (SELECT MAX(salary) FROM employees AS e2 WHERE e1.department_id = e2.department_id) 會計算該部門的最高薪水，然後外部查詢選取薪水等於這個最高值的員工。

8.5 作為資料來源的子查詢

子查詢的結果可以作為一個臨時表，供外部查詢使用。這通常在 FROM 子句中完成。

-- 計算每個部門的員工數量和平均薪水
SELECT
    department_id,
    COUNT(*) AS employee_count,
    AVG(salary) AS average_salary
FROM
    (SELECT department_id, salary FROM employees WHERE active = TRUE) AS active_employees
GROUP BY
    department_id;

內容解密

  • SELECT department_id, COUNT(*) AS employee_count, AVG(salary) AS average_salary FROM (SELECT department_id, salary FROM employees WHERE active = TRUE) AS active_employees GROUP BY department_id;: 這個範例展示瞭如何使用子查詢作為資料來源計算每個部門的員工數量和平均薪水。子查詢 (SELECT department_id, salary FROM employees WHERE active = TRUE) 選取所有 active 為 TRUE 的員工的部門 ID 和薪水，然後外部查詢按照部門 ID 分組，並計算每個部門的員工數量和平均薪水。

8.6 共通表表達式（Common Table Expressions, CTE）

共通表表達式（CTE）是一種命名的臨時結果集，可以在一個查詢中多次使用。它們可以簡化複雜的查詢，並提高可讀性。

-- 使用 CTE 計算每個部門的員工數量和平均薪水
WITH
    active_employees AS (
        SELECT
            department_id,
            salary
        FROM
            employees
        WHERE
            active = TRUE
    )
SELECT
    department_id,
    COUNT(*) AS employee_count,
    AVG(salary) AS average_salary
FROM
    active_employees
GROUP BY
    department_id;

內容解密

  • WITH active_employees AS (SELECT department_id, salary FROM employees WHERE active = TRUE) SELECT department_id, COUNT(*) AS employee_count, AVG(salary) AS average_salary FROM active_employees GROUP BY department_id;: 這個範例展示瞭如何使用 CTE 計算每個部門的員工數量和平均薪水。CTE active_employees 選取所有 active 為 TRUE 的員工的部門 ID 和薪水，然後外部查詢按照部門 ID 分組，並計算每個部門的員工數量和平均薪水。

透過本章的學習，玄貓可以掌握 Snowflake 中子查詢的使用技巧，編寫更複雜、更強大的 SQL 查詢，從資料中提取更豐富的資訊。

子查詢和 CTE 都是資料函式庫查詢中不可或缺的工具。適當使用它們可以簡化複雜的查詢邏輯，提高查詢效率和可讀性。玄貓在實際應用中應根據具體情況選擇最合適的方法。

文章的最後，玄貓建議讀者多加練習，嘗試使用不同的子查詢和 CTE 來解決實際問題。只有透過不斷的實踐，才能真正掌握這些工具的精髓。

玄貓

玄貓

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