221005 TIL - 정보처리기사 - 응용 SQL 활용 (집계성 SQL, 집계 함수, 그룹 함수, 윈도 함수)

 

정보처리기사 실기

---

VII. SQL 응용

Chapter 01. 데이터베이스 기본

다시 정리하기!

• DB 스키마란? 데이터베이스의 구조, 제약 조건 등의 정보를 담고 있는 기본적인 구조이다.
• 조인(Join)

  SELECT A.컬럼1, A.컬럼2, ... B.컬럼1, B.컬럼2, ... FROM 테이블1 A [INNER] JOIN 테이블2 B ON 조인조건 [WHERE 검색조건];
  

  ② 왼쪽 외부 조인③ 오른쪽 외부 조인④ 완전 외부 조인⑤ 교차 조인⑥ 셀프 조인
• SELECT A.컬럼1, A.컬럼2, ... B.컬럼1, B.컬럼2 FROM 테이블1 A [INNER] JOIN 테이블1 B ON 조인조건 [WHERE 검색조건];
• SELECT A.컬럼1, A.컬럼2, ... B.컬럼1, B.컬럼2 FROM 테이블1 A CROSS JOIN 테이블2 B;
• SELECT A.컬럼1, A.컬럼2, ... B.컬럼1, B.컬럼2 FROM 테이블1 A FULL [OUTER] JOIN 테이블2 B ON 조인조건 [WHERE 검색조건];
• SELECT A.컬럼1, A.컬럼2, ... B.컬럼1, B.컬럼2 ... FROM 테이블1 A RIGHT [OUTER] JOIN 테이블2 B ON 조인조건 [WHERE 검색조건];
• SELECT A.컬럼1, A.컬럼2, ... B.컬럼1, B.컬럼2, ... FROM 테이블1 A LEFT [OUTER] JOIN 테이블2 B ON 조인조건 [WHERE 검색조건];
• ① 내부조인
• GRANT 란? 관리자(DBA)가 사용자에게 데이터베이스에 대한 권한을 부여하는 명령어
• 트랜잭션이란? 데이터베이스 시스템에서 하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본 단위이다.
• 스키마란? 사용자나 개발자의 관점에서 필요로 하는 데이터베이스의 논리적 구조를 외부스키마, 데이터베이스의 전체적인 논리적 구조를 나타낸 개념스키마, 물리적 저장장치의 관점에서 보는 데이터베이스를 나타낸 내부스키마로 나뉜다.
• 인덱스(index) 란? 데이터를 빠르게 찾을 수 있는 수단으로서 테이블에 대한 조회 속도를 높여주는 자료 구조이다.
• WHERE 절에는 비교, 범위, 집합, 패턴, NULL, 복합조건이 있다.
• // 비교 = // 값이 같은 경우 <>, != // 값이 다른 경우 <,<=,>,>= // 대소 비교, 비교 연산에 해당하는 데이터 조회 // 범위 : BETWEEN 컬럼 BETWEEN 값1 AND 값2 // 집합 : IN, NOT IN 컬럼 IN (값1, 값2, ...) // 컬럼이 IN 안에 포함된 경우 데이터 조회 컬럼 NOT IN (값1, 값2, ...) // 컬럼에 IN 안에 포함되어 있지 않은 경우 데이터 조회 // 패턴 : %, [], [^], _ 컬럼 LIKE % // 0개 이상 문자열과 일치 컬럼 LIKE [] // 1개 이상 문자열과 일치 컬럼 LIKE [^] // 1개 이상 문자열과 불일치 컬럼 LIKE _ // 특정 위치의 1개의 문자와 일치 // NULL : NULL, NOT NULL 컬럼 IS NULL // 컬럼이 NULL 인 데이터 조회 컬럼 IS NOT NULL // 컬럼이 NULL 이 아닌 데이터 조회 // 복합 조건 : AND, OR, NOT, ! 조건1 AND 조건2 // 조건1과 조건2를 모두 만족하는 데이터 조회 조건1 OR 조건2 // 조건1과 조건2 중 하나라도 만족하는 데이터 조회 NOT 조건 // 조건에 해당하지 않는 데이터 조회
• SELECT 명령어
• SELECT [ALL | DISTINCT] 속성명1, 속성명2, ... FROM 테이블1, ... [WHERE 검색조건] [GROUP BY 속성1, 속성2, ... ] [HAVING 그룹조건] [ORDER BY 속성 [ASC | DESC]];
• 데이터베이스 언어에는 데이터 정의 언어 DDL(Data Definition Language), 데이터 조작 언어 DML(Data Manipulation Language), 데이터 제어 언어 DCL(Data Control Language) 이 있다.
• 데이터베이스 기본 연산 개념에는 CRUD(Create, Read, Update, Delete) 가 있다.
• DML 에는 삽입 INSERT, 조회 SELECT, 갱신 UPDATE, 삭제 DELETE 가 있다.
• SELECT 에는 조인(Join), 서브쿼리(Sub-Query), 집합 연산자(Set Operator) 가 있다.
• 집합 연산자(Set Operator) 는 테이블을 집합 개념으로 보고, 두 테이블의 연산에 집합 연산자를 사용하는 방식이다. 또한 여러 질의 결과를 연결하여 하나로 결합하는 방식을 사용한다.
• 집합 연산자에는 UNION, UNION ALL, INTERSECT, MINUS 가 있다.
• UNION 은 중복 행이 제거된 쿼리 결과를 반환하는 집합 연산자. UNION ALL 은 중복 행이 제거되지 않은 쿼리 결과를 반환하는 집합 연산자. INTERSECT 는 두 쿼리 결과에 공통적으로 존재하는 결과를 반환하는 집합 연산자. MINUS 는 첫 쿼리에 있고 두 번째 쿼리에는 없는 결과를 반환하는 집합 연산자이다. 집합 연산자는 두 SELECT 문 사이에 넣는다.
• // 집합 연산자 ex) SELECT 속성1 FROM 테이블1 WHERE 조건1 UNION // 해당하는 자리에 UNION ALL, INTERSECT, MINUS 를 넣으면 된다. SELECT 속성2 FROM 테이블2 WHERE 조건2
• INSERT(데이터 삽입) 명령어
• INSERT INTO 테이블명(속성명1, ...) VALUES (데이터1, ...);

