分析函数改善自连接

下面我们看一下各种求中间值的写法及性能比较。

1.数据准备

DROP table Student;

CREATE TABLE Student

(StuName VARCHAR2(10) NOT NULL,

 Subject VARCHAR2(10),

 Score INTEGER);

 

insert into Student values('A1','数学',60);

insert into Student values('A2','数学',90);

insert into Student values('A3','英语',80);

insert into Student values('A4','英语',40);

insert into Student values('A5','数学',70);

insert into Student values('A6','数学',80);

insert into Student values('A7','英语',80);

insert into Student values('A8','英语',100);

insert into Student values('A9','英语',70);

commit;

2.检索要求

求每个科目的中间值，

3.SQL写法及性能比较

select s1.Subject,s1.Score

from Student s1,Student s2

where s1.Subject=s2.Subject

group by s1.Subject,s1.Score

having sum(case when s1.Score<=s2.Score then 1 else 0 end)>=count(*)/2

        and sum(case when s1.Score>=s2.Score then 1 else 0 end) >=count(*)/2;

with tmp as

(select s.SUBJECT

       ,s.Score

          ,ROW_NUMBER() over (partition by s.SUBJECT order by s.Score desc) as rn1

          ,ROW_NUMBER() over (partition by s.SUBJECT order by s.Score asc) as rn2

 from Student s)

select distinct t.SUBJECT,t.Score

from tmp t

where t.rn1 in (rn2-1,rn2,rn2+1);

通过上面两种写法比较，分析函数减少一次表扫描，还是分析函数性能更好。