[231229] SQLD: JOIN, 집합연산자, 서브쿼리, 그룹함수, 윈도우함수

* 수강 강의명: SQLD 자격증 대비반 - 11~14주차 

 [요약] 

1. JOIN 

1)  개요 

: 두 개 이상의 테이블을 연결 또는 결합하여 데이터를 출력하는 것

: FROM 절에 여러 테이블을 나열하여 JOIN 가능한데, 이때 JOIN 발생은 두 테이블만 가능(여러 개 합치려면 JOIN을 여러 번)

: 두개의 테이블을 JOIN 하기 위해서는 적어도 하나의 공통된 컬럼 필요

 

- EQUI JOIN (등가 조인) 

: 두 테이블 간에 칼럼의 값들이 서로 같은(=) 경우 두 데이터를 하나의 데이터로 합치는 JOIN

SELECT 테이블1.칼럼명, 테이블2.칼럼명, ... 
FROM 테이블1, 테이블2 
WHERE 테이블1.칼럼명1 = 테이블2.칼럼명2;

 

- Non EQUI JOIN (비등가 조인)

: 두 개의 테이블 간에 칼럼들이 서로 정확하게 일치하는 것이 아닌 특정 범위 내에 있는 경우 JOIN을 하고자 할 때 사용

(cf) JOIN 조건이 정의되지 않았거나 잘못된 경우, 하나의 테이블이 다른 테이블의 모든 행과 JOIN이 됨.

       즉, 모든 행의 조합이 나오는데 이를 Cartesian Product(카티션 곱)이라 부름

 

- 3개 이상 TABLE JOIN

: 3개 이상의 테이블을 JOIN을 할 때는 FROM 절에 JOIN 하고자 하는 테이블을 차례대로 작성

: 첫 번째와 두 번째 순서의 테이블이 먼저 JOIN 되고, 그 다음 두 번째와 세 번째 테이블 JOIN됨

: JOIN 진행 시 시스템의 부하가 되므로 역정규화 하여 최적의 테이블 구조를 만드는 것이 중요

 

2) 표준 조인

: 관계형 대수는 크게 4개의 일반 집합 연산자와 4개의 순수 관계 연산자로 나뉨

 

-일반 집합 연산자

└ UNION 연산: 합집합. UNION ALL은 공통 집합을 그대로 출력하는 연산자. 정렬 및 중복 작업을 안해 효율이 좋음 
└ INTERSECTION 연산: 교집합. 두 집합의 공통 행을 추출할 때 사용 

└ DIFFERENCE 연산: 차집합. 오라클에서는 MINUS, ANSI 표준 회사에서는 EXCEPT로 사용 

└ PRODUCT 연산: 곱집합. JOIN 조건 없는 경우 추출되는 모든 데이터 조합. CARTESIAN PRODUCT(데카르트의 곱)

 

-순수 관계 연산자

└ SELECT 연산: 특정 행에 대한 부분 집합. SELECT 문의 WHERE절로 표현 가능
└ PROJECT 연산: 특정 열에 대한 부분 집합. 원하는 칼럼을 뽑아야 할 경우 사용하는 연산자

└ JOIN 연산: 공통 속성을 중심으로 2개의 테이블을 하나로 합쳐서 새로운 테이블을 만드는 연산자

└ DIVIDE 연산: SQL 문으로 구현되지 않은 연산자 

 

 

- 대표적인 JOIN 방법

조인 형태	설명
INNER JOIN	- JOIN 조건에서 일치하는 행만 반환 - EQUI 조인이라고도 함
NATURAL JOIN	- 두 테이블 간의 동일한 이름을 갖는 모든 칼럼에 대해 INNER JOIN 수행 - Oracle에서만 지원
USING 조건절	- 같은 이름을 가진 칼럼에서 원하는 칼럼만 선택적으로 INNER JOIN 가능 - Oracle에서만 지원
ON 조건절	- 명시적으로 JOIN 조건을 지정하는 데 사용 - 칼럼명이 달라도 JOIN 조건 사용 가
CROSS JOIN	- PRODUCT 개념으로 테이블 간 모든 데이터의 조합을 의미
OUTER JOIN	- INNER JOIN과 대비하여 JOIN 조건에서 동일한 값이 없는 행도 결과 집합에 포함시킬 때 사용

(1) INNER JOIN

