[정보처리기사 실기] 8. SQL 응용-Chapter 2. 응용 SQL 작성하기

정리 내용은 [수제비 2020 정보처리기사 실기]책을 기반으로 작성하였습니다.

2020 수제비 정보처리기사 실기(1권+2권 합본세트)

---

1. 데이터 분석 함수

개념
총합, 평균 등의 데이터 분석을 위해서는 복수 행 기준의 데이터를 모아서 처리하는 것을 목적으로 하는 다중 행 함수
단일 행을 기반으로 산출하지 않고, 복수 행을 그룹별로 모아놓고, 그룹당 단일 계산 결과를 반환
GROUP BY 구문을 활용하여 복수 행 그룹핑 / SELECT, HAVING, ORDER BY 구문에 활용

 

종류
집계함수: 여러 행 또는 테이블 전체 행으로부터 하나의 결과값 반환하는 함수
그룹함수: 소그룹 간의 소계 및 중계 등 중간 합계 분석 데이터를 산출하는 함수
윈도함수: 데이터베이스를 사용한 온라인 분석 처리 용도로 사용하기 위해서 표준 SQL에 추가된 기능

 

2. 집계함수

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

 

구문

집계함수 사용방법

WHRER 조건으로 지정된 데이터의 집합으로부터 그룹화된 집합에 대한 조건 선택시 HAVING을 사용하는 것GROUP BY 구문 뒤에 테이블을 구분하는 컬럼을 기재하여 그룹화HAVING 구문은 그룹화된 집합에 대한 조건 지정 시 사용하고, 상수나 집약 함수, 집약 키를 사용할 수 있음

(1) GROUP BY 구문: 실제 구체적 데이터 분석값을 보고자 하는 컬럼단위를 선정할 때 사용되는 기준이 되면, 이부분의 조정을 통해 사용자가 원하는 분석 데이터를 볼 수 있다.

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

 

종류

집계함수	설명	집계함수	설명
COUNT	복수 행의 줄의 수	MIN	복수 행의 해당 컬럼 중 최소값
SUM	복수 행의 해당 컬럼 간의 합계	STDDEV	복수 행의 해당 컬럼 간의 표준편차
AVG	복수 행의 해당 컬럼 간의 평균	VARIAN	복수 행의 해당 컬럼 간의 분산
MAX	복수 행의 해당 컬럼 중 최댓값

3. 그룹함수

개념
전체 행을 하나 이상의 컬럼을 기준으로 컬럼 값에 따라 그룹화하여 그룹별로 결과를 출력하는 함수ROLLUP / CUBE / GROUP_SETS 함수 존재

 

ROLLUP 함수

지정된 컬럼은 소그룹의 합계 등 중간 집계 값을 산출하기 위한 그룹 함수
지정 컬럼 수보다 더 큰 레벨만큼의 중간 집계 값 생성
순서가 바뀌면 수행결과가 바뀜소계 집계 대상이 되는 컬럼을 ROLLUP 뒤에 기재하고, 소계 집계 대상이 아닌 경우 GROUPBY 뒤에 기재

ROLLUP 함수 사용방법

CUBE 함수
결합 가능한 모든 값에 대한 다차원 집계를 생성하는 그룹함수
ROLLUP은 소계, 중간집계를 나타내 주지만, CUBE는 결합 가능한 모든 값에 대해 다차원 집계 생성, 세분화된 소계가 구해짐

CUBE 함수 사용방법

GROUPING SETS 함수
집계 대상 컬럼에 대한 개별 집계를 구할 수 있으면, CUBE와 같이 컬럼 간 순서와 무관한 결과를 얻을 수 있는 그룹함수
개별 집계를 구할 수 있으며, ROLLUP 게층 구조와 달리 평등한 관계라 순서에 상관없이 동일한 결과 나옴

GROUPING SETS 함수 사용방법

[예시] 아래와 같은 그래프가 있다면...

예시

4. 윈도함수

개념
데이터 베이스를 사용하는 온라인 분석 처리 용도로 사용하기 위해 표준 SQL에 추가된 함수
OLAP 함수라고도 한다.
* OLAP(OnLine Analytical Processing): 의사결정 지원 시스템. 사용자가 동일한 데이터를 여러 기준을 이용하는 다양한 방식으로 바라보면서 다차원 데이터 분석을 할 수 있도록 도와주는 기술

 

구문

윈도함수 사용방법

PARTITION BY는 선택항목. 순위를 정할 대상 범위의 컬럼을 설정 PARTITION BY 구에는 GROUP BY구가 가진 집약 기능이 없으며, 이로 인해 레코드가 줄어들지 않음PARTITION BY를 통해 구분된 레코드 집합을 윈도라고 함
윈도 함수는 OVER 구문이 필수적
ORDER BY 뒤에는 SORT 컬럼을 입력(어떤 열을 어떤 순서로 순위를 정할지를 지정)

 

분류-순위함수
코드의 순위를 계산하는 함수. RANK, DENSE_RANK, ROW_NUMBER 함수가 존재

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

5. 특정 기능 수행 SQL문 작성

응용시스템 DBMS 접속 기술
JDBC: JAVA에서 DBMS에 접근할 수 있게 있는 기술 SQL 사용하여 DBMS에 질의하고 데이터 조작하는 API사용
Mybatis: SQL Mapping 기반 오픈 소스 Access FrameworkDBMS에 질의하기 위한 SQL 쿼리를 별도 XML 파일로 분리하고 Mapping을 통해 SQL 실행. 장점: 복잡한 JDBC 코드를 단순화 할 수 있음 / SQL을 거의 그대로 사용 가능 / Spring 기반 프레임워크와 통합 기능을 제공 / 우수한 성능을 보여줌

 

Mybatis 작성 문법
1. SQL 문장의 파라미터 사용 방법
응용시스템을 통해 SQL을 실행할 때 가장 중요한 부분 중 하나는 입력변수에 대해 처리하는 방법
Mybatis에서는 #[파라미터 명]으로 처리
2. 동적 SQL

Mybatis는 조건에 따라 구문 자체를 변경할 수 있다는 것이 장점
응용 프로그램 실행 시 수행할 sql문이 결정된다. 위에서 설명한 입력 파라미터만 변경하는 것이 아니라 SQL에 포함된 다양한 부분을 조건에 따라 변경 가능하다.

3. 절차형 SQL 호출
절차형 SQL인 사용자 정의함수, 트리거, 프로시저의 실행이 가능하다. 주로 프로시저를 호출함
프로시저 호출 경우 statemetType를 반드시 CALLABLE로 설정 / 호출 전에도 CALL 사용

 

6. 데이터 제어어(DCL)

개념
데이터베이스 관리자가 데이터 보안, 부결성 유지, ㅂ여행 제어, 회복을 위한 관리자 가 사용하는 제어용 언어

유형

분류	설명
GRANT	관리자가 사용자에게 데이터베이스 권한을 부여하는 명령어 GRANT 권한 ON 테이블 TO 사용자 [WITH 권한 옵션]; WITH GRANT OPTION: 사용자가 권한을 받고난 후 다른 사람들과 권한을 나눠가질 수 있는 옵션
REVOKE	관리자가 사용자에게 부여했던 권한을 회수하기 위한 명령어 REVOKE 권한 ON 테이블 FROM 사용자 [CASCADE CONSTRAINTS]; CASCADE CONSTRAINTS: 연쇄적인 권한을 해제할 때 입력(WITH GRANT OPTION으로 부여된 사용자들의 권한 까지 취소)