INTERVAL 데이터타입에 집계함수와 분석함수 사용 가능
23c 버전부터 interval 데이터 타입에 대해서도
집계함수, 분석함수를 사용할 수 있게되었습니다.
새로운 기능을 통해 개발자가 시간에 대한 계산을 별도로 만들 필요없이
오라클이 제공하는 집계, 분석 함수를 사용하여 제공할 수 있을 거 같습니다.
아래는 이 기능을 테스트해 본 내용입니다.
21c에서 가능한지 여부와 23c에 수행한 내용으로 정리해 봤습니다.
먼저 테이블을 만듭니다.
|
1
2
3
4
5
6
7
|
SQL> create table batch_result
2 (batch_run_date varchar2(8)
3 ,admin_id varchar2(10)
4 ,start_tm timestamp
5 ,end_tm timestamp);
테이블이 생성되었습니다.
|
세션 레벨에서 timestamp 데이터 표현 형식을 지정해 줍니다.
|
1
2
3
|
SQL> alter session set nls_timestamp_format='YYYYMMDD hh24:mi:ss';
세션이 변경되었습니다.
|
23c 버전에서 샘플데이터를 생성해 봅니다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL> insert into batch_result values
2 ('20230418','kwan',to_date('20230418 09:00:00','YYYYMMDD hh24:mi:ss'),to_date('20230418 09:02:00','YYYYMMDD hh24:mi:ss')),
3 ('20230418','kwan',to_date('20230418 09:02:00','YYYYMMDD hh24:mi:ss'),to_date('20230418 09:05:00','YYYYMMDD hh24:mi:ss')),
4 ('20230418','kwan',to_date('20230418 09:07:00','YYYYMMDD hh24:mi:ss'),to_date('20230418 09:10:00','YYYYMMDD hh24:mi:ss')),
5 ('20230418','kwan2',to_date('20230418 09:10:00','YYYYMMDD hh24:mi:ss'),to_date('20230418 09:13:00','YYYYMMDD hh24:mi:ss')),
6 ('20230418','kwan2',to_date('20230418 09:13:00','YYYYMMDD hh24:mi:ss'),to_date('20230418 09:15:00','YYYYMMDD hh24:mi:ss')),
7 ('20230418','kwan2',to_date('20230418 09:15:00','YYYYMMDD hh24:mi:ss'),to_date('20230418 09:20:00','YYYYMMDD hh24:mi:ss'));
6 행이 생성되었습니다.
SQL> commit;
커밋이 완료되었습니다.
|
데이터를 생성했습니다. 그런데 insert 문장이 조금 낯설게 보일 겁니다.
23c에서 새로 선보인 Table Value Constructor 기능을 사용한 형식입니다. 이 부분은 추후에 다시 기능을 살펴보도록 하겠습니다.
아래는 21c에서 같은 문장을 사용했을 때 어떻게 다른지 확인해 봤습니다.
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> insert into batch_result values
2 ('20230418','kwan',to_date('20230418 09:00:00','YYYYMMDD hh24:mi:ss'),to_date('20230418 09:02:00','YYYYMMDD hh24:mi:ss')),
3 ('20230418','kwan',to_date('20230418 09:02:00','YYYYMMDD hh24:mi:ss'),to_date('20230418 09:05:00','YYYYMMDD hh24:mi:ss')),
4 ('20230418','kwan',to_date('20230418 09:07:00','YYYYMMDD hh24:mi:ss'),to_date('20230418 09:10:00','YYYYMMDD hh24:mi:ss')),
5 ('20230418','kwan2',to_date('20230418 09:10:00','YYYYMMDD hh24:mi:ss'),to_date('20230418 09:13:00','YYYYMMDD hh24:mi:ss')),
6 ('20230418','kwan2',to_date('20230418 09:13:00','YYYYMMDD hh24:mi:ss'),to_date('20230418 09:15:00','YYYYMMDD hh24:mi:ss')),
7 ('20230418','kwan2',to_date('20230418 09:15:00','YYYYMMDD hh24:mi:ss'),to_date('20230418 09:20:00','YYYYMMDD hh24:mi:ss'));
('20230418','kwan',to_date('20230418 09:00:00','YYYYMMDD hh24:mi:ss'),to_date('20230418 09:02:00','YYYYMMDD hh24:mi:ss')),
*
2행에 오류:
ORA-00933: SQL 명령어가 올바르게 종료되지 않았습니다
|
위와 같이 명령어가 올바르게 종료되지 않았습니다라는 에러 메세지를 보이고 실행되지 않습니다.
21c에서 입력을 하려면 아래와 같이 해야 합니다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
SQL> insert into batch_result values ('20230418','kwan',to_date('20230418 09:00:00','YYYYMMDD hh24:mi:ss'),to_date('20230418 09:02:00','YYYYMMDD hh24:mi:ss'));
1 개의 행이 만들어졌습니다.
SQL> insert into batch_result values ('20230418','kwan',to_date('20230418 09:02:00','YYYYMMDD hh24:mi:ss'),to_date('20230418 09:05:00','YYYYMMDD hh24:mi:ss'));
1 개의 행이 만들어졌습니다.
SQL> insert into batch_result values ('20230418','kwan',to_date('20230418 09:07:00','YYYYMMDD hh24:mi:ss'),to_date('20230418 09:10:00','YYYYMMDD hh24:mi:ss'));
1 개의 행이 만들어졌습니다.
SQL> insert into batch_result values ('20230418','kwan2',to_date('20230418 09:10:00','YYYYMMDD hh24:mi:ss'),to_date('20230418 09:13:00','YYYYMMDD hh24:mi:ss'));
1 개의 행이 만들어졌습니다.
SQL> insert into batch_result values ('20230418','kwan2',to_date('20230418 09:13:00','YYYYMMDD hh24:mi:ss'),to_date('20230418 09:15:00','YYYYMMDD hh24:mi:ss'));
1 개의 행이 만들어졌습니다.
SQL> insert into batch_result values ('20230418','kwan2',to_date('20230418 09:15:00','YYYYMMDD hh24:mi:ss'),to_date('20230418 09:20:00','YYYYMMDD hh24:mi:ss'));
1 개의 행이 만들어졌습니다.
SQL> commit;
커밋이 완료되었습니다.
|
입력된 샘플데이터를 23c에서 조회해 봅니다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
SQL> col batch_run_date for a15
SQL> col admin_id for a10
SQL> col start_tm for a20
SQL> col end_tm for a20
SQL> col working_time for a30
SQL> col sum_working_time for a30
SQL> col avg_working_time for a30
SQL> select * from batch_result;
BATCH_RUN_DATE ADMIN_ID START_TM END_TM
--------------- ---------- -------------------- --------------------
20230418 kwan 20230418 09:00:00 20230418 09:02:00
20230418 kwan 20230418 09:02:00 20230418 09:05:00
20230418 kwan 20230418 09:07:00 20230418 09:10:00
20230418 kwan2 20230418 09:10:00 20230418 09:13:00
20230418 kwan2 20230418 09:13:00 20230418 09:15:00
20230418 kwan2 20230418 09:15:00 20230418 09:20:00
6 행이 선택되었습니다.
SQL> select batch_run_date
2 ,admin_id
3 ,start_tm
4 ,end_tm
5 ,end_tm-start_tm working_time
6 from batch_result;
BATCH_RUN_DATE ADMIN_ID START_TM END_TM WORKING_TIME
--------------- ---------- -------------------- -------------------- ------------------------------
20230418 kwan 20230418 09:00:00 20230418 09:02:00 +000000000 00:02:00.000000
20230418 kwan 20230418 09:02:00 20230418 09:05:00 +000000000 00:03:00.000000
20230418 kwan 20230418 09:07:00 20230418 09:10:00 +000000000 00:03:00.000000
20230418 kwan2 20230418 09:10:00 20230418 09:13:00 +000000000 00:03:00.000000
20230418 kwan2 20230418 09:13:00 20230418 09:15:00 +000000000 00:02:00.000000
20230418 kwan2 20230418 09:15:00 20230418 09:20:00 +000000000 00:05:00.000000
6 행이 선택되었습니다.
|
위 결과 중 WORKING_TIME 이 INTERVAL 데이터 타입의 데이터를 보여주고 있습니다.
이제 이 내용을 가지고 23c에서 지원하는 집계함수를 사용해 보겠습니다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
|
SQL> select batch_run_date
2 ,admin_id
3 ,sum(end_tm-start_tm) sum_working_time
4 from batch_result
5 group by batch_run_date, admin_id;
BATCH_RUN_DATE ADMIN_ID SUM_WORKING_TIME
--------------- ---------- ------------------------------
20230418 kwan +000000000 00:08:00.000000000
20230418 kwan2 +000000000 00:10:00.000000000
SQL> select batch_run_date
2 ,admin_id
3 ,avg(end_tm-start_tm) avg_working_time
4 from batch_result
5 group by batch_run_date, admin_id;
BATCH_RUN_DATE ADMIN_ID AVG_WORKING_TIME
--------------- ---------- ------------------------------
20230418 kwan +000000000 00:02:40.000000000
20230418 kwan2 +000000000 00:03:20.000000000
SQL> select batch_run_date
2 ,admin_id
3 ,sum(end_tm-start_tm) sum_working_time
4 from batch_result
5 group by batch_run_date, admin_id
6 having sum(end_tm-start_tm) > interval '5' minute;
BATCH_RUN_DATE ADMIN_ID SUM_WORKING_TIME
--------------- ---------- ------------------------------
20230418 kwan +000000000 00:08:00.000000000
20230418 kwan2 +000000000 00:10:00.000000000
SQL> select batch_run_date
2 ,admin_id
3 ,avg(end_tm-start_tm) avg_working_time
4 from batch_result
5 group by batch_run_date, admin_id
6 having avg(end_tm-start_tm) > interval '3' minute;
BATCH_RUN_DATE ADMIN_ID AVG_WORKING_TIME
--------------- ---------- ------------------------------
20230418 kwan2 +000000000 00:03:20.000000000
|
위와 같이 결과가 조회되는 것을 볼 수 있습니다. 그리고 having 절을 통해 필터도 가능한 것을 확인할 수 있습니다.
이제 21c에서 같은 문장을 수행해 본 결과를 확인해 보겠습니다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
|
SQL> select batch_run_date
2 ,admin_id
3 ,sum(end_tm-start_tm) sum_working_time
4 from batch_result
5 group by batch_run_date, admin_id;
,sum(end_tm-start_tm) sum_working_time
*
3행에 오류:
ORA-00932: 일관성 없는 데이터 유형: NUMBER이(가) 필요하지만 INTERVAL DAY TO SECOND임
SQL> select batch_run_date
2 ,admin_id
3 ,avg(end_tm-start_tm) avg_working_time
4 from batch_result
5 group by batch_run_date, admin_id;
,avg(end_tm-start_tm) avg_working_time
*
3행에 오류:
ORA-00932: 일관성 없는 데이터 유형: NUMBER이(가) 필요하지만 INTERVAL DAY TO SECOND임
SQL> select batch_run_date
2 ,admin_id
3 ,sum(end_tm-start_tm) sum_working_time
4 from batch_result
5 group by batch_run_date, admin_id
6 having sum(end_tm-start_tm) > interval '5' minute;
,sum(end_tm-start_tm) sum_working_time
*
3행에 오류:
ORA-00932: 일관성 없는 데이터 유형: NUMBER이(가) 필요하지만 INTERVAL DAY TO SECOND임
SQL> select batch_run_date
2 ,admin_id
3 ,avg(end_tm-start_tm) avg_working_time
4 from batch_result
5 group by batch_run_date, admin_id
6 having avg(end_tm-start_tm) > interval '3' minute;
,avg(end_tm-start_tm) avg_working_time
*
3행에 오류:
ORA-00932: 일관성 없는 데이터 유형: NUMBER이(가) 필요하지만 INTERVAL DAY TO SECOND임
|
같은 문장도 21c에서는 에러가 발생하는 것을 확인할 수 있습니다.
23c에서 추가된 신규 기능임을 확인할 수 있습니다.
댓글 0
| 번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
|---|---|---|---|---|
| 11 | (23c 신기능) GROUP BY 절에 컬럼 별칭이나 포지션 사용 | 명품관 | 2023.04.26 | 502 |
| 10 | (23c 신기능) UPDATE 문장을 위한 Default 값 NULL 설정 가능 | 명품관 | 2023.04.26 | 400 |
| 9 | (23c 신기능) PL/SQL 에서 SQL로 Transpiler 자동변환(Automatic PL/SQL to SQL Transpiler) | 명품관 | 2023.04.25 | 147 |
| » | (23c 신기능) INTERVAL 데이터타입에 집계함수와 분석함수 사용 가능 | 명품관 | 2023.04.20 | 187 |
| 7 | (23c 신기능) 23c에서 개발자를 위해 새로 추가된 DB_DEVELOPER_ROLE role | 명품관 | 2023.04.19 | 137 |
| 6 | (23c 신기능) IF EXISTS와 IF NOT EXISTS 사용 | 명품관 | 2023.04.18 | 794 |
| 5 | (23c 신기능) Annotation 사용하기(comment와 유사한) | 명품관 | 2023.04.18 | 89 |
| 4 | (23c 신기능) Direct Joins for UPDATE and DELETE Statements(직접 조인을 사용한 UPDATE, DELETE) | 명품관 | 2023.04.12 | 87 |
| 3 | JSON-Relational Duality View 튜토리얼 | 명품관 | 2023.04.10 | 62 |
| 2 | 오라클 프로세스 prefix가 ora_ 에서 db_로 변경 | 명품관 | 2023.04.06 | 74 |
| 1 | Oracle Database 23c Free Install with Oracle Linux 8.2(리눅스 8.2 버전에서 오라클 23c 설치) [1] | 명품관 | 2023.04.05 | 1057 |