[엘리스sw] SQL로 데이터 다루기 Ⅱ

---

◆  집합연산자 & 계층형질의

◆  JOIN 심화

◆  서브쿼리 심화

◆  그룹 함수 & 윈도우 함수

---

◆   집합연산자 & 계층형질의

집합연산자

STANDARD SQL

 

순수 관계 연산

---

집합 연산자

집합 연산자

• 두 개 이상의 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회하는 방법 중 하나
• 테이블에서 SELECT한 컬럼의 수와 각 컬럼의 데이터타입이 테이블 간 상호 호환 가능해야 한다.
• UNION, UNION ALL, INTERSECT, EXCEPT

UNION

• 두 개의 테이블을 하나로 만드는 연산
• UNION에 사용할 컬럼의 수와 데이터 형식이 일치해야 하며
• 합친 후에 테이블에서 중복된 데이터는 제거
• 이를 위해 UNION은 테이블을 합칠 때 정렬 과정을 발생시킴.
• 하지만 최종 결과에 대해 올바른 정렬을 하기 위해서는 ORDER BY 구문을 사용해야 함.
• 관계형 대수의 일반 집합 연산에서 합집합의 역할

 

UNION ALL

• UNION과 거의 같은 기능을 수행함.
• 다만, UNION과 달리 중복 제거와 정렬을 하지 않음.

INTERSECT

• 두 개의 테이블에 대해 겹치는 부분을 추출하는 연산
• 추출 후에는 중복된 결과를 제거
• 관계형 대수의 일반 집합 연산에서 교집합의 역할

Oracle/Maria DB에서는 지원되지만, MySQL에서는 지원되지 않음.

EXCEPT (MINUS)

• 두 개의 테이블에서 겹치는 부분을 앞의 테이블에서 제외하여 추출하는 연산
• 추출 후에는 중복된 결과를 제거
• 관계형 대수의 일반 집합 연산에서 차집합의 역할

Oracle/Maria DB(10.3 version부터)에서는 지원되지만, MySQL에서는 지원되지 않음.

---

계층형 질의

계층형 질의

• 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해 사용하는 것
• 대표적인 데이터베이스 : ORACLE, SQL Server

계층형 데이터

• 동일 테이블에 계층적으로 상위와 하위 데이터가 포함되어 있는 데이터

Oracle

• LPAD(a, b) : a문자열을 b개수만큼 기존 데이터의 왼쪽에 추가시켜줌.

키워드	설명
LEVEL	검색 항목의 깊이를 의미하며, 계층구조에서 루트(최상위)의 레벨이 1
CONNECT_BY_ROOT	현재 전개할 데이터의 루트(최상위) 데이터 값 표시
CONNECT_BY_ISLEAF	현재 전개할 데이터가 리프(최하위) 데이터 인지에 대한 값 표시(0 or 1)
SYS_CONNECT_BY_PATH(A, B)	루트 데이터부터 현재까지 전개한 경로 표시(A: 컬럼명, B: 구분자)

 

 

SQL Server/MariaDB

• SQL Server version.2000 이전
  → 저장 프로시저를 재귀 호출 / While 루프 문에서 임시테이블 사용
• SQL Server version.2005 이후
  → CTE(Common Table Expression)을 이용하여 재귀 호출

---

◆  JOIN 심화

JOIN(교집합)

• 두 개 이상의 테이블들을 연결 또는 결합하여 데이터를 출력하는 것
• 연산자에 따라 JOIN 방식 분류 시 : EQUI JOIN, Non EQUI JOIN

EQUI JOIN(등가 교집합)

• 두 개의 테이블 간에 서로 정확하게 일치하는 경우를 활용하는 조인
• 간단히 말해, 등가 연산자를 사용한 조인을 의미함. (=)
• 대부분 기본키-외래키 관계를 기반으로 발생하나, 모든 조인이 그런 것은 아님.

Non EQUI JOIN(비등가 교집합)

• 두 개의 테이블 간에 서로 정확하게 일치하지 않는 경우를 활용하는 조인
• 간단히 말해, 등가 연산자 이외의 연산자들을 사용한 조인을 의미함.
  (>, >=, <=, <, BETWEEN)

INNER JOIN

• 내부 조인이라고 하며, JOIN 조건에서 동일한 값이 있는 행만 반환
• INNER JOIN은 JOIN의 기본값으로 'INNER' 생략 가능

 

INNER JOIN

 

USING 조건절

