常用窗口函数

常用窗口函数

创建表

CREATE TABLE powersi.t1 (
	deptno varchar(100) NULL,
	ename varchar(100) NULL,
	sal int4 NULL
)

插入数据

INSERT INTO powersi.t1
(deptno, ename, sal)
VALUES('20', 'ford', 3000);
INSERT INTO powersi.t1
(deptno, ename, sal)
VALUES('20', 'JONES', 2975);
INSERT INTO powersi.t1
(deptno, ename, sal)
VALUES('20', 'ADAMS', 1100);
INSERT INTO powersi.t1
(deptno, ename, sal)
VALUES('20', 'SMITH', 800);
INSERT INTO powersi.t1
(deptno, ename, sal)
VALUES('20', 'SMITH2', 900);
INSERT INTO powersi.t1
(deptno, ename, sal)
VALUES('10', 'jaccka', 5000);
INSERT INTO powersi.t1
(deptno, ename, sal)
VALUES('10', 'king', 5000);
INSERT INTO powersi.t1
(deptno, ename, sal)
VALUES('10', 'clark', 2450);
INSERT INTO powersi.t1
(deptno, ename, sal)
VALUES('10', 'welan', 2450);
INSERT INTO powersi.t1
(deptno, ename, sal)
VALUES('10', 'tebage', 1300);
INSERT INTO powersi.t1
(deptno, ename, sal)
VALUES('10', 'miller', 1300);

原表数据

select * from powersi.t1

rank

select 
deptno ,
ename ,
sal ,
rank() over ( order by sal desc) as rank
from powersi.t1 t 

row_number

select 
deptno ,
ename ,
sal ,
row_number () over ( order by sal desc) as rank
from powersi.t1

dense_rank

select 
deptno ,
ename ,
sal ,
dense_rank () over ( order by sal desc) as rank
from t1 t 

lag

select 
deptno ,
ename ,
sal ,
lag (sal) over ( order by ename ) as pre_sal,
sal - lag (sal) over ( order by ename ) as sal_diff
from t1 t

sum

select 
deptno ,
ename ,
sal ,
sum (sal) over (partition by deptno  ) as rank
from t1 t

ntile

select 
deptno ,
ename ,
sal ,
ntile (5) over (order by sal  ) as rank
from t1 t