데이터베이스 질문과 답변

MSSQL 쿼리 질문합니다. 0 2 826

by 라슈 [SQLServer] [2016.09.22 18:32:42]

id strat_time end_time
aaaa 2016-08-02 01:24:14 2016-08-02 01:25:59
aaaa 2016-08-02 15:01:13 2016-08-02 17:16:44
aaaa 2016-08-02 20:10:50 2016-08-03 01:34:17
bbbb 2016-08-01 14:15:28 2016-08-01 15:39:03
bbbb 2016-08-01 23:47:46 2016-08-02 00:22:26
cccc 2016-08-01 15:11:33 2016-08-02 01:01:37
cccc 2016-08-02 19:18:08 2016-08-03 00:12:32
dddd 2016-08-02 14:11:37 2016-08-02 14:39:12
eeee 2016-08-01 14:00:33 2016-08-01 19:26:20

 

WITH t AS
(
select 'eeee' as id, '2016-08-01 14:00:33' as sdm, '2016-08-01 19:26:20' as edm
union all
select 'bbbb' as id, '2016-08-01 14:15:28' as sdm, '2016-08-01 15:39:04' as edm
union all
select 'cccc' as id, '2016-08-01 15:11:34' as sdm, '2016-08-02 01:01:37' as edm
union all
select 'bbbb' as id, '2016-08-01 23:47:47' as sdm, '2016-08-02 00:22:27' as edm
union all
select 'aaaa' as id, '2016-08-02 01:24:14' as sdm, '2016-08-02 01:25:59' as edm
union all
select 'dddd' as id, '2016-08-02 14:11:38' as sdm, '2016-08-02 14:39:12' as edm
union all
select 'aaaa' as id, '2016-08-02 15:01:13' as sdm, '2016-08-02 17:16:44' as edm
union all
select 'cccc' as id, '2016-08-02 19:18:09' as sdm, '2016-08-03 00:12:33' as edm
union all
select 'aaaa' as id, '2016-08-02 20:10:51' as sdm, '2016-08-03 01:34:17' as edm
)

 

위와 같은 데이터가 있을때, 검색 조건을 2016-08-01에서 2016-08-02를 주었을때

결과값이

시간 이용자수 시작인원수 종료 인원수
14:00~14:59 3 3 1
15:00~15:59 4 2 1
16:00~16:59 3 0 0
17:00~17:59 3 0 1
18:00~18:59 2 0 0
19:00~19:59 3 1 1
20:00~20:59 3 1 0
21:00~21:59 3 0 0
22:00~22:59 3 0 0
23:00~23:59 4 1 0
24:00~24:59 4 0 2
25:00~25:59 3 1 3

이와 같이 나와야 합니다.

종료시간은 다음날 새벽 2시 까지 입니다.

이용자수는 그시간에 있는 사람들의 수이고, 시작인원수는 해당시간에 시작한 사람, 종료인원수는 해당시간에 종료한 사람 수 입니다.

 

고수님들의 답변 부탁드립니다.

잘 부탁 드립니다.

 

 

 

 

by jkson [2016.09.23 13:06:00] 
WITH T AS
(
SELECT 'eeee' AS ID, TO_DATE('2016-08-01 14:00:33','yyyy-mm-dd hh24:mi:ss') AS SDM, TO_DATE('2016-08-01 19:26:20','yyyy-mm-dd hh24:mi:ss') AS EDM FROM DUAL UNION ALL
SELECT 'bbbb' AS ID, TO_DATE('2016-08-01 14:15:28','yyyy-mm-dd hh24:mi:ss') AS SDM, TO_DATE('2016-08-01 15:39:04','yyyy-mm-dd hh24:mi:ss') AS EDM FROM DUAL UNION ALL
SELECT 'cccc' AS ID, TO_DATE('2016-08-01 15:11:34','yyyy-mm-dd hh24:mi:ss') AS SDM, TO_DATE('2016-08-02 01:01:37','yyyy-mm-dd hh24:mi:ss') AS EDM FROM DUAL UNION ALL
SELECT 'bbbb' AS ID, TO_DATE('2016-08-01 23:47:47','yyyy-mm-dd hh24:mi:ss') AS SDM, TO_DATE('2016-08-02 00:22:27','yyyy-mm-dd hh24:mi:ss') AS EDM FROM DUAL UNION ALL
SELECT 'aaaa' AS ID, TO_DATE('2016-08-02 01:24:14','yyyy-mm-dd hh24:mi:ss') AS SDM, TO_DATE('2016-08-02 01:25:59','yyyy-mm-dd hh24:mi:ss') AS EDM FROM DUAL UNION ALL
SELECT 'dddd' AS ID, TO_DATE('2016-08-02 14:11:38','yyyy-mm-dd hh24:mi:ss') AS SDM, TO_DATE('2016-08-02 14:39:12','yyyy-mm-dd hh24:mi:ss') AS EDM FROM DUAL UNION ALL
SELECT 'aaaa' AS ID, TO_DATE('2016-08-02 15:01:13','yyyy-mm-dd hh24:mi:ss') AS SDM, TO_DATE('2016-08-02 17:16:44','yyyy-mm-dd hh24:mi:ss') AS EDM FROM DUAL UNION ALL
SELECT 'cccc' AS ID, TO_DATE('2016-08-02 19:18:09','yyyy-mm-dd hh24:mi:ss') AS SDM, TO_DATE('2016-08-03 00:12:33','yyyy-mm-dd hh24:mi:ss') AS EDM FROM DUAL UNION ALL
SELECT 'aaaa' AS ID, TO_DATE('2016-08-02 20:10:51','yyyy-mm-dd hh24:mi:ss') AS SDM, TO_DATE('2016-08-03 01:34:17','yyyy-mm-dd hh24:mi:ss') AS EDM FROM DUAL
)
SELECT TO_CHAR(SDT,'hh24') SDT, TO_CHAR(EDT,'hh24') EDT
      ,SUM(CASE WHEN EDM >= SDT AND SDM < EDT THEN 1 ELSE 0 END) USERCNT
      ,SUM(CASE WHEN SDM >= SDT AND SDM < EDT THEN 1 ELSE 0 END) SCNT
      ,SUM(CASE WHEN EDM >= SDT AND EDM < EDT THEN 1 ELSE 0 END) ECNT
 FROM
