정보처리기사 실기 - 7. SQL 응용(2) /트랜잭션/집계성SQL/윈도우함수

NCS 정보처리기사

- 트랜잭션 제어

한번에 수행되어야 하는 데이터베이스 연산들의 논리적인 모임

 -> 데이터베이스 동시성 제어와 회복을 위한 기본 개념

 

- 트랜잭션 완료(commit) : 트랜잭션이 성공적으로 완료되었음

- 트랜잭션 철회(abort) : 비성공적 종료 -> 원자성을 보장하기 위해 갱신 사항을 트랜잭션 수행 전 상태로 되돌림

 

- 동시성 제어 : 다수 사용자가 테이터베이스를 동시에 접근하도록 허용하면서 연관성을 유지하는 DBMS 기능

- 회복 : 데이터베이스 갱신중에 시스템이 고장나도 데이터베이스의 일관성을 유지하는 DBMS 기능

• 로그를 이용한 회복 : [Trans ID, start]와 [Trans ID, commit]의 짝검사 -> [Trans ID, commit]가 없는 트랜잭션들은 취소
• 로그 먼저 쓰기(WAL, Write-Ahead-Logging) : DBMS는 데이터베이스 버퍼보다 로그 버퍼를 먼저 디스크에 기록 -> 메모리는 휘발성이기 때문

 

- COMMIT 명령 : INSERT, UPDATE, DELETE 문장을 사용한 후에 변경 작업이 완료되었음을 데이터베이스에 알려주기 위해 사용

- ROLLBACK 명령 : 테이블에 입력한 데이터나 수정한 데이터, 삭제한 데이터에 대하여 COMMIT 이전에 변경 사항을 취소할 경우

 

- SAVEPOINT 명령

 -> 체크포인트 : 회복 시 재수행할 트랜잭션의 수를 줄이기 위해 주기적 수행

  -> 주기억 장치의 버퍼 내용이 디스크에 강제로 기록됨

 -> SAVEPOINT 문 : 현재의 트랜잭션 내에 저장점을 표시하여 트랜잭션을 더 작은 부분으로 나눔

 -> ROLLBACK TO SAVEPOINT 문 : 현재의 트랜잭션에서 지정된 SAVEPOINT 이후에 갱신된 내용만 되돌림

 

- 집계성 SQL

총합, 평균 등의 데이터 분석을 위해 복수행을 기준으로 데이터를 분석하는 함수

 

- 집계함수

• COUNT()
• SUM()
• AVG()
• MAX()
• MIN()
• STDDEV()
• VARIANCE()

-> GROUP BY 절과 함께 사용됨

-> SELECT 절과 HAVING 절에만 나타남

-> COUNT(*)를 제외한 모든 집계함수는 NULL값을 제외

-> DISTINCT 사용 시 중복을 제거하여 계산

-> 어떤 조건을 만족하는 그룹들에 대해서만 집계 함수를 적용하도록 제한할 때는 WHERE 절이 아니라 HAVING 절 사용

 

- 그룸 함수

소그룹의 합계, 총계와 같이 레벨별로 집계 결과를 조회하기 위해 사용

 

- ROLLUP() 함수 : 소계 등 중간 집계값을 산출하기 위해 사용함

 -> 지정 칼럼을 계층별로 구성되기 때문에 순서가 바뀌면 수행 결과 바뀜

- CUBE() 함수 : 결합 가능한 모든 값에 대해 다차원 집계를 생성하는 그룹 함수

 -> 내부적으로 대상 컬럼 순서를 변경하여 또 한번의 쿼리 수행, 총계는 양쪽 쿼리에서 수행 후 한쪽 제거

SELECT dno_job, SUM(salary) FROM employee GROUP BY CUBE(dno, jao);

 

- GROUPING SETS() 함수 : 집계 대상 컬럼들에 대한 개별 집계를 구함

 -> 컬럼 간 순서와 무관한 결과 생성

 -> GROUP BY 한 SQL들을 UNION ALL 해서 보여주는 것과 같음

 

- 윈도우 함수

행과 핸간의 관계를 쉽게 정의하기 위해 만든 함수로 분석(ANALYTIC) 함수 또는 순위 함수

 

- 문법

SELECT WINDOW_FUNCTION(argument) OVER ([PARTITION BY 컬럼][ORDER BY 절][WINDOWING 절]) FROM 테이블명;

-> WINDOW_FUNCTION(argument) : 순위, 집계, 행 순서, 비율 관련 윈도우 함수

-> PARTITION BY 절 : 전체 집합을 소그룹으로 나누기 위한 기준 컬럼

-> ORDER BY 절 : 어떤 항목에 순위를 지정할 지 기준 기술

-> WINDOWING 절 : 함수의 대상이 되는 행 기준의 범위를 ROWS 또는 RANGE로 지정

  -> ROWS는 현재 행의 앞뒤 행들을 범위

  -> RANGE는 현재 행의 데이터 값을 기준으로 데이터 값의 범위

ROW BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

-> 현재 행을 포함해서 파티션 내의 마지막 행까지

RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING

-> 현재 행 값을 기준으로 -50에서 50의범위 내에 포함된 모든 행

 

- 함수 종류

• 순위관련 함수 : RANK(), DENSE_RANK(), ROW_NUMBER()
• 집계관련 함수 : SUM(), MAX(), MIN(), AVG(), COUNT()
• 행 순서 관련 함수 ; FIRST_VALUE(), LAST_VALUE(), LAG(), LEAD()
• 비율관련 함수 : CUME_DIST(), PERCENT_RANK(), NTILE()

 

정보처리기사 실기 -7. SQL 응용(3) /절차형SQL