MySQL中位数查询：ROW_NUMBER窗口函数一条SQL解决奇偶与分组

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位置 1位置 2实际取值111第 1 行212第 1、2 行平均322第 2 行423第 2、3 行平均533第 3 行634第 3、4 行平均这张表建议你亲手推一遍。比如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或rangeExtra里出现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是VARCHAR10可能排在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 常见问题速查表现象常见原因处理方式FUNCTION median does not existMySQL 没有 median 聚合用窗口函数或变量法报错 nearOVER版本低于 8.0升级或用变量法、临时表法结果行数多于一行外层没有聚合用AVG(val)并确保只取中间位置中位数偏大或偏小NULL 参与了计数和排序CTE 里加val IS NOT NULL字符串数字排序错乱按字典序排序用CAST转成数值类型分组后少组该组全为 NULL 被过滤左连接部门表补全查询很慢无索引触发 filesort建(val)或(group_id, val)索引变量法结果不稳定用户变量赋值顺序问题改用窗口函数或临时表这张表建议贴在项目笔记里。遇到问题时先对照现象再决定是改写法还是改索引。中位数查询本身不复杂复杂的是边界条件和数据质量。把 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)短、稳、能打日常查询和面试复盘都够用。