MySQL：按列分组计算值的中位数

Question

我有如下表：

+------------+-------+
| SchoolName | Marks |
+------------+-------+
| A          |    71 |
| A          |    71 |
| A          |    71 |
| B          |   254 |
| B          |   135 |
| B          |   453 |
| B          |   153 |
| C          |   453 |
| C          |   344 |
| C          |   223 |
| B          |   453 |
| D          |   300 |
| D          |   167 |
+------------+-------+

这里是按学校名称分组的分数平均值：

+------------+------------+
| SchoolName | avg(Marks) |
+------------+------------+
| A          |    71.0000 |
| B          |   289.6000 |
| C          |   340.0000 |
| D          |   233.5000 |
+------------+------------+

https://www.db-fiddle.com/f/5t7N3Vx8FSQmwUJgKLqjfK/9

然而，我想计算按学校名称分组的成绩的中位数。

我正在使用：

SELECT AVG(dd.Marks) as median_val
FROM (
SELECT d.Marks, @rownum:=@rownum+1 as `row_number`, @total_rows:=@rownum
  FROM tablename d, (SELECT @rownum:=0) r
  WHERE d.Marks is NOT NULL
  ORDER BY d.Marks
) as dd
WHERE dd.row_number IN ( FLOOR((@total_rows+1)/2), FLOOR((@total_rows+2)/2) );

我想要计算整个成绩列的平均值，但不知道如何为每个学校单独计算。

- Pardeep Kumar

---

你使用的是哪个版本的MySQL？ - GMB

我正在使用MySQL 8。 - Pardeep Kumar

请问您能否展示一下您期望的结果？ - GMB

Answer 1

您的查询使用用户变量计算行号，这使得处理分区更加复杂。由于您使用的是MySQL 8.0，我建议改用窗口函数。

以下代码应该可以让您接近预期的结果：

select 
    SchoolName, 
    avg(Marks) as median_val
from (
select 
    SchoolName,
    Marks, 
    row_number() over(partition by SchoolName order by Marks) rn,
    count(*) over(partition by SchoolName) cnt
  from tablename
) as dd
where rn in ( FLOOR((cnt + 1) / 2), FLOOR( (cnt + 2) / 2) )
group by SchoolName

算法保持不变，但我们在具有相同“SchoolName”的记录组中使用窗口函数（而不是在初始查询中进行全局分区）。然后，外部查询按SchoolName进行过滤和聚合。

在你的DB Fiddlde中，这将返回：

| SchoolName | median_val |
| ---------- | ---------- |
| A          | 71         |
| B          | 254        |
| C          | 344        |
| D          | 233.5      |

- GMB

---

符合我的要求！非常感谢你！ :) - Pardeep Kumar

欢迎@PardeepKumar！很高兴它有所帮助。 - GMB

修复我的TiDB问题！ - undefined