SQL數學運算與統計分析應用

– 10 分鐘閱讀

SQL 提供了豐富的數學函式和運算子，方便開發者進行資料分析。從基本的加減乘除到進階的指數、平方根、階乘運算，SQL 都能輕鬆應付。在除法運算中，需要注意整數除法和浮點數除法的差異，並善用 CAST 函式進行型別轉換。此外，SQL 的運算子優先順序與一般數學規則相同，可利用括號控制運算順序。利用美國人口普查資料，可以示範如何計算自然增長率、驗證資料一致性，以及計算水域面積佔比等實際應用。瞭解運算子優先順序對於正確計算至關重要，例如在計算人口變化時，需要正確使用括號確保計算結果的準確性。在資料分析中，百分比和百分比變化是常用的指標，SQL 提供了簡潔的語法實作這些計算。聚合函式 SUM() 和 AVG() 可用於計算總和與平均值，但平均值容易受到極端值的影響。中位數作為一個穩健的統計指標，更能反映資料的中心趨勢。PostgreSQL 提供了 percentile_cont() 和 percentile_disc() 函式計算中位數和其他百分位數，方便進行更深入的資料分析。

SQL 中的數學運算

SQL 不僅用於資料查詢和管理，還可以用於執行各種數學運算。PostgreSQL 支援多種數學運算，包括基本的加、減、乘、除，以及更進階的指數、根號和階乘運算。

基本的加、減、乘運算

SQL 中的基本數學運算與一般數學運算相同。以下是一些範例：

SELECT 2 + 2;
SELECT 9 - 1;
SELECT 3 * 4;

這些查詢分別傳回 4、8 和 12。值得注意的是，當我們不查詢任何表格時，結果會顯示在一個名為 ?column? 的欄位下。如果需要，可以為結果欄位指定別名，例如：

SELECT 3 * 4 AS result;

內容解密：

  • SELECT 陳述式用於檢索資料或執行運算。
  • 2 + 2、9 - 1 和 3 * 4 是基本的數學表示式。
  • 結果顯示在一個預設的欄位名稱下，可以使用 AS 關鍵字自定義欄位名稱。

除法和模數運算

SQL 中的除法運算需要注意資料型別。如果兩個整數相除，結果將是整數商，不包含餘數。例如：

SELECT 11 / 6;  -- 傳回 1

如果需要取得餘數，可以使用模數運算子 %：

SELECT 11 % 6;  -- 傳回 5

要進行小數除法，可以將其中一個數值轉換為浮點數或使用 CAST 函式：

SELECT 11.0 / 6;  -- 傳回 1.83333
SELECT CAST(11 AS numeric(3,1)) / 6;  -- 傳回 1.83333

內容解密：

  • / 運算子用於整數除法，傳回整數商。
  • % 運算子用於取得餘數。
  • 將運算元轉換為浮點數或使用 CAST 可以進行小數除法。

指數、根號和階乘運算

PostgreSQL 提供了一系列進階數學函式和運算子，包括指數、根號和階乘。

SELECT 3 ^ 4;  -- 指數運算，傳回 81
SELECT |/ 10;  -- 平方根，傳回 sqrt(10)
SELECT sqrt(10);  -- 同上，使用 sqrt() 函式
SELECT ||/ 10;  -- 立方根
SELECT factorial(4);  -- 階乘，傳回 4! = 24
SELECT 4 !;  -- 同上，使用 ! 運算子（僅限 PostgreSQL 13 及更早版本）

內容解密：

  • ^ 是指數運算子，用於計算一個數的冪次方。
  • |/ 和 sqrt() 用於計算平方根。
  • ||/ 用於計算立方根。
  • factorial() 和 ! 用於計算階乘。

注意運算順序

SQL 的運算順序遵循標準的數學規則：先計算指數和根號，接著是乘法、除法和模數，最後是加法和減法。如果需要改變運算順序，可以使用括號。

SELECT 7 + 8 * 9;  -- 傳回 79
SELECT (7 + 8) * 9;  -- 傳回 135

內容解密：

  • SQL 的運算順序與數學規則一致。
  • 使用括號可以改變預設的運算順序。

SQL 數學運算的實際應用：以美國人口普查資料為例

