SQL 15일차

한희수·2023년 4월 4일
sql
0

빅데이터 분석 SQL

목록 보기
15/17

20230403 SQL

■ 날짜 타입
❍ 현재 접속한 지역의 timezone 수정

  • ALTER SESSION SET TIME ZONE = '+09:00';
    ❍ 날짜 표현법
  • DATE
  • TIMESTAMP
  • TIMESTAMP WITH TIMEZONE
  • TIMESTAMP WITH LOCAL TIMEZONE
  • INTERVAL YEAR TO MONTH
  • INTERVAL DAY TO SECOND
    ❍ to_date 함수
  • 문자를 날짜형으로 변환하는 함수
    ❍ to_timestamp 함수
  • 문자를 날짜형(timestamp)으로 변환하는 함수(시분초 표현)
    ❍ to_timestamp_tz 함수
  • 문자를 날짜형(timestamp with time zone)으로 변환하는 함수
    ❍ to_yminterval 함수
  • 문자를 날짜형(INTERVAL YEAR TO MONTH)으로 변환하는 함수
    ❍ to_dsinterval 함수
  • 문자를 날짜형(INTERVAL DAY TO SECOND)으로 변환하는 함수
    ■ 날짜 계산
  • 날짜 + 일수 = 날짜
  • 날짜 – 일수 = 날짜
  • 날짜 – 날짜 = 일수
  • 날짜 + 날짜 = 오류!!!
  • 날짜 + interval year to month = 날짜
  • 날짜 + interval day to second = 날짜
    ■ extract
  • 날짜형을 숫자형으로 추출하는 날짜 함수
    ■ 분석 함수 over()
    ■ TOP-N
    ❍ 동점자 처리된 순위 구하는 방법
  • rank : 순위를 구하는 함수(동일한 순위가 있을 경우 다음 순위의 갭이 생김)
  • dense_rank : 순위를 구하는 함수(동일한 순위가 있더라도 연이은 순위를 구함)
    -- partition by, order by

■ 날짜 타입

SELECT
sysdate,
systimestamp,
current_date,
current_timestamp,
localtimestamp
FROM dual;

▽▽▽(결과창)

23/04/03 23/04/03 09:44:18.923000000 +09:00 23/04/03 23/04/03 09:44:18.000000000 ASIA/SEOUL 23/04/03 09:44:18.000000000

  • sysdate, systimestamp : server(서버) 시간 정보
  • current_date, current_timestamp, localtimestamp : client 시간 정보

❍ 현재 접속한 지역의 timezone 수정
ALTER SESSION SET TIME_ZONE = '+08:00';
ALTER SESSION SET TIME_ZONE = '-05:00'; -- 미 동부지역 시간대
SELECT
sysdate,
systimestamp,
current_date,
current_timestamp,
localtimestamp
FROM dual;

❍ 날짜 표현법

  • DATE : sysdate, current_date
  • TIMESTAMP 연월일 시분초 이하 9자리 : localtimestamp
  • TIMESTAMP WITH TIMEZONE 년월일시분초.9자리,TIMEZONE시간 : systimestamp, current_timestamp
  • TIMESTAMP WITH LOCAL TIMEZONE 년월일시분초.9자리 : 보는 지역에 따라 날짜, 시간 정보가 자동 정규화하는 날짜 타입
  • INTERVAL YEAR TO MONTH : 기간을 나타내는 날짜 타입, 년수, 개월수
  • INTERVAL DAY TO SECOND : 기간을 나타내는 날짜 타입, 일수, 시분초.9자리

CREATE TABLE time_test(
a date,
b timestamp with time zone,
c date,
d timestamp with time zone,
e timestamp,
f timestamp with local time zone);
Table TIME_TEST이(가) 생성되었습니다.

desc time_test;
INSERT INTO time_test(a,b,c,d,e,f)
VALUES(sysdate, systimestamp, current_date, current_timestamp, localtimestamp, localtimestamp);
commit;
SELECT*FROM time_test;

ALTER SESSION SET TIME_ZONE = '+08:00';
SELECT
sysdate,
systimestamp, -- 서버 시간 보여줌
current_date,
current_timestamp,
localtimestamp -- client 시간 보여줌
FROM dual;

SELECT*FROM time_test;

((RUN SQL COMMAND LINE)) -- sql plus에서 확인해야 함(프로그램 문제)

SQL> SELECT e,f FROM time_test;

E

F

23/04/03 10:00:18.000000
23/04/03 10:00:18.000000

