반응형
/************************************************************************************************
-- Title : [PGS9.2] Scripts using windows function(rank function)
-- Reference : dbrang.tistory.com
-- Key word : postgresql 윈도우 함수 랭크 함수 랭킹 함수 window function rank function ranking
순위함수
************************************************************************************************/
-- Title : [PGS9.2] Scripts using windows function(rank function)
-- Reference : dbrang.tistory.com
-- Key word : postgresql 윈도우 함수 랭크 함수 랭킹 함수 window function rank function ranking
순위함수
************************************************************************************************/
-------------
-- initiation
-------------
drop table usertbl;
create table usertbl
( userid char(8) ,
name varchar(10) ,
birthyear int ,
addr char(4) ,
mobile1 char(3),
mobile2 char(8),
height smallint
);
insert into usertbl values(n'pjs', n'박지성', 1983, n'서울', n'011', n'1111111', 181);
insert into usertbl values(n'pjy', n'박주영', 1986, n'경기', n'011', n'2222222', 175);
insert into usertbl values(n'jjj', n'조재진', 1986, n'충북', n'019', n'3333333', 179);
insert into usertbl values(n'lcs', n'이천수', 1983, n'인천', n'011', n'4444444', 179);
insert into usertbl values(n'ajh', n'안정환', 1979, n'강원', null , null , 182);
insert into usertbl values(n'kni', n'김남일', 1977, n'경북', n'016', n'6666666', 183);
insert into usertbl values(n'lyp', n'이영표', 1983, n'전북', null , null , 178);
insert into usertbl values(n'skh', n'설기현', 1978, n'서울', n'011', n'8888888', 182);
insert into usertbl values(n'sjk', n'송종국', 1979, n'경기', n'018', n'9999999', 178);
insert into usertbl values(n'cjc', n'최진철', 1975, n'제주', n'019', n'0000000', 185);
insert into usertbl values(n'kkr', n'김기리', 1960, n'서울', n'017', n'1111555', 160);
insert into usertbl values(n'nhc', n'노홍칠', 2000, n'서울', n'016', n'2222333', 175);
insert into usertbl values(n'sbr', n'신보라', 1965, n'경기', n'010', n'9999999', 171);
------------------
-- window function
------------------
-- over()
select *
, sum(height) over(order by addr)
from usertbl
order by addr;
-- row_number() over(order by~)
select *
, row_number() over(order by height desc)
from usertbl;
select *
, row_number() over(order by height desc, name asc)
from usertbl
order by height desc;
-- row_number() over(partition by~ orer by~)
select *
, row_number() over(partition by addr order by height desc, name asc)
from usertbl
order by addr, height desc;
-- dense_rank() over(order by~)
select *
, dense_rank() over(order by height desc)
from usertbl
order by height desc;
-- rank() over(order by~)
select *
, rank() over(order by height desc)
from usertbl
order by height desc;
-- ntile() over(order by~)
select *
, ntile(2) over(order by height desc)
from usertbl
order by height desc;
select *
, ntile(4) over(order by height desc)
from usertbl
order by height desc;
-- percent_rank() : rank - 1/ all_rows - 1
select *
, percent_rank() over(order by height desc)
from usertbl
order by height desc;
-- cume_dist()
select *
, cume_dist() over(order by height desc)
from usertbl
order by height desc;
-- first_value()
select name, addr, height, birthyear, '■' "X"
, first_value(birthyear) over(partition by addr order by addr, height)
from usertbl
where addr in ('서울', '경기')
order by addr, height;
-- last_value() :: don't get it!!!
select name, addr, height, birthyear, '■' "X"
, last_value(birthyear) over(partition by addr order by addr, height)
from usertbl
where addr in ('서울', '경기')
order by addr, height;
-- nth_value()
select *
, first_value(birthyear) over(partition by addr order by height)
from usertbl
order by addr, birthyear desc;
반응형