Chapter 02. 응용 SQL 작성하기

[1] 집계성 SQL 작성

(1) 데이터 분석함수의 개념

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

(2) 데이터 분석 함수의 종류

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

(3) 집계 함수

[1] 집계 함수 구문

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

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

① GROUP BY 구문

• SQL 에서는 WHERE 구문을 활용하여 조건별 대상 ROW 를 선택한다.
• 복수 ROW 대상의 데이터 분석 시 그룹핑 대상이 되는 부분을 선별할 필요가 있는데, 이 경우 GROUP BY 를 사용하며, 다음과 같은 특성을 가진다.❷ SELECT에서 사용하는 것과 같은 ALIAS 사용이 불가하다.❹ WHERE 구문은 GROUP BY 보다 먼저 실행되고, 대상이 되는 단일 행을 사전에 선별하는 역할을 한다.
• ❸ WHERE 구문 안에 포함되지 않는다.
• ❶ NULL값을 가지는 ROW는 제외한 후 산출한다.
• GROUP BY 구문은 실제 구체적 데이터 분석값을 보고자 하는 컬럼 단위를 선정할 때 사용되는 기준이 되며, 이 부분의 조정을 통해 사용자가 원하는 분석 데이터를 볼 수 있게 해준다.

② HAVING 구문

• WHERE 구문 내에서는 사용할 수 없는 집계 함수의 구문을 적용하여 복수 행의 계산 결과를 조건별로 적용하는 데 사용된다.
• GROUP BY 뒤에 기재하며, GROUP BY 구문의 기준 항목이나 소그룹 집계 함수를 활용한 조건을 적용하는데 사용한다.
• GROUP BY 및 집계 함수에 대한 WHERE 구문이라고 할 수 있다.

[3] 집계 함수의 종류

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

[4] 집계 함수 활용 예시

SELECT COUNT(*)
FROM STUDENT
WHERE 국어 >= 80

SELECT SUM(국어), AVG(영어)
FROM STUDENT

SELECT MAX(국어), MIN(국어)
FROM STUDENT

SELECT STDDEV(국어), VARIAN(국어)
FROM STUDENT

(4) 그룹 함수

테이블의 전체 행을 하나 이상의 컬럼을 기준으로 컬럼 값에 따라 그룹화하여 그룹별로 결과를 출력하는 함수이다.

[1] 그룹 함수의 유형

① ROLLUP 함수

• 소그룹의 합계 등 중간 집계 값을 산출하기 위한 그룹 함수이다.
• 지정 컬럼의 수보다 하나 더 큰 레벨만큼 중간 집계 값이 생성된다.
• 지정 컬럼은 계층별로 구성되기 때문에 순서가 바뀌면 수행 결과가 바뀐다.

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

• 소계 집계 대상이 되는 컬럼을 ROLLUP 뒤에 기재하고, 아닌 컬럼은 GROUP BY 뒤에 기재한다.
• SELECT 뒤에 포함되는 컬럼을 GROUP BY 또는 ROLLUP 뒤에 기재해야 한다.
• ORDER BY 구문을 활용해 계층 내 정렬할 수 있다.

SELECT DEPT, JOB, SUM(SALARY)
FROM DEPT_SALARY
GROUP BY ROLLUP(DEPT, JOB);

② CUBE 함수

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

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

SELECT DEPT, JOB, SUM(SALARY)
FROM DEPT_SALARY
GROUP BY CUBE(DEPT, JOB);

③ GROUPING SETS 함수

• 집계 대상 컬럼들에 대한 개별 집계를 구할 수 있으며, ROLLUP이나 CUBE와는 달리 컬럼 간 순서와 무관한 결과를 얻을 수 있다.
• 다양한 소계 집합을 만들 수 있으며 ORDER BY를 활용할 수 있다.

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

SELECT DEPT, JOB, SUM(SALARY)
FROM DEPT_SALARY
GROUP BY GROUPING SETS(DEPT, JOB, ( ));

(5) 윈도 함수

• 데이터베이스를 사용한 온라인 분석 처리 용도로 사용하기 위해서 표준 SQL에 추가된 함수이다.
• OLAP(OnLine Analytical Processing) 함수라고도 한다.

SELECT 함수명(파라미터)
OVER
([PATITION BY 컬럼, ...]
[ORDER BY 컬럼A, ...])
FROM 테이블명

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

[1] 순위 함수

• RANK : 특정 항목에 대한 순위를 구한다. 동일 순위의 레코드가 존재 시 후순위는 넘어간다.
• DENSE_RANK : 레코드 순위 계산. 동일 순위가 존재해도 후순위를 넘어가지 않는다.
• ROW_NUMBER : 레코드 순위 계산. 동일 순위가 존재해도 무관하게 연속 번호를 부여한다.

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