2024.05.09 55일차 DB TCL, 그룹함수, 윈도우함수, DCL

트랜잭션

하나 이상의 데이터베이스 연산이 묶여서 하나의 논리적인 작업 단위로 처리
논리적 연산단위

트랜잭션의 특성 ACID

원자성(Atomicity): 트랜잭션은 일련의 연산 중 하나라도 실패하면 전체 연산이 취소
일관성(Consistency): 트랜잭션이 완료되면 데이터베이스의 일관성이 유지
격리성(Isolation): 여러 개의 트랜잭션이 동시에 수행될 때 각각의 트랜잭션이 서로에게 영향을 미치지 않음6
지속성(Durability): 트랜잭션이 완료되면 그 결과가 영구적으로 반영됨

사례 - 은행 계좌이체

ACID를 보장하는 은행 앱에서의 계좌 이체 작업은 트랜잭션으로 묶여 처리되며
데이터의 일관성과 무결성을 보장하고, 데이터베이스 시스템에서 트랜잭션 처리를 안정적으로 보장할 수 있음

원자성(Atomicity):
- 계좌 이체 작업은 출금 연산과 입금 연산
- 출금 연산이 성공하고 입금 연산이 실패하면, 계좌 간 이체 작업이 완전하지 않으므로 이전 상태로 롤백되어야 함
- 출금 연산과 입금 연산을 하나의 트랜잭션으로 묶어서 처리하고, 하나의 연산이 실패하면 전체 작업이 롤백됩니다.

일관성(Consistency):
- 애플리케이션에서는 트랜잭션 실행 전에 데이터베이스를 락(lock)하여 다른 사용자가 데이터를 수정하지 못하도록 함

격리성(Isolation):
- 동시에 여러 개의 계좌 이체 작업이 발생할 수 있으므로, 트랜잭션 간 상호 간섭을 방지해야 함
- 트랜잭션 간 격리 수준(Isolation level)을 설정하여 다른 트랜잭션의 영향을 받지 않도록 함

지속성(Durability):
- 계좌 이체 작업이 완료되면 데이터베이스에 반영되어야 함
- 트랜잭션이 완료되었을 때, 데이터베이스에 변경 내용을 저장하고,
장애가 발생해도 이전 상태로 롤백되지 않도록 복구와 백업 작업을 수행

오라클 TCL

TCL(Transaction Control Language)은 트랜잭션의 처리를 위한 명령어들의 집합

COMMIT
- 현재까지 수행한 트랜잭션의 결과를 데이터베이스에 저장
- COMMIT 명령어를 실행하면 트랜잭션이 종료되고, 데이터베이스의 일관성이 유지됨
ROLLBACK
- 현재까지 수행한 트랜잭션의 결과를 취소하고, 트랜잭션을 초기 상태로 되돌림
- ROLLBACK 명령어를 실행하면 트랜잭션이 종료되고, 데이터베이스의 일관성이 유지됨
SAVEPOINT
- 현재 트랜잭션 내에서 중간에 SAVEPOINT를 설정하여, 해당 지점까지의 작업을 롤백할 수 있도록 함
- SAVEPOINT를 설정하면 ROLLBACK TO SAVEPOINT를 사용하여 해당 지점까지의 작업을 취소할 수 있음
ROLLBACK TO SAVEPOINT
- SAVEPOINT 지점까지 수행한 작업을 취소하고, SAVEPOINT 이후의 작업은 유지

-- 트랜잭션 시작
BEGIN;

-- 테이블에 데이터를 삽입
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary)
VALUES (301, 'John', 'Doe', 'john.doe@example.com', '01-JAN-20', 'IT_PROG', 5000);

-- 테이블에서 데이터를 갱신
UPDATE employees SET salary = 6000 WHERE employee_id = 301;

-- 저장점 설정
SAVEPOINT before_delete;

-- 테이블에서 데이터를 삭제
DELETE FROM employees WHERE employee_id = 301;

-- 저장점 이전 상태로 롤백
ROLLBACK TO SAVEPOINT before_delete;

-- 트랜잭션 종료
COMMIT;

** 오라클은 DDL 수행시 자동커밋 vs SQL Server는 자동커밋 안됨(AUTO COMMIT FALSE)

