mysql高级刷题-01-求中位数

题目：


解题代码

select sum(num) / count(num) as median
from (select num,
             row_number() over (order by num desc,id desc ) as desc_math,
             row_number() over (order by num ,id )          as asc_math
      from number) as t1
where asc_math in (desc_math, desc_math + 1, desc_math - 1)

步骤1：内部子查询
sql

SELECT num,
       ROW_NUMBER() OVER (ORDER BY num DESC, id DESC) AS desc_math,
       ROW_NUMBER() OVER (ORDER BY num, id) AS asc_math
FROM number

这部分查询为每行数据生成两个行号：

desc_math: 按照 num 列递减排序（如果 num 值相同，则按 id 递减排序）。
asc_math: 按照 num 列递增排序（如果 num 值相同，则按 id 递增排序）。
这两个行号帮助我们在后续步骤中确定中位数的位置。

步骤2：外部查询
sql

SELECT SUM(num) / COUNT(num) AS median
FROM (
    -- 内部子查询...
) AS t1
WHERE asc_math IN (desc_math, desc_math + 1, desc_math - 1);

外部查询根据行号筛选出可能是中位数的行：

asc_math 是递增排序后的行号
desc_math 是递减排序后的行号
WHERE asc_math IN (desc_math, desc_math + 1, desc_math - 1) 条件确保选择的数据行满足以下情况：

asc_math 等于 desc_math，即中间的那个数（如果总数是奇数）
asc_math 等于 desc_math + 1 或 desc_math - 1，即中间的两个数之一（如果总数是偶数）
最终计算
sql

SELECT SUM(num) / COUNT(num) AS median
SUM(num) / COUNT(num) 

计算被选中的 num 值的平均数。对于奇数个数，这只是中间那个数的值；对于偶数个数，这会计算中间两个数的平均值。

完整的查询流程
生成行号：

使用窗口函数 ROW_NUMBER() 分别按照递增和递减顺序为每行数据生成行号。
筛选中位数候选行：

使用 WHERE 子句筛选出可能是中位数的行。
计算中位数：

计算被选中行的 num 值的平均数。
示例数据及其应用
假设 number 表的数据如下：

执行上述查询步骤后：
生成行号：
desc_math 行号：7, 6, 5, 4, 3, 2, 1
asc_math 行号：1, 2, 3, 4, 5, 6, 7
筛选中位数候选行：
中位数行：asc_math 为 4, desc_math 也是 4（总数为奇数，直接取中间值）
计算中位数：
中位数为 6
因此，这个查询能够在 MySQL 8.0 中有效地计算数据表中的中位数。