一.項(xiàng)目背景

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

二.實(shí)現(xiàn)過程

1.Python實(shí)現(xiàn)
》》創(chuàng)建DataFrame
》》分組計(jì)算中位數(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)計(jì)值
print(data.groupby('company')['salary'].median().reset_index().rename({'salary':'median_salary'},axis=1))

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

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

計(jì)算中位數(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)