SQL> ALTER SESSION SET TIME_ZONE = '+08:00';

Session altered.

SQL> SELECT e,f FROM time_test;

E

F

23/04/03 10:00:18.000000
23/04/03 09:00:18.000000

SQL> ALTER SESSION SET TIME_ZONE = '-05:00';

Session altered.

SQL> SELECT e,f FROM time_test;

E

F

23/04/03 10:00:18.000000
23/04/02 20:00:18.000000

SQL> SELECT d,e,f FROM time_test;

D

E

F

23/04/03 10:00:18.000000 +09:00 -- 어느 타임존에서 작성하였는지 알 수 있음
23/04/03 10:00:18.000000
23/04/02 20:00:18.000000 -- 보는 지역의 타임존으로 바꾸어 보여줌

❍ to_date 함수

  • 문자를 날짜형으로 변환하는 함수

SELECT to_date('20230403','yyyymmdd')
FROM dual;

SELECT to_date('2023-04-03 10:20:00.12345','yyyy-mm-dd hh24:mi:ss.sssss’')
FROM dual;
--> 오류
ORA-01836: hour conflicts with seconds in day
01836. 00000 - "hour conflicts with seconds in day"

SELECT to_date('2023-04-03 10:20:00','yyyy-mm-dd hh24:mi:ss')
FROM dual;

▽▽▽(결과창)

23/04/03

❍ to_timestamp 함수

  • 문자를 날짜형(timestamp)으로 변환하는 함수(시분초 표현)

SELECT to_timestamp('20230403','yyyymmdd') -- 이렇게 해도 시분초 나옴
FROM dual;

SELECT to_timestamp('2023-04-03 10:17:00.123456789','yyyy-mm-dd hh24:mi:ss.ff')
FROM dual;

❍ to_timestamp_tz 함수

  • 문자를 날짜형(timestamp with time zone)으로 변환하는 함수

SELECT to_timestamp_tz('2023-04-03 10:20:00.123456789 +09:00','yyyy-mm-dd hh24:mi:ss.ff tzh:tzm')
FROM dual;

▽▽▽(결과창)

23/04/03 10:20:00.123456789 +09:00

❍ to_yminterval 함수

  • 문자를 날짜형(INTERVAL YEAR TO MONTH)으로 변환하는 함수

SELECT sysdate, add_months(sysdate, 14) --> 복잡!
FROM dual;

SELECT sysdate, sysdate + to_yminterval('10-2') --> 10년 2개월 후
FROM dual;

SELECT sysdate, sysdate - to_yminterval('10-2') --> 10년 2개월 전
FROM dual;

SELECT sysdate, sysdate + to_yminterval('10-00') --> 10년
FROM dual;

SELECT sysdate, sysdate + to_yminterval('00-11') --> 11개월
FROM dual;

SELECT sysdate, sysdate + to_yminterval('1-00') --> 1년
FROM dual;

SELECT sysdate, sysdate + to_yminterval('00-12') --> 12개월X 1년으로 해야함
FROM dual;

❍ to_dsinterval 함수

  • 문자를 날짜형(INTERVAL DAY TO SECOND)으로 변환하는 함수

SELECT sysdate, sysdate + 100 --> 100일 더하기
FROM dual;

SELECT sysdate, localtimestamp + 100
FROM dual;

▽▽▽(결과창)

23/04/03 23/07/12 --> 시간 정보 표현 생략됨

SELECT sysdate, to_char(localtimestamp + 100, 'yyyy-mm-dd hh24:mi:ss')
FROM dual;

▽▽▽(결과창)

23/04/03 2023-07-12 09:46:49

ALTER SESSION SET TIME_ZONE = '+09:00';

SELECT sysdate, to_char(localtimestamp + 100 + 10/24, 'yyyy-mm-dd hh24:mi:ss')
FROM dual;

▽▽▽(결과창)

23/04/03 2023-07-12 20:47:39

SELECT sysdate, sysdate + to_dsinterval('100')
FROM dual;

  • 오류 남
    ORA-01867: the interval is invalid
  1. 00000 - "the interval is invalid“

SELECT sysdate, sysdate + to_dsinterval('100 00:00:00') --> 이렇게 써야함
FROM dual;

SELECT sysdate, localtimestamp + to_dsinterval('100 00:00:00') --> to_char보다 나음
FROM dual;

▽▽▽(결과창)

23/04/03 23/07/12 10:50:16.226000000

❍
CREATE TABLE time_test_1(
a interval year to month,
b interval day to second);

