[SQL 入門與面試實戰系列] 5. 數據類型深掘：字串、日期與數字的高效處理

終於來到 [SQL 入門與面試實戰系列] 第五章了各位！

本章要和大家介紹如何使用各種函數，來處理字串、日期與數字這些不同類型的資料。之所以放到第五章才來進行講解，主要是因為這些函數在不同的數據庫系統中可能略有差異，但基本概念是通用的。大家只要了解函數本身的功用，在不同環境撰寫 SQL 時，就可以透過 Google 或 ChatGPT 搜尋，找出相對應的函數來解決問題！

[SQL 入門與面試實戰系列] 5_字串、日期與數字函數.ipynb

• 字串函數
• 時間函數
• 數字函數

• 字串函數

讓我們從處理字串的函數開始。字串資料基本上是除了數字資料以外最常見的，例如會員編號、訂單編號、商品名稱等。常用的字串函數功能包含：

1. 尋找符合條件的字串值：LIKE + 萬用字元

2. 擷取字串：SUBSTR( )

3. 連接字串：|| ||、CONCAT( )

4. 替代特定字符：REPLACE( )

1. 尋找符合條件的字串值：LIKE + 萬用字元

我們已經很熟悉針對數值資料進行條件設定，比如 SUM(Total) ≥ 40，設定數值介於某個範圍為條件。之前我們針對字串的條件設定都是指定明確的條件值，比如 Country = 'USA'；除此之外，我們也可以對字串訂定更靈活的規則，例如字串包含哪些字符、資料開頭是什麼字符等。這時我們就可以使用 LIKE，搭配萬用字元 ‘%’ 和 ’_’ 來設定條件。

• % (百分比符號)：代表任意數量（包括零）個字元
• _ (底線)：代表剛好一個字元

讓我們直接來看例子就會馬上理解！假如我們想要從 `customers` 表找出所有名字以 ‘A’ 開頭的顧客，我們可以這樣寫：

SELECT FirstName FROM customers
WHERE FirstName LIKE 'A%'

在這個例子中，’%’ 代表 ‘A’ 之後可以有任意數量的字元，也就是說只要 FirstName 欄位的資料是 A 開頭的就符合條件。

再來看另一個例子，假設我們想要找出歌名包含 ‘Love’ 的歌曲，我們可以這樣寫：

SELECT TrackId, Name FROM tracks
WHERE Name LIKE '%Love%'

在這個例子中，’%’ 代表 ‘Love’ 之前與之後可以有任意數量 (也包含沒有) 的字元，也就是不論 ‘Love’ 出現在字串的哪個位置都可以。所以我們可以看到 TrackId 為 24 的 ‘Love In An Elevator’，以及 TrackId 為 195 的 ‘Let Me Love You Baby’ 都符合條件。

當然，我們也可以加上 NOT，查詢歌名「不」包含 ‘Love’ 的歌曲：

SELECT TrackId, Name FROM tracks
WHERE Name NOT LIKE '%Love%'

接著試看看明確定位字串中特定位置的字符。假設我們想要查找名字的第二個字母是 'a' 的顧客：

SELECT FirstName
FROM customers
WHERE FirstName LIKE '_a%'

這裡的 ’_’ 代表在 ‘a’ 前面只能有一個字元，而 ‘%’ 則表示在 ‘a’ 之後可以有任意多的字元。因此，’_a%’ 表示任何以 ‘a’ 為第二個字母的名字都將被匹配。

實務中，我們最常對字串設的條件就是「開頭為特定字符」以及「包含特定字符」，例如查詢產品名稱中包含某個品牌或品項的字串。由於 ’%’ 代表任意數量的字符 (包括零個字符)，這種靈活性讓我們不需要知道條件字串的確切位置或數量，也因此我們更常使用 ‘%’ 作為萬用字元。相較之下 ‘_’ 的靈活性太低 (只能是一個字元)，反而較不常使用。

2. 擷取字串：SUBSTR( )

如果我們要從較長的字串中，精確地擷取字串中的特定部分，就可以使用 SUBSTRING 函數。SUBSTRING (子串)，顧名思義就是從原始的完整字串 (主串) 中提取的一小段字串。由於 SUBSTRING 寫起來很冗長，所以多數環境支援簡寫成 SUBSTR。SUBSTRING 的基本格式如下：

