[SQLD] SQL 활용 - 2

💡 SQLD 자격증 시험 대비 학습한 내용을 요약정리합니다.

그룹 함수

데이터 분석을 위한 3가지 함수

1. Group Function
2. Aggregate Function
3. Window Function

Group Function(그룹 함수)

• 테이블의 전체 행을 하나 이상의 컬럼을 기준으로 컬럼값에 따라 그룹화하여 결과를 출력하는 함수
• 복수 행 함수라고도 한다.
• ROLLUP, CUBE, GROUPING SETS 함수가 있다.

(Group)Aggregate Function

• Group Function의 한 부분
• 각종 집계 함수들이 포함(COUNT, SUM, AVG, MAX 등)

ROLLUP 함수

• 소그룹 간의 소계를 계산하는 함수
• GROUP BY의 확장된 형태로 사용하기 쉽고, 병렬 수행 가능
• 계층적 분류를 포함하고 있어 데이터 집계에 적합
• ROLLUP에 지정한 Grouping Column의 List는 Subtotal을 생성하기 위해 사용.
• Grouping Column의 수를 n이라고 했을 때, n+1 레벨의 Subtotal이 생성.
• 인수 순서에 따라 결과가 바뀌게 되므로 순서에 주의해야 함
• 계층 간 집계에 대해 레벨 별 순서로 정렬함

GROUPING : Subtotal의 total을 생성

CUBE 함수

• 결합 가능한 모든 값에 대하여 다차원적인 집계 생성
• ROLLUP에 비해 다양한 데이터를 얻는 장점이 있지만 시스템에 부하를 많이 주는 단점이 있다.
• 내부적으로 Grouping Column의 순서를 바꿔 또 한번 쿼리를 추가 수행 해야 한다.
• 표시된 인수들에 대한 계층별 집계를 구할 수 있다.
• 표시된 인수들은 평등한 관계를 가져 순서가 바뀌더라도 결과에 영향을 미치지 않는다.
• Grouping Column의 수를 n이라고 했을 때, 2^n 레벨의 Subtotal를 생성

GROUPING SETS 함수

• 원하는 부분의 소계만 추출하는 함수
• 표시된 인수들에 대한 개별 집계를 구할 수 있다.
• 표시된 인수들은 평등한 관계를 가져 순서가 바뀌더라도 결과에 영향을 미치지 않는다.

💡 ROLLUP, CUBE, GROUPING SETS 함수는 결과에 대한 정렬이 필요한 경우 ORDER BY 절에 정렬 컬럼을 명시해야
한다.

---

Window Function

윈도우 함수

• 행과 행간의 관계를 정의하기 위해 만든 함수
• 분석함수, 순위 함수로도 불리운다.
• 다른 함수와 달리 중첩해서 사용하지 못하며, 서브쿼리에서 사용가능하다.
• OVER 문구가 키워드로 필수 포함된다.

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

• ARGUMENTS : 함수에 따라 0~n개의 인수 지정
• PARTITION BY절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.
• WINDOWING 절 : 함수에 대상이 되는 행 기분의 범위를 강력하게 지정. ROWS, RANGE 둘 중 하나만 사용 가능
  • ROWS : 물리적인 결과 행의 수
  • RANGE : 논리적인 값에 의한 범위

💡 WINDOWING 절은 SQL Server 에서는 지원하지 않음

윈도수 함수의 종류

• 그룹 내 순위 : RANK, DENSE_RANK, ROW_NUMBER
• 그룹 내 집계 : SUM, MAX, MIN, AVG, COUNT
• 그룹 내 순서 : FIRST_VALUE, LAST_VALUE, LAG, LEAD
• 그룹 내 비율 : COME_DIST, PERCENT_RANK, NTILE, RATIO_REPORT
• 선형 분석을 포함한 통계 분석 관련 함수 : 통계에 특화

그룹 내 순위 함수

RANK 함수

• ORDER BY를 포함한 쿼리문에서 특정 항목(컬럼)에 대한 순위를 구하는 함수
• 특정 범위(PARTITION) 내에서 순위를 구하거나, 전체 데이터에 대한 순위를 구한다.
• 동일한 값에 대해서 동일한 순위를 부여

DENSE_RANK 함수

• RANK과 동일한 기능
• RANK와 다르게 동일한 순위를 하나의 건수로 취급

ROW_NUMBER 함수

