[SQLD] Chapter4 SQL 활용(SQLD)

📌 개인적인 공간으로 공부를 기록하고 복습하기 위해 사용하는 블로그입니다.
정확하지 않은 정보가 있을 수 있으니 참고바랍니다 :😸
[틀린 내용은 댓글로 남겨주시면 복받으실거에요]

Chapter4 SQL 활용

서브쿼리

서브쿼리

SQL 문에서 함수가 들어갈 수 있는 위치에 또 다른 독립적인 SQL 문을 넣는 것이 가능하며 이를 서브 쿼리라고 한다.

1. 서브쿼리 위치에 따른 분류

   종류	서브쿼리가 들어가는 위치	특징
   스칼라 서브쿼리	SELECT 문의 컬럼 입력 위치	반환은 하나의 컬럼만 가져야 한다.
   인라인 뷰	FROM 절의 테이블 입력 위치	복잡한 쿼리를 단계적으로 사용 가능,
   테이블의 일부분만 불러와 비교횟수를 줄일 수 있다.
   중첩 서브쿼리	WHERE, HAVING 절의 컬럼 또는 테이블 입력 위치	다양한 반환값을 가질 수 있다.

   • 중첩 서브쿼리의 반환 값 유형

     반환 값 유형	설명
     단일행	반환되는 값이 단일행, 단일행 비교연산자의 연산대상으로 사용할 수 있다.
     다중행	반환되는 값이 다중 행, 비교연산자의 연산 대상으로 사용 가능
     다중컬럼	반환되는 값이 여러 컬럼 즉 벡터인 경우 , 스칼라 서브쿼리와 달리 반환 값이 여러 컬럼을 가진 테이블 형태

2. 메인쿼리의 칼럼 사용 여부에 따라 나뉜다.

   종류	설명
   연관 서브쿼리	메인쿼리의 칼럼을 서브쿼리에서 사용
   비연관 서브쿼리	메인쿼리의 칼럼을 서브쿼리에서 사용하지 않음

3. 서브쿼리 특징

   1. 서브쿼리에서는 메인쿼리의 컬럼을 모두 사용할 수 없다.
   2. 메인쿼리에서는 서브쿼리의 컬럼을 사용할 수 없다.
   3. 서브쿼리는 괄호로 감싸서 사용한다.
   4. 서브쿼리에서는 ORDER BY 절을 사용할 수 없다.

뷰

인라인 뷰의 경우 반환결과가 하나의 테이블이 된다고 했는데 특별한 이름이 없고 메인쿼리 안에 SELECT 문의 형태로 들어가는데 아예 별도로 이름을 붙여서 DBMS에 등록해놓고 마치 테이블을 참조하는 것과 같이 사용할 수 있도록 만든 것을 뷰라고 한다.

1. 테이블과 뷰

   구분	테이블	뷰
   개념	정의된 스키마에 따라 실제 데이터를 물리적으로 생성	실제의 테이블을 참조하여 생성하는 논리적인 가상의 테이블
   데이터 저장	실제 데이터를 디스크에 저장	실제 데이터를 저장하고 있지 않음
   생성 방식	정적으로 생성	동적으로 생성
   인덱스 생성	자신만의 인덱스 생성 가능	자신만의 인덱스 생성 불가능
   삽입/수정/삭제	제약 없음	제약 있음

2. 뷰의 특징

   특징	설명
   편리성	복잡하고 긴 쿼리를 뷰로 만들어 두고 사용하면 쿼리를 단순하게 작성할 수 있다.
   보안성	원래의 테이블에서 외부에 노출하며 안되는 컬럼을 제외하고 뷰를 생성하여 제공할 수 있다.
   독립성	테이블의 구조가 변경되더라도 뷰가 변경되지 않는다면 뷰를 통해 접근하는 애플리케이션은 변경할 필요가 없다.

집합연산자

특정한 기준 키 없이 두 테이블의 레코드들에 대해서 합집합과 교집합 연산을 수행하므로 두 테이블 칼럼 구성, 즉 스키마가 동일해야 한다.

UNION ALL/ UNION

1. UNION: 스키마가 같은 두 개의 테이블에 대한 합집합, 중복된 개수를 한 번만 포함
2. UNIONALL : 중복된 개수만큼 포함

