SQL 高階技巧與異常值檢測

在資料函式庫操作中，高效的數字處理和異常值檢測至關重要。本文將探討如何使用 SQL 進行高階的數字處理，例如計算特定部門薪水佔比、處理可空欄位聚合，以及排除極端值計算平均數。接著，將介紹兩種異常值檢測方法：中位數絕對偏差（MAD）和貝氏定律，並提供不同資料函式庫系統的 SQL 實作範例，幫助讀者更好地理解和應用這些技巧。

計算總計的百分比與處理可空欄位的聚合運算

計算部門薪水佔總薪水的百分比

在進行資料分析時，經常需要計算某個部門的薪水佔總薪水的百分比。這個問題看似簡單，但在不同的資料函式庫系統中，處理方式可能會有所不同。

傳統解決方案

傳統上，可以使用條件聚合函式來計算特定部門的薪水總和，然後除以所有部門的薪水總和。例如，計算部門10的薪水佔總薪水的百分比：

SELECT 
    (CAST(SUM(CASE WHEN deptno = 10 THEN sal END) AS DECIMAL) / SUM(sal)) * 100 AS pct
FROM emp;

使用視窗函式的替代方案

另一種方法是使用視窗函式來計算總薪水和特定部門的薪水。這種方法在DB2、SQL Server和Oracle中尤其有用：

SELECT DISTINCT 
    CAST(d10 AS DECIMAL) / total * 100 AS pct
FROM (
    SELECT 
        deptno,
        SUM(sal) OVER () AS total,
        SUM(sal) OVER (PARTITION BY deptno) AS d10
    FROM emp
) x
WHERE deptno = 10;

內容解密：

視窗函式的使用：SUM(sal) OVER () 計算所有員工的薪水總和，而 SUM(sal) OVER (PARTITION BY deptno) 則計算每個部門的薪水總和。
資料過濾：由於視窗函式是在WHERE子句之後執行的，因此需要在外部查詢中過濾部門編號。
資料型別轉換：使用 CAST 將整數轉換為小數，以確保除法運算的結果是小數。

處理可空欄位的聚合運算

當對可空欄位進行聚合運算時，需要特別注意NULL值的處理。預設情況下，聚合函式會忽略NULL值。

將NULL值轉換為0

可以使用 COALESCE 函式將NULL值轉換為0，以確保它們被包含在聚合運算中：

SELECT AVG(COALESCE(comm, 0)) AS avg_comm
FROM emp
WHERE deptno = 30;

內容解密：

COALESCE函式：將NULL值轉換為指定的值（在本例中為0），以確保它們被包含在聚合運算中。
NULL值的影響：如果不使用 COALESCE，聚合函式將忽略NULL值，導致平均值計算不準確。

排除最高和最低值的平均值計算

在某些情況下，需要排除最高和最低值來計算平均值，以減少極端值的影響。

使用子查詢排除最高和最低值

SELECT AVG(sal)
FROM emp
WHERE sal NOT IN (
    (SELECT MIN(sal) FROM emp),
    (SELECT MAX(sal) FROM emp)
);

使用視窗函式排除最高和最低值

SELECT AVG(sal)
FROM (
    SELECT sal, MIN(sal) OVER () AS min_sal, MAX(sal) OVER () AS max_sal
    FROM emp
) x
WHERE sal NOT IN (min_sal, max_sal);

內容解密：

子查詢的使用：透過子查詢找出最高和最低薪水，並在主查詢中排除這些值。
視窗函式的使用：在子查詢中使用視窗函式一次性取得每個員工的薪水以及最高和最低薪水，然後在外部查詢中排除最高和最低薪水。

數字處理的高階技巧

在處理資料函式庫中的數字資料時，我們經常需要進行一些複雜的運算和轉換，以滿足特定的業務需求。本篇文章將探討一些高階的數字處理技巧，包括計算修剪平均值、將字母數字字串轉換為數字，以及修改執行總計中的值。

7.14 將字母數字字串轉換為數字

問題描述

假設我們有一個字母數字的字串，例如 “paul123f321”，我們希望從中提取出數字部分，得到 “123321”。