SELECT SUBSTR("欄位名稱", '起點位置', '長度') AS "新欄位名稱"
FROM "資料表名"

• 欄位名稱：要擷取的字串欄位
• 起點位置：從哪一個字元開始擷取，位置計數從頭開始為 1
• 長度：要擷取多少個字

我們直接來擷取顧客的姓名來看看：

SELECT FirstName, SUBSTR(FirstName, 1, 5) AS ShortName
FROM customers

在這個例子中，我們從 `FirstName` 字段中，提取了每位顧客名字的前五個字符。

再比如我們想要對 `invoices` 表的訂單日期欄位 (例如 ‘2009–01–01 00:00:00’)，擷取日期部分的字串 (’2009–09–01’)。我們從第一個字開始，擷取日期部分的長度 (10)：

SELECT InvoiceId, InvoiceDate,
 SUBSTR(InvoiceDate, 1, 10) AS ExtractedDate
FROM invoices

這裡，我們從 `InvoiceDate` 的第一個字符開始，擷取接下來的 10 個字符，這樣就只獲得了日期部分。

如果我們想擷取時間的部分 (’00:00:00’)，我們可以算一下時間是從字串的第 12 個字開始，並擷取時間的長度 (8)：

SELECT InvoiceId, InvoiceDate, 
  SUBSTR(InvoiceDate, 12, 8) AS ExtractedTime
FROM invoices

以上是 SUBSTRING 的基本用法。SUBSTRING 的參數還有許多種變化，例如我們可以在起點參數使用負數，從字串的結尾開始數：

SELECT InvoiceId, InvoiceDate, 
  SUBSTR(InvoiceDate, -8, 8) AS ExtractedTime
FROM invoices

如果字串很長，我們又不想增加老花眼慢慢數要從哪個位置開始，也可以搭配 LENGTH 來計算字串的長度，再確定擷取的起點。寫法如下：

SELECT InvoiceId, InvoiceDate, 
  SUBSTR(InvoiceDate, LENGTH(InvoiceDate) - 7, 8) AS ExtractedTime
FROM invoices

LENGTH(InvoiceDate) 算出 `InvoiceDate` 字串的長度 (19)，減掉 7 (-8+1) 即是我們想要擷取時間的起始點。

此外，如果我們要擷取的字串，有固定的分隔符來區分不同的信息 (常見的包括空格或底線)，可以透過 INSTR () 函數找出特定字符的位置，再來進行擷取。

SELECT INSTR("欄位名", '查找字串')

此函數用來查找指定子串在主串第一次出現的位置。一樣以擷取 `InvoiceDate` 欄位的時間部分為例，我們可以這樣寫：

SELECT InvoiceId, InvoiceDate, 
 SUBSTR(InvoiceDate, INSTR(InvoiceDate, ' ') + 1, 8) AS ExtractedTime
FROM invoices

最後補充，有一些環境支援 LEFT 與 RIGHT 的函數，直接指定從字串的左邊或右邊開始擷取多少長度 (我們在 Colab 使用的 SQLite 就不支援)。寫法如下：

SELECT InvoiceId, InvoiceDate, 
  LEFT(InvoiceDate, 10) AS ExtractedDate,
  RIGHT(InvoiceDate, 8) AS ExtractedTime
FROM invoices

在多數情境下，時間數據多以文字形式 (‘YYYY-MM-DD HH:MM:SS’) 進行記錄，因此字串函數與時間函數經常相互搭配。時間函數的部分我們馬上就會探討。

3. 連接字串：|| ||、CONCAT( )

如果我們需要將兩個欄位的字串合併成一個字串，在我們使用的 SQLite 中可以使用 || || (兩個直線 + 連接字符 + 兩個直線)。我們直接來看它的使用方式，例如我們想要連接 `customers` 表中， `FirstName` 欄位與 `LastName` 欄位成一個全名欄位，可以這樣寫：

SELECT FirstName, LastName, 
  FirstName ||' '|| LastName AS FullName
FROM customers

兩個直條線與兩個直條線之間的 ' ' (單引號中的空格) 作為兩個欄位之間的連接字符，確保 `FirstName` 和 `LastName` 在合併時有一個空格分隔。