desc time_test_1;

Table TIME_TEST_1이(가) 생성되었습니다.

이름 널? 유형

A INTERVAL YEAR(2) TO MONTH
B INTERVAL DAY(2) TO SECOND(6)

DROP TABLE time_test_1 PURGE;
CREATE TABLE time_test_1(
a interval year(3) to month,
b interval day(3) to second);)

desc time_test_1;

Table TIME_TEST_1이(가) 생성되었습니다.

이름 널? 유형

A INTERVAL YEAR(3) TO MONTH
B INTERVAL DAY(3) TO SECOND(6)

INSERT INTO time_test_1(a,b)
VALUES('10-2','100 10:00:00');

SELECT*FROM time_test_1;

▽▽▽(결과창)

+10-02 +100 10:00:00.000000

INSERT INTO time_test_1(a,b)
VALUES(to_yminterval('5-10'),to_dsinterval('200 20:00:00'));
SELECT*FROM time_test_1;

▽▽▽(결과창)

+10-02 +100 10:00:00.000000
+05-10 +200 20:00:00.000000

SELECT
localtimestamp,
localtimestamp + a,
localtimestamp - b
FROM time_test_1;

▽▽▽(결과창)

23/04/03 10:57:30.043000000 33/06/03 10:57:30.043000000 22/12/24 00:57:30.043000000
23/04/03 10:57:30.043000000 29/02/03 10:57:30.043000000 22/09/14 14:57:30.043000000

■ 날짜 계산

  • 날짜 + 일수 = 날짜
  • 날짜 – 일수 = 날짜
  • 날짜 – 날짜 = 일수
  • 날짜 + 날짜 = 오류!!!
  • 날짜 + interval year to month = 날짜
  • 날짜 + interval day to second = 날짜

■ extract

  • 날짜형을 숫자형으로 추출하는 날짜 함수
    SELECT
    sysdate,
    to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss.sssss')
    FROM dual;

SELECT extract(year from sysdate) year FROM dual; --> 2023

SELECT extract(month from sysdate) month FROM dual; --> 4

SELECT extract(day from sysdate) day FROM dual; --> 3

SELECT extract(hour from localtimestamp) hour FROM dual; --> 11

SELECT extract(minute from localtimestamp) minute FROM dual; --> 5

SELECT extract(second from localtimestamp) second FROM dual; --> 42.996

SELECT extract(timezone_hour from current_timestamp) timezone_hour FROM dual; --> 9

SELECT extract(timezone_minute from current_timestamp) timezone_minute FROM dual; --> 0

SELECT extract(second from current_timestamp) second FROM dual; --> 8.759

SELECT extract(timezone_second from current_timestamp) second FROM dual;
==> 이건 왜 안돼

SELECT extract(timezone_region from current_timestamp) timezone_region FROM dual; --> UNKNOWN

SELECT extract(timezone_abbr from current_timestamp) timezone_abbr FROM dual;
--> UNK

[문제104] 각 사원의 last_name, hire_date 및 근속 연수를 출력하는 query를 작성합니다.
사원의 근속 연수가 5년 이상인 경우 ‘5 years of service’를 출력합니다.
사원의 근속 연수가 10년 이상인 경우 ‘10 years of service’를 출력합니다.
사원의 근속 연수가 15년 이상인 경우 ‘15 years of service’를 출력합니다.
어떠한 조건과도 일치하지 않을 경우 ‘maybe next year!’를 출력합니다.
단, 근속 연수 출력은 case, to_yminterval을 사용하세요.

SELECT last_name, hire_date, CASE
WHEN 근속 연수가 5년 이상 THEN '5 years of service',
WHEN 근속 연수가 10년 이상 THEN '10 years of service',
WHEN 근속 연수가 15년 이상 THEN '15 years of service',
ELSE 'maybe next year!'
END
FROM employees;

SELECT last_name, hire_date, CASE
WHEN to_char(sysdate - to_yminterval('15-00'), 'yyyy') >= to_char(hire_date, 'yyyy') THEN '15 years of service'
WHEN to_char(sysdate - to_yminterval('10-00'), 'yyyy') >= to_char(hire_date, 'yyyy') THEN '10 years of service'
WHEN to_char(sysdate - to_yminterval('05-00'), 'yyyy') >= to_char(hire_date, 'yyyy') THEN '5 years of service'
ELSE 'maybe next year!'
END
FROM employees;

