题目描述

我们有一个表：

  • class_grade：包含班级成绩信息，包括成绩档位 grade 和该档位的学生人数 number。

目标是查询出学生成绩的中位数档位。如果只有一个中位数，输出一个档位；如果有两个中位数，按档位升序输出。

知识点

  • 窗口函数：使用SUM窗口函数计算每个成绩档位的累积人数。
  • 子查询：使用子查询计算总人数。
  • 条件判断：使用WHERE子句判断中位数所在的档位。
  • 排序：使用ORDER BY子句按成绩档位升序排列结果。

关键问题分析

1. 计算每个成绩档位的累积人数

我们使用SUM窗口函数计算每个成绩档位的累积人数，分别按升序和降序排列：

sum(number) over(order by grade asc) as ascnum,
sum(number) over(order by grade desc) as descnum
  • SUM(number) OVER (ORDER BY grade ASC) AS ascnum: 计算每个成绩档位的累积人数，按成绩档位升序排列。
  • SUM(number) OVER (ORDER BY grade DESC) AS descnum: 计算每个成绩档位的累积人数，按成绩档位降序排列。

2. 计算总人数

我们使用子查询计算总人数：

(select sum(number) from class_grade) as total
  • (SELECT SUM(number) FROM class_grade) AS total: 计算总人数。

3. 判断中位数所在的档位

我们通过WHERE子句判断中位数所在的档位：

where ascnum >= total/2 and descnum >= total/2
  • ascnum >= total/2 AND descnum >= total/2: 确定中位数所在的档位。

可以使用累计和来判断中位数的解释如下：

在传统统计学中，中位数 是将一组数据分成两半的数，使得：

  • 一半的数据小于等于它
  • 另一半的数据大于等于它

在这个定义下，累计和等效于 找到第一个使得其累积频率在正序和逆序中都超过一半的元素。

若某个数 满足：

那么 必然位于所有数列的中间部分，且这个数的累积分布在中间区间，因此可以视为中位数。

以 A1个，B2个，C3个，D4个 为例：
元素 频数 正序累计 逆序累计
A 1 1 10
B 2 3 9
C 3 6 7
D 4 10 4

总数，

  • 正序累计 ≥ 5 的数是 C、D（C: 6, D: 10）。
  • 逆序累计 ≥ 5 的数是 A、B、C（A: 10, B: 9, C: 7）。
  • 交集 = C，因此 C 是中位数。

解释：

  • 正序来看，C 及其之前的元素占了 6 ≥ 5，说明从左往右看，C 已经跨过一半的数据。
  • 逆序来看，C 及其之后的元素占了 7 ≥ 5，说明从右往左看，C 也是中间的值。
  • 因此，C 是满足条件的中位数！

4. 排序输出

我们按成绩档位升序排列输出结果：

order by grade asc
  • ORDER BY grade ASC: 按成绩档位升序排列。

完整代码

select grade
from (
    select grade,
           sum(number) over(order by grade asc) as ascnum,
           sum(number) over(order by grade desc) as descnum,
           (select sum(number) from class_grade) as total
    from class_grade
) sub
where ascnum >= total/2 and descnum >= total/2
order by grade asc;