如果不需要在合併的字串之間添加任何字符，可以直接將欄位名稱用 || 連接，不加任何間隔字符：

SELECT FirstName, LastName, 
  FirstName || LastName AS FullName
FROM customers

其他的 SQL 環境 (例如 MySQL) 中，則是使用 CONCAT() 函數來合併字串，寫法如下：

SELECT CONCAT(FirstName, ' ', LastName) AS FullName
FROM customers

(補充：不同環境對於連接欄位有空值的情況，返回的連接結果會有不同。例如，假設 `LastName` 欄位有空值，有些環境在連接操作時會將空值視為空字符，還是會返回 `Firstname`；有些環境則會直接返回空值。如果實際遇到類似的情況，再去 Google 或 GPT 確認使用的環境如何處理空值)

4. 替代特定字符：REPLACE( )

如果我們需要在字串中查找特定字符進行替換，就可以使用 REPLACE。通常是對數據的清洗，或是進行統一格式時會使用到。REPLACE 函數的基本格式如下：

SELECT REPLACE("欄位名稱", '查找子串', '替換的新子串') AS "新欄位名稱"
FROM "資料表名"

例如，我們針對 `customers` 表中的 `Email` 欄位，將當中結尾是 ‘@gmail.com’ 的電子郵件，替換成 ‘@example.com’，我們可以這樣寫：

SELECT Email,
  REPLACE(Email, '@gmail.com', '@example.com') AS UpdatedEmail
FROM customers

我們可以看到 `Email` 欄位當中有 ‘@gmail.com’ 的值，在結尾被替換成了 ‘@example.com’；而其他沒有 ‘@gmail.com’ 的值則保持原始字串。

REPLACE 函數在執行時，會對欄位中每一個出現的指定子串進行替換。而如果原始字串中沒有找到指定的子串，則不會進行任何替換，原始字串將保持不變返回。

以上，就是最常使用關於處理字串的函數。

• 時間函數

時間值的欄位在實務中也非常常見，例如訂單時間、會員註冊時間等。時間函數在不同 SQL 環境中，名稱與參數可能有所差異。在這裡我們主要以 SQLite 為例，探討其時間函數的用法，並提及其他數據庫系統的相關函數作為補充。時間函數最主要的功用包括：

1. 提取日期和時間信息：DATE( )、DATETIME( )、CURRENT_DATE

2. 設定日期和時間格式：strftime( )、DATE_FORMAT( )

3. 進行時間計算
a. 對日期時間加減單位時間：DATE( )、DATEADD( )
b. 計算兩個日期時間的差異：julianday( )、DATEDIFF( )

1. 提取日期和時間信息：DATE( )、DATETIME( )、CURRENT_DATE

前面有提到，實務中紀錄時間的欄位主要以字串形式存儲，通常遵循 ‘YYYY-MM-DD HH:MM:SS’ (例如 2024–01–01 12:00:00) 或 ‘YYYY-MM-DD’ (例如 2024–01–01) 的格式 (符合 ISO 8601 標準)。我們使用的 SQLite 套件很聰明，會自動將這些格式的字串視為時間類型；而在其他環境中，可能需要先將字串轉為時間格式，才能進行時間相關的操作。(可參考章節最後面的 CAST( ) 函數用法)

我們最常使用以下幾個函數來提取時間信息：

• DATE( )：用來獲得日期。
• TIME( )：用來獲得時間。
• DATETIME( )：獲得日期和時間。

讓我們直接來操作！我們對 `invoices` 表的 `InvoiceDate` 欄位，使用函數來獲取時間信息：

SELECT InvoiceDate, 
  DATE(InvoiceDate) AS ExtractedDate, 
  TIME(InvoiceDate) AS ExtractedTime, 
  DATETIME(InvoiceDate) AS ExtractedDatetime
FROM invoices

我們不僅可以對欄位值進行時間信息的獲取，還可以透過以下時間函數，直接獲取當前的日期與時間信息：

SELECT CURRENT_DATE, -- 返回當前的日期，格式為 YYYY-MM-DD
  CURRENT_TIME, -- 返回當前的時間 (UTC+0)，格式為 HH:MM:SS
  CURRENT_TIMESTAMP, -- 返回當前的日期和時間，格式為 YYYY-MM-DD HH:MM:SS

