18. 응용 SQL 작성하기

데이터 분석 함수

1. 집계 함수

• COUNT : 복수 행의 줄 수를 반환하는 함수
• SUM : 복수 행의 해당 칼럼 간의 합계를 계산하는 함수
• AVG : 복수 행의 해당 칼럼 간의 평균을 계산하는 함수
• MAX : 복수 행의 해당 칼럼 중 최댓값을 계산하는 함수
• MIN : 복수 행의 해당 칼럼 중 최솟값을 계산하는 함수
• STDDEV : 복수 행의 해당 칼럼 간의 표준편차를 계산하는 함수
• VARIAN : 복수 행의 해당 칼럼 간의 분산을 계산하는 함수

SELECT 컬럼1, 컬럼2, ...
FROM 테이블명
[WHERE 조건]
GROUP BY 컬럼1, 컬럼2, ...
[HAVING 조건식(집계함수 포함)]

2. 그룹 함수

• ROLLUP : 중간 집계 값을 산출하기 위한 그룹 함수
  

  SELECT 컬럼1, 컬럼2, ..., 집계함수
  FROM 테이블명
  [WHERE ...]
  GROUP BY [컬럼1, ...] ROLLUP 컬럼
  [HAVING ...]
  [ORDER BY ...]​

• CUBE : 결합 가능한 모든 값에 대해 다차원 집계를 생성하는 그룹 함수
  

  SELECT 컬럼1, 컬럼2, ..., 집계함수
  FROM 테이블명
  [WHERE ...]
  GROUP BY [컬럼1, ...] CUBE (컬럼1, 컬럼2, ...)
  [HAVING ...]
  [ORDER BY ...]

• GROUPING SETS : 집계 대상 칼럼들에 대한 개별 집계를 구할 수 있으며, ROLLUP이나 CUBE와는 달리 칼럼 간 순서와 무관한 결과를 얻을 수 있는 그룹 함수
  

  SELECT 컬럼1, 컬럼2, ..., 집계함수
  FROM 테이블명
  [WHERE ...]
  GROUP BY [컬럼1, ...] GROUPING SETS (컬럼1, 컬럼2, ...)
  [HAVING ...]
  [ORDER BY ...]​

윈도 함수

1. 순위 함수

• RANK : 특정항목(칼럼)에 대한 순위를 구하는 함수로 동일 순위의 레코드 존재 시 후순위는 넘어감(2위가 3개인 레코드인 경우 : 2위, 2위, 2위, 5위, 6위)
• DENSE_RANK : 레코드의 순위를 계산하는 함수로 동일 순위의 레코드 존재 시에도 후순위를 넘어가지 않음(2위가 3개인 레코드인 경우 : 2위, 2위, 2위, 3위, 4위)
• ROW_NUMBER : 레코드의 순위를 계산하는 함수로 동일 순위의 값이 존재해도 이와 무관하게 연속 번호를 부여(2위가 3개인 레코드인 경우 : 2위, 3위, 4위, 5위, 6위)

SELECT NAME, SALARY,
RANK() OVER (ORDER BY SALARY DESC) AS A,
DENSE_RANK() OVER (ORDER BY SALARY DESC) AS B,
ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS C
FROM EMPLOYEE;

/*
이름(NAME)	연봉(SALARY)	A	B	C
장길산		3000		1	1	1
임꺽정		2500		2	2	2
홍길동		2500		2	2	3
김철수		2200		4	3	4
한유리		2000		5	4	5
*/

2. 행 순서 함수

• FIRST_VALUE : 파티션 별 윈도에서 가장 먼저 나오는 값을 찾음, FIRST_VALUE의 OVER 절에서 내림차순일 경우 MAX와 동일한 결과, OVER 절에서 오름차순일 경우 MIN과 동일한 결과를 출력
• LAST_VALUE : 파티션 별 윈도에서 가장 늦게 나오는 값을 찾음, LAST_VALUE의 OVER 절에서 내림차순일 경우 MIN과 동일한 결과, OVER 절에서 오름차순일 경우 MAX와 동일한 결과를 출력
• LAG : 파티션 별 윈도에서 이전 로우의 값 반환
• LEAD : 파티션 별 윈도에서 이후 로우의 값 반환

SELECT NAME, SALARY,
FIRST_VALUE(NAME) OVER (ORDER BY SALARY DESC) AS A,
LAST_VALUE(NAME) OVER (ORDER BY SALARY DESC) AS B,
LAG(NAME) OVER (ORDER BY SALARY DESC) AS C,
LEAD(NAME) OVER (ORDER BY SALARY DESC) AS D
FROM EMPLOYEE;

/*
이름(NAME)	연봉(SALARY)	A	B	C	D
장길산		3000		장길산	한유리		임꺽정
임꺽정		2500		장길산	한유리	장길산	홍길동
홍길동		2500		장길산	한유리	임꺽정	김철수
김철수		2200		장길산	한유리	홍길동	한유리
한유리		2000		장길산	한유리	김철수
*/

3. 그룹 내 비율 함수

• RATIO_TO_REPORT : 주어진 그룹에 대해 합을 기준으로 각 로우의 상대적 비율을 반환하는 함수로, 결괏값은 0~1의 범위 값을 가지며 OVER 괄호 안에 칼럼 생략 시 그룹은 테이블 전체가 대상임
• PERCENT_RANK : 주어진 그룹에 대해 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여, 값이 아닌 행의 순서별 백분율을 구하는 함수로, 결괏값은 0~1의 범위 값을 가짐

SELECT NAME, SALARY,
RATIO_TO_REPORT(SALARY) OVER () AS A,
PERCENT_RANK() OVER (ORDER BY SALARY DESC) AS B
FROM EMPLOYEE;

/*
이름(NAME)	연봉(SALARY)	A	B
장길산		3000		0.2459	0
임꺽정		2500		0.2459	0.25
홍길동		2500		0.2459	0.25
김철수		2200		0.1803	0.75
한유리		2000		0.1639	1
*/