解決方案

我們可以使用 TRANSLATE 和 REPLACE 函式來實作這一目標。以下是針對不同資料函式倉管理系統的解決方案：

DB2

select cast(
    replace(
        translate('paul123f321',
            repeat('#',26),
            'abcdefghijklmnopqrstuvwxyz'),
        '#','')
    as integer) as num
from t1

Oracle、SQL Server 和 PostgreSQL

select cast(
    replace(
        translate('paul123f321',
            'abcdefghijklmnopqrstuvwxyz',
            rpad('#',26,'#')),
        '#','')
    as integer) as num
from t1

內容解密：

內部運作原理：首先，TRANSLATE 函式將字串中的非數字字元替換為 #。然後，REPLACE 函式將所有的 # 移除，最後使用 CAST 將結果轉換為整數。
語法差異：DB2 使用 REPEAT 函式，而其他資料函式庫使用 RPAD 函式來生成一個由 # 組成的字串。此外，TRANSLATE 函式的引數順序在不同資料函式庫中可能有所不同。
替代方案：如果字串中可能包含其他特殊字元，我們可以採用另一種方法：先找出所有的數字字元，然後移除其他字元。這需要巢狀使用 TRANSLATE 和 REPLACE 函式。

select replace(
    translate('paul123f321',
        replace(translate('paul123f321',
            '0123456789',
            rpad('#',10,'#')),
        '#',''),
        rpad('#',length('paul123f321'),'#')),
    '#','') as num
from t1

7.15 修改執行總計中的值

問題描述

假設我們有一個信用卡帳戶的交易記錄，包括交易金額和交易型別（購買或付款）。我們希望計算出每次交易後的餘額。

解決方案

我們可以使用視窗函式 SUM OVER 來計算執行總計，並結合 CASE 表示式來根據交易型別調整金額。

create view V (id, amt, trx)
as
select 1, 100, 'PR' from t1 union all
select 2, 100, 'PR' from t1 union all
select 3, 50, 'PY' from t1 union all
select 4, 100, 'PR' from t1 union all
select 5, 200, 'PY' from t1 union all
select 6, 50, 'PY' from t1;

select trx_type, amt, balance
from (
    select 
        case trx when 'PR' then 'PURCHASE' when 'PY' then 'PAYMENT' end as trx_type,
        amt,
        sum(case trx when 'PR' then amt when 'PY' then -amt end) over (order by id) as balance
    from V
) as subquery;

內容解密：

視窗函式的使用：SUM OVER 用於計算執行總計，根據 id 的順序進行累加或累減。
CASE 表示式的應用：根據 trx 的值決定是否將 amt 加到或減去執行總計中。同時，將 trx 的值轉換為更具描述性的字串，如 “PURCHASE” 或 “PAYMENT”。
子查詢的使用：為了簡化邏輯，將計算執行總計和轉換交易型別的操作放在子查詢中，然後在外層查詢中選擇需要的欄位。

使用中位數絕對偏差（Median Absolute Deviation）找出異常值

在資料分析中，異常值的檢測是一項重要的任務。異常值可能是由於資料收集錯誤、資料錄入錯誤或是特殊情況下的真實資料。傳統上，人們使用標準差來檢測異常值，但這種方法在資料分佈不對稱或非正態分佈時可能會失效。

問題描述

給定一組資料，如何使用中位數絕對偏差來找出其中的異常值？

解決方案

首先，我們需要計算資料的中位數。然後，計算每個資料點與中位數之間的絕對偏差。接著，再計算這些絕對偏差的中位數，即中位數絕對偏差。最後，透過比較每個資料點與中位數之間的絕對偏差與中位數絕對偏差的比率，來判斷是否為異常值。

SQL Server 解決方案

SQL Server 提供了 PERCENTILE_CONT 函式，可以簡化中位數的計算。以下是使用 CTE（公用表表達式）來計算中位數絕對偏差的 SQL 程式碼：

