企业官网建设流程全解析

1. 为什么 MySQL 查询求中位数总让人绕弯

MySQL 查询求中位数，很多人第一反应是写SELECT MEDIAN(val) FROM nums;，然后在客户端里看到FUNCTION median does not exist。这不是语法写错了，而是 MySQL 本身没有提供 median 聚合函数。你要么用窗口函数自己算位置，要么用变量模拟行号，要么把数据拉到应用层用两个堆去维护。标题里说的是“最简单的写法”，那答案其实很明确：MySQL 8.0 及以上，用ROW_NUMBER()加COUNT(*) OVER()，一条 SQL 就能把奇数和偶数情况一起处理掉。它适合做报表、数据分析、后台统计、面试题复盘，也适合平时经常写 SQL 但不想为了一个中位数去建存储过程的人。

1.1 中位数的本质：排序后取中间

中位数不是平均数，它只关心数据排序后的位置。把一列数字从小到大排好，如果总行数是奇数，正中间那个值就是中位数；如果总行数是偶数，中间两个值的平均就是中位数。比如1, 3, 2, 5, 4排序后是1, 2, 3, 4, 5，总行数 5，正中间是第 3 行，中位数就是 3。再比如1, 3, 2, 5, 4, 6排序后是1, 2, 3, 4, 5, 6，总行数 6，中间是第 3 行和第 4 行，中位数就是(3 + 4) / 2 = 3.5。这个定义看起来简单，但在 SQL 里要表达“正中间的位置”，就得先知道总行数，再知道每一行排序后的行号。窗口函数刚好同时提供这两个信息，所以写法才会那么短。

生活里可以把它想成排队：一队人按身高从矮到高站好，中位数就是站在队伍正中间那个人的身高。如果人数是双数，就取中间两个人的平均身高。数据库里没有“正中间”这个物理位置，只有行号，所以我们要用ROW_NUMBER()给每行发一个排序后的号码牌，再用COUNT(*) OVER()数出总人数，最后用算术找出中间号码牌对应的行。这个思路一旦理解，后面的 SQL 就只是把思路翻译成语法。

1.2 MySQL 没有 median 聚合函数的现实

MySQL 8.0 有窗口函数、CTE、递归查询、JSON 函数，但确实没有内置的median()聚合。某些数据库有PERCENTILE_CONT之类的函数，MySQL 里没有对应的直接替代。所以网上才会出现各种写法：变量法、自连接法、临时表法、应用层两个堆法。变量法在 MySQL 5.7 里很常见，但依赖用户变量，写法不够直观，还容易受优化器影响。自连接法在重复值多的时候容易出错，性能也差。两个堆法适合流式数据，但那是应用层算法，不是单条 SQL 能解决的。所以如果你的 MySQL 是 8.0 以上，优先用窗口函数，代码短、逻辑清楚、结果也稳。

我见过不少人为了求中位数，先写一个存储过程，再写游标，最后还要建临时表。不是说这些方法不能用，而是对“查询求中位数”这个需求来说太重了。报表里偶尔求一次中位数，用窗口函数最划算；如果是高频接口，每次请求都扫全表排序，那就不是 SQL 写法问题，而是架构问题，应该考虑预计算或缓存。先把最简单的单条查询掌握，再根据数据量和调用频率决定要不要优化，这个顺序比较合理。

1.3 所谓“最简单”到底简单在哪

我判断一个中位数写法简不简单，看几个点：是不是一条 SQL 能跑完，要不要建临时表，要不要写存储过程，能不能自动处理奇偶，能不能过滤 NULL，能不能顺手支持分组。窗口函数版基本都满足。它不需要你提前知道总行数，也不需要你手动判断奇数偶数，(cnt + 1) DIV 2和(cnt + 2) DIV 2会把两种情况统一掉。DIV是 MySQL 的整数除法，比FLOOR和CEIL更短，读起来也更像“取第几个位置”。如果你只记一个模板，我建议就记这个。

