oracle：中位数查询整理
中位数的数学定义
中位数的定义如下图所示:
在oracle数据查询中，当N为偶数时，一般不取平均值，因为这有可能创建出新的样例使得查询语句为空，所以通常为偶数时，一般返回最中间的一组数据。
oracle中位数查询
1.求解思路
目前常见的求解主要包括以下两种思路：
1)从数值角度出发；
先按照目标列进行排序，然后按照总行数的奇偶性，利用类似上图的数学方法进行方法进行筛选。
2）从数值索引(所在位置)出发
这种方法从中位数可能出现的位置，进行巧妙构造，以选取目标位置。
类似于python中根据目标索引对list进行切片。其更符合中位数的定义，即有一半的数大于中位数，有一半数小于中位数。
实际应用中出于不同的考虑,对于内置函数median选择性使用
2.应用案例
为了方便说明，采用leetcode上的案例：编写SQL查询来查找每个公司的薪水中位数。
| Id | Company | Salary |
|---|---|---|
| 1 | A | 2341 |
| 2 | A | 341 |
| 3 | A | 15 |
| 4 | A | 15314 |
| 5 | A | 451 |
| 6 | A | 513 |
| 7 | B | 15 |
| 8 | B | 13 |
| 9 | B | 1154 |
| 10 | B | 1345 |
| 11 | B | 1221 |
| 12 | B | 234 |
| 13 | C | 2345 |
| 14 | C | 2645 |
| 15 | C | 2645 |
| 16 | C | 2652 |
| 17 | C | 65 |
- 表创建语句：
create table employee(
id number(3) primary key,
company varchar2(2),
salary number(8)
);
insert into employee values(1,'A',2341);
insert into employee values(2,'A',341);
insert into employee values(3,'A',15);
insert into employee values(4,'A',15314);
insert into employee values(5,'A',451);
insert into employee values(6,'A',513);
insert into employee values(7,'B',15);
insert into employee values(8,'B',13);
insert into employee values(9,'B',1154);
insert into employee values(10,'B',1354);
insert into employee values(11,'B',1221);
insert into employee values(12,'B',234);
insert into employee values(13,'C',2345);
insert into employee values(14,'C',2645);
insert into employee values(15,'C',2645);
insert into employee values(16,'C',2652);
insert into employee values(17,'C',65);
(一)使用median函数的情况
select id,company,salary
from (
select tmp.* ,median(r_num) over(partition by company) med
from (
select e.*, row_number() over(partition by company order by salary) r_num
from employee e) tmp)
where abs(r_num-med)<=0.5;
程序解释:利用median求出的中位数是数学上的定义,不是我们所需要的.所以利用median进行改造.思路:获取每一份组的行号row_number(对应的新列记为r_num)和median(对应的新列记为med),当abs(r_num-med)<=0.5时,说明取到了中位数;
统计结果如下:
(二)不使用median函数的情况
（1）----->>>对应的第一种方法
这种方法需要区分不同分组数据量的奇偶性，在使用where的时候需要考虑单值和多值混合查询。
具体查询语句：
select id,company,salary
from (
select e.*, count(*) over(partition by company) x,row_number() over(partition by company order by salary) r_num
from employee e) tmp
where r_num in (ceil(x/2),x/2+1);
最终的查询结果：
值得注意的是where r_num in (ceil(x/2),x/2+1)的设定。当x是奇数时，(ceil(x/2),x/2+1)中只有一个有效（即ceil(x/2)=（x+1）/2）;当x是偶数时，(ceil(x/2),x/2+1)=（x/2,x/2+1）就是最中间的一组数据。
这类方法的其它查询案例,大多需要通过group by+连接构造,相对于上面的比较复杂,本文暂且不考虑其它方法.
（2）----->>>对应的第一种方法
select e1.id,e1.company,e1.salary
from employee e1,employee e2
where e1.company=e2.company(+)
group by e1.company,e1.salary,e1.id
having sum(decode(e1.salary-e2.salary,0,1,0))
>=abs(sum(sign(e1.salary-e2.salary)))
order by e1.id;
程序分解：以A公司为例，共有6条数据。
上述程序中核心思路是采用自连接+having条件。having条件构造的特别巧妙。所以将对其进一步分解：如下图所示。
中间的命令行截图为A公司数据的自连接结果(按照salary)排序。其中“+”表示大于当前对象的样例个数(后面的具体数字)；“0”表示相等；“-”表示小于。sum(sign())表示having中的部分结果。x=sign(a)为符号函数，a>0,x=1;a=0,x=0,a<0,x=-1.
其中having的统计结果如下图所示，每一组中相等的情况中只有一种图表中的A列(A公司正好没有重复数据)，abs()的统计结果为(图表中的B列)，具体计算方法见上图（关联结果）
（2）----->>>对应的第二种方法
思路仍然是从中位数的定义出发，只不过引用的核心函数是row_number,和count（*）。其中row_number的使用方法，请查看oracle学习笔记(六)：oracle中排序函数及其应用_数据库_qq_40584718的博客-CSDN博客 。
具体程序(来源于leetcode)如下：
/* Write your PL/SQL query statement below */
select
id, company, salary
from
(select
id, company, salary,
row_number() over (partition by company order by salary) as rn, -- 各薪水记录在其公司内的顺序编号
count(1) over (partition by company) as cnt -- 各公司的薪水记录数
from employee
)
where abs(rn - (cnt+1)/2) < 1 -- 顺序编号在公司薪水记录数中间的，即为中位数
核心程序解读：
(1) row_number() over() 按照公司分组，并按照薪水排序，将该结果保存为新列rn;
(2) count(1) over()应该和count(*) over()效果相同，是用来统计不同公司的样例条数；
(3) where调价，这个程序的灵魂。rn可以理解为一个列表，where的运行过程可以理解为下图：
where abs(rn - (cnt+1)/2) < 1 -- 顺序编号在公司薪水记录数中间的，即为中位数
这里的<1说明rn中有（cnt+1）/2特别靠近的行号(或者理解为索引或位置)存在。举例说明：
假如rn=[1,2,...8],则（cnt+1）/2=4.5，中对数对应的索引为4和5；
若rn=[1,2,...,7],则（cnt+1）/2=4，那么这个中位数对应的索引只能是4.
浙公网安备 33010602011771号