MySQL 取得中位數

發表於 更新於 所需閱讀時間 ≈ 2 分鐘

MySQL 如何取得中位數

中位數定義

  • 中位數簡稱中數，是一種集中量數，可以用來代表一群分數的集中情形。中位數通常以數學符號Md來表示。
  • 數列大小排列後，依序最中間的數字
  • 如果數列數量為奇數，中位數為 第(N+1)/2個分數
  • 如果數列數量為偶數，中位數為 第N/2 & (N/2)+1 的平均分數

參考網站: http://terms.naer.edu.tw/detail/1302280/

設定假資料

id name money
1 A001 100
2 C001 420
3 B021 280
4 Y158 690
5 A202 195
6 D022 540

目標

  • 將列表進行排序，將每一行的排名列出來
  • 找到列表的中位數的位置
    • 總共6筆就是 第3筆與第4筆加總除與2
    • 總共7筆資料就是 第4筆資料
  • 找到第 N 筆的數值為多少

使用參數化

  • 將列表進行排序，將每一行的排名列出來
    1
    2
    3
    4
    5
    SET @num := 0;

    SELECT t1.name, t1.money, @num:=@num+1 AS sort, @totalnum:=@totalnum
    FROM median AS t1
    ORDER BY money
    會先排序後將數值填上

  • 不需考慮總數是奇數還是偶數，相加除以二就是中位數

    1
    2
    3
    4
    5
    6
    7
    8
    SET @num := 0;
    SELECT AVG(t2.money) FROM
    (
        SELECT t1.name, t1.money, @num:=@num+1 AS sort, @totalnum:=@totalnum AS totalnum
        FROM median AS t1
        ORDER BY money
    ) AS t2
    WHERE t2.sort IN ( CEIL((totalnum+1)/2), FLOOR((totalnum+1)/2) )

  • 取得中位數值

    • 如果總筆數為7 取得的排序為第4與第4
      • (7+1)/2 無條件進位=> 4
      • (7+1)/2 無條件捨去=> 4
    • 如果總筆數為6 取得的排序為第3與第4
      • (6+1)/2 無條件進位=> 3
      • (6+1)/2 無條件捨去=> 4

不使用參數化

  • 將列表進行排序，將每一行的排名列出來

    1
    2
    3
    4
    SELECT t1.name, t1.money, count(1) AS sort
    FROM median AS t1, median AS t2
    WHERE t1.money > t2.money
    GROUP BY t1.name, t1.money

  • 取得中位數值

    • 如果總筆數為7 取得的排序為第4與第4
      • (7+1)/2 無條件進位=> 4
      • (7+1)/2 無條件捨去=> 4
    • 如果總筆數為6 取得的排序為第3與第4
      • (6+1)/2 無條件進位=> 3
      • (6+1)/2 無條件捨去=> 4
        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        SELECT * FROM
        (
            SELECT t1.name, t1.money, count(1) AS sort
            FROM median AS t1, median AS t2
            WHERE t1.money > t2.money
            GROUP BY t1.name, t1.money
        ) AS t3
        WHERE 
            t3.sort = (SELECT FLOOR((count(1)+1)/2) FROM median)
         OR t3.sort = (SELECT CEIL((count(1)+1)/2) FROM median)

  • 不需考慮總數是奇數還是偶數，相加除以二就是中位數

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SELECT avg(t3.money) AS median FROM
    (
        SELECT t1.name, t1.money, count(1) AS sort
        FROM median AS t1, median AS t2
        WHERE t1.money > t2.money 
            OR (t1.money = t2.money AND t1.name <= t2.name) 
        GROUP BY t1.name, t1.money
    ) AS t3
    WHERE 
        t3.sort = (SELECT FLOOR((count(1)+1)/2) FROM median)
     OR t3.sort = (SELECT CEIL((count(1)+1)/2) FROM median)

遇到的錯誤紀錄

  • 1248 - Every derived table must have its own alias
    • 指說每一個派生出來的 Table 都需要命名