当然，简单不等于万能。数据量特别大时，窗口函数仍然要排序，排序就是成本。分组特别多时，PARTITION BY也会带来额外开销。数据里如果有 NULL，你必须先过滤，否则总行数会把 NULL 算进去，行号也会被 NULL 占掉，中位数就偏了。重复值不会影响最终的平均结果，因为重复的值相同，取到哪一行都一样。把这些边界想清楚，再写 SQL，基本就不会翻车。

2. 一条窗口函数 SQL 解决 90% 的中位数需求

2.1 直接可抄的完整 SQL 模板

假设表名是nums，数值列是val，下面这条 SQL 就是我最常用的中位数查询模板。它用 CTE 先算出每行的排序行号和总行数，再在外层取中间位置。MySQL 8.0 以上直接复制就能跑，把nums和val换成你的表名和列名即可。

WITH ranked AS ( SELECT val, ROW_NUMBER() OVER (ORDER BY val) AS rn, COUNT(*) OVER () AS cnt FROM nums WHERE val IS NOT NULL ) SELECT AVG(val) AS median FROM ranked WHERE rn IN ((cnt + 1) DIV 2, (cnt + 2) DIV 2);

ROW_NUMBER() OVER (ORDER BY val)给排序后的每一行发一个从 1 开始的行号。COUNT(*) OVER ()不分组，直接统计整个结果集的总行数。外层用AVG(val)是因为偶数个数据要取中间两个值的平均，奇数个数据时两个位置相同，AVG一个值等于它本身。WHERE val IS NOT NULL放在 CTE 里面，是为了让行号和总行数都不被 NULL 干扰。这个模板没有临时表，没有变量，没有存储过程，属于单条查询里最省事的写法。

2.2(cnt + 1) DIV 2和(cnt + 2) DIV 2为什么能通吃奇偶

这两个表达式是整条 SQL 的灵魂。DIV是整数除法，会丢掉小数部分。对于总行数cnt，第一个位置是(cnt + 1) DIV 2，第二个位置是(cnt + 2) DIV 2。当cnt是奇数时，两个位置相等；当cnt是偶数时，两个位置正好是中间相邻的两行。用表格看得更清楚：

这张表建议你亲手推一遍。比如cnt = 5，(5 + 1) DIV 2 = 3，(5 + 2) DIV 2 = 3，两个位置都是 3，外层只取到一行，AVG就是这一行的值。cnt = 6，(6 + 1) DIV 2 = 3，(6 + 2) DIV 2 = 4，外层取到第 3 行和第 4 行，AVG就是两行平均。这个写法比FLOOR和CEIL更短，也比CASE WHEN判断奇偶更直接。记住这个规律，以后遇到类似“取中间位置”的需求都能套。

2.3 重复值、NULL、非数字列的处理

重复值不会影响中位数结果，但会影响行号的分配。比如1, 2, 2, 3，排序后行号可能是 1、2、3、4，两个 2 谁拿 2 号谁拿 3 号并不重要，因为值一样，最后AVG出来还是 2。真正要小心的是 NULL。COUNT(*)会把 NULL 行也算进总行数，ROW_NUMBER()在升序时通常把 NULL 排在最前面，结果就是行号被 NULL 占掉，中位数取到了错误的位置。所以一定在 CTE 里加WHERE val IS NOT NULL。如果你的列是字符串数字，比如'12'、'8'，要先CAST(val AS DECIMAL(18,4))，否则排序可能按字符串字典序走，'12'会排在'8'前面。

WITH ranked AS ( SELECT CAST(val AS DECIMAL(18,4)) AS num, ROW_NUMBER() OVER (ORDER BY CAST(val AS DECIMAL(18,4))) AS rn, COUNT(*) OVER () AS cnt FROM nums WHERE val IS NOT NULL AND val <> '' ) SELECT AVG(num) AS median FROM ranked WHERE rn IN ((cnt + 1) DIV 2, (cnt + 2) DIV 2);

金额、分数、温度这类需要精确小数的场景，建议把转换后的类型统一成DECIMAL，不要用FLOAT做平均值比较。如果业务上 NULL 代表 0，那就在 CTE 里用COALESCE(val, 0)，而不是直接过滤。到底过滤还是补零，取决于业务定义，不要为了 SQL 好看而随意改语义。我一般会在查询旁边写一句注释，说明 NULL 的处理规则，方便以后自己或同事回看。

