中位數是指一個系列的數目中裡的中間值。如果總共的資料筆數是奇數,而我們總共有n個數字,那中位數就是排名第(n+1)/2的數字(在這裡排名第(n+1)/2高和排名第n+1)/2低是同一個數字)。舉例來說,如果我們總共有7個數字,那中位數就是第四個最大(或第四個最小)的數字。

如果總共的資料筆數是偶數,那中間值就不是某一個數字,而是最接近中間兩個數字的平均值。如果我們有n個數字,那中位數就是第n/2大和第 (n+2)/2大這兩個數字的平均。我們也可以想說這是第n/2小和第 (n+2)/2小這兩個數字的平均。舉例來說,如果我們總共有8個數字,那中位數就是第四大個第五大數的平均。

根據以上的定義,我們計算中位數的方式如下:

  1. 將所有數字依順序排列。從大往小或從小往大排都可以,因為最後的答案都是一樣。
  2. 依照以上的規則,用 CASE 來決定我們要取哪兩個數字的平均值。當總共的資料筆數是奇數時,這用來算平均值的兩個數字其實是同一個數字。
  3. 計算出這兩個數字的平均值。這就是我們要的中位值。

這不是一個簡單的計算。我們需要運用到好幾個SQL的技巧:

  • 要分辨奇數和偶數的不同作法,我們需用到 CASE 指令
  • 要找出每個數字的排名,我們需要用到 窗口函數
  • 要經過好幾個步驟並且把每個步驟算出的值暫時儲存起來,我們需要用到 CTE 的作法。

例子一

假設我們有以下的表格:

Total_Sales 表格
 Name   Sales 
 John   10 
 Jennifer   15 
 Stella   20 
 Sophia   40 
 Greg   50 
 Jeff   22 

要找出中位數,我們就鍵入:

With Count_Total as
(SELECT COUNT(*) row_count from Total_Sales)
,

From_To as
(SELECT
CASE WHEN row_count % 2 = 1 THEN (row_count+1)/2 ELSE row_count/2 END as begin_count,
CASE WHEN row_count % 2 = 1 THEN (row_count+1)/2 ELSE (row_count+2)/2 END as end_count
From Count_Total)

SELECT AVG(Sales) Median from
(SELECT Name, Sales, ROW_NUMBER() OVER (ORDER BY Sales ASC) RK
FROM Total_Sales)
WHERE RK BETWEEN (SELECT begin_count FROM From_To) and (SELECT end_count FROM From_To);

結果:

Median
21

最後的結果,21,是第三大數字(22)和第四大數字(20)的平均值。

SQL 解釋

第一個 CTE, Count_Total, 算出表格內總共有多少筆資料(總共六筆)。

第二個 CTE, From_To, 算出我們要取哪兩個數的平均值。(第三大值和第四大值)

最終的 SQL 用窗口函數和ROW_NUMBER()來算出每一筆資料由小到大的排名。由於我們在平手的狀況下,我們不要有相同的排名,所以這裡我們用ROW_NUMBER()WHERE 子句中用了兩個內嵌視觀表 (inline view)來找出要用哪兩個數字的平均。

例子二

我們加一筆資料到以上的表格:

Table Total_Sales
 Name  Sales 
 John  10 
 Jennifer  15 
 Stella  20 
 Sophia  40 
 Greg  50 
 Jeff  22 
 Angel  60 

現在同樣的 SQL 會產生以下的結果:

Median
22

在這裡,我們要算出第四大數字和第四大數字的平均,所以結果就是第四大數字本身,22。

下一頁:SQL 累積總計

本頁最近於 2022年6月25日更新



Copyright © 2022   1keydata.com   版權所有