Snowflake 提供豐富的字串與數值函式，有效處理資料清理、轉換和分析。本文涵蓋字串清理技巧，例如利用 trim()、ltrim() 和 rtrim() 移除空白字元，並使用 length() 計算字串長度。字元替換則可運用 translate() 函式，移除特定字元或進行字元對映。此外，position() 和 substr() 函式方便搜尋子字串位置和提取部分字串。數值處理方面，Snowflake 支援基本算術運算、內建函式如 power()、pi()、mod()、sign() 和 abs()，以及數值轉換函式 cast()、:: 運算元和 to_decimal()。日期和時間戳記處理則可使用 to_date()、to_timestamp()、date_from_parts()、time_from_parts() 和 date_trunc() 等函式，搭配格式化字串精確處理時間資料。
字串資料處理
在資料處理中，字串操作是非常常見的需求。Snowflake 提供了一系列的字串函式，能夠有效地進行字串的清理、搜尋和提取。
字串清理
在處理字串時，經常需要移除多餘的空白字元。Snowflake 提供了 ltrim()、rtrim() 和 trim() 函式來實作這一點。
SELECT
ltrim(str.val) AS 左邊空白移除,
rtrim(str.val) AS 右邊空白移除,
trim(str.val) AS 兩邊空白移除
FROM
(VALUES (' abc ')) AS str(val);
內容解密：
ltrim(str.val)：移除字串左邊的空白字元。rtrim(str.val)：移除字串右邊的空白字元。trim(str.val)：移除字串兩邊的空白字元。
然而，透過 length() 函式可以發現，ltrim() 和 trim() 傳回的字串長度是不同的。
SELECT
length(ltrim(str.val)) AS str1_len,
length(rtrim(str.val)) AS str2_len,
length(trim(str.val)) AS str3_len
FROM
(VALUES (' abc ')) AS str(val);
內容解密：
length(ltrim(str.val))：計算移除左邊空白後字串的長度。length(rtrim(str.val))：計算移除右邊空白後字串的長度。length(trim(str.val))：計算移除兩邊空白後字串的長度。
字元替換
如果想要確保某些字元不出現，可以使用 translate() 函式將它們替換為空字串。例如，移除電話號碼中的格式化字元：
SELECT
translate('(857)-234-5678', '()-', '') AS 電話號碼清理;
內容解密：
translate('(857)-234-5678', '()-', '')：將(857)-234-5678中的(、)和-替換為空字串。
另外，translate() 也可用於將一組字元替換為另一組字元：
SELECT
translate('AxByCz', 'ABC', 'XYZ') AS 字元替換;
內容解密：
translate('AxByCz', 'ABC', 'XYZ')：將AxByCz中的A、B、C分別替換為X、Y、Z。
字串搜尋與提取
要找出子字串在字串中的位置，可以使用 position() 函式。如果子字串未找到，則傳回 0。此外，還可以指定搜尋的起始位置。
SELECT
position('here', str.val) AS pos1,
position('here', str.val, 10) AS pos2,
position('nowhere', str.val) AS pos3
FROM
(VALUES ('here, there, and everywhere')) AS str(val);
內容解密：
position('here', str.val)：找出'here'在字串中的位置。position('here', str.val, 10)：從第 10 個字元開始找出'here'的位置。position('nowhere', str.val)：找出'nowhere'在字串中的位置。
如果只需要字串的一部分，可以使用 substr() 函式。它接受一個字串、起始位置和可選的字元數量。
SELECT
substr(str.val, 1, 10) AS start_of_string,
substr(str.val, 11) AS rest_of_string
FROM
(VALUES ('beginning ending')) AS str(val);
內容解密：
substr(str.val, 1, 10)：提取字串的前 10 個字元。substr(str.val, 11)：提取從第 11 個字元開始的剩餘字串。
也可以將 position() 函式作為引數傳遞給 substr() 函式，以動態確定起始位置。
SELECT
substr(str.val, position('every', str.val)) AS 子字串提取
FROM
(VALUES ('here, there, and everywhere')) AS str(val);
內容解密：
substr(str.val, position('every', str.val))：從'every'的位置開始提取子字串。
Snowflake 也提供了多個傳回布林值的函式，如 startswith()、endswith() 和 contains()，可用於篩選條件。
SELECT
str.val
FROM
(VALUES ('here, there, and everywhere')) AS str(val)
WHERE
startswith(str.val, 'here');
內容解密：
startswith(str.val, 'here')：檢查字串是否以'here'開頭。
數字資料處理
生成數字資料相對簡單，可以直接輸入數字、從表格欄位中檢索數字，或使用算術運算子進行計算。
SELECT
10 AS radius,
2 * 3.14159 * 10 AS circumference;
內容解密：
2 * 3.14159 * 10：計算半徑為 10 的圓周長。
也可以使用括號來指定計算優先順序。
SELECT
(3 * 6) - (10 / 2) AS 結果;
內容解密：
(3 * 6) - (10 / 2)：先計算括號內的運算。
對於更複雜的計算，需要熟悉 Snowflake 中可用的內建數字函式。
數值函式與轉換
在前面的範例中，我們展示瞭如何計算圓的周長。如果您還需要計算面積（πr²），可以使用power()函式：
PUBLIC>select 10 as radius,
2 * 3.14159 * 10 as circumference,
3.14159 * power(10,2) as area;
+
---
-
---
-+
---
-
---
-
---
-
---
+
---
-
---
--+
| RADIUS | CIRCUMFERENCE | AREA |
|
---
-
---
-|
---
-
---
-
---
-
---
|
---
-
---
--|
| 10 | 62.83180 | 314.159 |
+
---
-
---
-+
---
-
---
-
---
-
---
+
---
-
---
--+
Snowflake 也提供了一個方便的pi()函式，這樣您就不需要查詢π的值：
PUBLIC>select 10 as radius,
2 * pi() * 10 as circumference,
pi() * power(10,2) as area;
+
---
-
---
-+
---
-
---
-
---
-
---
+
---
-
---
-
---
-
---
+
| RADIUS | CIRCUMFERENCE | AREA |
|
---
-
---
-|
---
-
---
-
---
-
---
|
---
-
---
-
---
-
---
|
| 10 | 62.83180 | 314.159265359 |
+
---
-
---
-+
---
-
---
-
---
-
---
+
---
-
---
-
---
-
---
+
內容解密：
power(10,2)計算10的平方，即100。pi()傳回π的值，約為3.14159。2 * pi() * 10計算半徑為10的圓的周長。
如果您需要計算一個數字除以另一個數字時的餘數，可以使用mod()函式：
PUBLIC>select mod(70, 9);
+
---
-
---
-
---
-+
| MOD(70, 9) |
|
---
-
---
-
---
-|
| 7 |
+
---
-
---
-
---
-+
內容解密：
mod(70, 9)計算70除以9的餘數，結果為7。
在處理可能為負數或正數的數字時，您可能會想使用sign()函式來判斷數字的正負（-1表示負數，0表示零，+1表示正數），也可以使用abs()函式來傳回數字的絕對值：
PUBLIC>select sign(-7.5233), abs(-7.5233);
+
---
-
---
-
---
-
---
+
---
-
---
-
---
---
+
| SIGN(-7.5233) | ABS(-7.5233) |
|
---
-
---
-
---
-
---
|
---
-
---
-
---
---
|
| -1 | 7.5233 |
+
---
-
---
-
---
-
---
+
---
-
---
-
---
---
+
內容解密：
sign(-7.5233)傳回-1，表示輸入值為負數。abs(-7.5233)傳回7.5233，即輸入值的絕對值。
最後，在處理非整數值（實數）時，有一些函式可以用來移除小數部分（trunc()）、四捨五入到最近的值（round()）或四捨五入到最近的整數（floor()和ceil()）：
PUBLIC>select trunc(6.49), round(6.49, 1), floor(6.49), ceil(6.49);
+
---
-
---
-
---
--+
---
-
---
-
---
-
---
-+
---
-
---
-
---
--+
---
-
---
-
---
-+
| TRUNC(6.49) | ROUND(6.49, 1) | FLOOR(6.49) | CEIL(6.49) |
|
---
-
---
-
---
--+
---
-
---
-
---
-
---
-+
---
-
---
-
---
--|
---
-
---
-
---
-|
| 6 | 6.5 | 6 | 7 |
+
---
-
---
-
---
--+
---
-
---
-
---
-
---
-+
---
-
---
-
---
--+
---
-
---
-
---
-+
內容解密：
trunc(6.49)移除小數部分，傳回6。round(6.49, 1)四捨五入到小數點後一位，傳回6.5。floor(6.49)向下取整，傳回6。ceil(6.49)向上取整，傳回7。
圖表翻譯：
此圖示展示了不同數值函式的運作方式，包括trunc()、round()、floor()和ceil()。
@startuml
skinparam backgroundColor #FEFEFE
skinparam componentStyle rectangle
title Snowflake 字串與數值資料處理技巧
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圖表翻譯： 此圖表呈現了不同數值函式對輸入值6.49的處理結果，包括截斷、四捨五入、向下取整和向上取整。
數值轉換
如果您需要將字串轉換為數字，有幾種策略：明確轉換和隱式轉換（也稱為強制轉換）。讓我們先來看一個隱式轉換的例子：
PUBLIC>select 123.45 as real_num
union
select '678.90' as real_num;
+
---
-
---
---
+
| REAL_NUM |
|
---
-
---
---
|
| 123.45 |
| 678.90 |
+
---
-
---
---
+
在這個例子中，第一個查詢定義了real_num的資料型別為數字，這使得伺服器嘗試將第二個查詢中的字串轉換為數字型別。在這種情況下，轉換成功了。但是，如果轉換失敗，Snowflake會丟擲錯誤：
PUBLIC>select 123.45 as real_num
union
select 'AAA.BB' as real_num;
100038 (22018): Numeric value 'AAA.BB' is not recognized
您可以明確請求資料轉換，使用以下方法之一：
cast()函式::轉換運算元- 特定的轉換函式，如
to_decimal()
以下是使用這三種選項將字串轉換為number(7,2)型別的例子：
PUBLIC>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);
+
---
-
---
---
+
---
-
---
-
---
---
+
---
-
---
-
---
-+
| CAST_VAL | CAST_OPR_VAL | TO_DEC_VAL |
|
---
-
---
---
|
---
-
---
-
---
---
|
---
-
---
-
---
-|
| 15873.26 | 15873.26 | 15873.26 |
+
---
-
---
---
+
---
-
---
-
---
---
+
---
-
---
-
---
-+
內容解密：
cast(str.val as number(7,2))使用cast()函式將字串轉換為數字。str.val::number(7,2)使用::運算元進行轉換。to_decimal(str.val,7,2)使用to_decimal()函式進行轉換。
處理包含非數字字元的字串
有時，數字字串可能包含非數字字元，如貨幣符號。to_decimal()函式提供了一個可選的格式化字串，可以用來處理這種情況：
PUBLIC>select to_decimal(str.val,'$99999.99',7,2) as to_dec_val
from (values ('$15873.26')) as str(val);
+
---
-
---
-
---
-+
| TO_DEC_VAL |
|
---
-
---
-
---
-|
| 15873.26 |
+
---
-
---
-
---
-+
內容解密：
to_decimal(str.val,'$99999.99',7,2)使用格式化字串'$99999.99'來正確解析包含美元符號的字串。
如果您想在實際進行轉換之前測試字串是否可以成功轉換，可以使用try_to_decimal()函式，如果轉換失敗，它會傳回null而不會丟擲錯誤：
PUBLIC>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);
+
---
-
---
---
+
---
---
+
| GOOD | BAD |
|
---
-
---
---
|
---
---
|
| 15873.26 | NULL |
+
---
-
---
---
+
---
---
+
內容解密：
- 第一個
try_to_decimal()呼叫成功轉換並傳回結果。 - 第二個呼叫因格式不符而失敗，傳回
null。
處理數值與時間資料
在處理資料時，經常需要生成特定的數值或時間資料。Snowflake 提供了多種函式來生成和操作這些資料。
生成數值資料
Snowflake 中的 generator() 函式是一個表格函式，可以用來生成指定數量的行。結合 random() 或 seq1() 函式，可以生成隨機數或序列數。
生成隨機數
select random()
from table(generator(rowcount => 5));
輸出結果：
+
---
-
---
-
---
-
---
-
---
---
+
| RANDOM() |
|
---
-
---
-
---
-
---
-
---
---
|
| -3677757698388611270 |
| -3592261260448828970 |
| 3109671761060067515 |
| 9164189189028838701 |
| 254977226160947522 |
+
---
-
---
-
---
-
---
-
---
---
+
生成序列數
select seq1()
from table(generator(rowcount => 5));
輸出結果：
+
---
-
---
-+
| SEQ1() |
|
---
-
---
-|
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
+
---
-
---
-+
生成日期資料
select to_date('01/' ||
to_char(seq1() + 1) ||
'/2023','DD/MM/YYYY') as first_of_month
from table(generator(rowcount => 12));
輸出結果：
+
---
-
---
-
---
-
---
-+
| FIRST_OF_MONTH |
|
---
-
---
-
---
-
---
-|
| 2023-01-01 |
| 2023-02-01 |
| 2023-03-01 |
| ... |
| 2023-12-01 |
+
---
-
---
-
---
-
---
-+
程式碼解析：
- 使用
generator()函式生成12行資料。 seq1()函式生成序列號（0到11）。- 將序列號加1後轉換為字元，並與固定字串拼接形成日期字串。
- 使用
to_date()函式將日期字串轉換為日期格式。
處理時間資料
時間資料的處理相對複雜，因為日期的表示方式多種多樣。Snowflake 能夠自動識別多種日期格式，但也可以透過指定格式化字串來明確日期的解釋方式。
使用格式化字串
select to_timestamp('04-NOV-2022 18:48:56',
'DD-MON-YYYY HH24:MI:SS') as now;
輸出結果：
+
---
-
---
-
---
-
---
-
---
-
---
--+
| NOW |
|
---
-
---
-
---
-
---
-
---
-
---
--|
| 2022-11-04 18:48:56.000 |
+
---
-
---
-
---
-
---
-
---
-
---
--+
程式碼解析：
- 使用
to_timestamp()函式將字元轉換為時間戳。 - 指定格式化字串
'DD-MON-YYYY HH24:MI:SS'以明確字元的解釋方式。
生成日期與時間戳
Snowflake 提供了一系列函式用於生成日期和時間戳，例如 date_from_parts() 和 time_from_parts()。
生成特定日期和時間
select date_from_parts(2023, 3, 15) as my_date,
time_from_parts(10, 22, 47) as my_time;
輸出結果：
+
---
-
---
-
---
-+
---
-
---
---
+
| MY_DATE | MY_TIME |
|
---
-
---
-
---
-+
---
-
---
---
|
| 2023-03-15 | 10:22:47 |
+
---
-
---
-
---
-+
---
-
---
---
+
程式碼解析：
- 使用
date_from_parts()生成特定日期。 - 使用
time_from_parts()生成特定時間。
操作日期和時間戳
Snowflake 提供了一系列函式用於操作日期和時間戳，例如 date_trunc()。
使用 date_trunc() 函式
select date_trunc('month', current_date) as first_day_of_month;
輸出結果：
+
---
-
---
-
---
-
---
-
---
--+
| FIRST_DAY_OF_MONTH |
|
---
-
---
-
---
-
---
-
---
--|
| 2023-03-01 |
+
---
-
---
-
---
-
---
-
---
--+
程式碼解析：
- 使用
date_trunc()將當前日期截斷到月份的第一天。