多數環境都支持 CURRENT_DATE 的寫法；有些環境則以 GETDATE( ) 來獲取當前日期。實務中我們經常使用 CURRENT_DATE 來動態獲取最新的數據，比如即時訂單資料。

如果遇到不能直接使用時間函數的字串格式 (例如使用斜杠作為分隔符)，這時需要搭配前面學到的字串處理函數進行轉換，再使用時間函數進行操作。(有些環境對於時間為非標準格式的容忍度比較高，但還是建議先將時間字串變為 ‘YYYY-MM-DD’ 格式)

-- 如果遇到斜杠作為分隔符，可以使用 REPLACE 替換成破折號
SELECT 
  DATE('2024/01/01') AS FormattedDateTest,  -- 無法直接轉換，返回為空值
  DATE(REPLACE('2024/01/01', '/', '-')) AS FormattedDate

2. 設定日期和時間格式：strftime( )、DATE_FORMAT( )

接下來，我們可以變更日期和時間值的呈現格式，例如轉換為只有年份和月份。在 SQLite 中，可以使用 strftime( ) 函數來定義輸出格式：

SELECT strftime('格式字符串', '日期時間值')

它接受一個格式字符串和一個日期時間值，並根據格式字符串返回相應的格式。格式字符串主要使用以下字符：

• ‘%Y’：四位數的年份 (如 2024)
• ‘%m’：兩位數的月份 (01 至 12)。記得 ‘m’ 是小寫，跟分鐘區別。
• ‘%d’：兩位數的日 (01 至 31)
• ‘%H’：小時 (00 至 23)
• ‘%M’：分鐘 (00 至 59)。記得 ‘M’ 是小寫，跟月份區別。
• ‘%S’：秒 (00 至 59)

例如，我們想要將 `InvoiceDate` 欄位值轉換為年月 (YYYY-MM) 與月日 (MM-DD) 格式，可以這樣寫：

SELECT InvoiceDate, 
  strftime('%Y-%m', InvoiceDate) AS ExtractedYM,
  strftime('%m-%d', InvoiceDate) AS ExtractedMD
FROM invoices

再比如我們想提取 `InvoiceDate` 欄位，日期對應月份的第一天，可以這樣寫：

SELECT 
  InvoiceDate, 
  strftime('%Y-%m-01', InvoiceDate) AS FirstDayOfMonth
FROM invoices

實際應用中，我們常將時間轉換不同的格式，使我們能夠以不同的時間維度進行聚合分析。例如計算 invoices 表每月的訂單數、顧客數與銷售業績：

SELECT 
  strftime('%Y-%m', InvoiceDate) AS ExtractedYM,
  COUNT(DISTINCT InvoiceId) AS OrderCnt,
  COUNT(DISTINCT CustomerId) AS CustomerCnt,
  SUM(Total) AS MonthSales
FROM invoices
GROUP BY ExtractedYM
ORDER BY ExtractedYM

而在其他環境 (例如 MySQL) 中，則使用 DATE_FORMAT( ) 函數來轉換時間格式：

-- 第一個參數是日期時間值，第二個參數是格式字符串
SELECT DATE_FORMAT(CURRENT_DATE, '%Y-%m') AS ExtractedYM

3. 進行時間計算
a. 對日期時間加減單位時間：DATE( )、DATEADD( )
b. 計算兩個日期時間的差異：julianday( )、DATEDIFF( )

a. 對日期時間加減單位時間：DATE( )、DATEADD( )

在 SQLite 當中，我們可以直接在 DATE( )、DATETIME( ) 等獲取時間信息的函數中使用。方法是在日期時間字符串後，直接加上或減去特定的單位時間 (如天、月、年等)。

例如對特定日期 (’2024–01–01’) 加減天數：

SELECT 
  DATE('2024-01-01', '+10 days') AS DatePlus10Days,
  DATE('2024-01-01', '-1 day') AS DateMinus1Day

對日期加減月份與年份：

SELECT 
 DATE('2024-01-01', '-12 months') AS DateMinus12Months,
 DATE('2024-01-01', '-1 year') AS DateMinus1Year

也可以對時間進行加減：

SELECT
 DATETIME('2024-01-01 00:00:00', '+10 minutes') AS TimePlus10Mins,
 DATETIME('2024-01-01 00:00:00', '+1 hour') AS TimePlus1Hour

