Snowflake 數值函式與轉換技巧詳解

Snowflake 資料函式庫在資料分析和轉換的核心環節中，數值處理扮演著至關重要的角色。無論是進行基礎的數學運算，還是處理複雜的資料格式轉換，熟練運用 Snowflake 提供的各種函式和技巧都能事半功倍。本文將深入淺出地介紹 Snowflake 的數值世界，讓你在資料處理的過程中更加得心應手。首先，我們會探討 Snowflake 提供的各種數學函式，包含圓周長、面積計算、π 值的應用、求餘數、正負值判斷、絕對值計算，以及各種捨入方法。接著，我們將介紹數值轉換的技巧，包含 Snowflake 的隱式和顯式轉換、特定轉換函式的使用，以及如何處理特殊格式的數值字串。

在實際應用中，我們常常需要產生測試資料或模擬特定情境。Snowflake 的 generator() 函式搭配其他函式，可以輕鬆生成隨機數和連續數字序列。例如，結合 random() 函式可以產生指定數量的隨機數，而 seq1() 函式則可以生成連續的數字序列。我們也會示範如何運用這些函式產生日期序列，例如 2023 年每個月的第一天。時間資料的處理也是資料函式庫中常見的挑戰，因為時間有多種不同的表示方式。Snowflake 在處理日期格式上相當彈性，會嘗試使用多種常見的格式字串來解析日期。然而，為了確保資料處理的準確性，我們仍然建議明確指定格式字串。我們將示範如何使用 to_timestamp() 函式搭配格式字串來解析時間戳記，以及如何檢視和修改 Snowflake 的時間戳記輸出格式。

此外，Snowflake 也提供了多種內建函式來產生日期和時間戳記，例如 date_from_parts()、time_from_parts() 和 timestamp_from_parts()。這些函式可以讓我們指定年、月、日、時、分、秒等元件來產生日期和時間。date_from_parts() 函式還支援使用零或負數來表示月份和日期，方便我們往前推算日期，例如計算每個月的最後一天。最後，我們將介紹 Snowflake 提供的時間資料運算函式，例如 date_trunc() 函式，可以讓我們輕鬆取得某個日期的月初、季初或年初。

駕馭數值：玄貓解密 Snowflake 的數值函式與轉換技巧

在 Snowflake 中，數值處理是資料分析和轉換的核心。無論是簡單的數學運算，還是複雜的資料格式轉換，掌握 Snowflake 提供的各種函式和技巧都至關重要。玄貓將帶領大家深入探索 Snowflake 的數值世界，讓你在資料處理的道路上更加得心應手。

數學運算：玄貓的精準計算之道

Snowflake 提供了豐富的內建函式，讓你可以輕鬆進行各種數學運算。

圓的奧秘：從周長到面積

還記得前面章節中計算圓周長的例子嗎？如果我們想更進一步，計算圓的面積（πr²），可以使用 power() 函式：

SELECT
    10 AS radius,
    2 * 3.14159 * 10 AS circumference,
    3.14159 * POWER(10, 2) AS area;

這個查詢可以同時計算出半徑為 10 的圓的周長和麵積。

π 的簡便取用：pi() 函式

為了更精確地計算圓的面積，可以使用 Snowflake 提供的 pi() 函式，它能直接傳回 π 的值，無需手動輸入：

SELECT
    10 AS radius,
    2 * 3.14159 * 10 AS circumference,
    PI() * POWER(10, 2) AS area;

玄貓認為，使用 pi() 函式不僅能提高精確度，還能讓程式碼更易讀。

求餘數的妙用：mod() 函式

當需要計算一個數除以另一個數的餘數時，mod() 函式就派上用場了：

SELECT MOD(70, 9);

這個查詢將傳回 70 除以 9 的餘數，結果為 7。

正負判斷與絕對值：sign() 和 abs() 函式

在處理可能為正或負的數值時，可以使用 sign() 函式判斷數值的正負性（-1 表示負數，0 表示零，+1 表示正數），並使用 abs() 函式取得絕對值：

SELECT SIGN(-7.5233), ABS(-7.5233);

玄貓在處理金融資料時，經常使用這兩個函式來判斷交易方向和計算金額。

捨入的藝術：trunc(), round(), floor(), 和 ceil() 函式

對於非整數值（實數），Snowflake 提供了多種捨入函式：

• trunc()：移除小數部分。
• round()：四捨五入到指定的小數位數。
• floor()：向下捨入到最接近的整數。
• ceil()：向上捨入到最接近的整數。

SELECT
    TRUNC(6.49),
    ROUND(6.49, 1),
    FLOOR(6.49),
    CEIL(6.49);

玄貓建議，在選擇捨入函式時，應根據實際需求和業務邏輯來決定。

數值轉換：玄貓的格式轉換技巧

在資料處理過程中，經常需要將字串轉換為數值。Snowflake 提供了多種方法來實作這一目標。

隱式轉換：Snowflake 的自動轉換

Snowflake 會在必要時嘗試自動進行資料型別轉換（也稱為強制轉換）。例如，當使用 UNION 運算元合併數值和字串時，Snowflake 會嘗試將字串轉換為數值：

