SQLD 특강 5일차

VIEW

CREATE (OR REPLACE) VIEW VIEW_NAME (COL1, COL2, COL3) AS
SELECT (COLUMN, AVG ...) FROM (ORIGINAL TABLE, GROUDED TABLE...)
WITH READ ONLY;

그룹함수: ROW기준의 계산을 위한 함수

• ROLLUP: 소계 생성
• CUBE: 다차원 소계 생성(인자로 a, b,를 넣으면 a소계, b 소계, 전체 소계 출력), ROLLUP이 더 많이 사용됨
• GROUPING SETS: GROUP BY함수 순서와는 상관 없이 특정항목에 대한 소계 계산

윈도우함수

• OVER가 필수적으로 들어감

순위 함수

• RANK() OVER(ORDER BY ~): 2등이 두명일 경우 1, 2, 2, 4, 5...
• DENSE_RANK OVER(ORDER BY ~): 2등이 두명일 경우 1, 2, 2, 3, 4...
• ROW_NUMBER() OVER(ORDER BY ~): 1부터 순서대로 쭉 부여 1,2,3,4...
• PARTITION BY: 지정한 파티션 마다 따로 순위가 매겨짐

집계함수

SUM: 합계

MAX: 최대값

MIN: 최소값

AVG: 평균

COUNT: 조건에 해당하는 컬럼 숫자 출력

MAX(SALARY) OVER (PARTITION BY DEPARTMENT_ID) as max, 	--해당 인물이 속한 부서에서 가장 높은 급여 
MIN(SALARY) OVER (PARTITION BY DEPARTMENT_ID) as min 	--해당 인물이 속한 부서에서 가장 낮은 급여 
FROM EMPLOYEES
ORDER BY DEPARTMENT_ID, SALARY;

행 순서 관련 함수

3. 그룹 내 행 순서 함수

1) FIRST_VALUE

파티션별 윈도우에서 가장 먼저 나온 값을 구한다. 공동 등수를 인정하지 않고 처음 나온 행만 가져오며 MIN함수를 쓰는 것과 결과가 동일하다.

2) LAST_VALUE

파티션별 윈도우에서 가장 마지막에 나온 값을 구한다. 공동 등수를 인정하지 않고 마지막에 나온 행만 가져오며 MAX함수를 쓰는 것과 결과가 동일하다.

3) LAG

이전 몇 번째 행의 값을 가져오는 함수이다. 인자를 최대 3개까지 가진다.

4) LEAD

이후 몇 번째 행의 값을 가져오는 함수로 LAG와 마찬가지로 인자를 최대 3개까지 갖는다.
--없는 경우 NULL

SELECT EMPLOYEE_ID AS 사원번호,
FIRST_NAME AS 사원명,
DEPARTMENT_ID AS 부서번호,
SALARY AS 급여,
FIRST_VALUE(FIRST_NAME) OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY) AS 부서내최저금액_사람,
LAST_VALUE(FIRST_NAME) OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS 부서내최고금액_사람,
LAG(SALARY, 1) OVER(ORDER BY SALARY) AS 이전사람의급여,
LEAD(SALARY, 1) OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY) AS 이후사람의급여
FROM EMPLOYEES E
ORDER BY 부서번호, 급여;

비율 관련 함수

4. 그룹 내 비율 함수
   1) RATIO_TO_REPORT

파티션 내 전체 SUM값에 대한 행별 컬럼 값의 백분율을 소수점으로 출력한다. 결과값은 0~1 사이이며 개별 비율의 합을 구하면 1이다.

2) PERCENT_RANK

파티션별로 가장 먼저 나오는 값을 0, 가장 마지막에 나오는 값을 1로 해서 행 순서별 백분율 출력한다. 구간을 나누어 백분율로 표시한다.

3) CUME_DIST

파티션별 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율을구한다.

4) NTILE

파티션별 전체 건수를 ARGUMENT값으로 N등분한 결과를 출력한다.

--14를 4로 나누면 몫이 3 나머지가 2, 나머지2는 앞의 조부터 할당되기 때문에 4,4,3,3으로 나눔

SELECT 	DEPARTMENT_ID,
		SALARY,
		RATIO_TO_REPORT(SALARY) OVER() AS 급여비율,
		PERCENT_RANK() OVER (ORDER BY SALARY) AS "급여비율순서별백분율", 
	    CUME_DIST() OVER(ORDER BY SALARY) "급여비율순서별누적백분율", 
    	NTILE(4) OVER(ORDER BY SALARY) "급여비율순서별등분"	
FROM	EMPLOYEES E
WHERE	DEPARTMENT_ID = 60
ORDER BY DEPARTMENT_ID;