안녕하세요
두 컬럼의 시간사이 데이터들의 카운트를 구하려하는데요
테이블 a_table / b_table
컬럼1 sta_time
컬럼2 end_time
데이터타입은 varchar2 입니다
예를들어 시작시간 00시 00분
종료시간은 00시01분01초 , 00시02분01초, 00시03분01초
이런식으로 로우데이터가있습니다
00시00분부터 00시10분사이에 1분단위로 종료되지 않은 데이터를 카운트하고싶은데
ex) 00시00분 3건
00시01분 3건
00시02분 2건
이런식으로 카운트를 해야하는데 어찌해야할지 감이 잡히질않습니다 도움좀 부탁드리겠습니다
사진은 편의를 위해 00초 시작은 sta_time은 갯수에 포함 / end_time은 종료된 것으로 보았습니다
WITH a_table AS ( SELECT 'a' user_id, '20240624000000' sta_time FROM dual UNION ALL SELECT 'b', '20240624000200' FROM dual UNION ALL SELECT 'c', '20240624000300' FROM dual UNION ALL SELECT 'd', '20240624000300' FROM dual UNION ALL SELECT 'e', '20240624000400' FROM dual UNION ALL SELECT 'f', '20240624000400' FROM dual UNION ALL SELECT 'g', '20240624000400' FROM dual UNION ALL SELECT 'h', '20240624000600' FROM dual UNION ALL SELECT 'i', '20240624000800' FROM dual UNION ALL SELECT 'j', '20240624000800' FROM dual ) , b_table AS ( SELECT 'a' user_id, '20240624000300' end_time FROM dual UNION ALL SELECT 'b', '20240624000300' FROM dual UNION ALL SELECT 'c', '20240624000500' FROM dual UNION ALL SELECT 'd', '20240624000800' FROM dual UNION ALL SELECT 'e', '20240624000800' FROM dual UNION ALL SELECT 'f', '20240624000800' FROM dual UNION ALL SELECT 'g', '20240624000900' FROM dual UNION ALL SELECT 'h', '20240624001100' FROM dual UNION ALL SELECT 'i', '20240624001300' FROM dual UNION ALL SELECT 'j', '20240624001300' FROM dual ) , search_condition AS ( SELECT TO_CHAR(stm + (LEVEL - 1) /24/60, 'yyyymmddhh24miss') stm , TO_CHAR(stm + (LEVEL - 0) /24/60, 'yyyymmddhh24miss') etm FROM (SELECT TO_DATE('20240624000000', 'yyyymmddhh24miss') stm , TO_DATE('20240624001000', 'yyyymmddhh24miss') etm FROM dual ) CONNECT BY LEVEL <= ROUND((etm - stm) * 24 * 60) ) SELECT c.stm , COUNT(a.user_id) cnt FROM search_condition c LEFT OUTER JOIN b_table b ON b.end_time > c.stm LEFT OUTER JOIN a_table a ON a.sta_time < c.etm AND a.user_id = b.user_id GROUP BY c.stm ORDER BY c.stm ;