[3] 데이터 분석 SQL 실습 - Between 조인, Cross 조인, Date와 Timestamp, ::Date, Interval 활용

3일차

---

오늘의 실습 내용 요약
1. BETWEEN 조인
2. CROSS 조인
3. 문자열을 Date, Timestamp로 변환하기
4. ::로 편리하게 형태 변환하기
5. Timestamp 연산 시 Interval 활용하기

Non Equi 조인과 Cross 조인

Equi 조인 

: 조인 시 연결하는 키 값이 같을 때

 

Non Equi 조인 (Between 조인..)

: = 이 아닌, 다른 연산자 (between, >, < ..)를 사용하는 조인

 

Cross 조인 (Cartesian Product)

: 조인 컬럼없이, 두 테이블 간 가능한 모든 연결을 결합

---

BETWEEN 조인 1

salgrade 테이블 - losal (low salary) ~ hisal (high salary) 와 그에 따른 grade 등급이 나와있음

-- 직원 정보와 급여등급 정보 추출
select a.*, b.grade as salgrade, b.losal, b.hisal
from hr.emp a
	join hr.salgrade b on a.sal between b.losal and b.hisal;

결과, 직원 정보에 따른 급여 등급(grade, losal, hisal)이 추출된 모습

---

BETWEEN 조인 2

**WHERE절과 비슷한 기능을 한다.

-- 직원 급여의 이력 정보를 나타내며, 해당 급여를 가졌던 시작 시점에서의 부서번호도 함께 가져오기
select a.*, b.*
from hr.emp_salary_hist a
	join hr.emp_dept_hist b on a.empno = b.empno;

M:N 조인이 되어 중복 값이 존재하는 모습

두 테이블 모두 pk가 emp_no + from_date 이기 때문에, M:N 조인이 되어, 중복된 값으로 출력된다.

▼

따라서 원하는 결과를 출력하기 위해서, BETWEEN 조인 활용

 

and절로 between 조인을 추가해주었더니 중복값이 사라진 모습

• and절에 between조인을 써서 emp_salary_hist에서 from date의 범위를 지정해주었다. salary 이력 테이블의 from date가 emp 이력 테이블 속 기간에 속하는 경우만 조인되어 출력된 것
• and 대신 where을 써도 결과가 완전히 같음
  • 단, between 조인 사용시 조인이 줄어들기에 성능 상 큰 데이터를 다룰 때 더 빠르게 수행이 된다.

---

CROSS 조인(&with문)

with
temp_01 as (
select 1 as rnum
union all
select 2 as rnum
)
select a.*, b.*
from hr.dept a
	cross join temp_01 b;

with문 속 테이블

• 이렇게 생긴 테이블과 가능한 모든 경우의 수를 보여주는 Cross join하면 조인 대상 테이블 X 2 결과가 나옴

출력 결과

---

Date / Timestamp / Time / Interval 타입

Date : 일자로서 년,월,일 정보만 가짐 YYYY-MM-DD

Timestamp : 일자 + 시간 정보까지 가짐 YYYY-MM-DD HH24 : MI : SS

Time : 오직 시간 정보만 가짐, 거의 쓰이지 않는다 HH24 : MI : SS

Interval : N days HH24 : MI_SS

문자열을 Date, Timestamp로 변환하기

to_date ( 변환할 문자열, 변환할 포맷팅 ) 

to_timestamp ( 변환할 문자열, 변환할 포맷팅)

to_char ( Date 컬럼, 변환할 포맷팅 )

** 기본 포맷팅 : yyyy-mm-dd hh24:mi:ss

포맷팅 패턴
- day : 요일 이름
- d : 요일 번호 (일[1]-토[7] )
- w : 월의 주 (1-5)
- ww : 년의 주 (1-52)
- tz : 시간대(timezone)
- hh12/24 : 하루 중 시간 (01-12 / 00-23)

PostgreSQL에서는 ::를 이용해서 편리하게 형 변환

: select to_date('2022-01-01', 'yyyy-mm-dd')::timestamp; 와 같이 date, timestamp, text앞에 ::를 붙여서 변환

-- 문자열을 formating에 따라 Date, Timestamp로 변환. 
select to_date('2022-01-01', 'yyyy-mm-dd');

select to_timestamp('2022-01-01', 'yyyy-mm-dd');

select to_timestamp('2022-01-01 14:36:52', 'yyyy-mm-dd hh24:mi:ss');

-- Timestamp를 Text로 변환
select to_timestamp('2022-01-01', 'yyyy-mm-dd')::text;

-- to_date, to_timestamp, to_char 실습-1
with
temp_01 as (
select a.*
	  , to_char(hiredate, 'yyyy-mm-dd') as hiredate_str
from hr.emp a
)
select empno, ename, hiredate, hiredate_str
	, to_date(hiredate_str, 'yyyy-mm-dd') as hiredate_01
	, to_timestamp(hiredate_str, 'yyyy-mm-dd') as hiretime_01
	, to_char(hiredate, 'yyyymmdd hh24:mi:ss') as hiredate_str_01
	, to_char(hiredate, 'month dd yyyy') as hiredate_str_02
;

with
temp_01 as (
select a.*
	  , to_char(hiredate, 'yyyy-mm-dd') as hiredate_str
from hr.emp a
)
select empno, ename, hiredate, hiredate_str
	, to_char(hiredate, 'MONTH dd yyyy') as hiredate_str_03
	, to_char(hiredate, 'yyyy month') as hiredate_str_04
	-- w 는 해당 달의 week, d는 일요일(1) 부터 토요일(7)
	, to_char(hiredate, 'MONTH w d') as hiredate_str_05
	-- day는 요일을 문자열로 나타냄. 
	, to_char(hiredate, 'Month, Day') as hiredate_str_06
