在 https://www.cnblogs.com/xiandedanteng/p/12677688.html 中我列举了三种求中值方案，其中日本人MICK的做法因为不适用于二百万结果集而放弃，取而代之是新方案一。
新方案一：
经过思考后我又得出了一种中值的新解法，那就是利用排序后正向序列和反向序列交叉点为中值区的原理，如果两个序列相减小于等于一则求所在区域的均值即可。
SQL：
select avg(a.salary) from
(select salary,dense_rank() over (order by salary asc) as seq,dense_rank() over (order by salary desc) as revseq from tb_employee order by salary) a where abs(a.seq-a.revseq)<=1
解释计划：
SQL> select avg(a.salary) from
2 (select salary,dense_rank() over (order by salary asc) as seq,dense_rank() over (order by salary desc) as revseq from tb_employee order by salary) a where abs(a.seq-a.revseq)<=1;
已用时间: 00: 00: 00.00 执行计划
----------------------------------------------------------
Plan hash value: 9035349 ---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | | 8850 (2)| 00:01:47 |
| 1 | SORT AGGREGATE | | 1 | 39 | | | |
|* 2 | VIEW | | 2000K| 74M| | 8850 (2)| 00:01:47 |
| 3 | WINDOW SORT | | 2000K| 7812K| 22M| 8850 (2)| 00:01:47 |
| 4 | WINDOW SORT | | 2000K| 7812K| 22M| 8850 (2)| 00:01:47 |
| 5 | TABLE ACCESS FULL| TB_EMPLOYEE | 2000K| 7812K| | 3045 (2)| 00:00:37 |
--------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter(ABS("A"."SEQ"-"A"."REVSEQ")<=1)
从Cost看是目前最快的。
执行时间：
SQL> select avg(a.salary) from
2 (select salary,dense_rank() over (order by salary asc) as seq,dense_rank() over (order by salary desc) as revseq from tb_employee order by salary) a where abs(a.seq-a.revseq)<=1; AVG(A.SALARY)
-------------
5500 已用时间: 00: 00: 02.46
从运行时间上看排第二。
经局部优化后两种方案SQL如下：
原有方案二：
select avg(b.salary) from
(select a.salary,abs(a.seq-a.revseq) as diff from (select id,salary,dense_rank() over (order by salary asc) as seq,dense_rank() over (order by salary desc) as revseq from tb_employee order by salary) a ) b
where b.diff=(select min(c.diff) from
(select abs(a.seq-a.revseq) as diff from (select dense_rank() over (order by salary asc) as seq,dense_rank() over (order by salary desc) as revseq from tb_employee order by salary) a ) c)
解释计划：
SQL> select avg(b.salary) from
2 (select a.salary,abs(a.seq-a.revseq) as diff from (select id,salary,dense_rank() over (order by salary asc) as seq,dense_rank() over (order by salary desc) as revseq from tb_employee order by salary) a ) b
3 where b.diff=(select min(c.diff) from
4 (select abs(a.seq-a.revseq) as diff from (select dense_rank() over (order by salary asc) as seq,dense_rank() over (order by salary desc) as revseq from tb_employee order by salary) a ) c);
已用时间: 00: 00: 00.00 执行计划
----------------------------------------------------------
Plan hash value: 3874635296 -----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | | 19969 (2)| 00:04:00 |
| 1 | SORT AGGREGATE | | 1 | 39 | | | |
|* 2 | VIEW | | 2000K| 74M| | 11119 (2)| 00:02:14 |
| 3 | WINDOW SORT | | 2000K| 19M| 38M| 11119 (2)| 00:02:14 |
| 4 | WINDOW SORT | | 2000K| 19M| 38M| 11119 (2)| 00:02:14 |
| 5 | TABLE ACCESS FULL | TB_EMPLOYEE | 2000K| 19M| | 3045 (2)| 00:00:37 |
| 6 | SORT AGGREGATE | | 1 | 26 | | | |
| 7 | VIEW | | 2000K| 49M| | 8850 (2)| 00:01:47 |
| 8 | WINDOW SORT | | 2000K| 7812K| 22M| 8850 (2)| 00:01:47 |
| 9 | WINDOW SORT | | 2000K| 7812K| 22M| 8850 (2)| 00:01:47 |
| 10 | TABLE ACCESS FULL| TB_EMPLOYEE | 2000K| 7812K| | 3045 (2)| 00:00:37 |
----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter(ABS("A"."SEQ"-"A"."REVSEQ")= (SELECT MIN(ABS("A"."SEQ"-"A"."REVSEQ"))
FROM (SELECT DENSE_RANK() OVER ( ORDER BY "SALARY") "SEQ",DENSE_RANK() OVER ( ORDER
BY INTERNAL_FUNCTION("SALARY") DESC ) "REVSEQ" FROM "TB_EMPLOYEE" "TB_EMPLOYEE" ORDER
BY "SALARY") "A"))
执行时间：
SQL> select avg(b.salary) from
2 (select a.salary,abs(a.seq-a.revseq) as diff from (select id,salary,dense_rank() over (order by salary asc) as seq,dense_rank() over (order by salary desc) as revseq from tb_employee order by salary) a ) b
3 where b.diff=(select min(c.diff) from
4 (select abs(a.seq-a.revseq) as diff from (select dense_rank() over (order by salary asc) as seq,dense_rank() over (order by salary desc) as revseq from tb_employee order by salary) a ) c);
AVG(B.SALARY)
-------------
5500
已用时间: 00: 00: 04.56
原有方案三：
select avg(a.salary) from
(select
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 avg(a.salary) from
2 (select
3 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
4 from tb_employee) a
5 where a.seq=a.ceil or a.revseq=a.ceil;
已用时间: 00: 00: 00.00 执行计划
----------------------------------------------------------
Plan hash value: 3541675306 -----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | | 14656 (3)| 00:02:56 |
| 1 | SORT AGGREGATE | | 1 | 52 | | | |
| 2 | SORT AGGREGATE | | 1 | | | | |
| 3 | INDEX FAST FULL SCAN| SYS_C0012264 | 2000K| | | 1474 (2)| 00:00:18 |
|* 4 | VIEW | | 2000K| 99M| | 14656 (3)| 00:02:56 |
| 5 | WINDOW SORT | | 2000K| 7812K| 22M| 14656 (3)| 00:02:56 |
| 6 | WINDOW SORT | | 2000K| 7812K| 22M| 14656 (3)| 00:02:56 |
| 7 | TABLE ACCESS FULL | TB_EMPLOYEE | 2000K| 7812K| | 3045 (2)| 00:00:37 |
----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id):
--------------------------------------------------- 4 - filter("A"."SEQ"="A"."CEIL" OR "A"."REVSEQ"="A"."CEIL")
消耗时间：
SQL> ;
1 select avg(b.salary) from
2 (select
3 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
4 - filter("A"."SEQ"="A"."CEIL" OR "A"."REVSEQ"="A"."CEIL")；
5* where a.seq=a.ceil or a.revseq=a.ceil
SQL> select avg(a.salary) from
2 (select
3 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
4 from tb_employee) a
5 where a.seq=a.ceil or a.revseq=a.ceil; AVG(A.SALARY)
-------------
5498 已用时间: 00: 00: 01.98
而单查表数量的解释计划大家也参考看一下：
SQL> select count(*) from tb_employee;
已用时间: 00: 00: 00.01 执行计划
----------------------------------------------------------
Plan hash value: 2866911338 ------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1474 (2)| 00:00:18 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| SYS_C0012264 | 2000K| 1474 (2)| 00:00:18 |
------------------------------------------------------------------------------
最后的大比拼表格：
| 方案 | Cost | 耗时 |
| 新方案一 | 8850 | 2.46秒 |
| 原有方案二 | 19946 | 4.56秒 |
| 原有方案三 | 14646 | 1.98 |
截至目前，方案三以较高效率胜出，新方案一其实也不错，原有方案三排第三位。
--2020年4月12日--