2.4 从建表到结果的实测过程

光看模板不够直观，我们实际跑一遍。先建一张简单的数字表，插入奇数和偶数两组数据，然后分别查询。

CREATE TABLE nums ( id INT PRIMARY KEY AUTO_INCREMENT, val INT ); INSERT INTO nums (val) VALUES (1), (3), (2), (5), (4);

这组数据排序后是1, 2, 3, 4, 5，总行数 5，中位数应该是 3。执行下面的查询：

WITH ranked AS ( SELECT val, ROW_NUMBER() OVER (ORDER BY val) AS rn, COUNT(*) OVER () AS cnt FROM nums WHERE val IS NOT NULL ) SELECT AVG(val) AS median FROM ranked WHERE rn IN ((cnt + 1) DIV 2, (cnt + 2) DIV 2);

结果会返回median = 3.0000。再插入一行变成 6 行：

INSERT INTO nums (val) VALUES (6);

现在排序是1, 2, 3, 4, 5, 6，总行数 6，中位数是(3 + 4) / 2 = 3.5。再跑同一条查询，结果会变成median = 3.5000。整个过程不需要改 SQL，也不需要判断奇偶，这就是这个写法的舒服之处。实测时我建议把 CTE 单独查一遍，看看rn和cnt是否符合预期，再查外层平均值，排查问题会快很多。

3. 老版本 MySQL 怎么写：5.7 及以前的替代方案

3.1 用户变量法：不用窗口函数也能跑

MySQL 5.7 没有窗口函数，只能用用户变量模拟行号。下面这个写法在 5.7 环境里很常见，思路是先按val排序，再用@rn逐行加一，最后用总行数算出中间位置。注意@rn初始值设为 0，赋值时先加一，这样第一行行号就是 1。

SET @rn := 0; SELECT AVG(val) AS median FROM ( SELECT val, @rn := @rn + 1 AS rn FROM nums WHERE val IS NOT NULL ORDER BY val ) AS t WHERE t.rn IN ( ((SELECT COUNT(*) FROM nums WHERE val IS NOT NULL) + 1) DIV 2, ((SELECT COUNT(*) FROM nums WHERE val IS NOT NULL) + 2) DIV 2 );

这个写法能跑，但有几点要注意。第一，ORDER BY必须写在派生表里，否则行号顺序不可靠。第二，用户变量的赋值顺序在复杂查询里可能被优化器打乱，尤其是带 JOIN 的时候。第三，每次查询前都要SET @rn := 0，不然上一次的值会残留。第四，如果以后升级到 MySQL 8.0，建议直接换窗口函数写法，不要继续维护变量法。变量法更像是过渡方案，能不用就不用。

3.2 临时表法：稳定但步骤多

如果你觉得变量法心里没底，可以用临时表把排序和行号固定下来。临时表的好处是结果已经物化，后续查询稳定，缺点是多了建表和删表的步骤，I/O 也更大。

CREATE TEMPORARY TABLE tmp_rank AS SELECT val, @rn := @rn + 1 AS rn FROM nums, (SELECT @rn := 0) AS init WHERE val IS NOT NULL ORDER BY val; SELECT AVG(val) AS median FROM tmp_rank WHERE rn IN ( ((SELECT COUNT(*) FROM tmp_rank) + 1) DIV 2, ((SELECT COUNT(*) FROM tmp_rank) + 2) DIV 2 ); DROP TEMPORARY TABLE tmp_rank;

这里把@rn := 0放在派生表init里，可以少写一条SET。CREATE TEMPORARY TABLE ... AS SELECT会把排序后的行号和值存下来，外层查询只读临时表。这个方案适合一次性分析任务，或者老版本里必须保证稳定的场景。临时表只在当前会话可见，断开连接后自动清理，不会污染正式表。但如果你在连接池环境里用，记得显式DROP TEMPORARY TABLE，避免会话复用导致表名冲突。

