데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

Database

Community

자유게시판

도움주신분들

Admin

운영게시판

최근게시물

Oracle Q&A 42010 게시물 읽기

News | Q&A | Columns | Tutorials | Devel | Files | Links

No. 42010

쿼리 질문 드립니다.
작성자	CK(yaksok43)	작성일	2025-07-31 15:53	조회수	1,705

T Data로 T_RESULT 결과를 얻고 싶은데 쿼리를 어떻게 작성해야 할까요?

도움 부탁 드립니다.

감사합니다.

with t as

(

select 'BUFF' as CLASS, '20250731130000' as EVENT_TM from dual union all

select 'WAIT' as CLASS, '20250731120000' as EVENT_TM from dual union all

select 'WAIT' as CLASS, '20250731110000' as EVENT_TM from dual union all

select 'INPROC' as CLASS, '20250731100000' as EVENT_TM from dual union all

select 'INPROC' as CLASS, '20250731090000' as EVENT_TM from dual union all

select 'LOAD' as CLASS, '20250731080000' as EVENT_TM from dual union all

select 'LOAD' as CLASS, '20250731070000' as EVENT_TM from dual union all

select 'WAIT' as CLASS, '20250731060000' as EVENT_TM from dual union all

select 'WAIT' as CLASS, '20250731050000' as EVENT_TM from dual union all

select 'WAIT' as CLASS, '20250731040000' as EVENT_TM from dual union all

select 'CHANGE' as CLASS, '20250731030000' as EVENT_TM from dual union all

select 'CHANGE' as CLASS, '20250731020000' as EVENT_TM from dual union all

select 'WAIT' as CLASS, '20250731010000' as EVENT_TM from dual

)

with t_result as

(

select 'BUFF' as CLASS, '20250731130000' as EVENT_TM , '' as POST_EVENT_TM from dual union all

select 'WAIT' as CLASS, '20250731110000' as EVENT_TM , '20250731130000' as POST_EVENT_TM from dual union all

select 'INPROC' as CLASS, '20250731090000' as EVENT_TM , '20250731110000' as POST_EVENT_TM from dual union all

select 'LOAD' as CLASS, '20250731070000' as EVENT_TM , '20250731090000' as POST_EVENT_TM from dual union all

select 'WAIT' as CLASS, '20250731040000' as EVENT_TM , '20250731070000' as POST_EVENT_TM from dual union all

select 'CHANGE' as CLASS, '20250731020000' as EVENT_TM , '20250731040000' as POST_EVENT_TM from dual union all

select 'WAIT' as CLASS, '20250731010000' as EVENT_TM , '20250731020000' as POST_EVENT_TM from dual

)

이 글에 대한 댓글이 총 1건 있습니다.

WITH t AS

(

SELECT 'BUFF' class, '20250731130000' event_tm FROM dual

UNION ALL SELECT 'WAIT'  , '20250731120000' FROM dual

UNION ALL SELECT 'WAIT'  , '20250731110000' FROM dual

UNION ALL SELECT 'INPROC', '20250731100000' FROM dual

UNION ALL SELECT 'INPROC', '20250731090000' FROM dual

UNION ALL SELECT 'LOAD'  , '20250731080000' FROM dual

UNION ALL SELECT 'LOAD'  , '20250731070000' FROM dual

UNION ALL SELECT 'WAIT'  , '20250731060000' FROM dual

UNION ALL SELECT 'WAIT'  , '20250731050000' FROM dual

UNION ALL SELECT 'WAIT'  , '20250731040000' FROM dual

UNION ALL SELECT 'CHANGE', '20250731030000' FROM dual

UNION ALL SELECT 'CHANGE', '20250731020000' FROM dual

UNION ALL SELECT 'WAIT'  , '20250731010000' FROM dual

)

SELECT class

     , MIN(event_tm) event_tm

     , LAG(MIN(event_tm)) OVER(ORDER BY MIN(event_tm) DESC) post_event_tm

  FROM (SELECT class, event_tm

             , ROW_NUMBER() OVER(ORDER BY event_tm) rn1

             , ROW_NUMBER() OVER(PARTITION BY class ORDER BY event_tm) rn2

          FROM t

        )

 GROUP BY class, rn1 - rn2

;

마농(manon94)님이 2025-08-01 11:12에 작성한 댓글입니다.

No.	제목	작성자	작성일	조회
42012	Oracle 왜 그리 비쌀까요?	세벌	2026-02-27	436
42011	쿼리 질문좀 드리겠습니다.ㅠㅠ [4]	장선각	2025-09-15	1668
42010	쿼리 질문 드립니다. [1]	CK	2025-07-31	1705
42009	Full scan Filter [1]	ohyouknow	2025-07-25	1696
42006	insert 속도 vs update 속도 [1]	워즈	2025-03-13	2371
42005	테이블의 데이터가 많아서 주기적 초기화 하는 방법?	작성자	2025-03-05	2172

All about the DATABASE... Copyleft 1999-2025 DSN, All rights reserved.

작업시간: 0.018초, 이곳 서비스는
PostgreSQL v18.1로 자료를 관리합니다