• 같은 이름을 가진 칼럼들 중 원하는 칼럼에 대해서만 선택적으로 등가 조인 가능
• SQL Server에서는 지원하지 않음.

 

NATURAL JOIN

• 추가적으로 ON 조건절이나 USING 조건절, WHERE 조건절에서 JOIN 조건 정의가 불가능하다.

 

CROSS JOIN

• JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 조회

OUTER JOIN

• 두 개의 테이블 간에 교집합을 조회하고 한쪽 테이블에만 있는 데이터도 포함시켜서 조회
• 빈 곳은 NULL 값으로 출력
• WHERE 조건절에서 한쪽에만 있는 데이터를 포함시킬 테이블 반대쪽으로 (+)를 위치 (Oracle DB)

SELECT * FROM USER, CLASS
WHERE USER.CLASS_ID (+) = CLASS.CLASS_ID;

→ +가 있는 반대쪽이 중심임! RIGHT OUTER JOIN 방식

 

INNER JOIN

• JOIN을 활용한 쿼리에서도 WHERE문을 이용하여 조건을 걸 수 있음.

• CROSS JOIN 사용 시에도 'CROSS'는 생략이 가능하다.
• 즉, (INNER) JOIN = (CROSS) JOIN이 될 수 있지만 ON 조건절을 정의하면 INNER JOIN, ON 조건절을 정의하지 않으면 CROSS JOIN을 나타내게 된다.

셀프 조인

• 동일 테이블 사이의 조인
• 동일 테이블 사이의 조인을 수행하면 테이블과 칼럼 이름이 모두 동일하기 때문에 식별을 위해 별칭 필수

---

◆  서브쿼리 심화

서브쿼리에 메인쿼리의 컬럼이 포함되는지에 따라 구분
동작하는 방식에 따른 서브쿼리 분류

• 연관 서브쿼리(Correlated Subquery)
• 비연관 서브쿼리(Un-Correlated Subquery)

연관 서브쿼리

• 메인쿼리의 컬럼이 서브쿼리에 포함되며,
• 메인쿼리의 컬럼은 서브쿼리에 특정 조건으로 사용된다

 

비연관 서브쿼리

• 메인쿼리 컬럼이 서브쿼리에 포함되지 않으며, 주로 메인 쿼리에 특정한 값을 제공할 때 사용된다.
• 서브쿼리 자체만으로 실행됨.

 

반환되는 데이터 형태에 따른 서브쿼리 분류

• 단일 컬럼(Single Column Subquery)
  - 단일 행 서브쿼리(Single Row Subquery)
  - 다중 행 서브쿼리(Multi Row Subquery)
• 다중 컬럼(Multi Column Subquery)

단일 행 서브쿼리

• 서브쿼리의 결과가 한 개의 행을 반환하며, 단일 행 비교 연산자(=, <, >, <=, >=)와 같이 사용된다.

 

다중 행 서브쿼리

• 서브쿼리의 결과가 두 개 이상 행을 반환할 수 있으며,
  다중 행 비교 연산자(IN, ALL, ANY, EXISTS)와 같이 사용된다.

특징	설명
IN	서브쿼리 결과에 존재하는 값들 중 하나와 일치해야 한다
EXISTS	서브쿼리 결과 값이 존재하는지 여부를 확인한다
ALL	서브쿼리 결과에 존재하는 모든 값들에 대해 조건을 만족해야 한다
ANY	서브쿼리 결과에 존재하는 값들 중 조건을 만족하는 것이 하나 이상 존재해야 한다

 

EXISTS는 존재만 나타내기 때문에 

EXISTS ( 
	SELECT 0 
    FROM 
    WHERE ~ 
)

이런식으로 SELECT문에 어떤 값을 써도 상관이 없음.

 

 

 

다중 컬럼 서브쿼리

• 서브쿼리의 결과가 여러 개의 컬럼을 반환하며, 메인쿼리의 조건과 동시에 비교된다.

---

스칼라 서브쿼리

• 스칼라 서브쿼리는 하나의 속성을 가지면서 하나의 행만을 반환하는 쿼리이다,
• 이는 SELECT, WHERE, HAVING 절 등에서 사용할 수 있다.

---

뷰

• 뷰는 다른 테이블에서 파생된 테이블
• 물리적으로 데이터가 저장되는 것이 아니라, 논리적으로만 존재하며 뷰를 사용한 질의 시에는 DBMS에서 뷰 정의에 따라 질의를 재작성하여 수행한다.

