[PLAYDATA] 데이터 엔지니어링 8월 4주차 8/24

• 8월 24일

계속해서 SQL수업을 나갔다. 직접 실습을 해 보면서 익숙해지는게 좋겠다. 이번 블로그 내용은 강사님이 지금까지 주신 모든 Oracle SQL PPT내용을 공부하여 정리한 내용이다.

 

 

KEY의 구분

KEY는 column에 해당하는 카테고리를 의미한다.

PRIMARY KEY는 table을 대표하는 key로, 값의 중복이 없어야 하며, NULL값을 가질 수 없다.

COMPOSITE KEY,는 PRIMARY KEY 역할을 하나, 열이 2개 이상으로 구성된 KEY이다.

ALTERNATE KEY는 CANDIDATE KEY에 속하지만, PRIMARY KEY가 아닌 모든 키를 말합니다.

즉 CANDIDATE KEY = PRIMARY + ALTERNATE KEY

FOREIGN KEY는 어떤 table에 있는 key이면서 동시에 다른 table의 PRIMARY KEY인 key를 말한다.

 

 

DB의 자료형

CHAR(length): 고정 길이의 문자열

VARCHAR(max_length): 가변 길이의 문자열

NUMBER(digit, 소수점 이하 자릿수): 수를 저장

DATE: 날짜 형식을 저장

 

 

데이터 조회하기

selection: 행 단위 조회 (조회되는 데이터의 기본 unit이 row라는 뜻)

projection: 열 단위 조회 (조회되는 데이터의 기본 unit이 column이라는 뜻)

join: 서로다른 테이블을 가로로(column을 늘림) 이어붙임

 

SELECT구문

SELECT <column_name, column_name, ...> FROM <table_name>;

특정 table에서 내가 원하는 column들만 추려서 출력하는 역할을 한다.

 

DISTINCT구문

~ DISTINCT <column_name, column_name, ...> ~

특정 column에 중복된 값이 있으면 제거하여 출력하는 구문이다. 단독으로 쓰이지 않고 다른 구문에 포함되어 사용된다(양 옆에 물결표시).

e.g.) SELECT DISTINCT <column_name1>, <column_name2> FROM <table_name>;

 

column name에 연산식 사용하기

column에 연산식을 적용하여 연산결과를 반영하여 출력할 수 있다.

e.g.) SELECT <column_name1>, <column_name2>*12+<column_name3> FROM <table_name>;

 

정렬

~ ORDER BY <column_name> [ASC | DESC] ~ 구문을 사용한다.

ASC는 오름차순, DESC는 내림차순이며, 생략시 기본적으로 ASC로 작동한다.

 

 

조건 달기

~ WHERE <condition> ~ 구문으로 조건을 걸 수 있다.

e.g.) SELECT * FROM <table_name> WHERE <condition>;

 

연산자

=: 두 값이 같은지를 비교하는 연산자이다. (C언어에서의 == 역할)

AND, OR, NOT이 있다.

추가로 산술 연산자(+-*/)를 포함시켜 조건문을 구성하는 것도 가능하다.

대소 비교 연산자: >, >=, <, <=

IN 연산자: 특정 column에 대해, 어떤 행이 지정한 데이터를 가지고 있으면 true.

~ <column_name> IN (<data>, <data>, ...) ~

BETWEEN 연산자: 특정 column의 값이 특정 구간에 포함되어 있으면 true.

~ <column_name> BETWEEN <min> AND <max> ~

(주의!) min, max값을 포함한다. 즉 '<'와 '>'가 아닌, '<='와 '>='를 의미.

 

문자(열) 연산자

LIKE 구문: 지정한 문자열의 패턴에 해당하는 문자열을 true로 반환한다.

~ <column_name> LIKE <string_pattern> ~

조건문에서 사용된다.

e.g.) WHERE NAME_ LIKE '%AB%'

패턴의 규칙: underscore(_)와 %를 사용한다.

_: 한 개의 문자를 의미한다.

%: 모든 문자 데이터를 의미한다. (*와 비슷한 역할)

e.g.) '_AB' -> 문자가 3개이며 뒤의 두 문자가 AB임

'A__B_' -> 문자가 5개이며 맨 앞의 문자는 A이고 4번째 문자가 B임

'%T' -> 맨 뒤의 문자가 T인 모든 문자열

'%SS%' -> SS 문자열을 포함하는 모든 문자열

'%G_' -> 맨 뒤에서 두번째 문자가 G인 모든 문자열

 

NULL 지정자

SQL에서 NULL은 아무런 데이터가 들어가 있지 않은 것을 의미한다.

IS NULL 연산자: 대상 데이터가 NULL일 때 true.

e.g.) WHERE <column_name> IS NULL -> 특정 column의 값이 NULL인 행만 true.

WHERE <column_name> IS NOT NULL

 

 

집합 연산

SQL에서는 각 row를 하나의 집합의 원소라고 생각하여 집합연산을 할 수 있다.

 