(
SELECT * 
  FROM T
 WHERE EDM < TO_DATE(:EDM,'yyyy-mm-dd') + 26/24
   AND SDM >= TO_DATE(:SDM,'yyyy-mm-dd') + 14/24
) A
,
(
SELECT * 
  FROM
    (
        SELECT TO_DATE(:SDM,'yyyy-mm-dd') + 14/24 + (LEVEL - 1) /24 SDT
             , TO_DATE(:SDM,'yyyy-mm-dd') + 14/24 + (LEVEL) /24 EDT
             , LEVEL LV
          FROM DUAL   
         WHERE TO_DATE(:SDM,'yyyy-mm-dd') + 14/24 + (LEVEL) /24 <= TO_DATE(:EDT,'yyyy-mm-dd') +26/24
        CONNECT BY LEVEL < 1000
    ) 
 WHERE TO_CHAR(SDT,'hh24') BETWEEN '14' AND '23' OR  TO_CHAR(SDT,'hh24') BETWEEN '00' AND '01'
) B
WHERE EDM >= SDT AND SDM < EDT
GROUP BY TO_CHAR(SDT,'hh24'), TO_CHAR(EDT,'hh24')
ORDER BY DECODE(SDT,'00','24','01','25',SDT)

SQL SERVER 환경이 아니라.. 오라클에서 만들어봤습니다. 참고하시면 되겠네요.

by 마농 [2016.09.26 11:44:43] 
WITH data_t AS
(
SELECT 'aaaa' id, '2016-08-02 01:24:14' start_time, '2016-08-02 01:25:59' end_time
UNION ALL SELECT 'aaaa', '2016-08-02 15:01:13', '2016-08-02 17:16:44'
UNION ALL SELECT 'aaaa', '2016-08-02 20:10:50', '2016-08-03 01:34:17'
UNION ALL SELECT 'bbbb', '2016-08-01 14:15:28', '2016-08-01 15:39:03'
UNION ALL SELECT 'bbbb', '2016-08-01 23:47:46', '2016-08-02 00:22:26'
UNION ALL SELECT 'cccc', '2016-08-01 15:11:33', '2016-08-02 01:01:37'
UNION ALL SELECT 'cccc', '2016-08-02 19:18:08', '2016-08-03 00:12:32'
UNION ALL SELECT 'dddd', '2016-08-02 14:11:37', '2016-08-02 14:39:12'
UNION ALL SELECT 'eeee', '2016-08-01 14:00:33', '2016-08-01 19:26:20'
)
, code_t AS
( -- 조회 기간 설정 --
SELECT CAST('02' AS VARCHAR(2)) gb
     , CAST('2016-08-01 02:00:00' AS DATETIME) stm
     , CAST('2016-08-01 02:59:59' AS DATETIME) etm
 UNION ALL
SELECT CAST(RIGHT(CONCAT('0', gb+1), 2) AS VARCHAR(2)) gb
     , DATEADD(hour, 1, stm)
     , DATEADD(hour, 1, etm)
  FROM code_t
 WHERE gb <= 24
)
SELECT CONCAT(gb, ':00~', gb, ':59') gb
     , COUNT(DISTINCT id) cnt
     , COUNT(DISTINCT CASE WHEN start_time BETWEEN stm AND etm THEN id END) cnt1
     , COUNT(DISTINCT CASE WHEN   end_time BETWEEN stm AND etm THEN id END) cnt2
  FROM code_t a
 INNER JOIN data_t b
--  LEFT OUTER JOIN data_t b
    ON b.start_time <= a.etm
   AND b.end_time   >= a.stm
 GROUP BY gb
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입