3.3 两个堆算法：流式数据的中位数正确姿势

如果你要处理的是持续插入的数据流，比如实时监控指标、在线评测分数、交易金额，每次插入后都要能立刻拿到中位数，那就不要在 MySQL 里反复扫全表。更合适的做法是在应用层维护两个堆：大顶堆放较小的一半，小顶堆放较大的一半，并且保持两个堆的大小差不超过 1。插入是O(log n)，取中位数是O(1)。下面是一个 Python 示例：

import heapq class MedianFinder: def __init__(self): self.small = [] # 大顶堆，存负数 self.large = [] # 小顶堆 def add(self, num): heapq.heappush(self.small, -num) heapq.heappush(self.large, -heapq.heappop(self.small)) if len(self.large) > len(self.small): heapq.heappush(self.small, -heapq.heappop(self.large)) def median(self): if len(self.small) > len(self.large): return -self.small[0] return (-self.small[0] + self.large[0]) / 2

这个算法和 SQL 查询解决的不是同一个问题。SQL 查询适合“已经有一批数据，我要算一次中位数”；两个堆适合“数据不断进来，我要随时知道中位数”。如果你在 MySQL 里硬用 SQL 模拟两个堆，既别扭又低效。实际项目里我会这样分工：离线报表用窗口函数 SQL，实时接口用两个堆或者专门的统计服务，两者不要混在一起。标题问的是 MySQL 查询，所以主写法还是 SQL，但面试或架构讨论时，两个堆是必须知道的补充。

4. 性能优化：别让中位数查询拖垮数据库

4.1 看懂执行计划：filesort 和 temporary 是重点

中位数查询绕不开排序。你在查询前加EXPLAIN，重点看Extra列有没有Using filesort或Using temporary。如果有Using filesort，说明 MySQL 需要额外排序，数据量大时就会慢。窗口函数的ORDER BY val也会触发排序，除非有合适的索引能直接提供顺序。

EXPLAIN WITH ranked AS ( SELECT val, ROW_NUMBER() OVER (ORDER BY val) AS rn, COUNT(*) OVER () AS cnt FROM nums WHERE val IS NOT NULL ) SELECT AVG(val) AS median FROM ranked WHERE rn IN ((cnt + 1) DIV 2, (cnt + 2) DIV 2);

如果type是ALL，说明全表扫描；如果Extra出现Using filesort，说明排序成本高。建了索引之后，理想情况是type变成index或range，Extra里出现Using index，表示覆盖索引生效。注意，COUNT(*) OVER ()仍然要统计总行数，所以完全避免扫描不太现实，但至少可以避免昂贵的随机排序。对于几万行以内的数据，随便跑都没事；上百万行就要认真看执行计划。

4.2 索引怎么建：单列、组合、覆盖

最简单的优化是给数值列建索引：

CREATE INDEX idx_nums_val ON nums (val);

如果查询里固定过滤val IS NOT NULL，这个索引也能帮忙。对于分组中位数，比如按部门求工资中位数，索引要建成组合索引：

CREATE INDEX idx_emp_dept_salary ON employees (dept_id, salary);

这样PARTITION BY dept_id ORDER BY salary可以尽量利用索引的顺序，减少排序。如果查询只需要dept_id和salary两列，这个索引本身就是覆盖索引，不需要回表。实际建索引时不要一次建太多，索引会占空间，也会拖慢写入。我的习惯是先用EXPLAIN看瓶颈，再决定建单列还是组合。如果一条中位数查询每天只跑一次，慢几秒可以接受，就不一定值得为它单独建索引。

4.3 大数据量下不要硬算精确中位数

精确中位数要求知道排序后的中间位置，数据量越大，排序成本越高。几千万行的表上每次查询都做精确中位数，数据库会很难受。这时候可以考虑几种替代方案：第一，预计算，把中位数按天、按小时算好存进统计表；第二，抽样近似，比如随机取 1% 的数据算中位数，结果会有误差，但趋势够用；第三，应用层流式计算，用两个堆维护实时中位数；第四，用直方图做近似分位数。MySQL 本身没有内置的近似分位数函数，所以这些方案通常要在应用层或数仓层实现。