VIEW의 장점

특징	설명
독립성	테이블 구조가 변경되어도 뷰를 사용하고 있는 응용 프로그램은 변경하지 않아도 된다.
편리성	자주 사용되는 복잡한 쿼리를 미리 뷰로 정의해 놓으면, 추후 쿼리는 간단한 형태로 표현할 수 있다.
보안성	사용자의 권한에 따라 열람 가능한 데이터를 다르게할 수 있다. 권한에 따라 확인 가능한 컬럼을 정의하여 뷰를 생성하면, 기본 테이블 노출 없이 접근 제어를 할 수 있다.

 

VIEW의 특징

• 생성된 뷰는 또 다른 뷰를 생성하는데 사용될 수 있다.
• 뷰의 정의는 변경할 수 없으며, 삭제 후 재생성이 필요하다.
• 뷰를 통한 갱신에는 제약이 따른다. 갱신을 위해서는 기본적으로 원천 테이블의 기본키가 포함되어야 한다.
  원천이 되는 테이블이나 뷰가 삭제되면 이를 기반으로 하는 뷰도 함께 삭제된다.

replace = create + delete

---

◆ 그룹 함수 & 윈도우 함수

윈도우 함수

• 순위, 집계 등 행과 행 사이의 관계를 정의하는 함수
• OVER 구문을 필수로 한다

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

구조	설명
ARGUMENTS	윈도우 함수에 따라서 필요한 인수
PARTITION BY	전체 집합에 대해 소그룹으로 나누는 기준
ORDER BY	소그룹에 대한 정렬 기준
WINDOWING	행에 대한 범위 기준
ROWS	물리적 단위로 행의 집합을 지정
UNBOUNDED PRECENDING	윈도우의 시작 위치가 첫 번째 행
UNBOUNDED FOLLOWING	윈도우의 마지막 위치가 마지막 행
CURRENT ROW	윈도우의 시작 위치가 현재 행

 

순위 함수

RANK() OVER ([PARTITION BY 컬럼][ORDER BY 컬럼][WINDOWING 절])

함수	설명
RANK	동일한 값에는 동일한 순위를 부여
DENSE_RANK	RANK와 같이 같은 값에는 같은 순위를 부여하나 한 건으로 취급
ROW_NUMBER	동일한 값이라도 고유한 순위를 부여

 

 

일반 집계 함수

• 일반 집계 함수(SUM, AVG, MAX, MIN, ...)를 GROUP BY 구문 없이 사용할 수 있다.

 

그룹 내 행 순서 함수

함수	설명
FIRST_VALUE	가장 먼저 나온 값을 구한다.
LAST_VALUE	가장 나중에 나온 값을 구한다.
LAG	이전 X번째 행을 가져온다.
LEAD	이후 X번째 행을 가져온다.

 

그룹 내 비율 함수

함수	설명
RATIO_TO_REPORT	파티션 내 전체 SUM에 대한 비율을 구한다.
PERCENT_RANK	파티션 내 순위를 백분율로 구한다.
CUME_DIST	파티션 내 현재 행보다 작거나 같은 건들의 수 누적 백분율을 구한다.
NTILE	파티션 내 행들을 N등분한 결과를 구한다.

숫자가 맞지 않는 경우에는 앞에 있는 그룹부터 1개씩 더 추가가 됨.

ex. 7개 : 1, 1, 1 / 2, 2 / 3, 3

---

그룹 함수

• 데이터를 통계 내기 위해서는, 전체 데이터에 대한 통계는 물론이고 데이터 일부에 대한 소계, 중계 또한 필요하다.
• 각 레벨 별 SQL을 UNION문으로 묶어 작성할 수도 있으나 ORACLE DB에서는 이러한 통계 데이터를 위한 몇 가지 함수를 제공한다.

GROUP BY

 

ROLL UP, CUBE

• ROLL UP  : 그룹화하는 컬럼에 대한 부분적인 통계를 제공해준다.

• CUBE : ROLLUP 함수에서 제공하는 결과를 포함해서, 그룹화하는 컬럼에 대해 결합 가능한 모든 경우의 수에 대해 다차원 집계를 생성한다.

 

• GROUPING SETS :
  - 명시된 컬럼에 대해 개별 통계를 생성한다.
  - 각 컬럼에 대해 GROUP BY로 생성한 통계를 모두 UNION ALL한 결과와 동일함.