資料分析師常使用 SPSS、SAS、R 或 Python 等工具進行進階統計分析，但 SQL 資料函式庫本身也具備強大的統計分析能力。標準 ANSI SQL，以及 PostgreSQL 的實作，提供豐富的統計函式，無需將資料匯出即可進行深入分析。本文將以美國人口普查局的縣級資料為例，示範如何使用 SQL 統計函式進行相關性分析、線性迴歸、變異數與標準差計算，以及排名分析。
透過 corr() 函式計算學士學位人口比例與家庭收入中位數的相關係數，可以衡量兩者之間的線性關係強度和方向。搭配 regr_slope() 和 regr_intercept() 函式，可以建立線性迴歸模型，預測家庭收入中位數。regr_r2() 函式則能計算判定係數，評估自變數對因變數變異的解釋程度。此外，var_pop()、var_samp()、stddev_pop() 和 stddev_samp() 函式可用於計算變異數和標準差，衡量資料的離散程度。最後，rank() 和 dense_rank() 等視窗函式則能進行排名分析，提供資料排序和比較的功能。
統計函式在SQL中的應用
在本章中，我們將探討SQL中的統計函式及其使用。當需要進行的分析超出簡單的求和與平均值計算時，資料分析師通常不會首選SQL資料函式庫。相反，他們傾向於使用功能全面的統計軟體包，如SPSS或SAS，程式語言如R或Python，甚至Excel等工具。然而，我們不應忽視資料函式庫的潛力。標準的ANSI SQL，包括PostgreSQL的實作，提供強大的統計函式和功能，能夠在無需將資料匯出至其他程式的情況下，揭示資料的多個層面。
建立人口普查統計表
讓我們再次利用我最喜歡的資料來源之一——美國人口普查局。這次，我們將使用2014–2018年美國社群調查（ACS）5年估計中的縣級資料，這是該局提供的另一項產品。使用清單11-1中的程式碼建立acs_2014_2018_stats表，並匯入CSV檔案acs_2014_2018_stats.csv。程式碼和資料可透過https://nostarch.com/practical-sql-2nd-edition/取得，記得將C:\YourDirectory\更改為CSV檔案的位置。
CREATE TABLE acs_2014_2018_stats (
geoid text CONSTRAINT geoid_key PRIMARY KEY,
county text NOT NULL,
st text NOT NULL,
pct_travel_60_min numeric(5,2),
pct_bachelors_higher numeric(5,2),
pct_masters_higher numeric(5,2),
median_hh_income integer,
CHECK (pct_masters_higher <= pct_bachelors_higher)
);
COPY acs_2014_2018_stats
FROM 'C:\YourDirectory\acs_2014_2018_stats.csv'
WITH (FORMAT CSV, HEADER);
SELECT * FROM acs_2014_2018_stats;
內容解密：
CREATE TABLE陳述式用於建立名為acs_2014_2018_stats的新表，其中包含七個欄位。geoid欄位作為主鍵，用於唯一標識每個縣的資料。county和st欄位分別儲存縣名和州名，且均設有NOT NULL約束，確保每個記錄都包含這兩個值。- 後四個欄位儲存了從ACS估計中衍生出的百分比和其他經濟指標，包括通勤時間、學歷和家庭收入中位數。
CHECK約束用於確保碩士學位及以上的人口百分比不大於學士學位及以上的人口百分比，這是因為在美國，學士學位通常在碩士學位之前獲得。COPY陳述式用於從CSV檔案中匯入資料到新建立的表中。- 最後的
SELECT陳述式用於檢視所有匯入的3142行資料，每行對應一個縣的調查結果。
使用corr(Y, X)衡量相關性
相關性描述了兩個變數之間的統計關係，衡量一個變數的變化與另一個變數的變化之間的關聯程度。在本文中，我們將使用SQL的corr(Y, X)函式來衡量一個縣中擁有學士學位的人口百分比與該縣家庭收入中位數之間的關係。如果存在相關性，我們還將確定教育的程度是否通常與更高的收入相對應，以及這種關係的強度。
首先，一些背景知識。皮爾森相關係數（通常表示為r）衡量兩個變數之間線性關係的強度和方向。具有強線性關係的變數在散點圖上會聚集在一條線附近。r的值介於-1和1之間；範圍的任一端表示完全相關，而接近零的值表示隨機分佈，相關性很小。正的r值表示直接關係：隨著一個變數的增加，另一個變數也會增加。當繪製在圖上時，直接關係中的每對值的資料點將從左到右向上傾斜。負的r值表示反比關係：隨著一個變數的增加，另一個變數會減少。
SELECT corr(pct_bachelors_higher, median_hh_income)
FROM acs_2014_2018_stats;
內容解密：
corr(pct_bachelors_higher, median_hh_income)函式計算pct_bachelors_higher和median_hh_income之間的相關係數。- 這個查詢傳回一個單一的值，表示這兩個變數之間的相關程度。
分析結果
透過執行上述查詢，我們可以得到相關係數的值。如果這個值接近1或-1，表示兩個變數之間存在強烈的線性關係；如果接近0，則表示兩者之間沒有明顯的線性關係。根據資料分析結果，我們可以進一步探討教育的程度與家庭收入之間的關係，以及這種關係對於理解社會經濟趨勢有何意義。
相關係數的解釋與應用
在統計分析中，相關係數（Correlation Coefficient）是用於衡量兩個變數之間線性關係強度的指標。相關係數的值域在 -1 至 1 之間，其中 1 表示完全正相關，-1 表示完全負相關，而 0 則表示無相關性。
相關係數的解讀
根據表 11-1 的指引，我們可以對相關係數進行如下解讀：
- 當相關係數為 0 時，表示兩個變數之間沒有線性關係。
- 當相關係數在 0.01 至 0.29 之間時，表示兩個變數之間的關係較弱。
- 當相關係數在 0.3 至 0.59 之間時，表示兩個變數之間存在中等程度的相關性。
- 當相關係數在 0.6 至 0.99 之間時，表示兩個變數之間存在強烈的正相關或負相關。
- 當相關係數為 1 或 -1 時，表示兩個變數之間存在完全的正相關或負相關。
使用 SQL 計算相關係數
在標準的 ANSI SQL 和 PostgreSQL 中，我們可以使用 corr(Y, X) 函式來計算皮爾森相關係數（Pearson Correlation Coefficient）。這個函式接受兩個輸入引數，分別是依賴變數 Y 和獨立變數 X。
程式碼範例
SELECT corr(median_hh_income, pct_bachelors_higher)
AS bachelors_income_r
FROM acs_2014_2018_stats;
內容解密：
corr(median_hh_income, pct_bachelors_higher)：此函式計算median_hh_income和pct_bachelors_higher之間的相關係數。AS bachelors_income_r：將計算出的相關係數別名為bachelors_income_r，以便於識別結果欄位。FROM acs_2014_2018_stats：指定資料來源為acs_2014_2018_stats表。
執行上述查詢後，我們得到了一個約為 0.70 的正相關係數，表明縣級教育程度與家庭收入之間存在較強的正相關關係。
視覺化相關性
透過將變數繪製在散點圖上，可以直觀地觀察到教育程度與收入之間的正相關趨勢，如圖 11-1 所示。儘管大多數資料點聚集在左下角，但整體上呈現出從左下到右上的趨勢。
檢查其他變數對之間的相關性
我們進一步計算了其他變數對之間的相關係數，如下所示：
SELECT
round(corr(median_hh_income, pct_bachelors_higher)::numeric, 2) AS bachelors_income_r,
round(corr(pct_travel_60_min, median_hh_income)::numeric, 2) AS income_travel_r,
round(corr(pct_travel_60_min, pct_bachelors_higher)::numeric, 2) AS bachelors_travel_r
FROM acs_2014_2018_stats;
內容解密：
round(corr(...), 2)：將相關係數計算結果四捨五入到小數點後兩位，以提高可讀性。::numeric：將corr函式傳回的浮點數值轉換為數字型別，以支援四捨五入操作。
結果顯示，教育程度與收入之間的相關係數約為 0.70，而收入與通勤時間之間的相關係數接近於 0，教育程度與通勤時間之間的相關係數則為 -0.14，表明教育程度越高，通勤時間越長的比例越低，但這個關係較弱。
線性迴歸分析
線性迴歸是一種用於預測依賴變數值的統計方法，透過建立獨立變數與依賴變數之間的線性方程來進行預測。在 SQL 中，我們可以使用相應的函式來執行線性迴歸分析，以回答諸如「給定某縣的教育程度，如何預測其家庭收入？」之類別的問題。
圖 11-2 在散點圖上疊加了一條迴歸線，直觀地展示了線性迴歸的結果。迴歸線代表了根據資料趨勢預測的值，可以用來估計未知資料點的值。
統計函式在SQL中的應用：迴歸分析與變異數計算
在資料分析中，統計函式扮演著至關重要的角色，特別是在瞭解變數之間的關係時。本文將探討如何使用SQL進行迴歸分析以及計算變異數和標準差。
最小平方法迴歸線
在分析兩個變數之間的關係時，我們經常使用散佈圖來視覺化資料。Figure 11-2展示了一個典型的散佈圖，其中橫軸代表擁有學士學位或以上的百分比，縱軸代表縣的中位家庭收入。圖中的直線是最小平方法迴歸線，它代表了資料的最佳擬合線。
迴歸線方程式
迴歸線的方程式為 $Y = bX + a$，其中：
- $Y$ 是預測值，即縱軸上的值（因變數）。
- $b$ 是斜率，可以是正值或負值。它衡量當 $X$ 增加一個單位時，$Y$ 的變化量。
- $X$ 是橫軸上的值（自變數）。
- $a$ 是 $Y$ 截距，即當 $X = 0$ 時，迴歸線與 $Y$ 軸的交點。
使用SQL計算斜率和截距
要計算 $b$ 和 $a$，我們可以使用SQL中的 regr_slope(Y, X) 和 regr_intercept(Y, X) 函式，如 Listing 11-4 所示。
SELECT
round(regr_slope(median_hh_income, pct_bachelors_higher)::numeric, 2) AS slope,
round(regr_intercept(median_hh_income, pct_bachelors_higher)::numeric, 2) AS y_intercept
FROM acs_2014_2018_stats;
內容解密：
regr_slope(median_hh_income, pct_bachelors_higher)：計算迴歸線的斜率，代表當pct_bachelors_higher每增加一個單位時，median_hh_income的預期變化量。regr_intercept(median_hh_income, pct_bachelors_higher)：計算迴歸線的 $Y$ 截距，代表當pct_bachelors_higher為 0 時的median_hh_income預期值。round(...::numeric, 2)：將結果四捨五入到小數點後兩位，以提高可讀性。
查詢結果顯示，斜率為 1016.55，$Y$ 截距為 29651.42。根據這些值，我們可以預測，當某縣擁有學士學位或以上的人口比例為 30% 時，其中位家庭收入預期為約 $60,148。
瞭解自變數的影響：r平方
除了確定兩個變數之間的關係方向和強度外，我們還可以計算自變數對因變數變異的解釋程度。這可以透過將相關係數 $r$ 平方來實作，得到判定係數，即 r平方。r平方的值介於 0 和 1 之間，代表自變數能夠解釋因變數變異的百分比。
使用SQL計算r平方
我們可以使用 regr_r2(Y, X) 函式來計算 r平方，如 Listing 11-5 所示。
SELECT round(regr_r2(median_hh_income, pct_bachelors_higher)::numeric, 3) AS r_squared
FROM acs_2014_2018_stats;
內容解密：
regr_r2(median_hh_income, pct_bachelors_higher)：計算 r平方，代表pct_bachelors_higher能夠解釋median_hh_income變異的百分比。round(...::numeric, 3)：將結果四捨五入到小數點後三位，以提供更精確的解釋。
查詢結果顯示，r平方為 0.490，意味著約 49% 的縣中位家庭收入變異可以被擁有學士學位或以上的人口比例所解釋。
變異數與標準差：衡量資料的離散程度
變異數（Variance）和標準差（Standard Deviation）是用於描述一組資料的離散程度的重要統計指標。變異數是每個資料點與平均值之間距離的平方的平均值，用於衡量資料的分散程度。標準差則是變異數的平方根，在評估資料分佈是否符合常態分佈時特別有用。
變異數與標準差的意義
在金融領域，變異數常用於衡量某支股票的波動性，即其每日收盤價與平均值的偏離程度。這可以幫助投資者評估該股票的風險。標準差則用於衡量資料點與平均值的接近程度。在常態分佈中，約有三分之二的資料點落在平均值的一個標準差範圍內，95%的資料點落在兩個標準差範圍內。
例如，一項研究發現，美國成年女性的平均身高約為65.5英寸，標準差為2.5英寸。假設身高資料符合常態分佈，這意味著約三分之二的女性身高在63英寸至68英寸之間。
計算變異數與標準差
在計算變異數和標準差時，需要注意它們的單位不同。標準差的單位與原始資料相同，而變異數的單位則是原始單位的平方。
SQL提供了多種函式來計算變異數和標準差：
var_pop(numeric)：計算總體變異數，用於包含所有可能值的資料集。var_samp(numeric)：計算樣本變異數，用於從總體中抽取的樣本資料。stddev_pop(numeric)：計算總體標準差。stddev_samp(numeric)：計算樣本標準差。
使用SQL進行排名分析
排名分析在新聞報導和資料分析中非常常見。使用SQL，可以透過rank()和dense_rank()等視窗函式來建立排名。
使用rank()和dense_rank()
rank()和dense_rank()都是用於對資料進行排名的視窗函式。兩者的主要區別在於處理並列排名的方式：rank()會在並列排名後產生間隔，而dense_rank()則不會。
CREATE TABLE widget_companies (
id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
company text NOT NULL,
widget_output integer NOT NULL
);
INSERT INTO widget_companies (company, widget_output)
VALUES
('Dom Widgets', 125000),
('Ariadne Widget Masters', 143000),
('Saito Widget Co.', 201000),
('Mal Inc.', 133000),
('Dream Widget Inc.', 196000),
('Miles Amalgamated', 620000),
('Arthur Industries', 244000),
('Fischer Worldwide', 201000);
SELECT
company,
widget_output,
rank() OVER (ORDER BY widget_output DESC),
dense_rank() OVER (ORDER BY widget_output DESC)
FROM widget_companies
ORDER BY widget_output DESC;
結果分析
執行上述查詢後，結果如下：
| company | widget_output | rank | dense_rank | |
-|
|
|
-| | Miles Amalgamated | 620000 | 1 | 1 | | Arthur Industries | 244000 | 2 | 2 | | Fischer Worldwide | 201000 | 3 | 3 | | Saito Widget Co. | 201000 | 3 | 3 | | Dream Widget Inc. | 196000 | 5 | 4 | | Ariadne Widget Masters | 143000 | 6 | 5 | | Mal Inc. | 133000 | 7 | 6 | | Dom Widgets | 125000 | 8 | 7 |
從結果可以看出，rank()和dense_rank()在處理並列排名時的差異。rank()會跳過並列後的下一個排名，而dense_rank()則會連續排名。
#### 內容解密：
rank()函式：用於對資料進行排名，當遇到相同的值時，會給予相同的排名，並且在下一個不同的值出現時，排名會跳過已經被佔用的位置。例如，在上述結果中，「Fischer Worldwide」和「Saito Widget Co.」並列第三名，下一個不同的值「Dream Widget Inc.」就被排名第五。dense_rank()函式：同樣用於對資料進行排名，但當遇到相同的值時，會給予相同的排名，並且下一個不同的值會緊接著前面的排名繼續。例如，在上述結果中，「Fischer Worldwide」和「Saito Widget Co.」並列第三名，下一個不同的值「Dream Widget Inc.」就被排名第四。OVER (ORDER BY widget_output DESC)子句：定義了視窗函式操作的視窗，即按照widget_output降序排列所有行。- 查詢結果：顯示了每個公司的
widget_output、使用rank()和dense_rank()得到的排名。