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

with t_range as(
    select
        if(sum(number) & 1, (sum(number) + 1) / 2, sum(number) / 2) a
        , if(sum(number) & 1, (sum(number) + 1) / 2, sum(number) / 2 + 1) b
    from class_grade
), t_rnk as(
    select
        grade, number
        , sum(number) over w - number + 1 ra
        , sum(number) over w rb
    from class_grade
    window w as(order by grade)
)
select distinct grade
from t_rnk, t_range
where ra <= b and rb >= a

将中位数问题转化为区间重叠问题：

1. 统计出中位数的区间a, b；
2. 统计出排名的区间rA, rB；
3. 根据[a, b] 与[rA, rB] 之间的关系输出结果.

区间重叠的关系：

1. [a, b] 包含在[rA, rB] 中：rA <= a <= b <= rB；
2. rA 介于[a, b] 之间，rB 在之外：a <= rA <= b <= rB；
3. rB 介于[a, b] 之间，rA 在之外：rA <= a <= rB <= b.

可知，三项都出现了rA 且它们都满足rA <= b，所以可提取公共特征：rA <= b. 同理，可得rb >= a.

故最终化简的条件为：ra <= b 且 rb >= a.

Pandas 解法

total = df.number.sum()
a, b = (total + 1) // 2, (total + 2) // 2
# a, b = ((total + 1) // 2, (total + 1) // 2) if total & 1 else (total // 2, total // 2 + 1)

(
    df
    .assign(rb = lambda x: x.number.cumsum())
    .assign(ra = lambda x: x.rb - x.number + 1)
    .query('ra <= @b and rb >= @a')
    
    [['grade']]
    .sort_values('grade')
)