[SQLD] 2-2. SQL 활용 (2)

SQL/SQLD

유달잇 2021. 5. 26. 21:48

728x90

5. 그룹 함수

5-1. 그룹 함수의 종류

ROLLUP
CUBE
GROUPING SETS

표현식	출력값
GROUP BY ROLLUP (E1,E2)	E1과 E2별 소계 / E1별 소계 / 총합계
GROUP BY CUBE (E1,E2)	E1과 E2별 소계 / E1별 소계 / E2별 소계 / 총합계
GROUP BY GROUPING SETS (E1,E2)	E1별 소계 / E2별 소계

5-2. ROLLUP

GROUP BY의 확장 형태로 병렬로 수행 가능하기 때문에 매우 효과적이다.
시간 및 계층적 분류를 포함하고 있는 데이터의 집계에 적합하다.
인자로 넣어준 맨 앞 칼럼을 기준으로 전체 데이터의 그룹 함수 결과를 반환한다.
계층 구조임으로 GROUP BY의 칼럼 순서가 바뀌면 결과 값도 바뀐다.

5-3. CUBE

결합 가능한 모든 값에 대하여 다차원적인 집계를 생성할 수 있다.
ROLLUP에 비해 다양한 데이터를 얻을 수 있다.
시스템에 부하를 많이 준다.

5-4. GROUPING SETS

원하는 부분의 소계만 손쉽게 추출할 수 있다.
인수들의 순서가 바뀌어도 결과는 같다.
GROUP BY 칼럼 순서와 무관하게 개별적으로 처리한다.

6. 윈도우 함수

6-1. 순위 함수

RANK : 중복 순위 포함 ex) 1 2 2 2 5 6,,
DENSE_RANK : 중복 순위 무시 ex) 1 2 2 3 4 5,,
ROW_NUMBER : 단순히 행 번호 표시, 값에 무관하게 고유한 순위 부여 ex)1 2 3 4 5 6,,

6-2. 집계 함수

SUM : 합
MAX : 최댓값
MIN : 최솟값
AVG : 평균값
COUNT : 개수

6-3. 행 순서 함수

FIRST_VALUE : 최초의 첫 값(=MIN)
LAST_VALUE : 마지막 값(=MAX)
LAG : 이전 행
LEAD : 이후 행

6-4. 비율 함수

PERCENT_RANK : 백분율 순서
CUME_DIST : 현재 행 이하 값을 포함한 누적 백분율
NTILE : 전체 데이터를 N등분
RATIO_TO_REPORT : 총 합계에 대한 값의 백분율

7. DCL

7-1. DCL 이란?

유저를 생성하고 권한(Privileges)을 제어할 수 있는 명령어를 말하며 보안을 위해 필요하다.

7-2. GRANT

권한을 부여할 때 사용한다.

  
  GRANT 권한 ON 오브젝트 TO 유저명;

7-3. REVOKE

권한을 회수, 제거할 때 사용한다.

  
  REVOKE 권한 ON 오브젝트 TO 유저명;

7-4. ROLE

권한의 집합으로 권한을 일일이 부여하지 않고 ROLE으로 편하게 여러 권한을 부여할 수 있다.

  
  [Oracle] 
  CONN SYSTEM/MANAGER
  CREATE ROLE 롤이름; 
  GRANT 부여권한, 부여권한, ..... TO 롤이름; 
  GRANT 롤이름 TO 유저명;

8. 절자형 SQL

8-1. 절자형 SQL 이란?

일반적인 개발 언어처럼 절차지향적인 프로그램을 작성할 수 있도록 제공하는 기능이다.

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

ex) Procedure, User Defined Function, Trigger 저장 모듈 생성 가능

8-2. PL/SQL 구조

DECLARE
- 선언부 (변수, 상수) : BEGIN ~ END 에서 사용할 변수나 인수 정의 및 타입 선언
BEGIN
- 실행부 : 개발자가 처리하고자 하는 SQL문, 필요한 로직 정의
EXCEPTION
- 예외 처리부 : BEGIN ~ END에서 실행되는 SQL문에 발생된 에러 처리
END
- 끝

8-3. 프로시저와 트리거

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