UPDATE a SET val = 10 WHERE id = 1; ---- 1번
CREATE TABLE b (id CHAR(3) PRIMARY KEY); ---- 2번
ROLLBACK; ---- 3번

- 오라클은 2번에서 자동커밋되면서 UPDATE도 적용됨
- SQL Server는 커밋되지 않아서 1번 적용되기 이전 상태로 돌아감

그룹함수

GROUP BY절에 사용하는 함수

그룹함수	설명	비고
ROLLUP(A, B)	A 통계, A+ B통계, 전체통계 계층적 집계, 인자 순서 중요 → 통계결과가 바뀜	ROLLUP(A) : A 통계, 전체통계 ROLLUP((A, B)) : A+B통계, 전체통계
CUBE(A, B)	A 통계, B 통계, A+B 통계, 전체 통계 다차원 집계	결합 가능한 모든 조합에 대한 집계로 시스템 부하가 큼
GROUPING SETS(A, B)	A 통계, B 통계 통계 항목을 명시적으로 지정 가능	GROUPING SETS(A, B, (A, B), ()) = CUBE(A, B) GROUPING SETS((상품id, 월))
GROUPING	결과 집합에서 특정 열 집계 여부를 확인하는 데 사용 해당 열이 집계되었으면 1, 그렇지 않으면 0을 반환	GROUP BY 결과에 대해 SELECT절에서 사용

** 집계된 컬럼 이외의 대상 컬럼 값은 null

→ GROUPING 함수를 이용해 null을 원하는 문구로 변경 가능

ROLLUP

SELECT location, product, SUM(sales) as total_sales
FROM sales_data
GROUP BY ROLLUP(location, product);

CUBE

SELECT location, product, SUM(sales) as total_sales
FROM sales_data
GROUP BY CUBE(location, product);

GROUPING SETS

SELECT location, product, SUM(sales) as total_sales
FROM sales_data
GROUP BY GROUPING SETS((location, product), (location), (product), ());

GROUPING

SELECT CASE WHEN GROUPING(컬럼) = 1 THEN '대체문구'
ELSE 컬럼 END AS 별명

윈도우함수

SQL에서 분석 및 집계 작업을 수행할 때 사용되는 함수
윈도우 함수는 행과 연관된 결과 집합(window) 내에서 특정 행에 대한 계산을 수행
PARTITION BY, ORDER BY, 그리고 ROWS 또는 RANGE 절과 함께 사용

윈도우함수	설명	비고
RANK()	- 해당 행의 순위를 반환 - 동일한 값이 있는 경우 동일한 순위를 할당하며, 그 다음 순위는 건너뜀	1 1 3 4
DENSE_RANK()	- 해당 행의 순위를 반환 - 동일한 값이 있는 경우 동일한 순위를 할당하며, 그 다음 순위를 건너뛰지 않음	1 1 2 3
ROW_NUMBER()	- 해당 행의 고유한 순위를 반환 - 동일한 값이 있더라도 고유한 순위를 할당	1 2 3 4
LEAD(컬럼, [offset])	- 현재 행보다 지정된 수만큼 다음 행의 값을 반환	SQL Server 지원 X
LAG(컬럼, [offset])	- 현재 행보다 지정된 수만큼 이전 행의 값을 반환	SQL Server 지원 X
FIRST_VALUE(컬럼)	- 윈도우 내의 첫 번째 행의 값을 반환
LAST_VALUE(컬럼)	- 윈도우 내의 마지막 행의 값을 반환

** 집계함수도 윈도우 함수로 사용 가능

SELECT name, department, salary,
       
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dense_rank,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as row_number,
       
       LEAD(salary) OVER (PARTITION BY department ORDER BY salary DESC) as lead,
       LAG(salary) OVER (PARTITION BY department ORDER BY salary DESC) as lag,
       
       FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC) as first_value,
       LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary DESC ROWS 
                          BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last_value
FROM employees;

PARTITION BY

결과 집합을 지정된 열을 기준으로 여러 그룹으로 구분
윈도우 함수는 각 그룹 내에서만 작동하게 되어 그룹별로 분석 작업을 수행

ex) department 열을 기준으로 직원을 그룹화하고, 각 부서별로 급여 순위를 계산

SELECT department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;