我自己的判断标准是：如果查询频率低、数据量小，直接窗口函数；如果查询频率高、数据量大，就不要让每次请求都扫全表。中位数不是平均值，平均值可以用SUM和COUNT增量维护，中位数很难用一个简单的累加值维护。所以报表和实时接口要分开设计，不要用一个 SQL 打天下。这个经验在真实项目里比写法本身更重要。

4.4 分组中位数：PARTITION BY 的完整写法

按类别、部门、地区求中位数，只需要在窗口函数里加PARTITION BY，外层再加GROUP BY。下面这个例子按部门求工资中位数：

WITH ranked AS ( SELECT dept_id, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary) AS rn, COUNT(*) OVER (PARTITION BY dept_id) AS cnt FROM employees WHERE salary IS NOT NULL ) SELECT dept_id, AVG(salary) AS median_salary FROM ranked WHERE rn IN ((cnt + 1) DIV 2, (cnt + 2) DIV 2) GROUP BY dept_id;

这里PARTITION BY dept_id让每个部门内部单独编号、单独计数，所以每个部门都能算出自己的中位数。WHERE salary IS NOT NULL要放在 CTE 里，不能等到外层再过滤，否则cnt会把 NULL 算进去。外层GROUP BY dept_id是因为偶数个数据时每个部门可能取到两行，需要按部门求平均。如果某个部门全被过滤掉了，结果里就不会出现这个部门。如果你希望所有部门都显示，即使没有数据也显示 NULL，那就用部门表左连接这个查询结果。

5. 常见问题与排查技巧实录

5.1 版本不支持窗口函数怎么办

最常见的报错是You have an error in your SQL syntax ... near 'OVER'，或者ROW_NUMBER() is not recognized。这通常说明 MySQL 版本低于 8.0。先执行SELECT VERSION();确认版本。如果是 5.7，就用前面的用户变量法或临时表法。如果版本是 8.0 以上还报错，检查是不是把窗口函数写在了不允许的位置，比如 WHERE 里。窗口函数只能在 SELECT 列表和 ORDER BY 里使用，不能直接写在 WHERE 条件中。正确顺序是先用 CTE 或子查询把rn和cnt算出来，再在外层过滤。

5.2 结果不对：先查 NULL，再查重复值

中位数结果偏大或偏小，第一嫌疑人是 NULL。COUNT(*)统计的是行数，不是非 NULL 值的数量。如果 NULL 参与了计数和排序，中间位置就会错。第二嫌疑人是排序方向，ORDER BY val默认升序，中位数定义通常按升序，不用改。第三嫌疑人是字符串排序，如果val是VARCHAR，'10'可能排在'9'前面，结果自然不对。第四才是重复值，但重复值通常不影响最终平均值。排查时先单独跑 CTE，看rn、cnt、val三列是否符合预期，再跑外层，问题很快就能定位。

5.3 分组后少了一些组

分组中位数查询结果里少了某些部门，通常是因为这些部门的salary全是 NULL，被WHERE salary IS NOT NULL过滤掉了。如果你需要保留这些部门，可以先用部门表左连接排名结果：

SELECT d.dept_id, m.median_salary FROM departments d LEFT JOIN ( WITH ranked AS ( SELECT dept_id, salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary) AS rn, COUNT(*) OVER (PARTITION BY dept_id) AS cnt FROM employees WHERE salary IS NOT NULL ) SELECT dept_id, AVG(salary) AS median_salary FROM ranked WHERE rn IN ((cnt + 1) DIV 2, (cnt + 2) DIV 2) GROUP BY dept_id ) AS m ON d.dept_id = m.dept_id;

这样没有工资数据的部门也会显示出来，中位数为 NULL。到底要不要补全，取决于报表需求。如果业务方要求“每个部门都必须有一行”，那就左连接；如果只关心有数据的部门，直接查即可。

5.4 中位数和平均值差异特别大

