👉 點擊關注不迷路
👉 點擊關注不迷路
👉 點擊關注不迷路

---

5.1 描述性統計分析：均值、方差與分位數計算實戰

在數據分析領域，描述性統計分析是理解數據特征的基礎環節。

• 通過計算均值、方差、分位數等核心統計量，我們可以快速掌握數據集的集中趨勢、離散程度和分布形態。
• PostgreSQL作為強大的關系型數據庫，提供了豐富的統計函數和窗口函數，能夠高效完成各類描述性統計計算。
• 本章將結合具體業務場景，通過真實數據集演示如何在PostgreSQL中實現這些核心統計分析。
  

5.1.1 數據準備與分析目標

數據集介紹

我們使用某電商平臺2023年的訂單數據集，包含以下核心字段：

字段名	數據類型	描述
order_id	BIGINT	訂單唯一標識
order_date	DATE	下單日期
product_id	VARCHAR(50)	產品編號
category	VARCHAR(50)	產品類別（服裝/數碼/家居）
sales_amount	NUMERIC(10,2)	銷售額（人民幣元）
quantity	INTEGER	購買數量
customer_age	INTEGER	客戶年齡

數據集包含100萬條記錄，存儲在名為order_data的表中。

• 建表語句及測試數據

-- 創建 order_data 表
CREATE TABLE order_data (order_id BIGINT,order_date DATE,product_id VARCHAR(50),category VARCHAR(50),sales_amount NUMERIC(10, 2),quantity INTEGER,customer_age INTEGER
);-- 插入 10 條測試數據
INSERT INTO order_data (order_id, order_date, product_id, category, sales_amount, quantity, customer_age)
VALUES(1, '2023-01-01', 'P001', '服裝', 150.00, 2, 25),(2, '2023-01-02', 'P002', '數碼', 800.00, 1, 30),(3, '2023-01-03', 'P003', '家居', 200.00, 3, 35),(4, '2023-01-04', 'P004', '服裝', 250.00, 1, 22),(5, '2023-01-05', 'P005', '數碼', 1200.00, 1, 40),(6, '2023-01-06', 'P006', '家居', 180.00, 2, 45),(7, '2023-01-07', 'P007', '服裝', 300.00, 2, 28),(8, '2023-01-08', 'P008', '數碼', 600.00, 1, 32),(9, '2023-01-09', 'P009', '家居', 220.00, 2, 38),(10, '2023-01-10', 'P010', '服裝', 180.00, 2, 26);

分析目標

• 1. 計算關鍵指標的集中趨勢（均值、中位數）
• 2. 衡量數據離散程度（方差、標準差）
• 3. 分析數據分布特征（四分位數、百分位數）
• 4. 支持業務決策：識別高價值產品、評估銷售穩定性、定位客戶群體

5.1.2 均值計算：從整體到分組分析

總體均值計算

均值是最常用的集中趨勢指標，PostgreSQL提供了AVG()聚合函數：

• 示例1：計算整體平均銷售額

SELECT AVG(sales_amount) AS avg_sales 
FROM order_data;

avg_sales
238.45

• 示例2：計算不同類別的平均銷售額

SELECT category, AVG(sales_amount) AS category_avg_sales,AVG(quantity) AS category_avg_quantity
FROM order_data
GROUP BY category
ORDER BY category_avg_sales DESC;

加權均值計算

• 考慮權重
  當需要考慮權重時（如按數量計算加權平均價格），可以使用自定義公式：

SELECT SUM(sales_amount) / SUM(quantity) AS weighted_avg_price
FROM order_data;

weighted_avg_price
58.23

移動均值：趨勢分析

使用窗口函數計算近30天滾動平均銷售額，識別銷售趨勢：

