题解 | #最差是第几名(二)#

两种方法：

• 利用lag()over()分奇偶两种情况进行区间卡位
• 中位数——正序/逆序均须不小于总个数的一半

  lag()over()

  由于窗口函数不可嵌套，需用
• sum() over()得t_rank组成q1
• lag() over()得t_rank1组成q2
• 分奇偶两种情况卡区间，筛选出对应的grade

  with q1 as
  (select grade,number,
  sum(number) over (order by grade) as t_rank,
  sum(number) over() as cnt
  from class_grade
  order by grade),
  q2 as 
  (select grade,t_rank,cnt,
  ifnull(lag(t_rank,1) over (order by grade),0) as t_rank1
  from q1
  )
  select grade
  from q2
  where (floor((cnt+1)/2)<=t_rank and floor((cnt+1)/2)>t_rank1)
  or (floor((cnt+2)/2)<=t_rank and floor((cnt+2)/2)>t_rank1)
  order by grade

  正序+逆序（此为题解区精华方法）

  select grade  
  from
  (select grade,sum(number) over (order by grade) as t_rank,
  sum(number) over (order by grade desc) as t_rank1,
  sum(number) over() as cnt
  from class_grade) as q
  where t_rank>=cnt/2 and t_rank1>=cnt/2
  order by grade