INTERSECT

교집합, 두 테이블에 공통적으로 포함된 레코드만 포함

MINUS/EXCEPT

차집합 수행, 두테이블에서 공통으로 포함된 레코드를 제외시킨 결과

그룹함수

GROUP BY절에 따른 결과에 대해서 그룹 별로 연산을 수행하는 함수

ROLLUP

GROUP BY절에 들어가는 컬럼을 대상으로 하위 그룹핑을 수행하는 함수

예를 들어 GROUP BY ROLLUP (날짜)을 할 경우 날짜를 먼저 그룹핑하고

GROUP BY ROLLUP (날짜, 이름)을 할 경우 (날짜, 이름) → (날짜) → (전체) 순으로 묶어준다.

CUBE

ROLL UP 과 비슷하지만 ROLL UP이 1차적인 하위 그룹핑 수행하는데 반하여 조합 가능한 모든 경우로 그룹핑을 수행한다.

예를 들어 GROUP BY CUBE(날짜, 이름)의 경우 (날짜, 이름) → (날짜) → (이름) → (전체) 순서로 하위 그룹을 묶어준다.

GROUPING SETS

그룹핑 할 대상을 지정하는 함수, 입력된 인자에 대해서만 소계를 구할 때 사용한다.

GROUPING

ROLLUP, CUBE, GROUPING SETS과 함께 사용하여 소계에 해당하는 결과 행과 그렇지 않은 행을 구분할 수 있도록 해준다. 소계에 해당하는 결과 행의 경우 1을 반환하고 그렇지 않은 경우 0을 반환한다.

GROUPING 함수와 CASE 문을 사용하여 소계나 총계를 표시하는 행에 대해서 그 의미에 맞는 텍스트를 값으로 지정할 수 있다.

SELECT
CASE GROUPING 입사연도
WHEN 1 THEN '소계' ELSE TO_CHAR 입사연도
END AS 연도

윈도우함수

행과 행 간의 관계를 나타내는 연산을 쉽게 하기 위한 함수

GROUP BY 연산과 유사하지만 GROUP BY 연산은 각 행을 대상으로 연산을 수행한 다음 새로운 구성을 만드는데 비하여, 윈도우 함수는 각 행의 기존 구성을 유지한 상태로 해당 행에 대해서 새로운 값을 추가하거나 아니면 기존의 값을 변경한다는 점에서 다르다.

순위 함수

함수이름	설명	예
RANK	동일 순위는 같은 순위를 가진다. 순위 값은 앞 순위까지의 누적개수 +1이 된다.	1, 2, 2, 4, 4, 4, 7, …
DENSE_RANK	동일 순위는 같은 순위 값을 가진다. 순위값은 단순하게 앞 순위 +1 이다.	1, 2, 2, 3, 3, 3, 4, …
ROW_NUMBER	동일 순위라도 각각의 행이 고유한 순위값을 가진다.	1, 2, 3, 4, 5, 6, 7, …

집계 함수

OVER절을 사용해서 파티션별로 집계하거나 누적 집계를 계산할 수 있다.

함수 이름	설명
COUNT	값이 Null 인 행을 제외한 행의 개수로 파티션 별로 집계하거나 누적 집계를 계산하여 반환한다.
SUM	입력된 컬럼에 대해 파티션 별 합계를 구하거나 누적 합계를 계산하여 반환한다.
AVG	입력된 컬럼에 대해 파티션 별 평균을 구하거나 누적 평균을 곗나하여 반환한다.
MIN	입력된 컬럼에 대해 파티션 별 최솟값을 반환한다.
MAX	입력된 컬럼에 대해 파티션 별 최댓값을 반환한다.

행순서함수

함수	설명
FIRST_VALUE	파티션 별로 그룹핑 하여 가장 처음 값을 반환한다.
LAST_VALUE	파티션 별로 그룹핑 하여 가장 마지막 값을 반환한다.
LAG	입력된 인자의 값만큼 이전 행의 값을 반환한다.
LEAD	입력된 인자의 값만큼 이후 행의 값을 반환한다.

비율함수

파티션 별로 합계를 구한 후 그에 대한 비율을 구하는 함수

