前言

假设我们有一张学生成绩表student_score，里面有三个字段：学生id：student_id,班级id：class_id,成绩：score，主键为student_id。现在让你求出每个班级学生成绩的中位数。

虽然hive里有内置的percentile()和percentile_approx()函数直接求解分位数，但在面试中，面试官老爷大概率不会让你直接就这么写，而是在你自信满满刷刷刷写完之后告诉你“哦，我们不能用内置函数”。

两种解法

谈到中位数，自然而然容易想到要先排序，然后根据个数的奇偶，如果是奇数个就取中间一位，如果是偶数就取中间两位的平均。关于排序，我们可以使用窗口函数row_number()¹，关于奇偶，我们不妨看看奇偶个数有没有共性。

如果总共有n个数，当n=5时，n/2=2.5，我们想要的输出是第3位；当n=6时，n/2=3，我们想要的输出是第3位和第4位的平均，那么可以发现：无论奇偶，中位数必然是在n/2和n/2+1之间的数(包含两端)的平均。

由此可以产生第一种解法

解法1:利用中位数的位次特征

先取出每个班级成绩排序以及总数，形成表t，再限制中位数是在n/2和n/2+1之间的数(包含两端)的平均²。

select 
  class_id
  ,avg(score) as score_median
from
   (
    select 
          class_id
          ,score
          ,row_number() over (partition by class_id order by score asc) as score_rank
          ,count(student_id) over(partition by class_id) as student_num
    from student_score
    )t
where 
  score_rank between student_num/2 and student_num/2+1
group by 
  class_id

解法2:利用升序与降序的差值

观察如下升序编号和降序编号可以发现，当n为奇数时，中位数对应的升序编号和降序编号的差值为0，当n为偶数时，中位数对应的升序编号和降序编号的差值为1或-1³

奇数情况

在这里插入图片描述

偶数情况

在这里插入图片描述
那么通过限定升序编号和降序编号的差值为1，-1或者0，我们可以有如下写法：

select 
  class_id
  ,avg(score) as score_median
from
   (
    select 
          class_id
          ,score
          ,row_number() over (partition by class_id order by score asc) as score_rank_asc
          ,row_number() over (partition by class_id order by score desc) as score_rank_desc
    from student_score
    )t
where 
  (score_rank_asc- score_rank_desc) in (1,-1,0)
group by 
  class_id

但是解法2有一个问题，我们知道row_number处理相同值的时候会随机给一个rank，所以对于不同student_id的相同分数，可能会产生不同的rank，具体来说：
在这里插入图片描述
这时候奇数情况也会存在升序编号和降序编号的差值为1或者-1，就会造成错误的输出。此时可以通过限定主键的方式来使得row_number对于相同分数的不同学生，降序排和升序排的名次在逻辑上是相同的(即保证五个人升序排我是第三名，降序排我也是第三名)。

解法2.1:

select 
  class_id
  ,avg(score) as score_median
from
   (
    select 
          class_id
          ,score
          ,row_number() over (partition by class_id order by score asc student_id asc) as score_rank_asc
          ,row_number() over (partition by class_id order by score desc student_id desc) as score_rank_desc
    from student_score
    )t
where 
  (score_rank_asc- score_rank_desc) in (1,-1,0)
group by 
  class_id

延伸问题：频次+分数

假设现在我们没有每个人的成绩了，只有每个班级的成绩及频次，即问题转换为我：学生成绩表student_score，里面有三个字段：班级id：class_id,成绩：score，频次：frequency。现在让你求出每个班级学生成绩的中位数。

这时候仍然可以考虑升序和降序的频数累积和，两个数都需要大于等于总数一半，即为中位数⁴。

由此有如下写法：

select
    class_id 
    ,avg(score) as score_median
from
(
    select 
        class_id
        ,score
        ,sum(frequency) over(partition by class_id order by score asc) as total_asc
        ,sum(frequency) over(order by scoredesc) as total_desc
        ,sum(frequency) over(partition by class_id ) as total_num
    from 
      student_score
)t
where 
  total_asc>=total_num/2
and 
  total_desc>=total_num/2
group by 
  class_id