728x90
반응형
2과목 2장, SQL 활용의 내용을 계속해서 다뤄볼 것이다. 이전글에서는 서브쿼리를 배웠고, 그룹함수, 윈도우 함수를 본 글에서 정리하고자 한다. 작년 시험일정과 비교해보니 약 열흘 간의 대비 기간이 있었는데 반해 올해는 불과 4일 가량 밖에 남지 않아 촉박한 상황이다. 그래도 포기하지 않고 꾸준히 공부하면 좋을 결과가 있지 않을까.
2024년도 이기적 SQLD 강의 내용은 보라색으로 표기했고, 유튜브 영상 혹은 교재와 함께 보는 것이 이해하는 데 도움이 되리라 본다. (2024.03.04 작성)
오늘은 3월 4일. 이론 파트 요약을 얼른 끝내야 할 텐데... Data On Air여... 나에게 힘을! 아, 이번 글부터는 이전글을 최하단으로 내렸다.
Index
5절 그룹 함수
1. 데이터 분석 개요
- 데이터 분석을 위한 3개 함수 (다중행 함수) (ANSI/ISO SQL 표준)
- 집계 함수 AGGREGATE FUNCTION : COUNT, SUM, AVG, MAX, MIN 등 집계 함수 포함.
- 그룹 함수 GROUP FUNCTION : 여러 레벨의 결산 보고서 작성 시 활용. ROLLUP, CUBE, GROUPING SETS 함수 등 - 다차원의 관점에서 집계 처리
- 윈도우 함수 WINDOW FUNCTION : 분석 함수(ANALYTIC FUNCTION) or 순위 함수 (RANK FUNCTION)로도 알려짐.
2. ROLLUP 함수
이기적 정리
- ROLLUP으로 소계와 합계를 한번에 추출 가능. (엑셀의 부분합과 유사)
GROUP BY ROLLUP (A, B);
=
GROUP BY A,B;
GROUP BY A;
GROUP BY 전체- A, B에 대한 합계, A에 대한 합계, 총합 표시.
- 각각의 쿼리를 UNION ALL로 묶는 것과 동일.
DATA ON AIR 정리
- Grouping Columns의 List는 Subtotal을 생성하는 데 사용. (N+1 Level의 Subtotal 생성)
- 인수의 순서 주의
- ROLLUP이나 CUBE에 의한 소계가 계산된 결과에는 GROUPING(EXPR) = 1, 그 외 GROUPING(EXPR) = 0으로 표기됨.
- ROLLUP 함수 사용 시, 괄호로 묶은 컬럼은 하나의 집합 칼럼으로 간주하여 괄호 내 각 칼럼별 집계를 구하지 않음.
3. CUBE 함수
이기적 정리
- 입력된 컬럼의 모든 경우의 수를 집계.
GROUP BY CUBE (A, B);
=
GROUP BY A,B;
GROUP BY A;
GROUP BY B;
GROUP BY 전체- 총합, B별 합계, A1 합계, A1, B합계... 순으로 표시.
- 입력된 컬럼의 2의 N제곱만큼 GROUP BY 시행.
DATA ON AIR 정리
- 결합 가능한 모든 값에 대하여 다차원 집계를 생성.
- Grouping Columns의 순서를 바꾼 Query의 추가 수행 필요.
- 표시된 인수들에 대한 계층별 집계를 구할 수 있음.
- 정렬 필요 시, ORDER BY 절에 칼럼 표기.
- GROUPING COLUMNS의 수가 N이라면, 2의 N제곱 LEVEL의 Subtotal 생성.
4. GROUPING SETS 함수
이기적 정리
- 입력한 컬럼대로 집계 처리
GROUP BY GROUPPING SETS (A, B);
=
GROUP BY A;
GROUP BY B;- B별 합계, A별 합계로 표시.
- 전체 합계를 구하려는 경우, SET 괄호 안에 (), 즉 빈 괄호를 넣으면 됨.
- GROUPING SETS ((A,B), A, ()) = ROLLUP (A, B)
DATA ON AIR 정리
- 표시된 인수들에 대한 개별 집계 도출 가능.
- 정렬 필요 시, ORDER BY 절에 칼럼 표기.
* GROUPING()
select case when grouping(부서코드) = 1 and grouping(입사년도) = 1
then...- GROUPING 함수와 select case when ~ then ~ else ~ end as ... 으로 NULL값 대체 가능.
- GROUP BY로 사용되면 0, 사용되지 않으면 1 부여.
6절 윈도우 함수
1. WINDOW FUNCTION 개요
- 행과 행간의 관계를 정의하거나, 행과 행간을 비교, 연산하고자 만든 함수.
- 데이터웨어하우스에서 발전.
- WINDOW FUNCTION 종류
- 순위(RANK) 관련 함수: RANK, DENSE_RANK, ROW_NUMBER
- 그룹 내 집계(AGGREGATE) 관련 함수: SUM, MAX, MIN, AVG, COUNT
- 그룹 내 행 순서 관련 함수: FIRST_VALUE, LAST_VALUE, LAG, LEAD
- 그룹 내 비율 관련 함수: CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT
- 통계 분석 관련 함수
- WINDOW FUNCTION SYNTAX
- OVER 문구가 필수.
- ex) SELECT WINDOW_FUNCTION (ARGUMENTS) OVER ( [PARTITION BY 칼럼] [ORDER BY 절] [WINDOWING 절] ) FROM 테이블 명;
- 매개변수 ARGUMENT는 없을 수도 있음.
- PARTITION BY: 테이블 내부의 행을 특정 컬럼을 기준으로 그룹화. (GROUP BY와 유사)
- ORDER BY: 그룹화한 (PARTITION BY) 대상을 정렬.
- WINDOWING 절: 테이블에서 사용하고자 하는 행의 범위 설정. (WINDOWING 사용 시, ORDER BY 필수)
2. 순위 함수
1) RANK
select ...
RANK () OVER (PARTITION BY 국가 ORDER BY 수입액 DESC) AS 순위
from ...- ORDER BY를 포함한 QUERY 문에서 특정 항목(칼럼)에 대한 순위를 구하는 함수
- 동 순위가 있으면 순위를 건너뛰고 부여 (예. 1, 2, 3, 3, 5 / 1, 1, 3, 4, 5)
2) DENSE_RANK: 같은 순위가 있어도 건너뛰지 않음. (예. 1,2,3,3,4)
3) ROW_NUMBER: 동일한 값이라도 고유한 순위를 부여 (예. 1,2,3,4,5)
3. 일반 집계 함수
SELECT ...
,SUM(price) OVER (ORDER BY 상품코드, 상품명 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT NOW)
AS 누적합계
FROM ...;1) SUM : 파티션별 윈도우의 합
2) MAX, MIN : 파티션별 윈도우의 최대, 최소값
3) AVG : 지정한 조건에 맞는 데이터에 대한 통계값 (ex) ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ☞ 현재 행 기준, 파티션 내에서 앞의 한 건, 뒤의 한 건을 범위로 지정)
4. 그룹 내 행 순서 함수
- SQL Server 미지원
1) FIRST_VALUE : 파티션별 윈도우에서 가장 먼저 나온 값.
2) LAST_VALUE : 가장 나중에 나온 값.
SELECT ...
, LAG(A) OVER (ORDER BY A) AS LAG_V
, LAG(B) OVER (ORDER BY B) AS LEAD_V
FROM ...;3) LAG (컬럼, N, Default): N번째 이전 행의 컬럼값을 출력. 없으면 DEFAULT 출력. 윈도우에서 이전 몇 번째 행의 값
4) LEAD (컬럼, N, Default): N번째 이후 행의 컬럼값을 출력. 없으면 DEFAULT 출력.
5. 그룹 내 비율 함수
- SQL Server 미지원
1) RATIO_TO_REPORT : 파티션 내 전체 SUM(칼럼)값에 대한 행별 칼럼 값의 백분율을 소수점으로 표현. | 결과 값은 0~1 의 범위, 개별 RATIO의 합 = 1.
2) PERCENT_RANK : 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여, 값이 아닌 행의 순서별 백분율 계산.
3) CUME_DIST : 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율.
4) NTILE : 파티션별 전체 건수를 ARGUMENT 값으로 N 등분한 결과 도출.
윈도우 함수 종료. 이후 TOP-N으로...
7절 DCL
DCL은 2과목 3장으로 새로 편성되었다. 아래의 링크에서 학습하길 권장한다.
1. DCL 개요
SQL 문장 구분DDL : 테이블 생성과 조작DML : 데이터 조작TCL : TRANSACTION 제어DCL (DATA CONTROL LANGUAGE) : 유저 생성 및 권한 제어
2. 유저와 권한
Oracle 제공 유저
Oracle과 SQL Server의 사용자 아키텍처 차이Oracle : 유저를 통해 데이터베이스에 접속 ▷ 아이디와 비밀번호 방식으로 인스턴스에 접속하고, 이에 해당하는 스키마에 오브젝트 생성 등의 권한 부여.SQL : 로그인 생성. 인스턴스 내에 존재하는 다수의 데이터베이스에 연결하여 작업하기 위해 유저를 생성한 후 로그인과 유저를 매핑. 로그인 방식은 아래와 같음.Windows 인증 방식 : Windows에 로그인한 정보 SQL Server에 접속하는 방식. Windows에서 사용자 ID를 확인함.혼합 모드 방식 : Windows 인증으로도 SQL Server에 접속 가능 + Oracle의 인증 방식인 사용자 아이디와 비밀번호로 SQL Server에 접속. 강력한 암호 (숫자+문자+특수문자 등의 혼용)이 요구됨.
1) 유저 생성과 시스템 권한 부여
모든 DDL 문장(CREATE, ALTER, DROP, RENAME 등)은 상응하는 권한이 있어야 문장을 실행할 수 있음 ▷ 시스템 권한 필요!롤 (ROLE)을 이용해 간편하고 쉽게 권한을 부여.유저 생성 권한 (CREATE USER) 부여.ex) GRANT CREATE USER TO SCOTT;
2) OBJECT에 대한 권한 부여
오브젝트 권한 : 특정 오브젝트인 테이블, 뷰 등에 대한 SELECT, INSERT, DELETE, UPDATE 작업 명령어.다른 유저의 테이블에 접근하려면 해당 테이블에 대한 오브젝트 권한을 소유자로부터 받아야 함.ex) GRANT SELECT ON MENU TO SCOTT;
3. Role
유저와 권한 사이의 중개 역할ROLE에 포함되어 있는 권한이 필요한 유저에게 해당 ROLE만을 부여.시스템 권한과 오브젝트 권한 모두 부여 가능. ROLE은 유저에게 직접 or 다른 ROLE에 포함되어 유저에게 부여될 수도 있음.
ex) CREATE ROLE LOGIN_TABLE; GRANT CREATE SESSION, CREATE TABLE TO LOGIN_TABLE;Oracle : CONNECT와 RESOURCE 주로 사용.유저 삭제 : DROP USER ▷CASCADE 옵션 : 오브젝트 삭제 후, 유저 삭제. ex) DROP USER JISUNG CASCADE;
SQL Server : ROLE에 멤버로 참여하는 방식 사용. Role을 자주 사용하지 않고, 서버 수준 역할 및 데이터베이스 수준 역할을 이용해 로그인 및 사용자 권한 제어.
2024 개정 이전 범위
8절 절차형 SQL
1. 절차형 SQL 개요
- 절차형 SQL : SQL문의 연속적인 실행이나 조건에 따른 분기처리를 이용해 특정 기능을 수행하는 저장 모듈 생성 가능
- 절차형 SQL로 만들 수 있는 저장 모듈 : Procedure, User Defined Function, Trigger
- 저장 모듈 : PL/SQL 문장을 데이터베이스 서버에 저장해 사용자와 애플리케이션 사이에서 공유할 수 있게 만든 일종의 SQL 컴포넌트 프로그램. 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램.
2. PL/SQL
- PL/SQL 특징
- PL/SQL은 Block 구조로 되어 각 기능별로 모듈화 가능
- 변수, 상수 등을 선언해 SQL 문장 간 값을 교환
- IF, LOOP 등의 절차형 언어를 사용해 절차적인 프로그램 가능
- DBMS 정의 에러나 사용자 정의 에러를 사용 가능.
- Oracle에 내장되어 Oracle과 PL/SQL을 지원하는 서버에 프로그램을 옮길 수 있음.
- PL/SQL은 응용 프로그램의 성능 향상
- 여러 SQL 문장을 Block으로 묶고 한 번에 Block을 모두 서버로 보내므로 통신량 절감.
- PL/SQL 구조
- DECLARE : BEGIN ~ END 절에서 사용될 변수와 인수에 대한 정의 및 데이터 타입을 선언하는 선언부.
- BEGIN ~ END : 개발자가 처리하고자 하는 SQL문과 여러 가지 비교문, 제어문을 이용하여 필요한 로직을 처리하는 실행부.
- EXCEPTION : BEGIN ~ END 절에서 실행되는 SQL문이 실행될 때 에러가 발생하면 그 에러를 어떻게 처리할 것이지를 정의하는 예외 처리부
- ex) CREATE [OR REPLACE] Procedure [Procedure_name] ( argument1 [mode] data_type1, argument2 [mode] date_type2, ... ... ) IS [AS] ... BEGIN ... EXCEPTION ... ... END; /
3. T-SQL
- SQL Server를 제어하기 위한 언어. MS에서 ANSI/ISO 표준의 SQL에 약간의 기능을 더 추가.
- 프로그래밍 기능
- 변수 선언 기능 @@이라는 전역변수(시스템 함수)와 @이라는 지역변수가 있음.
- 지역변수 : 사용자가 자신의 연결 시간 동안만 사용하기 위해 만드는 변수 전역변수 : SQL서버에 내장된 값.
- 데이터 유형(Data Type)을 제공 ( int, float, varchar 등)
- 연산자 사용 가능
- 흐름 제어 기능 IF-ELSE와 WHILE, CASE-THEN 사용 가능.
- 주석 가능
- 구조, 문법 PL/SQL과 유사.
4. Procedure & Trigger
- User Defined Function : Procedure처럼 절차형 SQL을 로직과 함께 데이터베이스 내에 저장해 놓은 명령문의 집합. RETURN을 사용해 하나의 값을 반드시 되돌려 줘야 함.
- Trigger : DML문이 수행됐 때, 데이터베이스에서 자동으로 동작하도록 작성된 프로그램.
- 프로시저와 트리거의 차이
참고
- Data On Air
- 23.03.04 이론 작성 시작.
- 23.03.05 이론 작성 완료.
- 24.03.04 2024 이기적 SQLD 강의 반영
728x90
반응형