- JOIN 조건에서 일치하는(동일한 값이 있는) 행만 반환
- INNER는 생략 가능하며, 반드시 USING 조건절이나 ON 조건절을 함께 사용

SELECT 테이블1.칼럼1, 테이블2.칼럼2, ..
FROM 테이블1 [INNER] JOIN 테이블2 
	ON 테이블1.칼럼 = 테이블2.칼럼;

 

(2) NATURAL JOIN

- JOIN 조건을 지정하지 않아도 동일한 이름과 유형을 갖는 모든 칼럼을 자동으로 EQUI JOIN

- JOIN 기준 칼럼에 ALIAS나 접두사 등을 붙일 수 없음 

- SQL Server 에서는 지원하지 않는 기능

SELECT 칼럼1, 칼럼2, ..
FROM 테이블1 NATURAL JOIN 테이블2;

 

(3) USING 조건절

- 같은 이름을 가진 칼럼들 중에서 원하는 칼럼만 선택적으로 INNER JOIN

- USING 절을 이용하려면 NATURAL JOIN 절은 JOIN만 명시 

- JOIN 칼럼에서 ALIAS나 접두사 붙일 수 없으나, 다른 칼럼에서는 가능 
- SQL Server 에서는 지원하지 않는 기능

SELECT 칼럼1, 칼럼2, ..
FROM 테이블1 JOIN 테이블2 
	USING (EXPR);

 

(4) ON 조건절

- ON 조건절은 칼럼명이 다르더라도 JOIN 조건 사용 가능

- ON 조건절에는 ALIAS를 통해 칼럼명을 구분하는 것을 권장

- WHERE절과 함께 사용 가능하며, WHERE 대신 ON에 조건을 추가로 하는 것도 가능

 

- 3개 이상 테이블 조인 시, ON 다음에 JOIN을 중첩적으로 명시 

└ 최소 JOIN 조건의 수 = JOIN 테이블의 수 - 1

SELECT e.ename, e.deptno, d.deptno, d.dname
FROM emp e JOIN dept d 
	ON e.deptno = d.deptno
WHERE e.deptno = 30;

 

(5) CROSS JOIN

- 테이블 간 JOIN 조건이 없는 경우 모든 데이터 조합 결과를 출력 

- 일반 집합 연산자의 PRODUCT 개념과 같으며, 결과는 M*N 건의 데이터 조합으로 발생

- 필요한 경우는 많지 않으나 튜닝이나 리포트 작성 시 의도적으로 사용하기도 함

SELECT ename, dname
FROM emp CROSS JOIN dept
ORDER BY ename;

 

(6) OUTER JOIN

- LEFT JOIN / RIGHT JOIN / FULL JOIN* 으로 분류 

- OUTER 테이블은 모두 출력되고 INNER 테이블은 매칭되는 데이터만 출력

- 동일한 값이 없는 데이터도 반환할 때 사용 가능 

- USING 조건절이나 ON 조건절을 반드시 사용

* FULL JOIN은 LEFT와 RIGHT의 결과를 합집으로 처리 

SELECT A.DEPT_NO , A.DEPT_NM
	, NVL(B.EMP_NO, 'NULL') AS EMP_NO
	, NVL(B.EMP_NM, 'NULL') AS EMP_NM
FROM DEPT_EX A LEFT OUTER JOIN EMP_EX B
	ON (A.DEPT_NO = B.DEPT_NO AND A.DEPT_NM = '개발팀') 
WHERE A.DEPT_NO IS NOT NULL

 

 

3) 계층형 질의와 표준 셀프 조인

(1) 계층형 질의

: 계층형 데이터는 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 의미

: 계층형 데이터 조회를 위해 계층형 질의 필요

 

간단히 알아보는 용어 정리

· L EVEL - 각 데이터의 계층   

· NODE - 각각의 데이터  - A, B, C, D, E 

· ROOT (NODE) - 최상위 노드 - A

· Parent Node - 노드의 상위 노드 - A, C 

· Child Node - 노드의 하위 노드 - B,C / D,E

· Leaf Node - 하위 노드가 없는 노드 - D, E

 

(2) Oracle 계층형 질의