• RANK와 동일한 기능
• RANK와 달리 동일한 값이라도 고유한 순위를 부여

일반 집계 함수

SUM : 파티션 별 윈도우의 합
ex) 같은 매니저를 두고 있는 사원들의 월급 합

MAX : 파티션 별 윈도우의 최대값
ex) 같은 매니저를 두고 있는 사원들의 월급 중 최대값

MIN : 파티션 별 윈도우의 최소값
ex) 같은 매니저를 두고 있는 사원들의 월급 중 최소값

AVG : 파티션 별 ROWS 윈도우를 이용해 원하는 조건에 맞는 데이터에 대한 통계값
ex) 같은 매니저를 두고 있는 사원들 중, 자신 바로 앞, 바로 뒤의 사번인 직원만을 대상으로 평균 월급을 구해라

COUNT : 파티션 별 ROWS 윈도우를 이용해 원하는 조건에 맞는 데이터에 대한 통계값
ex) 본인의 급여보다 50 이하가 적거나 150 이하로 많은 급여를 받는 인원수

그룹 내 행 순서 함수

SQL Server에서 지원 X

FIRST_VALUE

• 파티션 별 윈도우에서 가장 먼저 나온 값.
• 공동 등수를 인정하지 않고 처음 나온 행만 처리

LAST_VALUE

• 파티션 별 윈도우에서 가장 나중에 나온 값.
• 공동 등수를 인정하지 않고 가장 나중에 나온 행만 처리

LAG : 파티션 별 윈도우에서 이전 몇 번째 행의 값

LEAD : 파티션 별 윈도우에서 이후 몇 번째 행의 값

그룹 내 비율 함수

SQL Server에서 지원 X (NTILE은 지원 O)

RATIO_TO_REPORT

• 파티션 내 전체 SUM(컬럼)값에 대한 행 별 컬럼 값의 백분율을 소수점으로 구할 수 있다.
• 0< 결과값 <= 1
• 개별 결과값의 합 = 1

PERCENT_RANK

• 파티션 별 윈도우에서 제일 먼저 나오는 것을 0, 제일 늦게 나오는 것을 1로 하여 값이 아닌 행의 순서별 백분율을 구함
• 0 <= 결과값 <= 1

CUME_DIST

• 파티션 별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율을 구함
• 0 < 결과값 <= 1

NTILE : 파티션 별 전체 건수를 ARGUMENT 값으로 N등분한 결과

---

DCL

유저를 생성하고 권한을 제어하는 명령어

오라클, SQL Server의 사용자에 대한 아키텍쳐 차이

오라클

• 유저를 통해 DB에 접속
• 아이디, 비밀번호 방식으로 인스턴스에 접속하고 그에 해당하는 스키마에 오브젝트 생성등의 권한을 부여

SQL Server

• 인스턴스에 접속하기 위해 로그인 생성
• 인스턴스 내에 존재하는 다수의 DB에 연결하여 작업하기 위해 유저를 생성한 후 로그인과 유저를 매핑
• 특정 유저는 특정 DB내의 특정 스키마에 대한 권한을 부여받음
• 유저는 DB마다 존재하기 때문에 유저 생성을 위해서 생성하고자 하는 유저가 속할 DB로 이동 후 처리해야 함함

SQL Server의 로그인 방식

1. 윈도우 인증 방식 : 윈도우에 로그인한 정보를 가지고 SQL Server에 접속
2. 혼합 모드 방식 : 윈도우 인증 또는 SQL 인증으로 진행됨

시스템 권한 : 사용자가 실행하는 모든 DDL 문장을 실행할 수 있는 권한
GRANT : 권한 부여
REVOKE : 권한 취소

💡 모든 유저는 각각 자신이 생성한 테이블 외에 다른 유저의 테이블에 접근하려면 해당 테이블에 대한 오 브젝트 권한을 소유자로부터 부여받아야 한다.

ROLE

• 각종 권한을 하나의 묶음으로 모아 유저에게 부여하는 것
• 시스템 권한과 오브젝트 권한을 모두 부여할 수 있다.
• 권한을 직접 부여하는 것보다 빠르고 안전하게 유저를 관리할 수 있다.

// 역할 생성
CREATE ROLE LOGIN_TABLE; 
// 권한 부여
GRANT CREATE TABLE TO LOGIN_TABLE; 
// 유저 삭제
DROP USER PJS CASCADE;