집합 연산자

UNION: 중복을 허용하지 않는 합집합

UNION ALL: 중복을 허용하는 합집합

MINUS: 차집합

INTERSECT: 교집합

일반적으로 2개의 SELECT문 사이에 사용된다.

e.g.) SELECT ~ UNION SELECT ~;

 

 

연산자 우선순위

우선순위를 외우는 건 현실적으로 힘들다. 따라서 우선순위를 잘 모를 땐 소괄호()로 묶어주자.

 

 

DATE자료형 연산

DATE+number: 날짜 데이터에서 숫자만큼의 day 이후의 날짜

DATE-number: 날짜 데이터에서 숫자만큼의 day 이전의 날짜

DATE-DATE: 두 날짜 데이터 간의 day차이

DATE+DATE -> 지원 안함

SYSDATE: 현재 날짜와 시간을 저장하는 예약어

 

DATE관련 함수

SQL에서 함수란 특정 요소들(값, column 등)을 넣으면 특정 요소 형태(마찬가지로 값, column 등)로 반환하는 문법이다.

~ ADD_MONTHS(<date>, <num>) ~

<num>개월 이후 날짜

TO_CHAR: NUMBER 또는 DATE 자료형을 CHAR형태로 변환

e.g.) SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') FROM <table_name>;

TO_NUMBER: CHAR데이터를 NUMBER로 변환

TO_DATE: CHAR데이터를 DATE로 변환

e.g.) SELECT TO_DATE('23/11/10', 'YY/MM/DD'), TO_DATE('22/12/13', 'YY/MM/DD') FROM <table_name>;

 

 

NULL 처리 함수

NVL: NULL이 아니면 PASS, NULL이면 지정한 값으로 채운다.

NVL(<column_name>, <return_value>)

-> column이 아니라 데이터가 올 수도 있다.

NVL2: NULL이 아닐 때 지정한 값, NULL일 때 지정한 값으로 각각 채운다.

NVL2(<column_name>, <return_if_not_null>, <return_if_null>)

-> column이 아니라 데이터가 올 수도 있다.

e.g.)SELECT COL1, COL2, NVL(COL1, 0), COL2+NVL(COL1, 0) FROM TB1;

 

 

일괄적으로 행의 내용을 조건에 의해 변경하기

DECODE나 CASE문을 사용하면, 조건식과 그에 따른 연산을 병렬적으로 기입할 수 있다.

반환형은 column이다.

 

DECODE문 구조

DECODE(<column_for_condition>,

<condition1>, <return_value1>,

<condition2>, <return_value2>,
<condition3>, <return_value3>,
...
<conditionN>, <return_valueN>,

<return value_else>)

 

CASE문 구조

CASE <column_for_condition>
WHEN <condition1> THEN <return_value1>
WHEN <condition1> THEN <return_value1>
...
WHEN <condition1> THEN <return_value1>
ELSE <return_value_else>
END

 

Tip) 출력 column의 이름을 지정하고 싶다면 AS 구문을 사용한다.

 

 

열을 입력으로 받고 하나의 데이터 출력

이를 다중행 함수 라고 한다.

하나의 행을 하나의 데이터(element)로 합치기 때문에, SELECT 구문에서 다른 열들과의 행의 갯수(각 column당 element 갯수)가 맞지 않으면 오류가 발생한다.

 

종류

SUM: 합계

COUNT: 데이터 개수

MAX: 최댓값

MIN: 최솟값

AVG: 평균값

 

사용법

예시) SELECT SUM(<column_name>) FROM <table_name>;

 

 

GROUP BY절에 조건걸기

GROUP BY에 관한 내용은 다음 링크 참고

https://na7381.tistory.com/8

[PLAYDATA] 데이터 엔지니어링 8월 4주차 회고 8/22

GROUP BY절은 간단하게 말하면 어떤 열을 기준으로 그 열의 값이 같은 행끼리 묶는 것이다.

이때 그룹 하나당 행 하나로 합쳐지기 때문에 다중행 함수를 이용해야 한다.

 

HAVING 구문

GROUP BY로 모인 그룹들 중에서 특정 조건을 만족시키는 그룹만 솎아내는 기능이 있는데, 이 기능을 사용하기 위해 HAVING 구문을 사용한다.

사용법: ~ GROUP BY <column_name> HAVING <condition>;

 

WHERE절과의 차이점

WHERE은 행에 대한 조건구문이고 HAVING은 그룹에 대한 조건구문이다.

 

 

조인(join)

SQL에서 조인(join)이란 서로다른 두 tables를 가로로 붙여주는 역할을 한다.

조인은 table 목록을 두 개 이상 언급하면 된다.

 

Tip) join 개념 이해의 핵심

join사용은 앞 table의 각 행마다 뒷table의 모든 행을 각각 대응시킨(즉 가능한 모든 경우의 행이 만들어진다) 단순join을 바탕으로 추가적인 조건들(WHERE구문 등)을 걸어서 원하는 행만을 걸러내는 식으로 사용한다.

 

