Ranking & Windowing

한 컬럼에 대해 group 별로 묶은 뒤

각 group 내에서 순위를 부여

 

순위 별로 정렬하려면 ORDER BY 추가

top10처럼 상위권 추출하고 싶다면 LIMIT n 추가

SELECT ID, dept_name,
	RANK () OVER (PARTITION BY dept_name ORDER BY GPA DESC)
AS dept_rank
FROM dept_grades
ORDER BY dept_name, dept_rank;

 

Type of Ranking

• RANK : 중복 값들에는 동일 순위로 표시, 중복 순위 다음 값은 중복 개수만큼 떨어진 순위로 출력

  

  
  

  • SELECT empNo, empName, salary,
    RANK() OVER (ORDER BY salary DESC) RANK등수
    FROM employee;

• 

  DENSE_RANK: 순위 사이 간격을 없앤 것

  • SELECT empNo, empName, salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) DENSE_RANK등수
    FROM employee;

• ROW_NUMBER : 중복 값들에 대해서도 순차적인 순위를 표시하도록 출력하는 함수

  

  • SELECT empNo, empName, salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) ROW_NUMBER등수
    FROM employee;

 

 

+) PERCENT_RANK : within partition - 백분율에서 순위 갖기 - 1등은 1, 꼴등은 0

-- employees테이블에서 급여가 $15,500이고, 수수료가 5%인 불확실한 종업원의 Percent Rank를 계산
SELECT 
	PERCENT_RANK(15000, .05) 
    WITHIN GROUP (ORDER BY salary, commission_pct) "Percent-Rank" 
FROM employees;

-- 출처 : http://statwith.com/percent_rank-oracle-function-list/
-- Percent-Rank
------------
--  .971962617

-- 종업원마다 부서내에서 종업원 급여의 Percent Rank를 계산한다.
SELECT department_id, last_name, salary, 
       PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS pr
FROM   employees
ORDER BY pr, salary;
-- 출처: http://statwith.com/percent_rank-oracle-function-list/

 

+) CUME_DIST : cumulative distribution : 그룹에 존재하는 값의 누적 분포 값을 계산

-- employees테이블에서 종업원 중에서 급여가 15,500달러이고, 수수료가 5%인 종업원의 누적 분포를 계산
SELECT CUME_DIST(15500, .05) WITHIN GROUP
   (ORDER BY salary, commission_pct) "Cume-Dist of 15500" 
   FROM employees;
-- 출처: https://statwith.tistory.com/415
-- Cume-Dist of 15500
------------------
--        .972222222

-- 구매 부분의 각 종업원의 급여 퍼센트를 계산한다. 예를 들어, 종업원의 40%가 Himuro의 급여 이하의 급여를 받는 것을 알 수 있다.
SELECT job_id, last_name, salary, CUME_DIST() 
   OVER (PARTITION BY job_id ORDER BY salary) AS cume_dist
   FROM employees
   WHERE job_id LIKE 'PU%';
-- 출처:https://statwith.tistory.com/415

 

 

NTILE

rank 뒤에 함께 적어주는 숫자만큼 등분을 하는 함수

1-9등급 매기기 좋음

-- 직원들 데이터에 대해서 salary 순서를 기준으로 4등분
SELECT empNo, empName, salary,
NTILE(4) OVER (ORDER BY salary DESC) NTILE등분
FROM employee;

 

 

출처 : https://doorbw.tistory.com/221

 

PARTITION BY

특정 속성 별로 구분하여 순위를 매기고자 할 때

-- 모든 사람이 아니라 직급별 순위를 매기고 싶다면
SELECT empName, job, salary,
RANK() OVER (PARTITION BY job ORDER BY salary DESC) RANK등수
FROM employee;

 

 

---

 

Windowing

이동평균선 구하기에 유용

(각 날짜에 대한 판매 값을 고려하여 각 날짜에 대해 해당 날짜, 전날 및 익일의 판매 평균을 계산하기)

select date, 
	sum(value) over (order by date between rows 1 preceding and 1 following)
from sales

 

 

범위 표현 예시

select date, 
	sum(value) over (order by date between rows unbounded preceding and current)
from sales


select date, 
	sum(value) over (order by date rows unbounded preceding)
from sales


select date, 
	sum(value) over (order by date range between 10 preceding and current row)
from sales
-- All rows with values between current row value –10 to current value


select date, 
	sum(value) over (order by date range interval 10 day preceding)
from sales
-- Not including current row

 

 

Window + Partition

-- Find total balance of each account after each transaction on the account
--  value is positive for a deposit and negative for a withdrawal
-- 그럼 잔액은 나중에 그 값을 다 더하기만 하면 됨

select account_number, date_time,
  sum (value) over
    (partition by account_number
    order by date_time
    rows unbounded preceding)
  as balance
from transaction
order by account_number, date_time