구문	설명
SELECT 칼럼	- 조회 칼럼 지정
FROM 테이블	- 대상 테이블 지정
WHERE	- 조건에 맞는 데이터 추출 - 계층적으로 진행 후 특정한 조건에 맞는 데이터만 추출
START WITH	- 계층 구조 전개의 시작 위치 지정 - 루트 데이터(최상위 노드)를 설정하는 구문
CONNECT BY [NOCYCLE]	- 연결될 자식 데이터의 조건을 지정하는 구문 (연결 고리) - NOCYCLE: 이미 전개된 데이터와 동일한 데이터가 나타나는 경우 = 사이클이 형성된 상황. 오류로 인식되나, NOCYCLE 구문 추가 시 사이클 이후 데이터는 미전개됨
[PRIOR] A AND B	- CONNECT BY 절에 사용되며, 현재 읽은 칼럼을 지정 - PRIOR 자식 = 부모 형태로 사용. 부모 → 자식은 순방향, 자식 → 부모는 역방향
ORDER SIBLINGS BY	- 형제 노드(동일한 레벨) 사이에서 정렬을 수행하는 구문

(cf) 순환 구조를 만든다 = 계층을 만든다 

 

- 계층형 질의 시 제공되는 가상 칼럼(Pseudo Column)

· LEVEL: 루트 데이터는 값이 1이며, 하위 데이터로 내려갈수록 1씩 증가
· CONNECT_BY_ISLEAF: 전개 과정에서 해당 데이터가 각 트리 경로의 마지막 값이면 1, 그렇지 않다면 0 출력
· CONNECT_BY_ISCYCLE: 전개 과정에서 자식 데이터를 확인해 부모 데이터가 있다면 1, 그렇지 않다면 0 출력. 무한 반복을 방지하기 위한 NOCYCLE 옵션 시에만 사용 가능 

SELECT COL,...[LEVEL, CONNECT_BY_ISLEAF, CONNECT_BY_ISCYCLE]
FROM TB1
WHERE condition AND condition
START WITH condition
CONNECT BY [NOCYCLE] condition AND condition ...
[ORDER SIBLINGS BY COL1, COL2, ...]

 

- 순방향 예시

SELECT LEVEL,
			 -- 값이 없을땐 null 출력됨 그래서 || empno 을 이용하여 현재 empno정보를 출력
		 	 LPAD(' ', 4 * (LEVEL-1)) || empno AS EMPNO, 
			 mgr,
			 CONNECT_BY_ISLEAF AS ISLEAF
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

(cf) LPAD("값", "문자의 총 길이", "채울 문자"): 지정한 길이만큼 왼쪽부터 특정한 문자로 채우는 명령어 (하단 EMPNO참조)

 

 

- 역방향 예시 

SELECT 
       LEVEL,
       LPAD(' ', 4 * (LEVEL - 1)) || empno AS EMPNO,
       mgr,
			 CONNECT_BY_IS_LEAF AS ISLEAF
FROM emp
START WITH empno = 7876 
CONNECT BY PRIOR mgr = empno;

 

(3) 셀프 조인(SELF JOIN)

- 동일한 테이블 간의 JOIN을 의미
- SELF JOIN을 수행 시 각 테이블을 식별하기 위해 반드시 테이블의 ALIAS(별칭) 필요

 

2. 집합연산자(SET OPERATOR)

- 개념: 2개 이상의 쿼리 수행 결과를 하나의 결과로 묶어주는 연산자. SELECT 절의 칼럼 수가 동일해야 사용 가능 

ORDER BY는 집합 연산을 적용한 최종 결과에 대해 한 번만 기술하면 됨

 

- 종류

집합 연산자	설명
UNION	- SQL문 결과에 대한 합집합 중복 X - 중복 배제하기 위한 정렬 연산이 있어 시스템의 부하가 있음
UNION ALL	- SQL문 결과에 대한 합집합 중복 O - 주로 여러 쿼리문의 결과가 상호 배타적일 때 많이 사용
INTERSECT (INTERSECTION)	- SQL문 결과에 대한 교집합 중복 X
MINUS(EXCEPT)	- SQL문 결과에 대한 차집합 중복 X - 특정한 SQL문 결과에서 다른 SQL문 결과를 뺀 값

 

 

3. 서브쿼리(Subquery)

- 개념: 하나의 SQL 문안에 포함되어 있는 또 다른 SQL 문

 

- 특징 

: 서브쿼리는 주인공 쿼리를 도와주는 작은 도우미 쿼리

: 주요 쿼리의 일부로서, 복잡한 정보를 가져오거나 원하는 조건을 충족키기 위해 사용 
: 서브쿼리는 메인쿼리와 같은 레벨로 취급 (메인에 포함된 형태)

