정보처리기사 실기 - SQL 응용 : 집계성 SQL 작성

정보처리기사 실기 - SQL 응용 : 집계성 SQL 작성

 

집계성 SQL 작성

 

① 데이터 분석 함수

 

(1) 데이터 분석 함수의 정의 및 목적 

 

• 관계형 데이터베이스에서 주로 단일 행 기준 처리
• 총합, 평균 등의 데이터 분석을 위해 복수 행 기준 데이터를 모아서 처리하는 것이 필수
• 다중 행 처리를 목적으로 하는 다중 행 함수 존재

 

(2) 데이터 분석 함수의 특성 

 

• 단일 행을 기반으로 산출하지 않고 복수 행을 그룹별로 모아 놓고 그룹당 단일 계산 결과 반환
• GROUP BY 구문을 활용하여 복수 행을 그룹핑
• SELECT, HAVING, ORDER BY 등의 구문에 활용

 

(3) 데이터 분석 함수의 유형

 

1. 집계 함수(AGGREGATE FUNCTION) 
2. 그룹 함수(GROUP FUNCTION) 
3. 윈도우 함수(WINDOW FUNCTION) 

• 집계 함수는 명령어도 상대적으로 단순하며 이를 기본으로 그룹 함수나 윈도우 함수에도 적용
• 집계 함수도 그룹 함수의 한 부분이며 통합하여 언급

 

② 집계 함수

SELECT [컬럼1, 컬럼2, 컬럼3,..] <GROUP FUNCTION>
FROM 테이블명 [WHERE ...]
GROUP BY 컬럼1, 컬럼2, 컬럼3,..
[HAVING 조건(GROUP FUNCTION 포함)]

• GROUP BY 구문을 활용하여 복수 행을 그룹핑하여 분석 결과 데이터 반환
• GROUP BY 구문 뒤 테이블을 구분하는 컬럼을 기재하여 그룹화하며 지정하는 열은 집약키 또는 그룹화 열
• HAVING 구문은 그룹화된 집합에 대한 조건 지정 시 사용
• WHERE 조건으로 지정된 데이터 집합으로부터 그룹화된 집합에 대한 조건 선택 시에 HAVING 사용
• HAVING 구문은 선택적, 상수나 집약 함수, 집약키를 사용 가능

 

(1) 집계 함수 분류

 

집계 함수	내용	입력값
COUNT(입력값)	복수 행의 줄(ROW) 수	*이나 상수인 경우 NULL이 포함된 행의 수 컬럼 적용 시 해당 입력값 내의 NULL이 아닌 ROW 수 산출
SUM(입력값)	복수 행의 해당 컬럼(COLUMN) 간의 합계	상수 입력 시 해당 ROW 수 * 상수의 값(1일 시 ROW COUNT) 컬럼 적용 시 해당 입력 값 내의 NULL이 아닌 값의 합계
AVG(입력값)	복수 행의 해당 컬럼 간의 평균	해당 입력값 내의 NULL이 아닌 값의 평균
MAX(입력값)	복수 행의 해당 컬럼 중 최댓값	해당 입력값 내의 최댓값 출력 문자열이나 날짜 데이터 형식에도 적용 가능
MIN(입력값)	복수 행의 해당 컬럼 중 최솟값	해당 입력값 내의 최솟값 출력 문자열이나 날짜 데이터 형식에도 적용 가능
STDDEV(입력값)	복수 행의 해당 컬럼 간의 표준편차	해당 입력값 내의 표준편차
VARIAN(입력값)	복수 행의 해당 컬럼 간의 분산	해당 입력값 내의 분산

 

(2) GROUP BY 구문

 

• 복수 ROW 대상의 데이터 분석 시 그룹핑 대상이 되는 부분 선별

1. NULL값을 가지는 ROW는 제외한 후 산출
2. SELECT에서 사용하는 것과 같은 ALIAS 사용 불가
3. WHERE 구문 안에 포함되지 않음
4. WHERE 구문은 GROUP BY보다 먼저 실행되고 대상이 되는 단일 행을 사전에 선별
5. GROUP BY 구문은 실제 구체적 데이터 분석 값을 보고자 하는 컬럼 단위를 선정할 때 사용되는 기준이 되며 조정을 통해 사용자가 원하는 분석 데이터를 볼 수 있게 해 준다

 