在 SQL 中進行數學運算不僅能夠簡化資料分析的過程，還能幫助我們從資料中挖掘出有價值的資訊。本篇文章將以美國 2019 年人口普查資料為例，介紹如何在 SQL 中使用數學運算來分析資料。

運算子優先順序的重要性

在進行 SQL 數學運算時，瞭解運算子的優先順序是非常重要的。不同的運算子具有不同的優先順序，這會影響到最終的計算結果。例如，乘法和除法的優先順序高於加法和減法。如果需要改變運算順序，可以使用括號來強制改變運算順序。

SELECT 7 + 8 * 9;
SELECT (7 + 8) * 9;

在第一個查詢中，由於乘法的優先順序高於加法，因此結果為 79。在第二個查詢中，由於使用了括號，因此加法運算優先進行，結果為 135。

內容解密：

  1. SELECT 7 + 8 * 9;：這個查詢展示了運算子優先順序的重要性。由於乘法優先於加法，因此首先計算 8 * 9，然後再加上 7。
  2. SELECT (7 + 8) * 9;：透過使用括號，強制先進行加法運算，然後再進行乘法運算。

在美國人口普查資料表中進行數學運算

接下來，我們將使用美國 2019 年人口普查資料表 us_counties_pop_est_2019 來進行一些實際的數學運算。首先，讓我們回顧一下這個表的結構。

SELECT county_name AS county,
       state_name AS state,
       pop_est_2019 AS pop,
       births_2019 AS births,
       deaths_2019 AS deaths,
       international_migr_2019 AS int_migr,
       domestic_migr_2019 AS dom_migr,
       residual_2019 AS residual
FROM us_counties_pop_est_2019;

內容解密：

  1. county_name AS county：將 county_name 列別名為 county，以簡化輸出結果。
  2. pop_est_2019 AS pop：將 pop_est_2019 列別名為 pop，代表 2019 年的人口估計數。
  3. 其他列同樣被賦予了簡短的別名，以便於理解和減少輸出結果的寬度。

加減運算在列上的應用

現在，讓我們嘗試使用兩個列進行簡單的計算。例如，計算每個縣的自然增長率（即出生人數減去死亡人數）。

SELECT county_name AS county,
       state_name AS state,
       births_2019 AS births,
       deaths_2019 AS deaths,
       births_2019 - deaths_2019 AS natural_increase
FROM us_counties_pop_est_2019
ORDER BY state_name, county_name;

內容解密：

  1. births_2019 - deaths_2019 AS natural_increase：計算每個縣的自然增長率，即出生人數減去死亡人數，並將結果列別名為 natural_increase。
  2. ORDER BY state_name, county_name;：按照州名和縣名的字母順序對結果進行排序。

驗證資料的正確性

為了驗證資料的正確性，我們可以進行一些簡單的檢查。例如，檢查 2019 年的人口估計數是否等於 2018 年的人口估計數加上出生、死亡、遷移和殘差等因素的變化。

SELECT county_name AS county,
       state_name AS state,
       pop_est_2019 AS pop,
       pop_est_2018 + births_2019 - deaths_2019 + international_migr_2019 + domestic_migr_2019 + residual_2019 AS components_total,
       pop_est_2019 - (pop_est_2018 + births_2019 - deaths_2019 + international_migr_2019 + domestic_migr_2019 + residual_2019) AS difference
FROM us_counties_pop_est_2019
ORDER BY difference DESC;

內容解密：

  1. pop_est_2018 + births_2019 - deaths_2019 + international_migr_2019 + domestic_migr_2019 + residual_2019 AS components_total：計算根據各個組成部分得出的總人口數。
  2. pop_est_2019 - (...) AS difference：計算實際的 2019 年人口估計數與根據組成部分計算出的總人口數之間的差異。
  3. ORDER BY difference DESC;：按照差異的大小降序排列結果，以便找出差異最大的縣。

計算佔總體的百分比

計算某個資料點佔總體的百分比是一種常見的資料分析方法。例如，我們可以計算每個縣的水域面積佔總面積的百分比。

SELECT county_name AS county,
       state_name AS state,
       area_water::numeric / (area_land + area_water) * 100 AS pct_water
FROM us_counties_pop_est_2019
ORDER BY pct_water DESC;

內容解密：

  1. area_water::numeric / (area_land + area_water) * 100 AS pct_water：計算水域面積佔總面積的百分比。其中，::numeric 用於將 area_water 轉換為數字型別，以確保除法運算是正確的。
  2. ORDER BY pct_water DESC;：按照水域面積百分比的大小降序排列結果，以便找出水域面積比例最高的縣。