: 서브쿼리는 메인쿼리의 칼럼을 모두 사용할 수 있으나 메인쿼리는 서브쿼리 칼럼을 사용할 수 없음

 

- 사용 시 주의사항

① 서브쿼리는 소괄호`()`로 감싸서 사용
② 서브쿼리는 단일행 또는 복수행 비교 연산자와 함께 사용 가능
  └ 서브쿼리 결과가 단일행은 반드시 1건, 복수행은 건수 상관 없음(1건 이)
③ 서브쿼리에서 ORDER BY를 사용 불가

- 분류  

① 동작방식

: 메인쿼리에서 읽힌 데이터에 대해 서브쿼리에서 해당 조건이 만족하는지 확인하는 방식으로 동작

· 연관 쿼리: 서브쿼리가 메인쿼리의 값을 사용하는 경우. 데이터가 서브쿼리 조건과 맞는지 확인 목적으로 사용 

· 비연관 쿼리: 서브쿼리가 메인쿼리의 값을 사용하지 않는 경우. '서브쿼리 실행 결과값' 제공 목적으로 사용

 

#연관 쿼리
SELECT A.EMPNO, A.ENAME, A.DEPTNO, A.SAL 
FROM EMP A
WHERE A.SAL >= (
				SELECT AVG(X.SAL)
			      FROM EMP X
				 WHERE X.DEPTNO = A.DEPTNO
				 GROUP BY X.DEPTNO );    -- 서브쿼리 조건과 맞는지 비교

#비연관 쿼리
SELECT first_name, height
FROM PARTICIPANT
WHERE height > (SELECT AVG(height) FROM PARTICIPANT); -- 서브쿼리 결과값 제공

(cf) EXISTS 서브쿼리: 서브쿼리 결과를 만족하는 값의 존재 여부를 확인하는 조건. 만족 시 건수에 상관 없이 1로 표기됨  

 

② 데이터 반환 형태

: 서브쿼리가 메인 쿼리에 몇 개의 행 혹은 칼럼을 반환하는지에 따라 단일행, 다중행, 다중칼럼 서브쿼리로 구분

 

· 단일행 서브쿼리(Single Row)

: 실행 결과가 항상 1건 이하. 단일 행 비교 연산자인 =, <, ≤, >, ≥, <>과 함께 사용

SELECT A.EMPNO, A.ENAME, A.SAL 
FROM EMP A
WHERE A.SAL >= (SELECT AVG(K.SAL) AS MAX_SAL FROM EMP K)
ORDER BY A.SAL ;

 

· 다중행 서브쿼리(Multi Row)

: 실행 결과가 여러 건. 다중 행 비교 연산자인 IN, ALL, ANY, SOME, EXISTS와 함께 사용

SELECT A.EMPNO, A.ENAME, A.DEPTNO 
	FROM EMP A
 WHERE A.DEPTNO IN      -- 단일행 비교 연산자 = 사용시 error
                 (
				   SELECT K.DEPTNO 
					FROM DEPT K
                    WHERE K.DNAME IN ('ACCOUNTING', 'SALES')
                 )
ORDER BY A.DEPTNO ;

└ IN(서브쿼리): 서브쿼리 결과값이 포함된, 동일한 조건 
└ ALL(서브쿼리): 서브쿼리 결과값이 모든 값을 만족하는 조건
└ ANY(서브쿼리) / SOME (서브쿼리): 서브쿼리 결과값이 어느 하나라도 만족하는 조건 
└ EXISTS(서브쿼리): 서브쿼리 결과값 존재 여부를 확인하는 조건 

 

· 다중칼럼 서브쿼리(Multi Column)

실행 결과로 여러 칼럼을 반환. 메인쿼리의 조건절에서 동일한 개수와 위치의 여러 칼럼을 동시 비교 가능

#WHERE절에 복수의 칼럼 조건을 넣은 예시 
SELECT first_name, last_name, height, weight
FROM PARTICIPANT
WHERE (nation_id, gender) IN (
    SELECT nation_id, 'F'
    FROM NATION
    WHERE country_name = 'United States'
);

#FROM절에 서브쿼리를 넣은 것도 다중칼럼 서브쿼리에 해당

 

③ 기타 서브쿼리

· SELECT 서브쿼리:  '스칼라 서브쿼리(Scala Subquery)'. 하나의 행과 하나의 칼럼(1 Row - 1 Column)만을 반환

