在資料函式庫應用中，驗證資料格式和進行統計分析是常見的需求。本文將探討如何使用 SQL 查詢來驗證電話號碼格式，並示範如何計算各種統計資料，例如平均值、最小值、最大值、總和、執行總計、平滑處理時間序列資料、眾數和中位數。我們將使用正規表示式來驗證電話號碼格式，並使用聚合函式和視窗函式來計算統計資料。同時，也會針對不同資料函式庫系統（例如 MySQL、PostgreSQL、Oracle、DB2 和 SQL Server）的語法差異提供對應的解決方案，確保程式碼在不同環境下的可移植性。
找出無效的電話號碼格式
在處理客戶資料時，經常需要檢查電話號碼的格式是否正確。本篇將介紹如何使用 SQL 查詢找出無效的電話號碼格式。
問題描述
假設我們有一個員工評論表 employee_comment，其中包含員工 ID 和評論文字。我們需要找出那些包含無效電話號碼格式的評論。
解決方案
首先，我們需要定義什麼是「有效的」電話號碼格式。假設有效的電話號碼格式為三位數字，接著是一個分隔符號（-、. 或 空格），然後是三位數字，再接著是相同的分隔符號，最後是四位數字。
select emp_id, text
from employee_comment
where regexp_like(text, '[0-9]{3}[-. ][0-9]{3}[-. ][0-9]{4}')
and regexp_like(
regexp_replace(text,
'[0-9]{3}([-. ])[0-9]{3}\1[0-9]{4}','***'),
'[0-9]{3}[-. ][0-9]{3}[-. ][0-9]{4}')
內容解密：
regexp_like(text, '[0-9]{3}[-. ][0-9]{3}[-. ][0-9]{4}')：找出包含類別似電話號碼格式的評論。[0-9]{3}匹配三位數字。[-. ]匹配分隔符號（-、. 或 空格）。
regexp_replace(text, '[0-9]{3}([-. ])[0-9]{3}\1[0-9]{4}','***')：將有效的電話號碼替換為***。[0-9]{3}([-. ])匹配三位數字和後面的分隔符號，並捕捉分隔符號。[0-9]{3}\1匹配三位數字和相同的分隔符號。[0-9]{4}匹配四位數字。\1反向參照第一個捕捉的分隔符號，確保前後分隔符號一致。
- 第二個
regexp_like檢查替換後的文字是否仍包含類別似電話號碼的格式。如果有，表示原文字中包含無效的電話號碼格式。
結果與討論
執行上述查詢後，我們可以得到包含無效電話號碼格式的評論。例如：
| EMP_ID | TEXT | |
-|
| | 7369 | 126 Varnum, Edmore MI 48829, 989 313-5351 | | 7844 | 989-387.5359 | | 9999 | 906-387-1698, 313-535.8886 |
這些評論中包含的電話號碼格式不一致，例如分隔符號不統一。
圖表示例
圖表翻譯： 此圖示展示了檢查無效電話號碼格式的流程。首先檢查評論是否包含類別似電話號碼的格式，接著替換有效的電話號碼，最後檢查是否仍存在類別似電話號碼的格式，以此判斷是否存在無效格式。
計算欄位的平均值、最小值、最大值與總和
在處理資料函式庫中的數值資料時，經常需要計算某個欄位的平均值、最小值、最大值或總和。SQL 提供了多個聚合函式來完成這些任務，包括 AVG、MIN、MAX 和 SUM。
計算平均值
若要計算某個欄位的平均值，可以使用 AVG 函式。如果沒有指定 WHERE 子句，AVG 函式將計算所有非 NULL 值的平均值。
SELECT AVG(sal) AS avg_sal
FROM emp;
內容解密：
AVG(sal)：計算sal欄位的平均值。AS avg_sal：將計算結果的欄位別名設為avg_sal。FROM emp：指定資料來源為emp表。
若要按特定條件分組計算平均值，可以使用 GROUP BY 子句。例如，按部門計算平均薪水：
SELECT deptno, AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno;
內容解密：
deptno：選擇部門編號欄位。AVG(sal) AS avg_sal：計算每個部門的平均薪水。GROUP BY deptno：根據部門編號進行分組。
查詢欄位中的最小/最大值
要查詢某個欄位中的最小或最大值，可以使用 MIN 和 MAX 函式。
SELECT MIN(sal) AS min_sal, MAX(sal) AS max_sal
FROM emp;
內容解密：
MIN(sal)：找出最低薪水。MAX(sal)：找出最高薪水。
若要按部門查詢最小和最大薪水，可以結合使用 MIN、MAX 和 GROUP BY：
SELECT deptno, MIN(sal) AS min_sal, MAX(sal) AS max_sal
FROM emp
GROUP BY deptno;
內容解密：
deptno：選擇部門編號。MIN(sal)和MAX(sal)：分別計算每個部門的最低和最高薪水。
計算欄位值的總和
要計算某個欄位的所有值的總和，可以使用 SUM 函式。
SELECT SUM(sal)
FROM emp;
內容解密：
SUM(sal)：計算所有員工的薪水總和。
若要按部門計算薪水總和，可以使用 SUM 和 GROUP BY：
SELECT deptno, SUM(sal) AS total_for_dept
FROM emp
GROUP BY deptno;
內容解密：
deptno：選擇部門編號。SUM(sal) AS total_for_dept：計算每個部門的薪水總和。
重點注意事項
- NULL值的處理：聚合函式（如
AVG、MIN、MAX、SUM）會忽略NULL值。 - GROUP BY的使用：當需要按特定欄位分組進行聚合運算時，使用
GROUP BY子句。 - SELECT列表的限制：在使用
GROUP BY時，SELECT列表中的欄位必須出現在GROUP BY子句中，除非它們被包含在聚合函式內。
使用 SQL 進行資料計數與執行總計
在資料函式倉管理與分析中，經常需要對資料進行計數、匯總以及計算執行總計等操作。SQL 提供了多種函式與子句來實作這些功能，包括 COUNT、GROUP BY 以及視窗函式如 SUM OVER。
計算資料表中的列數
若要計算資料表中的列數，可以使用 COUNT(*) 函式。
select count(*)
from emp
內容解密：
COUNT(*)用於計算資料表中的所有列數，無論列中的值是否為NULL。- 此查詢將傳回資料表
emp中的總列數。
分組計算列數
當需要按特定欄位分組計算列數時，可以結合使用 COUNT(*) 與 GROUP BY 子句。
select deptno, count(*)
from emp
group by deptno
內容解密：
GROUP BY deptno根據deptno欄位的值將資料分組。COUNT(*)計算每個分組內的列數。- 此查詢將傳回每個部門的編號及其對應的員工數量。
計算欄位中的非 NULL 值數量
若要計算某個欄位中非 NULL 值的數量，可以使用 COUNT(欄位名)。
select count(comm)
from emp
內容解密：
COUNT(comm)用於計算comm欄位中非NULL值的數量。- 在此範例中，它將傳回獲得手續費的員工數量，因為只有獲得手續費的員工在
comm欄位中有值。
生成執行總計
要計算某個欄位的執行總計，可以使用視窗函式 SUM OVER。
select ename, sal,
sum(sal) over (order by sal, empno) as running_total
from emp
order by sal
內容解密：
SUM(sal) OVER (ORDER BY sal, empno)根據sal和empno的排序順序計算薪水的執行總計。- 使用
sal和empno進行排序是為了避免因薪水相同而導致的執行總計錯誤。 - 此查詢將傳回每個員工的姓名、薪水以及截至目前的薪水執行總計。
生成執行乘積
若要計算某個欄位的執行乘積，可以使用視窗函式結合數學運算實作。
-- 這裡需要根據具體需求調整查詢
內容解密：
- 由於 SQL 標準中沒有直接提供執行乘積的函式，一種方法是利用對數和指數函式的特性來間接實作。
- 例如，可以使用
EXP(SUM(LOG(欄位名)))的形式來計算乘積，但需注意處理零和負值。
數值資料的平滑處理與眾數計算
在處理時間序列資料或統計資料時，我們經常需要對資料進行平滑處理以找出趨勢，或是計算眾數來瞭解資料的分佈特性。本篇文章將探討如何使用 SQL 來實作這些功能。
平滑處理時間序列資料
時間序列資料往往受到各種因素的影響而呈現出波動，例如每日銷售額可能會因為星期幾或資料收集方式的不同而有所不同。為了更好地理解資料的趨勢，我們可以使用移動平均法來平滑資料。
使用 LAG 函式計算移動平均
SELECT
date1,
sales,
LAG(sales, 1) OVER (ORDER BY date1) AS salesLagOne,
LAG(sales, 2) OVER (ORDER BY date1) AS salesLagTwo,
(sales + LAG(sales, 1) OVER (ORDER BY date1) + LAG(sales, 2) OVER (ORDER BY date1)) / 3 AS MovingAverage
FROM
sales;
內容解密：
LAG(sales, 1) OVER (ORDER BY date1)：取得前一天的銷售額。LAG(sales, 2) OVER (ORDER BY date1)：取得兩天前的銷售額。- 移動平均計算：將當天、前一天和兩天前的銷售額加總後除以 3，得到移動平均值。
加權移動平均
在某些情況下，我們可能希望給予最近的資料更大的權重。這時可以使用加權移動平均法。
SELECT
date1,
sales,
LAG(sales, 1) OVER (ORDER BY date1),
LAG(sales, 2) OVER (ORDER BY date1),
((3 * sales) + (2 * LAG(sales, 1) OVER (ORDER BY date1)) + LAG(sales, 2) OVER (ORDER BY date1)) / 6 AS SalesMA
FROM
sales;
內容解密：
- 加權計算：給予當天的銷售額權重 3，前一天的權重 2，兩天前的權重 1，然後除以總權重 6，得到加權移動平均值。
眾數計算
眾數是指在一組資料中出現頻率最高的值。我們可以使用 SQL 的聚合函式和視窗函式來計算眾數。
使用 DENSE_RANK 函式計算眾數
SELECT
sal
FROM (
SELECT
sal,
DENSE_RANK() OVER (ORDER BY cnt DESC) AS rnk
FROM (
SELECT
sal,
COUNT(*) AS cnt
FROM
emp
WHERE
deptno = 20
GROUP BY
sal
) x
) y
WHERE
rnk = 1;
內容解密：
- 內層查詢：計算每個薪水的出現次數。
DENSE_RANK()：根據出現次數對薪水進行排名。- 外層查詢：選出排名第一的薪水，即眾數。
使用 KEEP 擴充套件計算眾數（Oracle）
SELECT
MAX(sal) KEEP (DENSE_RANK FIRST ORDER BY cnt DESC) AS sal
FROM (
SELECT
sal,
COUNT(*) AS cnt
FROM
emp
WHERE
deptno = 20
GROUP BY
sal
);
內容解密：
KEEP：保留根據cnt排序後的第一個值。MAX(sal)：如果有多個眾數，則傳回最大的那一個。
7.10 計算中位數
問題
要計算某個欄位中數值的中位數。例如，想要找出部門 20 的薪水中位數。根據以下薪水資料：
select sal
from emp
where deptno = 20
order by sal;
結果如下：
SAL
---
-
---
---
800
1100
2975
3000
3000
中位數是 2975。
解決方案
除了 Oracle 使用內建函式計算中位數外，其他資料函式庫系統（如 DB2、PostgreSQL 和 SQL Server）都可以使用視窗函式來更有效地計算中位數。
DB2 和 PostgreSQL
使用視窗函式 PERCENTILE_CONT 來找出中位數：
select percentile_cont(0.5)
within group(order by sal)
from emp
where deptno = 20;
SQL Server
同樣使用 PERCENTILE_CONT，但需要加上 OVER() 子句：
select percentile_cont(0.5)
within group(order by sal)
over()
from emp
where deptno = 20;
MySQL
MySQL 沒有 PERCENTILE_CONT 函式，因此需要使用 CUME_DIST 函式和 CTE（Common Table Expression）來模擬實作：
with rank_tab (sal, rank_sal) as (
select sal, cume_dist() over (order by sal)
from emp
where deptno = 20
),
inter as (
select sal, rank_sal from rank_tab
where rank_sal >= 0.5
union
select sal, rank_sal from rank_tab
where rank_sal <= 0.5
)
select avg(sal) as MedianSal
from inter;
Oracle
Oracle 可以直接使用 MEDIAN 或 PERCENTILE_CONT 函式：
select median(sal)
from emp
where deptno = 20;
select percentile_cont(0.5)
within group(order by sal)
from emp
where deptno = 20;
詳細解析
Oracle、PostgreSQL、SQL Server 和 DB2
除了 Oracle 的 MEDIAN 函式，其他資料函式庫的解決方案結構相同。PERCENTILE_CONT 函式可以直接套用中位數的定義，即第 50 百分位數。因此，使用這個函式並傳入適當的引數（0.5）即可找到中位數。
MySQL
由於 MySQL 沒有 PERCENTILE_CONT，需要先使用 CUME_DIST 對薪水進行排序並標記百分位數。然後在 CTE 中篩選出中位數附近的值，最後取平均得到中位數。這種方法適用於奇數和偶數列的情況。
7.11 計算佔總計的百分比
問題
要計算某個欄位的值佔總計的百分比。例如，想要知道部門 10 的薪水佔所有薪水的百分比。
解決方案
MySQL 和 PostgreSQL
直接使用條件聚合函式計算部門 10 的薪水總和，並除以所有薪水的總和：
select (sum(
case when deptno = 10 then sal end)/sum(sal)
)*100 as pct
from emp;
DB2、Oracle 和 SQL Server
使用內嵌檢視和視窗函式 SUM OVER 同時計算所有薪水的總和和部門 10 的薪水總和，然後在外部查詢中進行除法和乘法運算：
select distinct (d10/total)*100 as pct
from (
select deptno,
sum(sal)over() total,
sum(sal)over(partition by deptno) d10
from emp
) x
where deptno = 10;
詳細解析
MySQL 和 PostgreSQL
使用 CASE 陳述式篩選出部門 10 的薪水並求和，然後除以所有薪水的總和。由於聚合函式會忽略 NULL 值，因此不需要 ELSE 子句。
DB2、Oracle 和 SQL Server
內嵌檢視使用視窗函式 SUM OVER 同時計算所有薪水的總和和每個部門的薪水總和。外部查詢則篩選出部門 10 的資料並進行百分比計算。
圖表翻譯：
以下是一個簡單的流程圖，展示了計算中位數的步驟：
@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圖表翻譯： 此圖示展示了計算中位數的基本流程。首先讀取並排序資料，然後根據資料數量是奇數還是偶數，分別取中間值或中間兩個值的平均，最終得到中位數。