| 함수 | 설명 | | — | — | | CUME_DIST | 파티션 별 합계에 대한 누적 백분율을 소수점 단위로 계산하여 반환한다. 마지막 행의 값이 1이된다. | | PERCENT_RANK | 파티션 별로 그룹핑하여 백분위 순위 값을 반환한다. 가장 첫 행이 0, 마지막 행이 1이된다. | | NTILE | 파티션을 N 등분하여 1부터 N까지의 등급 값을 반환한다. 전체 개수를 N으로 나누고 나머지를 앞 등급 순서로 1씩 배분하여 등급별 개수를 정한다. ex ) 10개를 4등급으로 나누면 각 등급별 개수는 3, 3, 2, 2가 된다 | | RATIO_TO_REPORT | 파티션 별 합계에 대한 비율을 계산하여 반환한다. |

Top N 쿼리

상위 N 순위 까지를 추출하는 쿼리

기본적으로 순윌함수를 사용해서 쿼리를 작성할 수 있다.

ROWNUM 함수

• ROW_NUMBER 함수와 달리 현재 저장된 데이터를 그대로 두고 각 행에 순차적인 번호를 붙여주는 함수이다.

    SELECT ROWNUM, EMPNO, ENAME, SAL
    FROM EMP
    WHERE ROWNUM <= 5;
  

• 첫 행부터 차례로 순회하면서 값을 반환하기 때문에 중간을 건너뛰고 값을 가져올 수 없다는 점이다. WHERE 절에 ROWNUM을 사용할 경우 조건식이 FALSE가 되면 순회를 멈추고 결과를 반환하므로 주의해야 한다.

    SELECT ROWNUM, EMPNO, ENAME, SAL
    FROM EMP
    WHERE ROWNUM = 5;
      
    -- 등식 비교를 하면 결과를 가져오지 못한다. 조건이 ROWNUM =1 일때 바로 FALSE 가 되어 더이상 순회하지 못하고 결과를 반환하기 때문
  

• ORDER BY와 함께 사용 시 정렬 수행 후 단순하게 각 행에 번호를 붙이고 싶어서 사용할 경우에는 정렬 수행 전에 번호가 붙여지므로 번호가 엉켜서 출력된다 → 이 때는 ROW_NUMBER를 사용해서 해결해야 한다.

윈도우 함수의 순위 함수

순위 함수를 사용해서 TOP N 쿼리를 작성할 수 있다.

SELECT *
FROM (SELECT RANK () OVER (ORDER BY SAL DESC) AS RANK, EMPNO, ENAME, SAL FROM EMP)
WHERE RANK <= 5 ;

계층형 질의와 셀프조인

각 레코드가 하나의 노드가 되고 노드의 부모-자식 관계를 정의하여 일종의 트리구조와 같이 데이터를 모델링 할 수가 있는데 이를 계층형 모델이라고 한다.

계층형 모델에서 상위 노드에서 하위노드로 연쇄적으로 데이터에 접근해서 결과를 조회해야 할 경우가 있는데 이럴 경우 사용할 수 있는 것이 계층형 질의와 셀프조인이다.

계층형 질의

계층형 질의는 계층 구조를 가지는 컬럼을 대상으로 데이터를 출력할 때 사용할 수 있는 특별한 키워드를 제공한다.

키워드	설명
LEVEL	전체 계층 구조에서 현재의 레벨 또는 깊이를 반환한다.
루트 노드가 1이 되며 한 레벨 내려갈 때마다 1 씩 증가한다
SYS_CONNECT_BY_PATH	최상위 루트 노드로부터 현재 노드까지 경로를 출력하는 함수
START WITH	경로의 시작이 되는 루트 노드가 되는 조건을 지정한다.
CONNECT BY	부모 노드로부터 자식 노드로의 연결을 지정한다.
CONNECT_BY_ROOT	루트노드의 지정된 컬럼값을 반환한다.
CONNECT_BY_ISLEAF	가장 말단 노드이면 1을 반환하고 그 외에는 0을 반환한다.
PRIOR	바로 상위의 부모 노드를 반환한다.
NOCYCLE	사이클 발생 이후의 데이터를 전개하지 않음으로써 무한 루프를 방지한다.
ORDER SIBLINGS BY	ORDER BY 절이 전체를 정렬하는 것에 비하여 ORDER SIBLINGS BY는 같은 레벨의 형제 노드끼리 정렬한다.