· FROM 서브쿼리 : '인라인 뷰(Inline View) 서브쿼리'.  FROM 절 서브쿼리 결과는 동적으로 생성된 테이블처럼 사용 가능
· WHERE 서브쿼리: '중첩(Nested) 서브쿼리'라고도 부름 
· HAVING 서브쿼리: 그룹핑 결과에 대해 부가적인 조건을 추가 하기 위해 사용
· UPDATE문의 SET 서브쿼리: 사용 시 수정 사항이 전체 테이블에 적용되지 않도록 추가 WHERE절을 사용해야 함

· INSERT문의 VALUES 서브쿼리: 서브쿼리를 통해 새로운 값 추가 
   

④ 뷰(VIEW)

· 개념  

: 데이터베이스에서 저장된 정보를 좀 더 편리하게 보여주기 위해 사용되는 가상의 테이블
: 이미 존재하는 데이터 테이블에서 원하는 정보를 필터링하거나 정리해서 새로운 가상 테이블을 만드는 것

: 테이블은 실제 데이터를 보유하고 있으나, 뷰는 데이터를 보유하고 있지 않음 

 

· 장점: 독립성 / 편리성 / 보안성 

 

· VIEW의 정의: SELECT문으로 구  

└ 칼럼명 생성 시 V에서 온 것이라는 의미로 V_ 를 주로 사용함

#뷰 생성

CREATE VIEW V_DEPT_EMP_FILTER AS
SELECT ENAME, JOB
FROM V_DEPT_EMP
WHERE EMPNO IN (7698, 7788);

#뷰로 데이터 조회 
SELECT ENAME,JOB
FROM V_DEPT_EMP
WHERE EMPNO IN (7698, 7788);

#뷰 제거
DROP VIEW V_DEPT_EMP;
DROP VIEW V_DEPT_EMP_FILTER;

 

 

4. 그룹 함수

: 데이터에 대한 결산 개념의 연산을 할 때 주로 사용하는 함수

 

(1) ROLLUP 함수

 - 개념: 칼럼으로 그룹을 만든 후 각 칼럼의 중간 합계를 만들기 위해 사용하는 함수 

  └ 계층 구조이기 때문에 함수 내 인자 순서가 바뀌면 결과도 달라짐

  └ ROLLUP 함수는 N + 1개 합계 결과가 생성되는데, +0은 그룹화된 칼럼들의 전체 합계를 의미

  └  L1(GROUP BY 표준 집계) > L2(지정 그룹별 중간합) > L3(전체 합계) 순으로 정렬 (칼럼명은 별도 ORDER BY 필요)

SELECT 
	DNAME, 
	JOB, 
	COUNT(*) "Total Empl", 
	SUM(SAL) "Total Sal"
FROM EMP,
     DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY ROLLUP (DNAME, JOB)
ORDER BY DNAME, JOB;

- GROUPING 함수: 그룹화 된 칼럼 소계값을 1로 표시하고, 그외는 0으로 표기. 소계와 합계로 이뤄진 행 구분에 사용

- ROLLUP과 CASE문: 중간 합은 기본 NULL로 출력되며, 별도 값을 입력하고 싶을 때 GROUPING과 CASE문 활용

SELECT 
    CASE GROUPING(DNAME) WHEN 1 THEN 'All Departments' ELSE DNAME END AS DNAME,
    CASE GROUPING(JOB) WHEN 1 THEN 'All Jobs' ELSE JOB END            AS JOB,
    COUNT(*) "Total Empl",
    SUM(SAL) "Total Sal"
FROM EMP,
     DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME, ROLLUP(JOB);

 

- ROLLUP과 괄호: 괄호 위치에 따라 중간 합계 기준이 달라져 결과치가 상이함

 

(2) CUBE 함수

 - 개념: 그룹화된 데이터의 모든 가능한 조합에 대해 합계를 계산하는 것
  └ 칼럼의 순서가 바뀌어도 정렬 순서는 바뀌지만 데이터 결과는 동일

  └ GROUPING 칼럼의 수가 N이라고 가정하면  2ⁿ개의 중간 합계를 생성

  └ ROLLUP 함수에 비해서 시스템의 연산 대상이 많은 것이 특징

