SQLD: SQL 활용 #1

임동혁 Ldhbenecia·2024년 8월 13일

Database sql

SQL 활용

집합 연산자

DataBase

목록 보기

9/14

SQLD 학습을 진행하며 기억할 내용을 간략히 기록합니다.
용어로 Entity(엔티티)를 엔터티라고 발음하지만 엔티티로 정리합니다.

SQL 활용

집합 연산자

UNION
- 여러 개의 SQL문의 결과에 대한 합집합, 중복된 행은 하나의 행으로 만든다. (중복 제거)
- 각각의 집합에 GROUP BY절을 사용할 수 있다.
UNION ALL
- 중복된 행도 그대로 결과를 반환한다(중복 제거 x)
- 일반적으로 여러 질의 결과가 상호 배타적일 때 많이 사용한다.
- 서로 중복되지 않는 경우 당연히 UNION과 결과가 동일하다.
INTERSECT
- 교집합, 중복을 제거한다.
MINUS, EXCEPT
- 차집합, 중복을 제거한다.

집합 연산자를 사용한 SQL의 ORDER BY절은 최종 결과를 정렬하기 때문에 각각의 집합이 아닌 가장 마지막 줄에 한번만 사용할 수 있다.

GROUP BY (집계 함수)

ROLLUP(컬럼1, 컬럼2)
- 소그룹의 합계를 계산한다.
- GROUP BY로 묶은 각각의 소그룹의 합계와 전체 합계를 전부 계산한다.
- 컬럼1별, (컬럼1, 컬럼2)별, 전체 그룹 연산
CUBE(컬럼1, 컬럼2)
- 다차원적인 소계를 계산한다.
- ROLLUP과 달리 GROUP BY절에 명시한 모든 컬럼에 대해 소그룹 합계를 계산한다.
- 컬럼1별, 컬럼2별, (컬럼1, 컬럼2)별, 전체 그룹 연산
GROUPING SETS(컬럼1, 컬럼2)
- 특정 항목에 대한 소계를 계산한다.
- 각 소그룹의 합계만 간단하게 보여준다.
- GROUPING SETS(컬럼1, 컬럼2, (컬럼1, 컬럼2), ()) 가능

CUBE와 ROLLUP은 함수의 인자가 2개 이상일 경우는 달라질 수 있지만 인자가 1개일 때는 결과가 같다.

윈도우 함수

서로 다른 행의 비교나 연산을 위해 만든 함수
GROUP BY 안쓰고 그룹 연산 가능

PARTITION BY: 출력할 총 데이터 수 변화 없이 그룹연산을 수행할 컬럼 지정

ORDER BY: RANK는 필수

ROWS|RANGE BETWEEN A AND B: 연산 범위 설정, ORDER BY 필수

NTILE(n)
설명: 결과 집합을 n개의 균등한 그룹(버킷)으로 나누고, 각 행이 속한 그룹의 번호를 반환합니다. 예를 들어, NTILE(4)는 결과를 4개의 버킷으로 나누고 각 행에 버킷 번호를 할당합니다.
용도: 데이터의 분포를 일정한 수의 그룹으로 나누어 분석할 때 사용됩니다.

LEAD(column, offset, default) - 이후 값
설명: 현재 행에서 지정된 offset만큼 떨어진 후의 행의 값을 반환합니다. offset은 기본값 1을 가지며, default는 offset이 범위를 벗어날 때 반환할 기본값을 설정할 수 있습니다.
용도: 현재 행과 비교하여 후속 행의 값을 참조할 때 사용됩니다.

LAG(column, offset, default) - 이전 값
설명: 현재 행에서 지정된 offset만큼 이전 행의 값을 반환합니다. offset은 기본값 1을 가지며, default는 offset이 범위를 벗어날 때 반환할 기본값을 설정할 수 있습니다.
용도: 현재 행과 비교하여 이전 행의 값을 참조할 때 사용됩니다.

CUME_DIST()
설명: 현재 행의 값이 윈도우 내에서 전체 집합의 몇 퍼센트를 차지하는지 누적 분포 비율을 반환합니다. 즉, 현재 행보다 작거나 같은 값의 비율을 백분율로 나타냅니다.
용도: 데이터의 상대적 위치를 백분율로 표현할 때 사용됩니다.

RANK

RANK(): 1, 2, 2, 4, 5 ...
DENSE_RANK(): 1, 2, 2, 3, 4 ...

그룹별로 순위를 구해야 할 때

SELECT DEPT 
     , ENAME 
     , SAL 
     , COMM 
     , RANK() OVER (PARTITION BY DEPT ORDER BY SAL DESC, COMM DESC) RANK 
  FROM EMP 
 ORDER BY DEPT, SAL DESC, COMM DESC

계층형

START WITH: 데이터 출력을 시작할 행 지정 조건
CONNECT BY PRIOR: 그 다음 행이 나올 조건
ORDER SIBLINGS BY: 형제 노드 사이에서 정렬을 지정하는 구문 (같은 레벨에서)

오라클에서 루트 노드의 LEVEL값은 1이다.

SQL Server에서 계층형 질의문은 CTE를 재귀 호출함으로써 계층 구조를 전개한다.
SQL Server에서 계층형 질의문은 앵커 멤버를 실행하여 기본 결과 집합을 만들고 이후 재귀 멤버를 지속적으로 실행한다.
오라클의 계층형 질의문에서 WHERE절은 모든 전개를 진행한 이후 필터 조건으로서 조건을 만족하는 데이터만을 추출하는데 활용된다.
오라클 계층형 질의문에서 PRIOR 키워드는 SELECT, WHERE절에서도 사용할 수 있다, CONNECT BY 절에서만 사용할 수 있는게 아니다.

순환참조가 무한히 지속되는 경우가 있을 수 있음
EMPLOYEE_ID = 1000, MANAGER_ID = 2000
EMPLOYEE_ID = 2000, MANAGER_ID = 1000

SELECT EMPLOYEE_ID, NAME, LEVEL
	FROM EMPLOYEES2
    START WITH EMPOYEE_ID = 1000
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

CONNECT BY NOCYCLE PRIOR EMPLOYEE_ID = MANAGER_ID;

NOCYCLE을 붙여서 순환참조를 막는다.

서브쿼리

단일 행 서브쿼리
- 서브쿼리의 실행 결과가 항상 1건 이하인 서브쿼리를 의미한다.
- 단일 행 비교 연산자와 함께 사용된다. =, <, <=, >, >=, <>
다중 행 서브쿼리
- 서브쿼리의 실행 결과가 여러 건인 서브쿼리를 의미한다.
- 다중행 서브쿼리는 다중 행 비교 연산자와 함께 사용된다. IN, ALL, ANY, SOME, EXIST
다중 칼럼 서브쿼리
- 서브쿼리의 실행 결과로 여러 컬럼을 반환한다.
- 메인커리의 조건절에 여러 칼럼을 동시에 비교할 수 있다.
- 비교하고자 하는 칼럼의 위치와 개수가 동일해야한다.
- SQL Server에서는 지원하지 않는다.

뷰

뷰는 단지 정의만을 가지고 잇으며, 실행 시점에 질의를 재작성하여 수행한다.
뷰의 장점 중 독립성은 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다.
뷰는 보안을 강화하기 위한 목적으로 활용 가능하다.
실제 데이터를 저장하고 있는 뷰를 생성하는 기능을 지원하는 DBMS도 있다.