--> 이거 안되는 이유: 08/03/23은 15년 이상, 08/04/04 입사사원은 10년 이상으로 분류되어야 하는데 연도로만 구분하면 모두 15년 이상으로 분류됨
(오늘 날짜가 23/04/03 이기 때문)
--> CASE WHEN THEN 뒤에 콤마(,) 붙지 않음 주의

(풀이 과정)
SELECT -- 기준 날짜 확인
sysdate,
sysdate – to_yminterval('15-00')
sysdate - to_yminterval('10-00'),
sysdate – to_yminterval('05-00')
FROM dual;

(답안)
SELECT last_name, hire_date, trunc(months_between(sysdate, hire_date)/12) 연수,
CASE
WHEN sysdate - to_yminterval('15-00') >= hire_date THEN '15 years of service'
WHEN sysdate - to_yminterval('10-00') >= hire_date THEN '10 years of service'
WHEN sysdate - to_yminterval('05-00') >= hire_date THEN '5 years of service'
ELSE 'maybe next year!'
END awards
FROM employees;

[문제105] 사원들의 근무연수별 인원수를 출력해주세요.
근무년수 인원수

22 1
20 7
19 11
.... ....

(내가 쓴 답안)
SELECT trunc((sysdate - hire_date)/365) "근무년수", count(*) "인원수"
FROM employees
GROUP BY trunc((sysdate - hire_date)/365)
ORDER BY 1 desc;

(INLINE VIEW 이용; 선생님 답안)
SELECT 근무년수, COUNT(*) 인원수
FROM (SELECT trunc(months_between(sysdate, hire_date)/12) 근무년수
FROM employees)
GROUP BY 근무년수
ORDER BY 1 DESC;

■ 분석 함수 over()

  • 예시: 전체 평균/합계과 개별 값 비교
    SELECT trunc(avg(salary)) --> 결과값 6503
    FROM employees;

SELECT employee_id, salary - 6503
FROM employees;

SELECT employee_id, salary, avg(salary) over() --> 전체 평균 계산 분석 함수
FROM employees;

SELECT employee_id, salary, trunc(avg(salary) over()) "전체 평균", salary - trunc(avg(salary) over()) "차이"
FROM employees;

SELECT employee_id, salary, trunc(sum(salary) over()) "전체 합", salary - trunc(sum(salary) over()) "차이"
FROM employees;

SELECT employee_id, salary, trunc(max(salary) over()) "최고 급여", salary - trunc(max(salary) over()) "차이"
FROM employees;

SELECT employee_id, salary, trunc(min(salary) over()) "최저 급여", salary - trunc(min(salary) over()) "차이"
FROM employees;

SELECT
employee_id,
salary,
sum(salary) over() --> 전체 합계 금액
sum(salary) over(order by employee_id) --> 누적 합계 금액
FROM employees;

SELECT
employee_id,
salary,
department_id,
sum(salary) over() "전체합",
sum(salary) over(partition by department_id) "부서별합"
FROM employees;
SELECT
employee_id,
salary,
department_id,
sum(salary) over() "전체 합",
sum(salary) over(partition by department_id) "부서별 합",
sum(salary) over(partition by department_id order by employee_id) "부서별 누적합"
FROM employees;

SELECT
employee_id,
department_id,
count() over() "전체 인원수",
count() over(partition by department_id) "부서별 인원수"
FROM employees;

SELECT
employee_id,
department_id,
salary,
max(salary) over() "최고 급여",
max(salary) over(partition by department_id) "부서별 최고급여",
min(salary) over() "최저 급여",
min(salary) over(partition by department_id) "부서별 최저급여"
FROM employees;

[문제106] 자신의 부서 평균급여보다 많이 받는 사원들의 employee_id, salary, department_name을 출력해주세요.

SELECT e2.employee_id, e2.salary, d.department_name
FROM(
SELECT round(avg(salary) over(partition by department_id)) as avg_sal
FROM employees) e1, employees e2, departments d
WHERE e2.department_id = d.department_id
AND e2.salary > e1.avg_sal; -> 내가 한 것, 이거 아님

1) join, correlated subquery 조인, 상호관련 서브쿼리
SELECT e.employee_id, e.salary, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.salary > (SELECT avg(salary)
FROM employees
WHERE department_id = e.department_id);

SELECT e.employee_id, e.salary, (SELECT department_name
FROM departments
WHERE department_id = e.department_id)
FROM employees e
WHERE e.salary > (SELECT avg(salary)
FROM employees
WHERE department_id = e.department_id);

