sql window function

zdc253212956

　　The SQL window function allows user to define a set of rows related to current row which is called a frame. Then user can apply aggregate function to the frame. It is standardized in SQL 2003 and supported by major RDBMS like:

•   SQL Server 2005+
  
•   Oracle
  
•   DB2
  
•   Postgre SQL
  

　　The window function can solve problem like ranking very easily without resorting to complex sub queries.
　　Here is an example. First comes the schema and sample data:

--dbext:profile=uca_dw_sh
create table empsalary (
empno       bigint                not null,
depname     varchar(30)           not null,
salary      decimal(20, 2)        not null
constraint PK_empsalary primary key (empno)
)
go
insert into empsalary(depname, empno, salary)
values
('develop',8,6000),
('develop',10,5200),
('develop',11,5200),
('develop',9,4500),
('develop',7,4200),
('personnel',2,3900),
('personnel',5,3500),
('sales',1,5000),
('sales',4,4800),
('sales',3,4800)
go

　　Then here are some SQLs to demonstrate the use of window function

-- list employee who is paid above department average
select t1.*
from ( select empno,
depname,
salary,
avg(salary) over (partition by depname) as dep_avg
from empsalary
) t1
where t1.salary > t1.dep_avg
go
-- rank employee accoring to salary within his department
select empno,
depname,
salary,
rank() over (partition by depname order by salary desc)
from empsalary
go

　　
　　A good explanation of the use of window function can be found:
http://www.postgresql.org/docs/8.4/interactive/tutorial-window.html