CASCADE : 하위 오브젝트까지 삭제

💡 SQL Server에서는 ROLE을 생성하여 사용하기 보다 기본적으로 제공되는 ROLE에 멤버로 참여하는 방식을 사용하며 서버, DB 수준 역할을 이용하여 로그인 및 사용자 권한을 제어한다.

---

절차형 SQL

SQL문의 연속적인 실행이나 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈을 생성할 수 있다.

PL/SQL

• 오라클에서 제공하는 절차적 프로그래밍을 가능하게 하는 트랜잭션 언어
• 다양한 저장 모듈을 개발할 수 있다.

저장 모듈

• PL/SQL문장을 DB 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램.
• 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램
• 오라클의 저장 모듈 : Procedure, User Defined Function, Trigger

PL/SQL의 특징

• Block 구조로 되어있어 각 기능별로 모듈화가 가능
• 변수, 상수등을 선언하여 SQL 문장 간 값을 교환한다.
• IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능
• DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다.
• 응용 프로그램의 성능을 향상시킨다.
• 여러 SQL문장을 Block으로 묶고 한 번에 Block 전부를 서버로 보내 통신량을 줄임

PL/SQL 아키텍쳐

• SQL 문장과 프로그램 문장을 구분하여 처리
• 프로그램 문장 : PL/SQL 엔진이 처리
• SQL 문장 : 오라클 서버의 SQL Statement Executor가 처리

PL/SQL 구조

• DECLARE : BEGIN-END 절에서 사용될 변수, 인수에 대한 정의 및 데이터 타입을 선언하는 선언부.
• BEGIN-END : 개발자가 처리하고자 하는 SQL문과 여러가지 비교문, 제어문을 이용하여 필요한 로직을 처리하는 실행부.
• EXCEPTION : BEGIN-END 절에서 실행되는 SQL문이 실행될 때 발생하는 에러를 처리하는 예외 처리부.

PL/SQL 기본 문법

// 프로시저 생성
CREATE Procedure [Procedure_name]
// DB에 같은 이름의 프로시저가 있을 시, 기존 내용을 무시하고 새 내용을 덮어씌움
REPLACE Procedure [Procedure_name]
// 프로시저 삭제
DROP Procedure [Procedure_name]
/ <- 컴파일 하라는 명령어 

T-SQL의 특징

• SQL Server를 제어하기 위한 언어
• 전역변수(SQL 서버에 내장된 값)와 지역변수를 선언할 수 있다.
• 데이터 유형(int, float, varchar 등)을 제공한다.
• 산술, 비교, 논리 연산자 사용 가능
• 흐름 제어 기능(IF-ELSE, WHILE, CASE-THEN) 사용 가능

T-SQL의 구조

• DECLARE : BEGIN-END 절에서 사용될 변수, 인수에 대한 정의 및 데이터 타입을 선언하는 선언부.
• BEGIN-END : 개발자가 처리하고자 하는 SQL문과 여러가지 비교문, 제어문을 이용하여 필요한 로직을 처리하는 실행부.
• ERROR 처리 : BEGIN-END 절에서 실행되는 SQL문이 실행될 때 발생하는 에러를 처리하는 예외 처리부.

💡 BEGIN-END문을 필수적으로 사용 X, 블록 단위로 처리하고자 할 때는 필수적이다.

CREATE Procedure schema_NAME.Procedure_name

User Defined Function : Return을 사용해 하나의 값을 반드시 반환해야 함

Trigger

• 특정한 테이블에 DML문이 수행되었을 때, DB에서 자동으로 동작하도록 작성된 프로그램
• 사용자가 직접 호출하여 사용하는 것이 아니라 DB에서 자동적으로 수행
• 전체 트랜잭션 작업에 대해 발생되는 Trigger와 각 행에 대해 발생하는 Trigger가 있다.

CREATE Trigger Trigger_name

프로시저와 트리거의 차이점

프로시저는 BEGIN~END 절 내에 COMMIT, ROLLBACK과 같은 트랜잭션 종료 명령어 사용가능, DB 트리거는 BEGIN~END 절 내에 사용 불가

프로시저	트리거
CREATE Procedure 문법 사용	CREATE Trigger 문법 사용
EXECUTE 명령어로 실행	생성 후 자동으로 실행
COMMIT, ROLLBACK 실행 가능	COMMIT, ROLLBACK 실행 안됨