[정보처리기사] 실기 정리 - 8과목 (2)

peach·2020년 10월 6일

정보처리기사 실기

정보처리기사

목록 보기

23/34

🧀 데이터 분석 함수

1. 개념

데이터 분석을 위해 복수행 기준의 데이터를 모아서 처리하는 다중행 함수

2. 종류

(1) 집계 함수

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

종류 : COUNT, SUM, AVG, MAX, MIN, STDDEC, VARIAN

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

GROUP BY 구문 : 복수행 대상의 데이터 분석 시 그룹핑 대상이 되는 부분을 선별해야 하는 경우 사용
HAVING 구문 : GROUP BY 구문의 기준 항목이나 소그룹 집계 함수를 활용한 조건을 적용하기 위해 사용

(2) 그룹 함수

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

ROLLUP 함수

지정된 컬럼의 중간 집계 값을 산출
순서가 바뀌면 수행 결과가 바뀜

SELECT 컬럼1, 컬럼2, ..., 그룹 함수
  FROM 테이블명
[WHERE ...]
GROUP BY [컬럼A, 컬럼B, ...] ROLLUP(컬럼a, 컬럼b, ...)
[HAVING ...]
[OPDER BY ...]

// SELECT 뒤에 포함되는 컬럼 중 소계 집계 대상인 컬럼은 ROLLUP 뒤에 기재하고, 
// 소계 집계 대상이 아닌 컬럼은 GROUP BY 뒤에 기재한다.

CUBE 함수

결합 가능한 모든 값에 대해 다차원 집계를 생성하는 그룹 함수
연산이 많음

SELECT 컬럼1, 컬럼2, ..., 그룹 함수
  FROM 테이블명
[WHERE ...]
GROUP BY [컬럼A, 컬럼B, ...] CUBE(컬럼a, 컬럼b, ...)
[HAVING ...]
[OPDER BY ...]

GROUPING SETS 함수

집계 대상 컬럼들에 대한 개별 집계를 생성
순서와 무관

SELECT 컬럼1, 컬럼2, ..., 그룹 함수
  FROM 테이블명
[WHERE ...]
GROUP BY [컬럼A, 컬럼B, ...] GROUPING SETS(컬럼a, 컬럼b, ...)
[HAVING ...]
[OPDER BY ...]

(3) 윈도우 함수

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

SELECT 함수명(파라미터) 
  OVER 
  ([PARTITION BY 컬럼1, 컬럼2, ...])
  [ORDER BY 컬럼A, 컬럼B, ...]
  FROM 테이블명
  
// PARTITION BY : 순위를 정할 대상 범위의 컬럼을 설정, 레코드는 줄어들지 않음

순위 함수 : 레코드의 순위를 계산하는 함수
- RANK : 동일 순위의 레코드 존재 시 후 순위는 넘어감 (ex. 1위, 2위, 2위, 4위)
- DENSE_RANK : 동일 순위의 레코드 존재 시 후 순위를 넘어가지 않음 (ex. 1위, 2위, 2위, 3위)
- ROW_NUMBER : 동일 순위의 레코드 존재 시 이와 무관하게 연속된 번호를 부여함 (ex. 1위, 2위, 3위, 4위)
행순서 함수 : 레코드에서 가장 먼저 나오거나 가장 나중에 나오는 값, 이전이나 이후의 값들을 출력하는 함수
- FIRST_VALUE : 파티션별 윈도우에서 가장 먼저 나오는 값 반환, 집계 함수의 MIN과 동일한 값
- LAST_VALUE : 파티션별 윈도우에서 가장 나중에 나오는 값 반환, 집계 함수의 MAX과 동일한 값
- LAG : 파티션별 윈도우에서 이전행의 값 반환
- LEAD : 파티션별 윈도우에서 이후행의 값 반환
그룹 내 비율 함수 : 주어진 그룹에 대해 비율과 관련된 통계를 보여주는 함수
- RATIO_TO_REPORT : 합을 기준으로 각 행의 상대적 비율을 반환
- PERCENT_RANK : 가장 먼저 나오는 것을 0으로 가장 나중에 나오는 것을 1로 하여 행의 순서별 백분율을 반환

🧀 특정 기능 수행 SQL문 작성

1. 자바 데이터베이스 연결 (JDBC)

JAVA를 이용하여 개발하며, SQL을 사용하여 DBMS에 질의하고 데이터를 조작하는 API를 제공하는 기술

사용 예시

private static final String SQL_QUERY
  = "SELECT * 
       FROM USER_INFO
       WHERE USER_NAME = ?
       AND AGE = ?";

connection = dataSource.getConnection();

sql_exec = connection.preparedStatement(SQL_QUERY);
sql_exec.setString(1, user_name);
sql_exec.setString(2, age);

sql_result = sql_exec.executeQuery();

2. MyBatis

SQL Mapping 기반 오픈 소스 Access Framework로, DBMS에 질의하기 위한 SQL 쿼리를 별도의 XML 파일로 분리하고 Mapping을 통해 SQL을 실행함

(1) 장점

복잡한 JDBC 코드의 단순화 가능
SQL을 거의 그대로 사용 가능
Spring 기반 프레임워크와 통합 기능을 제공
우수한 성능

(2) 문법

SQL 문장의 입력 파라미터 사용
- #{파라미터명}으로 처리

동적 SQL
- 조건에 따라 SQL 구문 변경
- <if>, <foreach>, <choose when otherwise> 등

절차형 SQL
- 프로시저, 사용자 정의 함수, 트리거 실행 가능
- 사용자 정의 함수, 트리거는 호출이 무의미하므로 주로 프로시저를 호출
- statementType = "CALLABLE"로 설정해야 함
- CALL 프로시저명(#{파라미터명}) 처럼 호출

🧀 데이터 제어어 명령문 작성

1. 개념

DCL(Data Control Language)는 데이터베이스 관리자가 데이터 보안, 무결성 유지, 병행 제어, 회복을 위해 사용하는 제어용 언어

2. 유형

(1) GRANT : 사용 권한 부여

GRANT 권한 ON 테이블 TO 사용자
[WITH GRANT OPTION];

[WITH GRANT OPTION] : 사용자가 권한을 받은 후 다른 사람들과 권한을 나눠가질 수 있는 옵션

(2) REVOKE : 사용 권한 회수

REVOKE 권한 ON 테이블 FROM 사용자
[CASCADE CONSTRAINTS];

[CASCADE CONSTRAINTS] : [WITH GRANT OPTION]으로 부여된 권한까지 연쇄적으로 회수하는 옵션

🐭 참고문헌

NCS 정보처리기술사 연구회. (2020). 수제비 정보처리기사 실기 (2판). 건기원.

peach

이전 포스트

[정보처리기사] 실기 정리 - 8과목 (1)

다음 포스트

[정보처리기사] 실기 정리 - 8과목 (2)