一.項目背景

中位數是數理統計中一個重要的指標，它可以自動忽略數據極差帶來的影響，
能夠很好的評估數據，在數理統計中很常用。本文主要介紹在Python中和Mysql
中如何來求中位數,重點讓大家掌握SQL計算中位數，也是面試?？碱}目之一。

二.實現過程

1.Python實現
》》創建DataFrame
》》分組計算中位數

import pandas as pd


#創建DataFrame
data=pd.DataFrame(
    {   'company':['A','A','A','A','B','B','B','B','B'],
        'salary':[1057,1874,2059,2268,6587,6637,6932,7415,7654]
    })
#輸出分組統計值
print(data.groupby('company')['salary'].median().reset_index().rename({'salary':'median_salary'},axis=1))

2.SQL實現
》》統計字段長度
》》按照奇偶長度分別計算字段一半
》》按照字段排序統計順序
》》篩選所需字段并計算

建表語句：
mysql> create table median_val(
    -> company varchar(20),
    -> salary int)
    -> engine=innodb default charset=utf8;
Query OK, 0 rows affected, 1 warning (0.07 sec)

插入數據：
mysql> insert into median_val(company,salary) values("A",1057),("A",1874),("A",2059),("A",2268),("B",7415),("B",7654),("B",6932),("B",6587),("B",6637);
Query OK, 9 rows affected (0.06 sec)
Records: 9  Duplicates: 0  Warnings: 0

計算中位數：
mysql> select
    -> company,
    -> round(avg(salary),1) as median_salary
    -> from
    -> (select
    -> company,
    -> salary,
    -> count(*) over(partition by company) as num_length,
    -> row_number() over(partition by company order by salary) as ranking,
    -> count(*) over(partition by company) /2 as num_company_even,
    -> ceil(count(*) over(partition by company) /2) as num_company_odd
    -> from
    -> median_val)a
    -> where
    -> (mod(num_length,2)=0 and ranking in (num_length/2,num_length/2+1))
    -> or
    -> (mod(num_length,2)=1 and ranking=ceil(num_length/2))
    -> group by
    -> company;
+---------+---------------+
| company | median_salary |
+---------+---------------+
| A       |        1966.5 |
| B       |        6932.0 |
+---------+---------------+
2 rows in set (0.00 sec)