** PARTITION BY 절이 없으면 전체 집합을 하나의 파티션으로 간주

ROWS

윈도우 프레임을 구성하는 방법 중 하나로, 현재 행과 관련된 행 수를 기준으로 윈도우 프레임의 범위를 설정
예를 들어, **ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING**은
현재 행을 기준으로 이전 행과 다음 행을 포함하는 윈도우 프레임을 생성

RANGE

윈도우 프레임을 구성하는 방법 중 하나로, 현재 행과 관련된 값 범위를 기준으로 윈도우 프레임의 범위를 설정
**RANGE**는 ORDER BY 절에 정의된 열의 값에 따라 윈도우 프레임의 범위를 설정
예를 들어, **RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING**은
현재 행을 기준으로 값이 1000 이하 행부터 값이 1000 이상 행까지 포함하는 윈도우 프레임을 생성

PRECEDING

윈도우 프레임 범위를 설정할 때 사용되는 키워드로, 현재 행보다 이전에 있는 행을 포함하도록 범위를 지정
**UNBOUNDED PRECEDING**은 현재 행을 기준으로 윈도우 프레임의 시작을 결과 집합의 첫 행으로 설정

FOLLOWING

윈도우 프레임 범위를 설정할 때 사용되는 키워드로, 현재 행보다 다음에 있는 행을 포함하도록 범위를 지정
**UNBOUNDED FOLLOWING**은 현재 행을 기준으로 윈도우 프레임의 끝을 결과 집합의 마지막 행으로 설정

DCL

DCL(Data Control Language)은 데이터베이스 내의 접근 권한과 관련된 작업을 수행하는 명령어
특정 사용자가 데이터베이스 내의 데이터나 객체에 접근하거나 조작할 수 있는 권한 관리
데이터베이스 관리자(DBA)는 데이터베이스의 보안을 유지하고 각 사용자가 수행할 수 있는 작업을 제어
민감한 데이터를 보호하고, 무결성을 유지

GRANT
- 사용자에게 데이터베이스 객체에 대한 권한을 부여
- 사용자는 해당 객체에 대해 조회, 수정, 삭제 등 수행 가능

GRANT SELECT, UPDATE, DELETE ON table_name TO user_name;

-- 사용자 **user_name**에게 table_name 테이블에 대한 
-- 조회(SELECT), 수정(UPDATE), 삭제(DELETE) 권한을 부여

REVOKE
- 사용자로부터 데이터베이스 객체에 대한 권한을 제거
- 회수 후 해당 사용자는 객체에 대한 작업 수행 불가

REVOKE SELECT, UPDATE, DELETE ON table_name FROM user_name;

-- 사용자 **user_name**로부터 table_name 테이블에 대한 
-- 조회(SELECT), 수정(UPDATE), 삭제(DELETE) 권한을 제거

ROLE의 개념

데이터베이스에서 ROLE은 권한을 편리하고 효과적으로 관리할 수 있도록 하는 기능
ROLE은 특정 권한들의 집합으로 사용자에게 필요한 권한을 일괄적으로 부여하거나 회수할 수 있음
사용자가 추가되거나 변경될 때 개별적으로 권한을 조절할 필요 없이, ROLE만 수정하여 일괄적으로 권한을 관리 가능

-- role 생성
CREATE ROLE role_name;

-- 권한 부여
GRANT SELECT, UPDATE ON table_name TO role_name;

-- role 할당
GRANT role_name TO user_name;

-- role 회수
REVOKE role_name FROM user_name;

코딩 일지

2024.05.09 55일차 DB TCL, 그룹함수, 윈도우함수, DCL

트랜잭션

트랜잭션의 특성 ACID

사례 - 은행 계좌이체

오라클 TCL

그룹함수

윈도우함수

PARTITION BY

ROWS

RANGE

PRECEDING

FOLLOWING

DCL

ROLE의 개념

티스토리툴바

2024.05.09 55일차 DB TCL, 그룹함수, 윈도우함수, DCL

트랜잭션

트랜잭션의 특성 ACID

사례 - 은행 계좌이체

오라클 TCL

그룹함수

윈도우함수

PARTITION BY

ROWS

RANGE

PRECEDING

FOLLOWING

DCL

ROLE의 개념

관련글

티스토리툴바