透過上述例子，我們可以看到 SQL 在資料分析中的強大功能。無論是簡單的加減乘除運算，還是複雜的資料驗證和百分比計算，SQL 都能夠提供簡潔而有效的解決方案。掌握這些技能，將有助於我們更好地理解和分析資料，從而獲得有價值的洞察。

資料分析中的數學運算

在資料分析中，經常需要進行各種數學運算來提取有用的資訊。本文將介紹如何使用 SQL 進行常見的數學運算，包括計算百分比、百分比變化、平均值和總和等。

計算百分比

計算百分比是一種常見的資料分析任務。例如，我們可以使用美國縣級人口普查資料來計算每個縣的水域面積佔總面積的百分比。

SELECT county, state, 
       (area_water / (area_water + area_land)) * 100 AS pct_water
FROM us_counties;

為了避免整數除法導致的結果為 0，我們需要將其中一個整數轉換為數值型別。這裡使用 PostgreSQL 的雙冒號符號將 area_water 轉換為數值型別。

SELECT county, state, 
       (area_water::numeric / (area_water + area_land)) * 100 AS pct_water
FROM us_counties;

內容解密：

  1. area_water::numeric 將 area_water 轉換為數值型別，以確保除法運算的結果是小數。
  2. (area_water + area_land) 計算縣級總面積。
  3. 將水域面積除以總面積並乘以 100，得到水域面積佔總面積的百分比。

追蹤百分比變化

百分比變化是一種重要的資料分析指標，用於衡量兩個數值之間的差異。例如，我們可以使用百分比變化來分析不同部門之間的支出變化。

CREATE TABLE percent_change (
    department text,
    spend_2019 numeric(10,2),
    spend_2022 numeric(10,2)
);

INSERT INTO percent_change
VALUES
('Assessor', 178556, 179500),
('Building', 250000, 289000),
('Clerk', 451980, 650000),
('Library', 87777, 90001),
('Parks', 250000, 223000),
('Water', 199000, 195000);

SELECT department,
       spend_2019,
       spend_2022,
       round(((spend_2022 - spend_2019) / spend_2019) * 100, 1) AS pct_change
FROM percent_change;

內容解密：

  1. spend_2022 - spend_2019 計算兩年之間的支出差異。
  2. (spend_2022 - spend_2019) / spend_2019 計算百分比變化。
  3. round(..., 1) 將結果四捨五入到小數點後一位。

使用聚合函式計算平均值和總和

SQL 中的聚合函式可以用於計算一列中的值的總和或平均值。例如，我們可以使用 sum() 和 avg() 函式來計算美國縣級人口普查資料中的總人口和平均人口。

SELECT sum(pop_est_2019) AS county_sum,
       round(avg(pop_est_2019), 0) AS county_average
FROM us_counties_pop_est_2019;

內容解密：

  1. sum(pop_est_2019) 計算所有縣級人口估計值的總和。
  2. avg(pop_est_2019) 計算所有縣級人口估計值的平均值。
  3. round(..., 0) 將平均值四捨五入到整數。

中位數的重要性

中位數是一種重要的資料分析指標，用於衡量一組數值的「中間」值。中位數可以減少異常值的影響，使資料分析結果更加可靠。

例如，假設六個孩子的年齡分別為 10、11、10、9、13 和 12，中位數可以更好地代表這組資料的「中間」值，而不是平均值。

統計分析中的平均值與中位數

在進行統計分析時，瞭解資料的分佈情況是非常重要的。平均值（average）和中位數（median）是兩個常見的統計指標，它們可以用來描述資料的集中趨勢。然而，在某些情況下，這兩個指標可能會給出不同的結果。

平均值的侷限性

平均值是所有資料值的總和除以資料的數量。當資料中存在極端值（outlier）時，平均值可能會被拉偏，導致它不能準確地代表資料的典型值。例如，假設有一群學生的年齡分別是 10、11、10、9、13、12 歲，平均年齡是 $(10 + 11 + 10 + 9 + 13 + 12) / 6 = 10.8$ 歲。如果這時有一位 46 歲的老師加入，平均年齡就會變成 $(10 + 11 + 10 + 9 + 13 + 12 + 46) / 7 = 15.9$ 歲。這個例子表明，當資料中存在極端值時，平均值可能會變得不夠可靠。

