题解 | #考试分数中位数(五)#

# 写完有点嫌弃自己，不知道为啥写了这么复杂，合理怀疑是自己水平太菜了
# 冷静了一下，思路该写还得写
# 1、科目内窗口函数分组排序分数，涉及中位数，奇数和偶数算法不同，所有再算一个每个科目的考试记录数
# ，根据考试记录数，标记该科目考试记录数是奇数还是偶数
# 2、考试记录数为奇数的科目：对应的分数排序号是，(考试记录数+1)/2
# 分数排序等于(考试记录数+1)/2时，也就是为1时，则该条记录是该科目中位数
# 3、考试记录数为偶数的科目：对应的分数排序号是，(考试记录数)/2和(考试记录数)/2+1，两条记录
# 分数排序等于(考试记录数)/2或者(考试记录数)/2+1时，也就是为1时，则这两条记录是该科目分数中位数
# 4、限定好条件，奇数和偶数分别计算，然后 union all 即可


with t1 as 
(
select id
      ,job
      ,score
      ,dense_rank() over(partition by job order by score desc) as rank_sc
	  ,count(id) over(partition by job) as job_cnt
	  ,MOD(count(id) over(partition by job),2) as is_mod
from grade
),
qs as 
(
select id
      ,job
      ,score
      ,rank_sc
	  ,job_cnt
	  ,is_mod
	  ,(max(job_cnt) over(partition by job)+1)/2 as is_mid_index
	  ,if(rank_sc=(max(job_cnt) over(partition by job)+1)/2,1,0) as is_mid_data
from t1			
where is_mod=1
),
os as 
(
select id 
      ,job
      ,score
      ,rank_sc
	  ,job_cnt
	  ,is_mod
	  ,if(rank_sc=(max(job_cnt) over(partition by job))/2,1,0) as is_mid_index1
	  ,if(rank_sc=(max(job_cnt) over(partition by job))/2+1,1,0) as is_mid_index2
from t1			
where is_mod=0
)
select id
      ,job
      ,score
      ,rank_sc
from qs
where is_mid_data=1
union all
select id
      ,job
      ,score
      ,rank_sc
from os
where is_mid_index1=1 or is_mid_index2=1
order by id asc