在其他環境中 (如 MySQL)，則使用 DATE_ADD( ) 與 DATE_SUB( ) 函數進行時間的加減。

SELECT
  DATE_ADD('2024-01-01', INTERVAL 10 DAY) AS DatePlus10Days,  -- 返回 2024-01-11
  DATE_SUB('2024-01-01 01:00:00', INTERVAL 1 HOUR) AS TimeMinus1Hour -- 返回 2024-01-01 00:00:00

實務中我們經常需要設定特定的時間範圍。例如我們想要獲取 `invoices` 表中，’2009–02–01’ 前 30 天的銷售總額：

SELECT SUM(Total) AS TotalSales
FROM invoices
WHERE InvoiceDate BETWEEN DATE('2009-02-01', '-30 DAYS') AND '2009-02-01'

進階應用：我們也可以計算銷售狀況的月環比 (MoM%) 或年同比 (YoY%) 。比如計算 invoices 表中，’2009–02–01’ 銷售業績的月環比 (與 ‘2009–01–01’ 比較)：

-- 先計算每日的銷售額
WITH DailySales AS (
  SELECT
    DATE(InvoiceDate) AS ExtractedDate,
    SUM(Total) AS TotalSales
  FROM invoices
  GROUP BY ExtractedDate
),
-- 選擇 '2009-02-01' 當日的銷售額
SelectedDateSales AS (
  SELECT TotalSales FROM DailySales
  WHERE ExtractedDate = '2009-02-01'
),
-- 選擇 '2009-02-01' 上月同期，也就是 '2009-01-01' 的銷售額
PreviousMonthSales AS (
  SELECT TotalSales FROM DailySales
  WHERE ExtractedDate = DATE('2009-02-01', '-1 month')
)

-- 計算 '2009-02-01' 銷售額的 MoM(%)
SELECT
  (SELECT TotalSales FROM SelectedDateSales) AS ThisMonthSales,
  (SELECT TotalSales FROM PreviousMonthSales) AS LastMonthSales,
  ((SELECT TotalSales FROM SelectedDateSales) - (SELECT TotalSales FROM PreviousMonthSales)) 
  / (SELECT TotalSales FROM PreviousMonthSales) * 100 AS MoMPercentage

b. 計算兩個日期時間的差異：julianday( )、DATEDIFF( )

在 SQLite 中，我們透過 julianday( ) 函數來計算兩個日期時間差了多少天。寫法如下：

SELECT 
  julianday('2024-01-31') - julianday('2024-01-01') AS DiffInDays

julianday() 函數返回的是日數，如果我們想計算小時數差，只需要將天數差再乘以 24：

SELECT 
  julianday('2024-01-01 12:00:00') - julianday('2024-01-01 00:00:00') AS DiffInDays, -- 返回 0.5(天)
  (julianday('2024-01-01 12:00:00') - julianday('2024-01-01 00:00:00')) * 24 AS DiffInHours

而在其他環境中 (如 MySQL、SQL Server)，則使用 DATEDIFF 函數計算兩者的天數差異。

-- 在 MySQL 中，結束日期放在前，開始日期放在後
SELECT DATEDIFF('2024-01-02', '2024-01-01') AS DiffInDays;

-- 在 SQL Server 中，第一個參數是計算差異的時間單位，開始日期在前，結束日期在後
SELECT DATEDIFF(DAY, '2024-01-01', '2024-01-02') AS DiffInDays

以上，就是時間函數的介紹。

• 數字函數

最後，我們來介紹幾個數字相關的函數。

1. 四捨五入與設定小數位數：ROUND( )

2. 其他數字函數

1. 四捨五入與設定小數位數

ROUND( ) 函數用於將數字四捨五入到指定的小數位數。如果省略小數位數參數，則默認四捨五入到最接近的整數。格式如下：

SELECT ROUND(123.4567, 2), -- 結果為 123.46
 ROUND(123.4567) -- 結果為 123

ROUND( ) 主要用於處理數據計算後的結果，例如兩個欄位相乘除、或聚合函數計算的數值。我們來實際應用，將 invoices 表中的顧客銷售額進行四捨五入：

SELECT CustomerId, 
  SUM(Total) AS CustomerSales,
  ROUND(SUM(Total)) AS CustomerSalesInt