WITH median (median)
AS (
    SELECT DISTINCT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sal) OVER ()
    FROM emp
),
Deviation (Deviation)
AS (
    SELECT ABS(sal - median)
    FROM emp JOIN median ON 1 = 1
),
MAD (MAD)
AS (
    SELECT DISTINCT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY deviation) OVER ()
    FROM Deviation
)
SELECT ABS(sal - MAD) / MAD, sal, ename, job
FROM MAD JOIN emp ON 1 = 1;

PostgreSQL 和 DB2 解決方案

PostgreSQL 和 DB2 將 PERCENTILE_CONT 視為聚合函式，因此語法略有不同：

WITH median (median)
AS (
    SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sal)
    FROM emp
),
devtab (deviation)
AS (
    SELECT ABS(sal - median)
    FROM emp JOIN median ON 1 = 1
),
MedAbsDeviation (MAD)
AS (
    SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY deviation)
    FROM devtab
)
SELECT ABS(sal - MAD) / MAD, sal, ename, job
FROM MedAbsDeviation JOIN emp ON 1 = 1;

Oracle 解決方案

Oracle 提供了 MEDIAN 函式，可以直接計算中位數：

WITH Deviation (Deviation)
AS (
    SELECT ABS(sal - MEDIAN(sal))
    FROM emp
),
MAD (MAD)
AS (
    SELECT MEDIAN(Deviation)
    FROM Deviation
)
SELECT ABS(sal - MAD) / MAD, sal, ename, job
FROM MAD JOIN emp ON 1 = 1;

MySQL 解決方案

MySQL 目前尚未支援 PERCENTILE_CONT 或 MEDIAN 函式，因此需要使用子查詢來計算中位數：

WITH rank_tab (sal, rank_sal) AS (
    SELECT sal, CUME_DIST() OVER (ORDER BY sal)
    FROM emp
),
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
),
medianSal (medianSal) AS (
    SELECT (MAX(sal) + MIN(sal)) / 2
    FROM inter
),
deviationSal (Sal, deviationSal) AS (
    SELECT Sal, ABS(sal - medianSal)
    FROM emp JOIN medianSal ON 1 = 1
),
distDevSal (sal, deviationSal, distDeviationSal) AS (
    SELECT sal, deviationSal, CUME_DIST() OVER (ORDER BY deviationSal)
    FROM deviationSal
),
DevInter (DevInter, sal) AS (
    SELECT MIN(deviationSal), sal
    FROM distDevSal WHERE distDeviationSal >= 0.5
    UNION
    SELECT MAX(DeviationSal), sal
    FROM distDevSal WHERE distDeviationSal <= 0.5
),
MAD (MedianAbsoluteDeviance) AS (
    SELECT ABS(emp.sal - (MIN(devInter) + MAX(devInter)) / 2)
    FROM emp JOIN DevInter ON 1 = 1
)
SELECT emp.sal, MedianAbsoluteDeviance, (emp.sal - deviationSal) / MedianAbsoluteDeviance
FROM (emp JOIN MAD ON 1 = 1) JOIN deviationSal ON emp.sal = deviationSal.sal;

結果討論

這些查詢都遵循相同的邏輯：首先計算資料的中位數，然後計算每個資料點與中位數之間的絕對偏差，接著計算這些絕對偏差的中位數（即中位數絕對偏差），最後比較每個資料點與中位數之間的絕對偏差與中位數絕對偏差的比率，以判斷是否為異常值。

程式碼詳解：

以上 SQL 程式碼片段均用於計算中位數絕對偏差，以找出資料中的異常值。

CTE 的使用：所有的查詢都使用了 CTE，以便於逐步計算中位數、絕對偏差和中位數絕對偏差。
PERCENTILE_CONT 或 MEDIAN 函式：用於計算資料的中位數，不同的資料函式庫系統有不同的實作方式。
絕對偏差的計算：透過將每個資料點與中位數之間的差值取絕對值來獲得。
中位數絕對偏差的計算：再次使用 PERCENTILE_CONT 或 MEDIAN 函式，對絕對偏差進行計算。
異常值的判斷：透過比較每個資料點與中位數之間的絕對偏差和中位數絕對偏差的比率來實作。