1. 순방향 전개

   CONNECT BY PRIOR 자식 = 부모

   상위 노드로 부모를 지정하므로 부모 → 자식 순으로 이어지는 순방향 전개

2. 역방향 전개

   CONNECT BY PRIOR 부모= 자식

   상위 노드로 자식을 지정하므로 자식→ 부모 순으로 이어지는 순방향 전개

셀프조인

같은 테이블에 대해서 조인을 수행하도록 쿼리를 작성하는 것이 가능하며 이런 경우를 셀프 조인이라고 한다.

셀프 조인을 하는 경우는 계층적인 분류 값을 가지는 컬럼을 다룰때,

상품분류나 주소 체계, 또는 회사의 조직도 등을 다룰 때, 부모-자식 관계의 모델링을 한 다음, 셀프 조인을 사용해서 쿼리를 작성하면 전체 분류 체계를 계층적으로 조회하여 출력하는 것이 가능하다.

단, 셀프 조인을 사용할 경우 계층의 깊이가 깊어질수록 쿼리가 반복되고 길어지는 단점이 있다.→ 이 문제는 계층형 질의로 해결할 수 있다.

PIVOT 절과 UNPIVOT절

PIVOT : 테이블의 행과 열을 전환하여 테이블을 재구성하는 것 (함수 아님)

1. PIVOT 절 : 행을 열로 바꾼다.
2. UNPIVON 절 : 열을 행으로 바꾼다.

SELECT *
FROM (SELECT E.JOB, D.DNAME 
			FROM EMP E, DEPT D
			WHERE E.DEPTNO = D.DEPTNO)
PIVOT (COUNT(*) FOR DNAME IN ('ACCORDING' AS ACCOUNTING,
															'RESEARCH' AS RESEARCH,
															'SALES' AS SALES));

정규표현식

정규표현식을 사용하면 문자열을 처리할 때, 패턴에 기반하여 검색, 치환, 추출 등을 수행할 수 있다. 메타 문자와 리터럴 문자를 조합하여 패턴을 만들고 이 패턴에 따라 문자열을 처리 할 수 있어 문자열을 처리하는데 효율적이다.

메타문자, 리터럴 문자

1. 정규표현식 메타문자

   메타문자는 문자 그 자신이 가진 의미가 아니라 다른 의미로 사용되는 문자를 말한다.

   예를 들어 정규 표현 식에서 ^ 문자는 문자 그자체를 나타내는 것이 아니라 입력문자열의 시작을 의미하는 메타문자이다.

   메타문자	의미	예
   \	메타문자를 리터럴 문자로 표시하거나 리터럴 문자와 결합하여 정해진 메타문자를 표시	\ : \
   \n : 줄바꿈(개행) 문자
   ^	개행으로 나뉜 문자열의 시작지점	^THE: The 로 시작하는 문자열
   $	개행으로 나뉜 문자열의 끝지점	ing$: ing로 끝나는 문자열
   .	임의의 한 문자 (개행문자 제외)	a.b : acb, a-b, a1b, …
   ?	선행문자 0 또는 1개	no? : n, no
   *	선행문자 0개 이상	no* : n, no, noo, nooo, …
   +	선행문자 1개 이상	no+: no, noo, nooo …
   		선택적 일치	a	b : a, b
   [ ]	대괄호 안의 문자들 중 하나와 일치	[abc] : a, b, c
   [-]	연속 문자의 범위를 지정	[a-z]: a부터 z까지 소문자 알파벳 문자
   [^]	대괄호 안의 문자들을 제외한 나머지 문자들 중 하나와 일치	[^abc]: d, e, z, … (a, b, c를 제외한 나머지 문자)
   ()	소괄호로 묶인 표현식을 한 단위로 취급	(ab) : ab

2. 정규표현식 리터럴문자

   문자 그 자체가 가진 의미 그대로 사용되는 문자.

   정규표현식에서 패턴 매칭을 수행할 때 처리되는 최소 단위는 문자이다.

   알파벳의 경우 a, A와 같이 단일 문자 하나가 그에 해당하고 한글의 경우에는 한음절의 한 개문자가 단위가 된다.

Database 카테고리 내 다른 글 보러가기