SELECT 
    CASE GROUPING(DNAME) WHEN 1 THEN 'All Departments' ELSE DNAME END AS DNAME,
    CASE GROUPING(JOB) WHEN 1 THEN 'All Jobs' ELSE JOB END            AS JOB,
    COUNT(*) "TotalEmpl",
    SUM(SAL) "Total Sal"
FROM EMP,
     DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY CUBE (DNAME, JOB);

 

(3) GROUPING SETS

 - 개념: 다양한 소계 집합을 만들 수 있는 함수

 └ 표시된 칼럼들에 대한 개별 집계를 구할 수 있으며, 순서가 바뀌어도 결과는 동일

 └ ROLLUP과 CUBE와 비슷한 결과를 얻을 수 있지만 명시적으로 원하는 그룹 수준 설정 가능

 └ 다수 칼럽을 그룹화하여, 하나의 인수로 사용 가능 (ex. GROUP BY GROUPING SETS ((A, B, C), (B, C), (A, C))

SELECT 
    DECODE(GROUPING(DNAME), 1, 'All Departments', DNAME) AS DNAME,
    DECODE(GROUPING(JOB), 1, 'All Jobs', JOB)            AS JOB,
    COUNT(*) "Total Empl",
    SUM(SAL) "Total Sal"
FROM EMP,
     DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY GROUPING SETS (DNAME, JOB);

 

 

5. 윈도우 함수

(1) 개요

- 정의: 행과 행 간의 관계를 쉽게 정의하기 위해 만들어진 함수 

└ GROUP BY 구문과 중첩(NEST) 사용할 수 없지만, 서브쿼리에서는 사용 가능

 

- 기본 구조 

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

 

- ARGUMENTS: 함수에 따라 0~N 개 인수가 지정

- PARTITION BY: 그룹핑 기준을 설정하는 절로 GROUP BY와 유사한 개념이나 분할만 수행 가능 
- ORDER BY: 어떤 항목을 기준으로 순위를 정할지 기술 
- WINDOWING: 대상이 되는 행 기준의 범위 지정. SQL Server에서는 미지원 

 └ ROWS: 물리적 단위로 출력된 행의 개수를 지정
.└ RANGE: 논리적 값의 범위

.└ PRECEDING: 이전 범위

.└ FOLLOWING: 이후 범위

.└ BETWEEN 시작 AND 끝: 윈도우의 시작과 끝

.└ CURRENT ROW: 윈도우 시작 위치가 데이터 출력된 현재 행임을 의미 

 

(2) 그룹 내 순위 함수

① RANK 함수

: 특정 칼럼에 대한 순위를 구하는 함수.

: 동일한 값에 동일한 순위를 부여하며, 다음 순위는 누적된 순위로 계산

SELECT JOB,ENAME, SAL,
       RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK
FROM EMP;

 

② DENSE_RANK 함수

: RANK와 동일하게 순위를 구하는 함수.

: 동일한 값에 동일한 순위를 부여하며, 누적 없이 순차대로 순위 부여

SELECT JOB, ENAME, SAL,
    RANK() OVER (ORDER BY SAL DESC) RANK,
    DENSE_RANK() OVER (ORDER BY SAL DESC) DENSE_RANK
FROM EMP;

 

③ ROW_NUMBER 함수

: 동일한 순위라도 고유한 순위 부여. 세부 정렬은 ORDER BY를 통해 정리

SELECT JOB, ENAME, SAL,
    RANK() OVER (ORDER BY SAL DESC)       RANK,
    ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUMBER
FROM EMP;

 

(3) 일반 집계 함수 

: 그룹 내 파티션 별로 윈도우의 합, 최대값, 최소값, 평균, 개수 등을 구하는 함수 

#예시
SELECT MGR, ENAME, SAL, 
	SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM
    MAX(SAL) OVER (PARTITION BY MGR) MGR_MAX
    MIN(SAL) OVER (PARTITION BY MGR) MGR_MIN
    AVG(SAL) OVER (PARTITION BY MGR) MGR_AVG
    COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) SIM_CNT
FROM EMP;

RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING
   -- 현재 값을 기준으로 -50과  +150 의 범위 내에 포함된 행을 의미

 

(4) 그룹 내 행 순서 함수  - SQL Server 미지원

· FIRST_VALUE: 파티션에서 가장 처음에 나오는 값. MIN으로 동일한 결과 도출 가능

· LAST_VALUE: 파티션에서 가장 나중에 나오는 값. MAX으로 동일한 결과 도출 가능