from temp_01;

---

-- to_date, to_timestamp, to_char 실습-2 
with
temp_01 as (
select a.*
	  , to_char(hiredate, 'yyyy-mm-dd') as hire_date_str
	  , hiredate::timestamp as hiretime
from hr.emp a
)
select empno, ename, hiredate, hire_date_str, hiretime
	, to_char(hiretime, 'yyyy/mm/dd hh24:mi:ss') as hiretime_01
	, to_char(hiretime, 'yyyy/mm/dd PM hh12:mi:ss') as hiretime_02
	, to_timestamp('2022-03-04 22:10:15', 'yyyy-mm-dd hh24:mi:ss') as timestamp_01
	, to_char(to_timestamp('2022-03-04 22:10:15', 'yyyy-mm-dd hh24:mi:ss'), 'yyyy/mm/dd AM hh12:mi:ss') as timestr_01
from temp_01;

** 대충 보고 넘어가면 헷갈리는 경우가 많지만 RDBMS에서 많이 쓰이기 때문에 익혀두기

---

Extract와 Date_part로 날짜와 시간 추출하기

Extract (추출할 대상 from 컬럼명)

Date_part (추출할 대상, 포맷팅)

-- extract와 date_part를 이용하여 년, 월, 일 추출
select a.* 
	, extract(year from hiredate) as year
	, extract(month from hiredate) as month
	, extract(day from hiredate) as day
from hr.emp a;

select a.*
	, date_part('year', hiredate) as year
	, date_part('month', hiredate) as month
	, date_part('day', hiredate) as day
from hr.emp a;

-- extract와 date_part를 이용하여 시간, 분, 초 추출. 
select date_part('hour', '2022-02-03 13:04:10'::timestamp) as hour
	, date_part('minute', '2022-02-03 13:04:10'::timestamp) as minute
	, date_part('second', '2022-02-03 13:04:10'::timestamp) as second
;

select extract(hour from '2022-02-03 13:04:10'::timestamp) as hour
	, extract(minute from '2022-02-03 13:04:10'::timestamp) as minute
	, extract(second from '2022-02-03 13:04:10'::timestamp) as second
;

• 시간, 분, 초를 추출할 때에는 괄호 안에서 ::를 이용해서 timestamp로 변환하여 바로 추출

---

날짜와 시간 연산 - Timestamp 연산 시 Interval의 활용

Date 타입에 숫자를 더하거나 빼서 연산 가능

ex. + 2

▼

하지만 Timestamp 타입에서는 불가하기 때문에, interval 타입을 이용해서 연산을 수행해야 한다.

ex. + interval ' 7 hour'

-- Timestamp는 interval 타입을 이용하여 연산 수행. 
select to_timestamp('2022-01-01 14:36:52', 'yyyy-mm-dd hh24:mi:ss') + interval '7 hour' as timestamp_01;

select to_timestamp('2022-01-01 14:36:52', 'yyyy-mm-dd hh24:mi:ss') + interval '2 days' as timestamp_01;

select to_timestamp('2022-01-01 14:36:52', 'yyyy-mm-dd hh24:mi:ss') + interval '2 days 7 hours 30 minutes' as timestamp_01;

 

Date 타입에 interval을 더할 시, Timestamp로 변환된다

-- Date 타입에 interval을 더하면 Timestamp로 변환됨. 
select to_date('2022-01-01', 'yyyy-mm-dd') + interval '2 days' as date_01;

 

+ interval ' n day' 와 ' n days' 모두 가능하나, day를 따옴표 밖에 쓸 경우 day만 가능 ( + '2' days [X] )

select to_date('2022-01-01', 'yyyy-mm-dd') + interval '2' day as date_01;

날짜간의 차이 구하기 - Date : 결과 값은 정수형

select to_date('2022-01-03', 'yyyy-mm-dd') - to_date('2022-01-01', 'yyyy-mm-dd') as interval_01
	, pg_typeof(to_date('2022-01-03', 'yyyy-mm-dd') - to_date('2022-01-01', 'yyyy-mm-dd')) as type ;

날짜간의 차이 구하기 - Timestamp : 결과 값은 Interval

select to_timestamp('2022-01-01 14:36:52', 'yyyy-mm-dd hh24:mi:ss') 
     - to_timestamp('2022-01-01 12:36:52', 'yyyy-mm-dd hh24:mi:ss') as time_01
     , pg_typeof(to_timestamp('2022-01-01 08:36:52', 'yyyy-mm-dd hh24:mi:ss') 
     - to_timestamp('2022-01-01 12:36:52', 'yyyy-mm-dd hh24:mi:ss')) as type
;

(당연히) Date끼리의 연산은 불가

select to_date('2022-01-03', 'yyyy-mm-dd') +  to_date('2022-01-01', 'yyyy-mm-dd')
--에러 남

현재 날짜와 계산해서 근속기간 구하기

with 
temp_01 as (
select empno, ename, hiredate, now(), current_timestamp, current_date, current_time
	, date_trunc('second', now()) as now_trunc
	, now() - hiredate as 근속기간
from hr.emp
)
select *
	, date_part('year', 근속기간)
	, age(hiredate)
	, date_part('year', age(hiredate))||'년 '||date_part('month', age(hiredate))||'월' as 근속년월_01
from temp_01;

• age() : 자동으로 now를 기반으로 해당 날짜와 차이나는 기간을 계산해줌
  • now에서 뺀 연산과 동일, 일종의 나이처럼 계산되어 age
• || : string 문자열 concat 할 때 사용

---