VII. SQL 응용_02. 응용 SQL 작성하기

1) 집계 함수

데이터 분석 함수 : 총합, 평균 드으이 데이터 분석을 위해 복수 행 기준의 데이터를 모아서 처리하는 것을 목적으로 하는 다중 행 함수

단일 행 기반 산출이 아닌 (복수행)을 그룹별로 모아 그룹당 단일 계산 결과 반환

(GROUP BY) 구문을 활용하여 복수 행 그룹핑

(SELECT), (HAVING), (ORDER BY) 등의 구문에 활용

 

다중행 연산자 : 서브쿼리의 결과가 여러개의 튜플들을 반환하는 다중행 서브쿼리에서 사용되는 연산자로 IN, ANY, SOME, ALL, EXISTS 사용, 다중행 비교 연산자는 단일 행 비교 연산자(<, >, = <>)와 결합하여 사용 가능

IN	리턴되는 값 중 조건에 해당하는 값이 있으면 참
ANY, SOME	서브쿼리에 의해 리턴되는 각 값과 조건을 비교하여 하나 이상 만족시 참
ALL	값을 서브쿼리에 의해 리턴되는 모든값과 조건값을 비교하여 모든값을 만족해야 참
EXISTS	메인 쿼리의 비교조건이 서브쿼리의 결과 중 만족하는 값이 하나라도 존재시 참

 

다중행 함수 종류 : SQL 표준에서 데이터 튜플간 상호 연관 및 계산 분석을 위한 함수로 집계함수, 그룹함수, 윈도함수가 있음

집계함수 : 여러행 또는 테이블 전체 행으로부터 하나의 결과값을 반환하는 함수

COUNT	복수 행의 줄 수 를 구하는 함수
SUM	복수 행의 해당 컬럼간 합계를 계산하는 함수
AVG	복수 행의 해당 컬럼간 평균를 계산하는 함수
MAX	복수 행의 해당 컬럼 중 최대값을 계산하는 함수
MIN	복수 행의 해당 컬럼 중 최소값을 계산하는 함수
STDDEN	복수 행의 해당 컬럼간 표준편차를 계산하는 함수
VARIANCE	복수 행의 해당 컬럼간 분산을 계산하는 함수

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

- GROUP BY : SQL 에서 WHERE 구문을 활용하여 조건별 대상 ROW 선택, 복수 ROW 대상의 데이터 분석시 그룹핑 대상이 되는 부분 선별시 사용, GROUP BY 구문은 실제 구체적 데이터 분석값을 보고자 하는 컬럼단위 선정시 사용 기준

NULL값을 가지는 ROW는 제외한 후 산출

SELECT에서 사용하는 것과 같은 ALIAS 사용 불가

WHERE 구문 안에 포함되지 않음

WHERE 구문은 GROUP BY보다 먼저 실행되고 대상이 되는 단일 행을 사전에 선별하는 역할

 

- HAVING : WHERE 무누내에서 사용할 수 없는 집계 함수의 구문을 적용하여 (복수행의 계산 결과를 조건별로 적용)하는데 사용, 일반적으로 GROUP BY 뒤에 기재하며, GROUP BY 구문의 (기준 항목)이나 소그룹 집계함수를 활용한 (조건을 적용)하는데 사용, GROUP BY 및 집계 함수에 대한 (WHERE 구문)

 

2) 그룹 함수 : 테이블 전체 행을 하나 이상의 컬럼을 기준으로 컬럼 값에 따라 그룹화하여 그룹별로 결과를 출력하는 함수, ROLLUP 함수(중간 집계값 산출), CUBE 함수(다차원 집계), GROUPING SETS 함수(개별 집계)

- ROLLUP : ROLLUP에 의해 지정된 컬럼은 소계(소그룹의 합계) 등 중간 집계 값 산출을 위한 그룹 함수, 지정 컬럼의 수보다 하나 더 큰 레벨만큼 중간 집계 값 생성, ROLLUP의 지정 컬럼은 계층별로 구성되므로 순서에 따라 수행결과가 바뀜

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

 

- CUBE : 결합 가능한 모든 값에 대해 다차원 집계를 생성하는 그룹함수, 연산이 많아 시스템에 부담

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

 

- GROUPING SETS : 집계 대상 컬럼들에 대한 개별 집계를 구할 수 있으며 ROLLUP이나 CUBE와 달리 컬럼간 순서와 무관한 결과를 얻을 수 있는 그룹 함수, GROUPING SETS를 이용해 다양한 소계 집합을 만들 수 있음, ORDER BY를 사용하여 집계 대상 그룹과의 표시 순서를 조정하여 체계적으로 보여줄 수 있음

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

 

3) 윈도 함수 : 데이터베이스를 사용한 온라인 분석 처리 용도로 사용하기 위해 표준 SQL에 추가된 함수(=OLAP 함수)

SELECT 함수명
   OVER
   ([PARTITION BY 컬럼1, ...]
   [ORDER BY 컬럼A, ...])
   FROM 테이블명

- 순위함수 : 레코드의 순위를 계산하는 함수

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위, ...)