· LAG: 현재 값을 기준으로 원하는 위치에 해당하는 이전(위) 행의 값을 도출. 디폴트면 바로 위 행의 값

· LEAD: 현재 값을 기준으로 원하는 위치에 해당하는 이후(아래) 행의 값 도출. 디폴트면 바로 아래 행의 값

 

(5) 그룹 내 비율 함수 

· RATIO_TO_REPORT: 파티션 내에 전체 값에 대한 행별 백분율을 도출. 0 < 결과값 ≤1 (SQL Server 미지원)

· PERCENT_RANK: 각 그룹의 순위를 퍼센트로 바꿔서 값을 표현. 0에 가까울 수록 1순위에 해당

· CUME_DIST: 파티션별 윈도우의 전체 건수에서 각 그룹에 대한 상대적인 누적 백분율 반환.  0 < 결과값 ≤1

· NTILE: 입력되는 인자 값으로 N등분한 결과를 구할 때 주로 사용하는 함수(ex. 팀 나누기)

6. 절차형 SQL 

- 절차형 SQL : SQL은 비절차적 언어라 데이터의 절차적 과정(HOW)를 제공하지 못하므로 이를 보완한 프로그램 

 

- PL/SQL 특징

· 프로그래밍 언어와 데이터 처리의 통합
· 블록 단위 구성: 블록 구조로 이뤄져 있어 기능별로 모듈화 가능
· 순차 처리 지원: IF, LOOP, FOR WHILE 등과 같은 제어문, 반복문 사용 가능
· 예외처리 지원: DBMS 정의 에러나 사용자 정의 에러를 정의 가능하며, 위반 시 예외 처리기 동작
· 플랫폼 독립적: Oracle과 PL/SQL을 지원하는 어떤 서버로도 프로그램 이전 가능

 

- 사용자 정의 함수:사용자가 임의로 만들어서 사용하는 함수

- 프로시저: 특정한 로직을 처리만 하고 결과값은 반환하지 않는 서브 프로그램. 외부적인 명령에 의해 실행

- 트리거: 이벤트 발생 시 관련 작업이 자동 수행되는 프로그램. 트리거링 사건(Triggering Event)에 의해 내부적으로 실행

 

 

7. SQL 실행 

(1) 옵티마이저 

- 옵티마이저 개념: SQL문의 결과 낼 때 최적의 실행 방법을 결정하는 역할을 하는 것. DBMS의 두뇌라고 할 수 있음

- 종류 

항목	규칙기반 옵티마이저	비용기반 옵티마이저
개념	사전에 정의된 규칙 기반	최소비용 계산 실행계획 수립
기준	실행우선 순위(Ranking)	액세스 비용(Cost)
인덱스	인덱스 존재 시 가장 우선시 사용	비용에 의한 결정
단점	예측 통계정보 요소 무시	최소 성능 보장 계획의 예측 제어 어려움
장점	판단이 매우 규칙적 실행 예상 가능	통계 정보를 통한 현실 요소 적용
성능	사용자 SQL작성 숙련도	옵티마이저의 예측 성능
특징	실행속도가 빠른 순으로 규칙 세우고, 우선순위 기준대로 실행 계획 생성	최적화를 위해 정확한 통계 정보 유지가 중요

 

(2) 실행계획

- 개념:  SQL에서 요구한 사항을 처리하기 위한 절차와 방법을 의미. 즉, 옵티마이저가 수립하는 작업 절차

- 구성 요소

· 조인 순서(Join Order)

· 조인 기법(Join Method)

· 액세스 기법(Access Method)

· 최적화 정보(Optimization Information)

· 연산(Operation)

 

- 읽는 법 

① 위에서 아래로 읽어 내려가면서 읽기 

② 같은 들여쓰기가 존재한다면 무조건 위 → 아래 순으로 읽기

③ 읽고자 하는 스텝보다 들여쓰기가 된 하위스텝이 있다면

    가장 안쪽으로 들여쓰기 된 스텝부터 한 단계씩 상위 스텝으로 읽기 

 

- SQL 처리 흐름도 

: 조인 순서 / 조인 방법 / 액세스 기법 / 액세스 건수 / 성공 건수 등 파악 가능 