SELECT 123.45 AS real_num
UNION
SELECT '678.90' AS real_num;

在這個例子中，Snowflake 成功地將字串 '678.90' 轉換為數值。

顯式轉換：玄貓的精確控制

為了避免隱式轉換可能導致的錯誤，玄貓建議使用顯式轉換，明確指定要轉換的資料型別。Snowflake 提供了以下幾種顯式轉換方法：

• cast() 函式
• :: 轉換運算元
• to_decimal() 等特定轉換函式

以下是一個使用這三種方法將字串轉換為 NUMBER(7,2) 型別的小數值的例子：

SELECT
    CAST(str.val AS NUMBER(7, 2)) AS cast_val,
    str.val::NUMBER(7, 2) AS cast_opr_val,
    TO_DECIMAL(str.val, 7, 2) AS to_dec_val
FROM (VALUES ('15873.26')) AS str(val);

處理特殊格式：to_decimal() 的格式字串

如果字串包含非數值字元（例如貨幣符號），可以使用 to_decimal() 函式的格式字串來指定字串的格式：

SELECT TO_DECIMAL(str.val, '$99999.99', 7, 2) AS to_dec_val
FROM (VALUES ('$15873.26')) AS str(val);

玄貓提醒，格式字串必須與字串的實際格式相符，否則轉換將失敗。

轉換測試：try_to_decimal() 函式

如果想在實際轉換之前測試字串是否可以轉換為數值，可以使用 try_to_decimal() 函式。如果轉換成功，它將傳回轉換後的數值；如果轉換失敗，它將傳回 NULL，而不會丟擲錯誤：

SELECT
    TRY_TO_DECIMAL(str.val, '$99999.99', 7, 2) AS good,
    TRY_TO_DECIMAL(str.val, '999.9', 4, 2) AS bad
FROM (VALUES ('$15873.26')) AS str(val);

玄貓建議在處理大量可能包含無效數值的資料時，使用 try_to_decimal() 函式來避免錯誤。

數值生成：玄貓的測試資料製造術

在某些情況下，我們需要生成一系列數值，例如用於建立測試資料。Snowflake 提供了多種內建函式來實作這一目標。具體的方法玄貓將在後續的文章中詳細介紹，敬請期待。

總結來說，掌握 Snowflake 的數值函式和轉換技巧，是成為資料專家的必備技能。無論是進行數學運算、格式轉換，還是生成測試資料，Snowflake 都提供了豐富的工具和方法，幫助你輕鬆應對各種挑戰。

隨機與序列：資料產生的多樣技巧

在資料函式庫操作中，有時我們需要產生一系列的測試資料，或是模擬特定情境。這時，隨機數和序列生成就成了非常有用的工具。在 Snowflake 中，generator() 函式搭配其他函式，可以輕鬆實作這些需求。

generator() 函式本身是一個表函式，它能產生指定數量的資料列。你可以把它想像成一個虛擬的資料表，讓我們可以針對每一列資料進行操作。

例如，以下範例使用 generator() 產生五列資料，並使用 random() 函式為每一列產生一個隨機數：

PUBLIC> select random()
from table(generator(rowcount => 5));

內容解密

• 這段程式碼展示如何使用 random() 函式生成五個隨機數。
• generator(rowcount => 5)：這部分程式碼使用 generator 函式建立一個包含五列的虛擬表。rowcount => 5 指定 generator 函式產生五列資料。
• from table(...)：這表示從 generator 函式產生的虛擬表中選取資料。
• select random()：這表示選取每一列，並對每一列呼叫 random() 函式，生成一個隨機數。

如果你需要的是一個連續的數字序列，而不是隨機數，可以使用 seq1() 函式：

PUBLIC> select seq1()
from table(generator(rowcount => 5));

內容解密

• 這段程式碼展示如何使用 seq1() 函式生成一個從 0 開始的序列。
• seq1() 函式會依序產生 0, 1, 2, 3, 4。

產生序列在很多場景下都非常方便。例如，以下範例產生 2023 年每個月的第一天：

PUBLIC> select to_date('01/' ||
to_char(seq1() + 1) ||
'/2023','DD/MM/YYYY') as first_of_month
from table(generator(rowcount => 12));

內容解密

• 這段程式碼結合 seq1() 函式和日期轉換函式，產生 2023 年每個月的第一天。
• seq1() + 1：由於 seq1() 從 0 開始，所以這裡加 1，使其產生 1 到 12 的數字，代表月份。
• to_char(seq1() + 1)：將數字轉換為字串。
• '01/' || to_char(seq1() + 1) || '/2023'：將日期字串組合成 DD/MM/YYYY 的格式。
• to_date(..., 'DD/MM/YYYY')：將字串轉換為日期格式。

時間資料的處理：格式與產生

時間資料是資料函式庫中相當複雜的一環，因為它有多種不同的表示方式。例如，同樣一個日期，可以表示為 11/03/2022、Thursday, November 3, 2022，或是 2022-11-03 15:48:56.092 -0700 等等。