(3) HAVING 구문

 

• WHERE 구문 내에는 사용할 수 없는 집계 함수의 구문을 적용하여 복수 행의 계산 결과를 조건별로 적용하는 데 사용
• 일반적으로 GROUP BY 뒤에 기재하며 GROUP BY 구문의 기준 항목이나 소그룹 집계 함수를 활용한 조건을 적용하는 데 사용
• 쉽게 생각하면 GROUP BY 및 집계 함수에 대한 WHERE 구문

 

③ 그룹 함수

 

• 집계 함수와 유사한 개념으로 집계 함수를 포함하지만 여기서는 소그룹 간의 소계 및 중계 등의 중간 합계 분석 데이터를 산출하는 부분을 지칭
• ⁠소계 및 총계 등을 구하기 위해서 집계 함수만 사용한다면 레벨별 집계를 위한 각 단계별 데이터 질의어(DQL, Data Query Language)를 UNION ALL 등으로 결합하고 표시 단계 필요하나 그룹 함수를 사용한다면 단일 DQL만으로도 원하는 작업 가능

 

(1) 그룹 함수 유형

 

(가) ROLLUP 

 

• 소계 등 중간 집계값을 산출하기 위해 사용
• 지정 컬럼의 수보다 하나 더 큰 레벨만큼의 중간 집계값이 생성
• ROLLUP의 지정 컬럼은 계층별로 구성되기 때문에 순서가 바뀌면 수행 결과가 바뀜을 유의

SELECT [컬럼1, 컬럼2, 컬럼3, ..] <GROUP FUNCTION>
FROM 테이블명 [WHERE ...]
GROUP BY [컬럼 ..] ROLLUP <컬럼 ..>
[HAVING ...] [ORDER BY ...]

• 소계 집계 대상이 되는 컬럼을 ROLLUP 뒤에 기재
• 소계 집계 대상이 아닌 경우 GROUP BY 뒤에 기재
• SELECT 뒤에 포함되는 컬럼이 GROUP BY 또는 ROLLUP 뒤에 기재되어야 하는 것을 숙지하고 쿼리 작성
• 일반적으로 소계나 총합계 등의 경우 집계 대상이 되는 행의 그룹핑 앞 또는 뒤에 표현
• ORDER BY 구문을 활용해 계층 내 정렬에 사용이 가능하며 SQL의 결과를 보다 체계적으로 보여줌

 

(나) CUBE 

 

• 결합 가능한 모든 값에 대해 다차원 집계를 생성하는 그룹 함수
• 가능한 한 소집계 만을 생성하는 ROLLUP과 구별
• 내부적으로 대상 컬럼의 순서를 변경하여 또 한 번 쿼리 수행
• 총계는 양쪽 쿼리에서 모두 수행 후 한 쪽에서 제거되는 과정에 의해 ROLLUP에 비해 계산이 많음

SELECT [컬럼1, 컬럼2, 컬럼3, ..], <GROUP FUNCTION>
FROM 테이블명 [WHERE ...]
GROUP BY [컬럼 ..] CUBE <컬럼 ..>
[HAVING ...] [ORDER BY ...]

 

(다) GROUPING SETS 

 

• 다양한 소계 집합 생성 가능
• 집계 대상 컬럼들에 대한 개별 집계를 구할 수 있고 ROLLUP이나 CUBE와는 달리 컬럼 간 순서와 무관한 결과를 얻음
• ORDER BY를 사용하여 집계 대상 그룹과의 표시 순서를 조정하여 체계적으로 보여줌

SELECT [컬럼1, 컬럼2, 컬럼3 ..], <GROUP FUNCTION>
FROM 테이블명 [WHERE ...]
GROUP BY [컬럼 ..] GROUPING SETS <컬럼 ..>
[HAVING ...] [ORDER BY ...]

 