程式碼範例：計算平均值

-- 建立測試表格
CREATE TABLE ages (
    age integer
);

-- 插入測試資料
INSERT INTO ages (age) VALUES
(10), (11), (10), (9), (13), (12), (46);

-- 計算平均年齡
SELECT avg(age) AS average_age
FROM ages;

內容解密：

  1. 首先，建立一個名為 ages 的表格，包含一個整數欄位 age。
  2. 然後，向 ages 表格中插入測試資料。
  3. 最後，使用 avg() 函式計算 age 欄位的平均值。

中位數的優勢

中位數是資料按順序排列後的中間值。當資料數量為奇數時，中位數是中間的那個值；當資料數量為偶數時，中位數是中間兩個值的平均數。在上面的例子中，當老師加入後，年齡資料的中位數是 11 歲，這比平均值更能代表這群人的典型年齡。

程式碼範例：計算中位數

-- 使用 percentile_cont() 函式計算中位數
SELECT percentile_cont(0.5)
WITHIN GROUP (ORDER BY age) AS median_age
FROM ages;

內容解密：

  1. 使用 percentile_cont(0.5) 函式計算 age 欄位的中位數。
  2. WITHIN GROUP (ORDER BY age) 子句指定了資料的排序方式。
  3. percentile_cont() 函式傳回連續的中位數值。

使用百分位數函式

PostgreSQL 中的 percentile_cont() 和 percentile_disc() 函式可以用來計算中位數和其他百分位數。percentile_cont() 傳回連續的百分位數值，而 percentile_disc() 傳回離散的百分位數值。

程式碼範例：比較 percentile_cont() 和 percentile_disc()

-- 建立測試表格
CREATE TABLE percentile_test (
    numbers integer
);

-- 插入測試資料
INSERT INTO percentile_test (numbers) VALUES
(1), (2), (3), (4), (5), (6);

-- 比較 percentile_cont() 和 percentile_disc()
SELECT 
    percentile_cont(0.5) WITHIN GROUP (ORDER BY numbers) AS continuous_median,
    percentile_disc(0.5) WITHIN GROUP (ORDER BY numbers) AS discrete_median
FROM percentile_test;

內容解密：

  1. 首先，建立一個名為 percentile_test 的表格，並插入測試資料。
  2. 然後，使用 percentile_cont(0.5) 和 percentile_disc(0.5) 函式計算中位數。
  3. percentile_cont(0.5) 傳回連續的中位數值（3.5），而 percentile_disc(0.5) 傳回離散的中位數值（3）。

分析人口普查資料

使用美國縣級人口估計資料，可以計算出縣級人口的總和、平均值和中位數。結果顯示，中位數（25726）和平均值（104468）相差很大，這表明平均值可能會誤導人們對資料的理解。

程式碼範例：分析縣級人口資料

-- 分析縣級人口資料
SELECT 
    sum(pop_est_2019) AS county_sum,
    round(avg(pop_est_2019), 0) AS county_average,
    percentile_cont(0.5) WITHIN GROUP (ORDER BY pop_est_2019) AS county_median
FROM us_counties_pop_est_2019;

內容解密：

  1. 使用 sum()、avg() 和 percentile_cont() 函式計算縣級人口的總和、平均值和中位數。
  2. 結果顯示，中位數和平均值相差很大，表明平均值可能不能準確地代表縣級人口的典型值。

將資料分成等份

除了計算中位數外，還可以使用百分位數函式將資料分成等份，例如四分位數（quartile）、五分位數（quintile）和十分位數（decile）。

程式碼範例：計算四分位數

-- 計算四分位數
SELECT 
    percentile_cont(ARRAY[0.25, 0.5, 0.75]) 
    WITHIN GROUP (ORDER BY pop_est_2019) AS quartiles
FROM us_counties_pop_est_2019;

內容解密：

  1. 使用 percentile_cont() 函式計算四分位數。
  2. 將多個百分位數值（0.25、0.5 和 0.75）放入一個陣列中，傳遞給 percentile_cont() 函式。
  3. 結果傳回一個包含四分位數值的陣列。

此圖示呈現了資料分析中使用平均值和中位數的流程：

@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

玄貓 BlackCat

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