참고1) Table의 열 이름 언급하는 법

<table_name>.<column_name>

참고2) Table의 별칭 설정법

<table_name>을 <table_name> <table_alias>로 치환

 

예시1) 단순 JOIN

SELECT * FROM TB1, TB2;

이렇게 하게되면 TB2 하나의 행이 TB1의 모든 행에 붙여지는 방식으로 조인이 된다. (즉 결과table의 행의 갯수는 TB1행갯수xTB2행갯수)

그러나 이런 단순join은 거의 사용되지 않고 WHERE같은 조건을 걸어서 사용한다.

예시2) 등가 JOIN

SELECT * FROM TB1, TB2 WHERE TB1.COL2 = TB2.COL1_1;

두 테이블의 가능한 모든 행의 경우의 수 중에서 TB1.COL2값과 TB2.COL1_1값이 일치하는 행만 걸러서 출력한다.

조건식에서 등가 기호(=)가 사용되기 때문에 등가 JOIN이라고 부른다.

예시3) 비등가 JOIN

SELECT T1.*, T2.* FROM TB1 T1, TB2 T2 WHERE T1.COL2 BETWEEN 3 AND 7;

조건식에서 등가 기호가 없기 때문에 비등가 JOIN이라고 부른다.

예시4) 자체 JOIN

SELECT T1.COL1, T1.NAME_ FROM TB1 T1, TB1 T2;

동일한 table을 JOIN하기 때문에 자체 JOIN이라고 부른다. 자체 JOIN은 FROM절에서 반드시 alias를 설정해서 구별해 주어야 한다(그렇지 않으면 ambiguous error).

특별한 조건을 걸지 않는다면 기존 table의 한 행이 기존 table의 전체 행의 갯수만큼 복제된다.

-> 예를들어 어떤 table의 행 갯수가 12개라면, 자체JOIN을 할 때 각 행이 전체 행의 갯수만큼 복제 되기 때문에 결과table의 행 갯수는 12x12개이다.

예시5) 외부 JOIN

SELECT T1.*, T2.* FROM TB1 T1, TB2 T2 WHERE T1.COL1 = T2.COL1(+);

OUTER JOIN이란 두개의 table중 한쪽 table은 "그대로 출력"하고, 나머지 한쪽 table은 조건에 맞춰서 행을 붙이고 나머지 빈 공간은 null로 채워서 행을 맞추는 기법이다.

기준 table에 다른 테이블이 맞춰주는 것이기 때문에, 조건을 만족하는 행 중에서 기존table에만 있는 행은 null로 맞춰지고, 반대로 맞춰주는 table에만 있는 행은 출력되지 않는다.

예시)

COL1	COL2	NAME_
11	8	A
11	6	B
22	2	C
33	3	D
11	1	E
33	7	F

테이블 TB1

 

COL1_1	COL2_1	ID_
1	111	a
2	111	b
3	222	c
9	333	d

테이블 TB2

 

COL1	COL2	NAME_	COL1_1	COL2_1	ID_
22	2	C	2	111	b
33	3	D	3	222	c
11	1	E	1	111	a

OUTER JOIN을 사용하지 않은 경우

SELECT T1.*, T2.* FROM TB1 T1, TB2 T2 WHERE T1.COL2 = T2.COL1_1;

 

COL1	COL2	NAME_	COL1_1	COL2_1	ID_
11	1	E	1	111	a
22	2	C	2	111	b
33	3	D	3	222	c
11	6	B	(null)	(null)	(null)
33	7	F	(null)	(null)	(null)
11	8	A	(null)	(null)	(null)

OUTER JOIN을 사용한 경우

SELECT T1.*, T2.* FROM TB1 T1, TB2 T2 WHERE T1.COL2 = T2.COL1_1(+);

 

LEFT/RIGHT OUTER JOIN

OUTER JOIN할 때에는 쿼리문장에 (+)를 붙이는데, (+)가 붙지 않은 table이 위에서 말한 그대로 출력되는 table이며 (+)가 붙은 table이 null에 의해 채워질 table이다.

조건문에서 (+)가 오른쪽에 있으면 LEFT OUTER JOIN이고

조건문에서 (+)가 왼쪽에 있으면 RIGHT OUTER JOIN이다. (기준 table을 따라 이름을 부른다고 생각하면 쉽다)

 

SQL-99 표준 문법의 JOIN

SQL-99 표준 문법에 의해, (+)기호 대신 다른 문법을 사용할 수 있다. (SQL-99를 지원한다는 가정 하에)

 

왼쪽 외부 조인

~ FROM TB1 T1 LEFT OUTER JOIN TB2 T2 ON <condition>;

 

오른쪽 외부 조인

~ FROM TB1 T1 RIGHT OUTER JOIN TB2 T2 ON <condition>;