中位数和平均值差异大，通常说明数据有偏态或者极端值。比如工资表里大多数人月薪 8000，但有几个高管月薪 80000，平均值会被拉高，中位数仍然接近 8000。这种时候中位数更能代表“普通人的水平”。排查时可以把数据按升序排列，看看最大和最小的几个值。如果业务方问“为什么中位数和平均值差这么多”，你可以用这个解释：平均值受极端值影响，中位数只看位置。这个知识点在数据分析和汇报里很常用，不只是 SQL 写法问题。

5.5 常见问题速查表

这张表建议贴在项目笔记里。遇到问题时先对照现象，再决定是改写法还是改索引。中位数查询本身不复杂，复杂的是边界条件和数据质量。把 NULL、重复值、字符串类型、分组缺失这四类问题处理好，基本就能稳定输出结果。

6. 我个人常用的封装与校验习惯

6.1 把常用中位数逻辑封装成视图

如果某张表的中位数查询经常被调用，我会把它封装成视图，避免每次都复制一长串 CTE。下面这个视图把nums表的中位数固定下来：

CREATE OR REPLACE VIEW v_nums_median AS WITH ranked AS ( SELECT val, ROW_NUMBER() OVER (ORDER BY val) AS rn, COUNT(*) OVER () AS cnt FROM nums WHERE val IS NOT NULL ) SELECT AVG(val) AS median FROM ranked WHERE rn IN ((cnt + 1) DIV 2, (cnt + 2) DIV 2);

以后直接SELECT * FROM v_nums_median;就能拿到中位数。视图的好处是调用简单，坏处是每次查询仍然会实时计算，数据量大时并不会自动变快。如果表数据更新频繁，视图结果也会跟着变。如果业务要求固定快照，那就不是视图，而是定时任务写入统计表。封装之前先想清楚调用频率和数据量，不要为了省事而制造慢查询。

6.2 用抽样和交叉校验确认结果

写完中位数 SQL 后，我习惯做一次交叉校验。最简单的方法是把数据按升序查出来，肉眼找中间值：

SELECT val FROM nums WHERE val IS NOT NULL ORDER BY val;

比如返回1, 2, 3, 4, 5，中间是 3；返回1, 2, 3, 4, 5, 6，中间是 3 和 4，平均 3.5。如果数据量太大不能全看，就抽样看头尾和中间几行：

SELECT val FROM nums WHERE val IS NOT NULL ORDER BY val LIMIT 10;

再用COUNT(*)确认总行数，用MIN(val)和MAX(val)确认范围。交叉校验不一定要很复杂，关键是要有一个独立于原 SQL 的参照结果。我踩过的坑里，最亏的就是直接相信一条复杂 SQL 的输出，结果 NULL 参与了计数，报表数字错了半天才发现。后来我养成了先查 CTE、再查外层、最后抽样核对的习惯，省了很多返工。

6.3 几个踩坑后的经验

第一，WHERE val IS NOT NULL一定要放在窗口函数之前，放在外层就晚了。第二，DIV比FLOOR、CEIL短，但你要知道它是整数除法，负数场景不要乱用，中位数这里都是正整数位置，没问题。第三，分组中位数记得建(分组列, 数值列)的组合索引，不然每个分组都排序，开销会叠加。第四，MySQL 5.7 的变量法不要在并发要求高的接口里用，会话变量虽然隔离，但赋值顺序不稳定，结果可能飘。第五，实时中位数不要硬套 SQL，两个堆或者专用统计服务更合适。第六，如果数据里 NULL 代表 0，就在 CTE 里COALESCE，不要一边过滤一边又期望它参与计算。

我在实际项目里最常说的是：先明确业务定义，再写 SQL。中位数到底是“非 NULL 值的中位数”还是“把 NULL 当 0 的中位数”，这两种结果可能完全不同。写法只是工具，定义才是根。把定义写进注释，把边界写进测试用例，下次换个人维护也不容易出错。至于最简单的写法，我还是推荐窗口函数版那一句rn IN ((cnt + 1) DIV 2, (cnt + 2) DIV 2)，短、稳、能打，日常查询和面试复盘都够用。