(3)  인덱스

 - 개념: 검색조건에 부합하는 데이터를 빠르게 검색할 수 있도록 돕는 것 (ex. 책 내 색인) 

 └ 데이터를 조회(SELECT)할 때 좋은데, 특히 전체 스캔에 비해 인덱스 스캔의 처리 속도가 뛰어남

 └ 단, 데이터베이스 크기가 작으면 오히려 인덱스가 시스템 부하에 영향을 줘서 전체 테이블 스캔이 유리하기도

 └ 데이터 추가/삭제 시 인덱스도 같이 수정이 필요해 시스템 부하를 일으킬 수 있음 

 

- 종류 

· 트리 기반 인덱스 

: B-Tree(Balanced-Tree) 인덱스가 가장 많이 쓰임.  노드의 삽입, 삭제 과정에서 일정한 규칙에 맞게 재정렬하는 트리

 └ 자식 노드가 정렬 형태로 되어 있어 데이터를 빠르게 찾을 수 있음 (업앤다운 게임 느낌으로 찾음)

 

- 전체 테이블 스캔과 인덱스 스캔

 

· 전체 테이블 스캔: 모든 데이터를 읽어 가면서 조건을 찾는 방식. 대용량 데이터에서는 비효율적

· 인덱스 스캔: 인덱스를 구성하는 칼럼 값을 기반으로 데이터를 추출하여 접근하는 기법.
                       데이터 용량이 작거나 데이터가 자주 변경되는 경우 성능 저하  

 └ 자주 사용되는 인덱스 스캔 방식 3가지
   유일 스캔 방식 (Index Unique Scan), 인덱스 범위 스캔 방식 (Index Range Scan), 인덱스 역순 범위 스캔 방식 (Index range scan descending)

 

(4) 조인수행 원리

- NL Join(Nested Loops Join)

: 중첩된 반복문과 유사한 방식으로 조인하는 기법. 랜덤 액세스 방식 사용

: 선행 테이블의 조건 만족하는 행 추출 > 후행테이블로 가는 것

: 선행 조건을 만족하는 조건이 많으면 성능이 저하될 수 있음 

- Sort Merge Join

: 조인 칼럼을 기준으로 데이터를 정렬하여 조인 수행

: 주로 정렬 스캔 방식으로 진행되며, 데이터가 많으면 전체 정렬이 어려워 성능 저하됨

: 동등 및 비동등 조인 모두 작업 가능   

- Hash Join

* 해싱(Hashing): 데이터를 고정된 길이의 값으로 변환하는 프로세스 

: 해시 함수를 이용하여 데이터를 결합하는 방법을 활용하여 조인 수행

: 결과 행의 수가 적은 테이블을 선행 테이블로 사용하는 것이 좋음

: (장점) 두 개 테이블 빠르게 조인 가능, 대량의 데이터를 처리할 때 성능 우수, 메모리 요구 사항 적음

: (단점) 해시 테이블 변환하는 단계가 필요해 조인 키 중복 및 데이터 분포가 성능에 영향, 메모리 관리도 주의 

 

---

[SQL 코드카타]

1. 1280. Students and Examinations
https://leetcode.com/problems/students-and-examinations/

 

1) 어떤 문제가 있었나

지난번 코드카타에서 SELF JOIN을 한 것을 생각하여,

LEFT 조인을 결과 출력했으나 시험을 치르지 않는 값, 즉 0을 추출하지 못해서 오답

 

2) 내가 시도해본 건 무엇인가

과목명만 적힌 Subjects 테이블의 정체를 고민해보았으나 답이 나오지 않아 

정답해설을 통해 본 문제에서는 CROSS JOIN이 필수로 사용된 점 확인

 

3) 어떻게 해결했나

CROSS JOIN과 LEFT JOIN을 한 뒤 COUNT 함수가 NULL값을 제외하여 셈하도록 칼럼명 삽입하여 문제 해결  

SELECT S.student_id , student_name , SJ.subject_name, COUNT(E.student_id) attended_exams 
  FROM Subjects SJ
 CROSS JOIN Students S 
  LEFT JOIN Examinations E ON S.student_id = E.student_id AND SJ.subject_name = E.subject_name 
GROUP BY 1, 3
ORDER BY 1, 3

 

4) 무엇을 새롭게 알았나

- CROSS JOIN의 실제 활용 예시를 알게 되었고, 값이 없는 경우 OUTER JOIN으로 NULL 값을 생성해 처리해야 함

  └ 아예 리스트 항목 자체가 필요한 경우에는 재귀 함수(with recursive)를 사용하기도 했음