반응형
/************************************************************************************************
-- 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;



반응형

+ Recent posts