Snowflake 在處理日期格式上相當寬容，會嘗試使用多種常見的格式字串來解析日期。不過，有時我們還是需要明確指定格式，才能確保 Snowflake 正確理解我們的日期或時間戳記。

以下範例示範如何使用 to_timestamp() 函式，並指定格式字串來解析時間戳記：

PUBLIC> select to_timestamp('04-NOV-2022 18:48:56',
'DD-MON-YYYY HH24:MI:SS') as now;

內容解密

• 這段程式碼使用 to_timestamp() 函式將特定格式的字串轉換為時間戳記。
• to_timestamp('04-NOV-2022 18:48:56', 'DD-MON-YYYY HH24:MI:SS')：這部分程式碼將字串 '04-NOV-2022 18:48:56' 轉換為時間戳記。'DD-MON-YYYY HH24:MI:SS' 指定了輸入字串的格式。

Snowflake 會使用你提供的格式字串來解析時間戳記，但輸出的格式則由 timestamp_output_format 引數決定。你可以使用以下指令檢視目前的輸出格式：

PUBLIC> show parameters like 'timestamp_out%';

內容解密

• 這段程式碼展示如何檢視 Snowflake 的 timestamp_output_format 引數，該引數決定了時間戳記的預設輸出格式。
• show parameters like 'timestamp_out%';：這行程式碼會顯示所有名稱以 'timestamp_out' 開頭的引數，包括 timestamp_output_format。

如果想要變更輸出格式，可以使用 alter session 指令：

PUBLIC> alter session set timestamp_output_format =
'MM/DD/YYYY HH12:MI:SS AM TZH';

內容解密

• 這段程式碼展示如何使用 alter session 指令變更 Snowflake 的 timestamp_output_format 引數，從而改變時間戳記的預設輸出格式。
• alter session set timestamp_output_format = 'MM/DD/YYYY HH12:MI:SS AM TZH';：這行程式碼將 timestamp_output_format 引數設定為 'MM/DD/YYYY HH12:MI:SS AM TZH'，這表示時間戳記將以 MM/DD/YYYY HH12:MI:SS AM TZH 的格式輸出。

除了指定格式字串外，我們也可以使用內建函式來產生日期和時間戳記。例如，date_from_parts() 和 time_from_parts() 函式可以讓我們指定年、月、日、時、分、秒等元件來產生日期和時間：

PUBLIC> select date_from_parts(2023, 3, 15) as my_date,
time_from_parts(10, 22, 47) as my_time;

內容解密

• 這段程式碼展示如何使用 date_from_parts() 和 time_from_parts() 函式分別建立日期和時間值。
• date_from_parts(2023, 3, 15)：這部分程式碼使用 date_from_parts() 函式建立一個日期，其中 2023 是年份，3 是月份，15 是日。
• time_from_parts(10, 22, 47)：這部分程式碼使用 time_from_parts() 函式建立一個時間，其中 10 是小時，22 是分鐘，47 是秒。

timestamp_from_parts() 函式則可以結合 date_from_parts() 和 time_from_parts() 的結果，產生一個完整時間戳記：

PUBLIC> select timestamp_from_parts(
date_from_parts(2023, 3, 15),
time_from_parts(10, 22, 47)) as my_timestamp;

內容解密

• 這段程式碼展示如何使用 timestamp_from_parts() 函式結合日期和時間值建立一個時間戳記。
• timestamp_from_parts(date_from_parts(2023, 3, 15), time_from_parts(10, 22, 47))：這部分程式碼使用 timestamp_from_parts() 函式建立一個時間戳記，其中日期部分由 date_from_parts(2023, 3, 15) 函式提供，時間部分由 time_from_parts(10, 22, 47) 函式提供。

date_from_parts() 函式還有一個有趣的特性，它允許月份和日期為零或負數。這讓我們可以輕鬆地往前推算日期。例如，將日期設為 0，就可以得到上個月的最後一天：

PUBLIC> select date_from_parts(2024, seq1() + 2, 0) as month_end
from table(generator(rowcount => 12));

內容解密

• 這段程式碼展示如何使用 date_from_parts() 函式和 seq1() 函式生成一年中每個月的最後一天。
• date_from_parts(2024, seq1() + 2, 0)：這部分程式碼使用 date_from_parts() 函式建立一個日期。年份固定為 2024。月份由 seq1() + 2 函式產生，seq1() 函式產生從 0 開始的序列，加 2 是為了產生從 2 月開始的月份。日設定為 0，這會自動計算為上個月的最後一天。

時間資料的運算

Snowflake 提供了許多內建函式，可以對時間資料進行各種運算。這些函式可以接受一個日期值作為引數，並傳回另一個日期值。例如，你可以使用 date_trunc() 函式來取得某個日期的月初、季初或年初：

內容解密

• date_trunc 函式可以用來截斷日期到不同的精確度，例如年、月、日等。

在資料分析和處理中，靈活運用這些函式，可以更有效地處理時間資料，並從中提取有用的資訊。玄貓認為，掌握這些技巧對於資料工程師和分析師來說，是相當重要的。