디비 이야기 12. 데이터 값 순위 매기기와 집합함수

1. RANK <-> DENSE_RANK

= 공통순위를 출력하되 공통순위만큼 건너뛰어 다음 순위 출력한다

EX) RANK() OVER([PARTITION BY 컬럼명] ORDER BY 컬럼명) 

-> 그룹으로 묶어서 순위를 매겨야할 내용 : partition 

   순위를 매길 함수 : order by 

 

ex) SELECT team_id, RANK() OVER(PARTITION BY team_id ORDER BY user_win desc) RANK FROM USERS;

 

2. ROW_NUMBER / ROWNUM

= 공통순위 없이 출력한다 / 각 행에 부여하는 일련변호 

테이블에서 원하는 만큼 행만 가져오고 싶을시 WHERE 절에서 사용한다 (+ 단일행 연산자)

EX) SELECT team_id, ROW_NUMBER() OVER(PARTITION BY team_id ORDER BY user_tot desc) RANK FROM USERS;

-> 단, PARTITION BY를 줄 컬럼에 중복된 값을 기준으로 등급을 매긴다 

(수학이란 과목명을 PARTITION BY로 주고 점수를 ORDER BY로 줄 경우 수학에 대한 등급이 매겨진다)

 

EX) SELECT team_id FROM USERS WHERE ROWNUM < 2;

 

3. OVER 함수 

= 그룹함수, 분석함수와 같이 쓰이며

GROUP BY / ORDER BY를 이용한 서브쿼리 계산 위해 등장함 

 

4. TOP 함수 (= LIMIT)

= 결과 집합으로 출력되는 행의 갯수를 제한한다 

문법 : SELECT TOP(반환할 행의 수) 컬럼명 FROM 테이블명 WHERE 조건 

EX) SELECT TOP(N) team_id FROM USERS (SQL)

-> N개 이상의 건을 조회할시 사용한다 

 

EX) SELECT * FROM DEPT WHERE DEPTNO LIMIT 3; (MYSQL)

-> 3줄만 나오게 된다 / 무조건 WHERE절 뒤에서 사용해야한다 

 

=> TOP / ROWNUM은 ORDER BY 절을 사용함에 따라 성능차이가 있다! 

 

 

5. FIRST_VALUE(=MIN) <-> LAST_VALUE(= MAX)

= 파티션별로 윈도우에서 가장 먼저 나온 값을 구한다

 

EX) SELECT DEPTNO, ENAME, SAL
     , FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) as DEPT_RICH
  FROM EMP;

 

6. LAG(컬럼명, 몇번쨰 앞 행, 기본값) <-> LEAD

= 선행, 후행 ROW 값을 가져온다. 

* 몇번째 앞 행의 정보를 가져와 세팅한다. 그리고 해당 ROW 선행/후행전까지 기본값으로 세팅된다. 

 

EX) SELECT ENAME, HIREDATE, SAL
     , LAG(SAL, 2, 0) OVER (ORDER BY HIREDATE) as PREV_SAL  // 2번이면 2번재까지 기본값으로 채워진다. 
  FROM EMP
 WHERE JOB = 'SALESMAN' ;

 

7. RATIO_TO_REPORT(컬럼명)

= 파티션 내 전체 SUM 값에 대한 컬럼 값의 백분율 소수점으로 구한다 

EX) SELECT ENAME, SAL
     , ROUND(RATIO_TO_REPORT(SAL) OVER (), 2) as R_R
  FROM EMP
 WHERE JOB = 'SALESMAN';

=> 전체합은 1이된다 

 

8. PERCENT_RANK()

= 파티션별로 젤 먼저 나온 걸 0 / 젤 늦게 나온것을 1로 하고, 값이 아닌 행의 순서로 백분률을 구한다

EX) SELECT DEPTNO, ENAME, SAL
     , PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as P_R
  FROM EMP;

 

9. CUME_DIST()

= 파티션 별로 전체 행보다 작거나 같은 건수에 대한 누적백분률을 구한다

EX) SELECT DEPTNO, ENAME, SAL
     , CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as P_R
  FROM EMP;

 

10. NTITLE(몇 등분할 수)

= 파티션별로 전체 건수를 N등분한 결과 

EX) select NTILE(5) OVER(ORDER BY FIRST_NAME) AS TEST 
     , ID
     , FIRST_NAME
     , LAST_NAME
  FROM sql_test_a;

// 순서대로 앞에 있는게 여러개가 된다. 나머지는 중복없이 번호가 할당된다.