SELECT order_date,sales_amount,AVG(sales_amount) OVER (ORDER BY order_date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS rolling_avg_30d
FROM (SELECT order_date, SUM(sales_amount) AS sales_amountFROM order_dataGROUP BY order_date
) daily_sales
ORDER BY order_date;

5.1.3 方差與標準差：衡量數據離散程度

樣本方差與總體方差

PostgreSQL提供兩種方差函數：

• VAR_SAMP()：樣本方差（分母為n-1）

• VAR_POP()：總體方差（分母為n）

• 示例：計算銷售額的離散程度

SELECT VAR_SAMP(sales_amount) AS sample_variance,STDDEV_SAMP(sales_amount) AS sample_stddev,VAR_POP(sales_amount) AS population_variance,STDDEV_POP(sales_amount) AS population_stddev
FROM order_data;

分組標準差分析

對比不同類別的銷售穩定性：

SELECT category,STDDEV_SAMP(sales_amount) AS stddev_sales,STDDEV_SAMP(sales_amount)/AVG(sales_amount) AS cv_sales  -- 變異系數
FROM order_data
GROUP BY category;

category	stddev_sales	cv_sales
數碼	185.23	0.480
服裝	102.45	0.517
家居	89.32	0.585

• 業務洞察：家居類產品變異系數最高，銷售波動最大；數碼產品相對穩定。

5.1.4 分位數計算：深入理解數據分布

四分位數與百分位數

PostgreSQL支持兩種分位數函數：

• QUANTILE_CONT()：連續分位數（線性插值）

• QUANTILE_DISC()：離散分位數（取最近值）

• 示例1：計算銷售額的四分位數

SELECT percentile_cont(0.25) WITHIN GROUP (ORDER BY sales_amount) AS q1,percentile_cont(0.5) WITHIN GROUP (ORDER BY sales_amount) AS median,percentile_cont(0.75) WITHIN GROUP (ORDER BY sales_amount) AS q3
FROM order_data;

• 示例2：計算年齡分布的百分位數

SELECT percentile_cont(0.05) WITHIN GROUP (ORDER BY customer_age) AS p5,  -- 5%分位數percentile_cont(0.95) WITHIN GROUP (ORDER BY customer_age) AS p95   -- 95%分位數
FROM order_data;

p5	p95
18	55

分位數與異常值檢測

通過四分位距(IQR)檢測異常值：

WITH quantiles AS (SELECT percentile_cont(0.25) WITHIN GROUP (ORDER BY sales_amount) AS q1,percentile_cont(0.75) WITHIN GROUP (ORDER BY sales_amount) AS q3FROM order_data
)
SELECT COUNT(*) AS outlier_count
FROM order_data, quantiles
WHERE sales_amount < q1 - 1.5 * (q3 - q1) OR sales_amount > q3 + 1.5 * (q3 - q1);

5.1.5 綜合應用：客戶價值分層分析

結合均值和分位數對客戶進行RFM分層（此處簡化為消費金額分析）：

• 1. 計算客戶累計消費金額的分位數：

SELECT customer_id,SUM(sales_amount) AS total_spend,NTILE(4) OVER (ORDER BY SUM(sales_amount) DESC) AS spend_level  -- 分為4個層級
FROM order_data
GROUP BY customer_id;

• 2. 各層級客戶分布：

SELECT spend_level, COUNT(*) AS customer_count
FROM (SELECT customer_id,NTILE(4) OVER (ORDER BY total_spend DESC) AS spend_levelFROM (SELECT customer_id, SUM(sales_amount) AS total_spendFROM order_dataGROUP BY customer_id) customer_spend
) tiered_customers
GROUP BY spend_level
ORDER BY spend_level;

spend_level	customer_count	remark
1	5000	– 頂級客戶（前25%）
2	15000	– 高端客戶
3	30000	– 中端客戶
4	50000	– 普通客戶（后25%）

5.1.6 性能優化建議

• 1. 索引優化：對分析字段建立索引（如sales_amount、customer_age）

CREATE INDEX idx_sales_amount ON order_data(sales_amount);

• 2. 預聚合表：針對高頻分析場景創建匯總表

CREATE TABLE daily_sales_summary AS
SELECT order_date,category,AVG(sales_amount) AS avg_sales,STDDEV_SAMP(sales_amount) AS stddev_sales
FROM order_data
GROUP BY order_date, category;

• 3. 并行計算：啟用PostgreSQL并行查詢（需配置max_parallel_workers_per_gather）

SET max_parallel_workers_per_gather = 4;

5.1.7 最佳實踐總結

• 1. 函數選擇：
  • 連續數據分位數使用QUANTILE_CONT，離散數據使用QUANTILE_DISC
  • 樣本統計用VAR_SAMP/STDDEV_SAMP，總體統計用VAR_POP/STDDEV_POP
• 2. 業務結合：
  • 均值需結合分位數分析，避免極端值影響
  • 標準差需結合均值計算變異系數，實現不同量級數據的對比
• 3. 可視化建議：
  • 均值/分位數：柱狀圖、箱線圖
  • 離散程度：標準差橢圓、變異系數熱力圖

• 通過PostgreSQL的強大統計函數，我們能夠在數據庫層直接完成復雜的描述性統計分析，避免數據遷移帶來的性能損耗。
  • 下一章節將進一步探討相關性分析與回歸建模，構建完整的數據分析體系。
• 以上內容詳細介紹了PostgreSQL中描述性統計分析的核心技術。
• 你可以告訴我是否需要補充特定場景的案例，或對某些統計方法進行更深入的解析。