FROM invoices
GROUP BY CustomerId

補充：ROUND( ) 函數適用於多數環境，主要用於數學四捨五入操作。而有些環境 (如 SQL Server) 還有支援 FORMAT( ) 函數，可進行更多數字格式化選項 (例如每千位數加逗號)。但通常我們使用 SQL，更多將焦點放在數據的分析與產出。針對數字格式化的選項，用 Excel 之類的工具可以更快進行更改。

2. 其他數字函數

再介紹幾個實務很少用到，但還是可以了解一下的函數。例如取絕對值可以使用 ABS( ) 函數：

SELECT ABS(-1) -- 結果為 1

如果我們要獲取兩數相除餘數的部分，可以使用 MOD( ) 函數，或是直接在被除數與除數之間放上 ‘%’ 符號：

SELECT MOD(10, 3), 10 % 3 -- 兩者結果都為 1

最後提醒大家在進行除法計算時，要注意除數可能為 0 的情況，這可能導致錯誤或無效的計算結果。例如我們想要計算每個顧客的的平均訂單購買金額 (AOV，Average Order Value)，這種分析可能會遇到有客戶從未進行過購買的情況 (即某些客戶 `invoices` 表中沒有記錄)，在進行平均計算時訂單數 (分母) 會是 0。此時我們需要使用此前學到的 CASE WHEN，對除數是否為 0 進行判定：

SELECT
  customers.CustomerId,
  customers.FirstName ||' '|| customers.LastName AS CustomerName,
  SUM(invoices.Total) AS CustomerGMV,
  COUNT(invoices.InvoiceId) AS OrderCnt,
  ROUND(
    CASE 
      WHEN COUNT(invoices.InvoiceId) = 0 THEN 0
      ELSE SUM(invoices.Total) / COUNT(invoices.InvoiceId)
    END, 2
  ) AS AOV
FROM customers
LEFT JOIN invoices ON customers.CustomerId = invoices.CustomerId
GROUP BY customers.CustomerId, CustomerName

本章節涵蓋了多種針對不同資料形態的處理函數，讓我們快速回顧一下重點：

1. 字串處理函數：最常使用 LIKE + 萬用字元來進行字串過濾，以及使用 SUBSTR( ) 函數來擷取字串的特定部分。
2. 時間處理函數：透過 DATE( )、strftime( ) 等函數來提取和轉換時間訊息，以及透過 DATE( ) 進行時間範圍的設定與調整。
3. 數字處理函數：比較常使用 ROUND( ) 進行四捨五入與設定小數位。注意除數可能為 0 的情況。

本章介紹的函數在實務中比較常運用到，也是多數環境通用的。但即便這些函數大致相同，不同環境中的函數名稱和參數還是可能有所差異，實際操作時還是經常需要查詢具體用法。因此，熟悉這些函數的基本功能是關鍵，對於具體的函數名稱和參數細節，我們可以根據需要再進行查找。

最後，如果有轉換數據類型的需求，例如將文本轉換為數字、轉換為日期，或是其他類型轉換，就可以使用 CAST( ) 函數。基本語法如下：

SELECT CAST('需要被轉換的值' AS '想轉換成的數據類型')

常用的數據類型名稱像是 INT (整數)、FLOAT (浮點數)、VARCHAR (字符串)、DATE (存儲日期)、TIMESTAMP (日期和時間)。

例如將包含日期時間的字符串，轉換為 TIMESTAMP 類型：

SELECT CAST('2023-03-25 17:45:00' AS TIMESTAMP)

或將數字字符串轉為文字：

SELECT CAST('123.6' AS INTEGER)

在使用 CAST( ) 函數進行數據類型轉換時，需要確保轉換是有意義且合理的。不當的類型轉換可能導致轉換錯誤，例如將 ‘Brian’ 文本轉換為數字或日期。此外，不同環境在數據類型和轉換規則上可能有所差異，因此在使用時也可以先查詢在該環境的使用方式。

以上，就是本章節的內容 (也是函數最多的一章節)！掌握了前五章所學的內容，大家已經具備幾乎所有題目會用到的解題技能。在下一章我們會探討窗口函數，補上最後一塊成為 SQL 高手的拼圖！

(高手、高手、高高手)