2) INLINE VIEW 이용
SELECT e2.employee_id, e2.salary, e2.department_id
FROM (SELECT department_id, avg(salary) avg_sal
FROM employees
GROUP BY department_id) e1, employees e2
WHERE e1.department_id = e2.department_id
AND e2.salary > e1.avg_sal;

SELECT e2.employee_id, e2.salary, (SELECT department_name
FROM departments
WHERE department_id = e2.department_id) department_name
FROM (SELECT department_id, avg(salary) avg_sal
FROM employees
GROUP BY department_id) e1, employees e2
WHERE e1.department_id = e2.department_id
AND e2.salary > e1.avg_sal;

===>>> 1,2번의 문제점: employees 테이블 두 번 방문, 부하 심함(악성 프로그램)

3) 분석 함수 이용

  • 개별 값과 평균 값 비교(decode, case)

SELECT e.employee_id, e.salary, (SELECT department_name
FROM departments
WHERE e.department_id = department_id) department_name
FROM (SELECT employee_id, salary, department_id,
CASE WHEN salary > avg(salary) over(partition by department_id) THEN 1 END case_sal
FROM employees) e
WHERE e.case_sal = 1;

■ TOP-N

((예시)) 최고 급여자 중 10위까지 출력?

a. 급여를 기준으로 내림차순 정렬
SELECT employee_id, salary
FROM employees
ORDER BY salary desc;

b-1.정렬한 결과를 가지고 10개까지 출력?
❍ rownum: select 결과를 화면에 출력할 때 fetch 번호

SELECT rownum, employee_id, salary
FROM(SELECT employee_id, salary
FROM employees
ORDER BY salary desc)
WHERE rownum <= 10; --> 동점자(중복) 처리가 안된다는 문제점 있음

((잘못된 예시))

SELECT rownum, employee_id, salary
FROM employees
WHERE rownum <= 10
ORDER BY salary desc;
-->> 주의!! 이건 결과 다름(where 먼저 돌아감)
random 하게 10개 추출한 후 그 결과의 salary를 이용해서 내림차순 정렬한 쿼리문장

❍ 동점자 처리된 순위 구하는 방법

  • rank : 순위를 구하는 함수(동일한 순위가 있을 경우 다음 순위의 갭이 생김)
  • dense_rank : 순위를 구하는 함수(동일한 순위가 있더라도 연이은 순위를 구함)

SELECT employee_id, salary,
rank() over (order by salary desc) rank1,
dense_rank() over (order by salary desc) rank2
FROM employees;

▽▽▽(결과창)

employee_id salary rank1 rank2
100 24000 1 1
101 17000 2 2
102 17000 2 2
145 14000 4 3
146 13500 5 4
.....

b-2. rank 이용
SELECT *
FROM (SELECT
employee_id, salary,
rank() over (order by salary desc) rank
FROM employees)
WHERE rank <= 10;

▽▽▽(결과창)

employee_id salary rank
100 24000 1
101 17000 2
102 17000 2
145 14000 4
146 13500 5
201 13000 6
108 12008 7
205 12008 7
147 12000 9
168 11500 10

b-3. dense_rank 이용
SELECT *
FROM (SELECT employee_id, salary,
dense_rank() over (order by salary desc) rank
FROM employees)
WHERE rank <= 10;

▽▽▽(결과창)

employee_id salary rank
100 24000 1
101 17000 2
102 17000 2
145 14000 3
146 13500 4
201 13000 5
108 12008 6
205 12008 6
147 12000 7
168 11500 8
114 11000 9
174 11000 9
148 11000 9
149 10500 10
162 10500 10

((예시; 부서별 사원들의 급여 순위))

SELECT employee_id, salary, department_id,
rank() over(partition by department_id order by salary desc) "부서별 rank",
dense_rank() over(partition by department_id order by salary desc) "부서별 dense_rank"
FROM employees;

▽▽▽(결과창)

employee_id salary department_id 부서별 rank 부서별 dense_rank
200 4400 10 1 1
201 13000 20 1 1
114 11000 30 1 1
115 3100 30 2 2
116 2900 30 3 3
117 2800 30 4 4
119 2500 30 5 5
203 6500 40 1 1
121 8200 50 1 1
120 8000 50 2 2
122 7900 50 3 3
123 6500 50 4 4
124 5800 50 5 5
184 4200 50 6 6
185 4100 50 7 7
192 4000 50 8 8
....

profile
한희수
이전 포스트

SQL 14일차

다음 포스트

SQL 16일차(필기시험)

0개의 댓글