我在 https://www.cnblogs.com/xiandedanteng/p/12637767.html 里谈到过两种中位数求法，我又找出了一种新的中位数求法，核心解法仍是正序和逆序序列。
如果存在奇数个元素，观察其seq，revseq和ceil三值，你将发现中值就是seq等于ceil那条，或是revseq等于ceil那条：
SQL> select 2 id,salary,rank() over (order by salary asc) as seq,rank() over (order by salary desc) as revseq,(select ceil(count(*)/2) from tb_coder1) as ceil 3 from tb_coder1 order by salary; ID SALARY SEQ REVSEQ CEIL ---------- ---------- ---------- ---------- ---------- 5 3083 1 9 5 6 6548 2 8 5 4 9057 3 7 5 8 9521 4 6 5 3 11458 5 5 5 9 14775 6 4 5 1 16292 7 3 5 2 17009 8 2 5 7 18825 9 1 5
如果存在偶数个元素，再观察seq，revseq和ceil三值，你将发现中值就是seq或是revseq等于ceil那两条：
SQL> select 2 id,salary,rank() over (order by salary asc) as seq,rank() over (order by salary desc) as revseq,(select ceil(count(*)/2) from tb_coder2) as ceil 3 from tb_coder2 order by salary; ID SALARY SEQ REVSEQ CEIL ---------- ---------- ---------- ---------- ---------- 3 3396 1 10 5 1 3682 2 9 5 5 5029 3 8 5 9 5859 4 7 5 7 9428 5 6 5 4 9691 6 5 5 6 13613 7 4 5 10 14366 8 3 5 2 16375 9 2 5 8 19127 10 1 5 已选择10行。
于是，最终的sql可以出来了：
select avg(a.salary) from (select id,salary,rank() over (order by salary asc) as seq,rank() over (order by salary desc) as revseq,(select ceil(count(*)/2) from tb_coder1) as ceil from tb_coder2 order by salary) a where a.seq=a.ceil or a.revseq=a.ceil
注意，有时需要把rank函数替换成row_number函数：
select avg(a.salary) from (select id,salary,row_number() over (order by salary asc) as seq,row_number() over (order by salary desc) as revseq,(select ceil(count(*)/2) from tb_employee) as ceil from tb_employee) a where a.seq=a.ceil or a.revseq=a.ceil
对于这两个函数的细微差别我目前还有欠缺的地方。
对于奇数行，结果是：
SQL> select 2 id,salary,rank() over (order by salary asc) as seq,rank() over (order by salary desc) as revseq,(select ceil(count(*)/2) from tb_coder1) as ceil 3 from tb_coder1 order by salary; ID SALARY SEQ REVSEQ CEIL ---------- ---------- ---------- ---------- ---------- 5 3083 1 9 5 6 6548 2 8 5 4 9057 3 7 5 8 9521 4 6 5 3 11458 5 5 5 9 14775 6 4 5 1 16292 7 3 5 2 17009 8 2 5 7 18825 9 1 5 已选择9行。 SQL> select avg(a.salary) from 2 (select 3 id,salary,rank() over (order by salary asc) as seq,rank() over (order by salary desc) as revseq,(select ceil(count(*)/2) from tb_coder1) as ceil 4 from tb_coder1 order by salary) a 5 where a.seq=a.ceil or a.revseq=a.ceil; AVG(A.SALARY) ------------- 11458
对于偶数行，结果是：
SQL> select 2 id,salary,rank() over (order by salary asc) as seq,rank() over (order by salary desc) as revseq,(select ceil(count(*)/2) from tb_coder2) as ceil 3 from tb_coder2 order by salary; ID SALARY SEQ REVSEQ CEIL ---------- ---------- ---------- ---------- ---------- 3 3396 1 10 5 1 3682 2 9 5 5 5029 3 8 5 9 5859 4 7 5 7 9428 5 6 5 4 9691 6 5 5 6 13613 7 4 5 10 14366 8 3 5 2 16375 9 2 5 8 19127 10 1 5 已选择10行。 SQL> select avg(a.salary) from 2 (select 3 id,salary,rank() over (order by salary asc) as seq,rank() over (order by salary desc) as revseq,(select ceil(count(*)/2) from tb_coder1) as ceil 4 from tb_coder2 order by salary) a 5 where a.seq=a.ceil or a.revseq=a.ceil; AVG(A.SALARY) ------------- 9559.5
--2020年4月11日--
千夫诺诺，不如一士谔谔。 能正面指出你的不足的人，才是真正对你有帮助的人。