圖表翻譯： 此圖示展示了使用中位數絕對偏差法找出異常值的步驟。首先，從原始資料開始，計算資料的中位數。然後，計算每個資料點與中位數之間的絕對偏差。接著，計算這些絕對偏差的中位數，即中位數絕對偏差。最後，透過比較每個資料點的絕對偏差與中位數絕對偏差的比率，判斷是否為異常值。

這些 SQL 程式碼和圖表解釋瞭如何使用中位數絕對偏差來檢測資料中的異常值，適用於不同的資料函式庫系統。

使用查詢來找出每個值與中位數偏差的比率

在統計分析中，我們經常需要檢測資料中的異常值。雖然標準差是一種常見的方法，但它假設資料遵循正態分佈。當資料不是正態分佈時，中位數絕對偏差（Median Absolute Deviation, MAD）是一種更穩健的方法。

中位數絕對偏差的計算

首先，我們需要計算中位數絕對偏差。這涉及到以下步驟：

計算資料的中位數。
計算每個資料點與中位數之間的絕對偏差。
計算這些絕對偏差的中位數，即為中位數絕對偏差。

使用中位數絕對偏差檢測異常值

一旦我們有了中位數絕對偏差，我們就可以用它來檢測異常值。例如，如果一個值距離中位數超過三個中位數絕對偏差，那麼它可以被視為異常值。

with 
SalaryData (SAL)
as
(select SAL from emp),
MedianSAL (Median)
as
(select PERCENTILE_CONT(0.5) within group (order by SAL) as Median from SalaryData),
AbsoluteDeviations (Deviation)
as
(select abs(SAL - Median) as Deviation from SalaryData cross join MedianSAL),
MedianAbsoluteDeviation (MAD)
as
(select PERCENTILE_CONT(0.5) within group (order by Deviation) as MAD from AbsoluteDeviations)
select SAL 
from SalaryData, MedianSAL, MedianAbsoluteDeviation
where abs(SAL - Median) > 3 * MAD;

內容解密：

首先，我們使用CTE（Common Table Expression）SalaryData來選取薪水資料。
然後，MedianSAL計算薪水的中位數。
AbsoluteDeviations計算每個薪水與中位數之間的絕對偏差。
MedianAbsoluteDeviation計算這些絕對偏差的中位數，即中位數絕對偏差。
最後，我們選取那些距離中位數超過三個中位數絕對偏差的薪水值。

貝氏定律（Benford’s Law）檢測異常資料

貝氏定律是一種檢測資料是否異常的方法，尤其是在檢測欺詐或人為操縱的數字時非常有用。它根據數字第一位的分佈預期。

with 
FirstDigits (FirstDigit)
as
(select left(cast(SAL as CHAR),1) as FirstDigit from emp),
TotalCount (Total)
as
(select count(*) as Total from emp),
ExpectedBenford (Digit, Expected)
as
(select value, (log10(value + 1) - log10(value)) as Expected from t10 where value < 10)
select 
    count(FirstDigit) as CountFirstDigit,
    Digit,
    coalesce(count(*)/Total,0) as ActualProportion,
    Expected
From FirstDigits
Join TotalCount on 1=1
Right Join ExpectedBenford on FirstDigits.FirstDigit = ExpectedBenford.Digit
group by Digit, Total, Expected
order by Digit;

內容解密：

FirstDigits CTE提取每個薪水值的第一位數字。
TotalCount CTE計算總記錄數。
ExpectedBenford CTE根據貝氏定律計算預期的第一位數字的分佈。
主查詢結合這些CTE，計算實際的第一位數字的分佈，並與預期分佈進行比較。

圖表翻譯：

此查詢結果可以用來繪製實際分佈與預期分佈的比較圖，以視覺化方式呈現資料是否遵循貝氏定律。

@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

圖表翻譯： 此圖示呈現了實際數字第一位的分佈與根據貝氏定律預期的分佈之間的比較。如果實際分佈與預期分佈相符，則表明資料正常；否則，可能存在資料異常，需要進一步調查。

玄貓 BlackCat

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