④ 윈도우 함수

 

• 데이터베이스를 사용한 온라인 분석 처리 용도로 사용하기 위해서 표준 SQL에 추가된 기능
• 온라인 분석 처리는 시장 분석, 통계 작성, 경영 계획 분석 및 수립 등 비즈니스 현장에서 자주 사용되는 분석이 포함되며 데이터 기반 의사 결정의 증가에 따라 그 중요성이 더욱 증가
• OLAP(OnLine Analytical Processing) 함수

 

(1) 윈도우 함수의 구문

SELECT <WINDOW FUNCTION> [ARGUMENTS] OVER
(PARTITION BY <컬럼1, 컬럼2, ..>])
[ORDER BY <컬럼1, 컬럼2, ..>] FROM 테이블명

• PARTITION BY는 선택 항목으로 순위를 정할 대상 범위의 컬럼 설정
• PARTITION BY구에는 GROUP BY구가 가진 집약 기능이 없으며 이로 인해 ROW 수가 줄어들지 않음
• 윈도우 : PARTITION BY를 통해 구분된 레코드 집합
• 윈도우 함수에는 OVER 문구가 필수적으로 포함되며 ORDER BY 뒤에는 SORT 컬럼 입력
• 어떤 열을 어떤 순서로 순위를 정할지를 지정하며 SELECT 문의 마지막에 사용하는 ORDER BY와 동일하게 사용

 

(2) 윈도우 함수의 예시

SELECT MAJOR, SUBJECT, STUDENT_NAME, GRADE,
	RANK() OVER (ORDER BY GRADE DESC) AS GRADE_RANK,
	DENSE_RANK() OVER (ORDER BY GRADE DESC) AS GRADE_DENSE_RANK,
	ROW_NUMBER() OVER (ORDER BY GRADE DESC) AS GRADE_ROW_NUMBER,
	RANK() OVER (PARTITION BY MAJOR ORDER BY GRADE DESC) AS GRADE_RANK_IN_SUB
FROM STUDENT_GRADE

• RANK(), DENSE_RANK(), ROW_NUMBER() 등의 목적에 맞는 윈도우 함수 사용 가능
• PARTITION BY 구문 뒤에는 순위의 기준이 되는 대상 조정 가능

 

(3) 윈도우 함수의 분류

 

(가) 집계 함수 

 

• 위의 집계 함수와 동일
• 대다수의 DBMS에서 지원

 

(나) 순위 함수

 

(a) RANK 

 

• 레코드의 순위 계산
• 동일 순위의 레코드 존재 시 후순위는 넘어감
• ex. 2위가 3개인 레코드인 경우 : 1위, 2위, 2위, 2위, 5위, 6위

 

(b) DENSE_RANK 

 

• 레코드의 순위 계산
• 동일 순위의 레코드 존재 시에도 후순위를 넘어가지 않음
• ex. 2위가 3개인 레코드인 경우: 1위, 2위, 2위, 2위, 3위, 4위

 

(c) ROW_NUMBER 

 

• 레코드의 순위 계산
• 동일 순위의 값이 존재해도 이와 무관하게 연속 번호 부여
• ex. 2위가 3개인 레코드인 경우: 1위, 2위, 3위, 4위, 5위, 6위

 

(다) 행순서 함수(SQL Server에서는 지원x)

 

(a) FIRST_VALUE 

 

• 파티션별 윈도우에서 가장 먼저 나오는 값을 찾음
• 집계 함수의 MIN과 동일한 결과 출력

 

(b) LAST_VALUE 

 

• 파티션별 윈도우에서 가장 늦게 나오는 값을 찾음
• 집계 함수의 MAX와 동일한 결과 출력

 

(c) LAG 

 

• 파티션별 윈도우에서 1부터 이전 몇 번째 행의 값을 가져옴

 

(d) LEAD 

 

• 파티션별 윈도우에서 1부터 이후 몇 번째 행의 값을 가져옴

 

(라) 그룹 내 비율 함수

 

1. RATIO_TO_REPORT
2. PERCENT_RANK
3. CUME_DIST
4. NTILE