一.項目背景

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

二.實現(xiàn)過程

1.Python實現(xiàn)
》》創(chuàng)建DataFrame
》》分組計算中位數(shù)

import pandas as pd


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

2.SQL實現(xiàn)
》》統(tǒng)計字段長度
》》按照奇偶長度分別計算字段一半
》》按照字段排序統(tǒng)計順序
》》篩選所需字段并計算

建表語句：
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)

插入數(shù)據(jù)：
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

計算中位數(shù)：
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)