SQL-中位数

问题

Employee表，根据表格找出每个公司的工资的中位数。id 是该表的主键列(具有唯一值的列)。

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| id           | int     |
| company      | varchar |
| salary       | int     |
+--------------+---------+

思路

首先，要获取中位数就必须知道每个公司有多少员工，并且工资要按照顺序排好。

select id, company, salary, row_number() over (partition by company order by salary) as rank, count(1) over (partition by company) as total
from employee;

输出

 id | company | salary | rank | total
----+---------+--------+------+-------
  3 | A       |     15 |    1 |     6
  2 | A       |    341 |    2 |     6
  5 | A       |    451 |    3 |     6
  6 | A       |    513 |    4 |     6
  1 | A       |   2341 |    5 |     6
  4 | A       |  15314 |    6 |     6
  8 | B       |     13 |    1 |     6
  7 | B       |     15 |    2 |     6
 12 | B       |    234 |    3 |     6
  9 | B       |   1154 |    4 |     6
 11 | B       |   1221 |    5 |     6
 10 | B       |   1345 |    6 |     6
 17 | C       |     65 |    1 |     5
 13 | C       |   2345 |    2 |     5
 14 | C       |   2645 |    3 |     5
 15 | C       |   2645 |    4 |     5
 16 | C       |   2652 |    5 |     5

这样，我们就得到了每个公司的总数以及每个工资的排序。我们现在的任务是找到中位数。如果总数是偶数，那就获取中间两个值，否则就获取中间的一个值。我们可以把不是中间的值的salary设置成负数或者0，再过滤掉

select id, company, salary
from (
  select id, company,
  case
    when mod(total,2) = 1 and rank = total / 2 + 1 then salary
    when mod(total,2) = 0 and rank = total / 2 then salary
    when mod(total,2) = 0 and rank = total / 2 + 1 then salary
    else -1 end as salary
  from (  
    select id, company, salary, row_number() over (partition by company order by salary) as rank, count(1) over (partition by company) as total
    from employee) as ranked_salary
) as filtered_salary
where salary >=0;

输出

 id | company | salary
----+---------+--------
  5 | A       |    451
  6 | A       |    513
 12 | B       |    234
  9 | B       |   1154
 14 | C       |   2645

至此，我们完成了整个题目

问题

思路

评论