꿈꾸는 개발자, DBA 커뮤니티 구루비

오라클 쿼리 질문드립니다!

by Viper [SQL Query] 오라클 거래일시 단위 [2022.01.17 20:49:51]

20220117_203936.jpg (3,360,864Bytes)

DISTINCT한 계좌번호와 거래일련번호만 갖고,

아래 사진과 같이

일련번호1 처럼 30분 단위로 먼저 그룹핑하고

그 그룹내에서 순위를 오름차순으로 매겨주고싶습니다 ㅠ

고수님들 답변 부탁드립니다. 감사합니다 ㅠ

by 마농 [2022.01.18 09:23:03]

WITH t AS
(
SELECT '10057' no, TO_DATE('2021-10-18 09:13:16', 'yyyy-mm-dd hh24:mi:ss') dt FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 09:28:27', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 09:52:38', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 09:55:22', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 09:57:54', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 09:59:47', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 10:01:35', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 10:03:16', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 10:05:12', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 10:08:27', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 10:12:31', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 10:27:50', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 11:05:49', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 11:14:22', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 12:46:51', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 12:48:55', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 12:50:57', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 13:43:42', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 14:25:33', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 14:25:48', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 15:18:26', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 15:19:52', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 15:20:31', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 15:25:44', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 15:26:50', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 15:28:54', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 15:30:46', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 15:37:27', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 15:44:47', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 15:59:17', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 21:27:45', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 21:30:05', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 21:32:53', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 21:55:26', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 21:57:51', 'yyyy-mm-dd hh24:mi:ss') FROM dual
UNION ALL SELECT '10057', TO_DATE('2021-10-18 22:00:47', 'yyyy-mm-dd hh24:mi:ss') FROM dual
)
, t1 AS
(
SELECT no, dt
     , ROW_NUMBER() OVER(PARTITION BY no ORDER BY dt) rn
  FROM t
)
, t2 (no, dt, rn, seq1, seq2, dt_1) AS
(
SELECT no
     , dt
     , rn
     , 1 seq1
     , 1 seq2
     , dt dt_1
  FROM t1
 WHERE rn = 1
 UNION ALL
SELECT b.no
     , b.dt
     , b.rn
     , CASE WHEN b.dt - a.dt_1 <= 1/24/60 * 30 THEN a.seq1     ELSE a.seq1 + 1 END seq1
     , CASE WHEN b.dt - a.dt_1 <= 1/24/60 * 30 THEN a.seq2 + 1 ELSE 1          END seq2
     , CASE WHEN b.dt - a.dt_1 <= 1/24/60 * 30 THEN a.dt_1     ELSE b.dt       END dt_1
  FROM t2 a
     , t1 b
 WHERE b.no = a.no
   AND b.rn = a.rn + 1
)
SELECT no
     , dt
     , seq1
     , seq2
  FROM t2
;

by Viper [2022.01.18 10:58:16]

우와 t2 부분이 대박이네요!

마농님 대단히 감사합니다!

그런데 제가 아직 경험이 많지 않은데요 ㅎ

이 정도 로직은 난이도를 상,중,하를 따지자면

어느 정도 될까요?

by 마농 [2022.01.18 13:06:08]

SELECT no
     , dt
     , DENSE_RANK() OVER(PARTITION BY no       ORDER BY dt_1) seq1
     , ROW_NUMBER() OVER(PARTITION BY no, dt_1 ORDER BY dt  ) seq2
  FROM (SELECT *
          FROM t
         MODEL
         PARTITION BY (no)
         DIMENSION BY (ROW_NUMBER() OVER(PARTITION BY no ORDER BY dt) rn)
         MEASURES (dt, dt dt_1)
         RULES (dt_1[rn > 1] = CASE WHEN dt[cv()] - dt_1[cv()-1] <= 1/24/60 * 30
                                    THEN dt_1[cv()-1] ELSE dt[cv()] END)
        )
;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변