데이터베이스 질문과 답변

mysql 날짜별 통계 질문입니다 0 3 2,253

by erea [MySQL] 통계 날짜 일별 [2014.12.23 10:26:03]

idx(시퀀스)   id(id)  sdate(시작날짜)  edate(종료날짜)
1                 a           2014.10.01    2014.10.05         
2                 b           2014.10.03    2014.10.10
3                 c           2014.10.01    2014.10.25
4                 d           2014.10.10    2014.10.20

 

이런 데이터 가있다고 했을떄

2014.10월에 예약이 몇명이 가능한지 쿼리로 알고싶을떄 

최대 50건까지만 가능하다고 가정하고

2014.10.01 : 48명 (예약가능)

2014.10.02 : 48명 (예약가능)

2014.10.03 : 47명 (예약가능)

2014.10.04 : 49명 (예약가능)

2014.10.05 : 49명 (예약가능)

~

2014.10.29 : 50명 (예약가능)

2014.10.30 : 50명 (예약가능)

 

이런식으로 데이터가없는날짜까지 표현해서 쿼리로 한번에 월단위로 뽑을수 있을까요?

by 마농 [2014.12.23 11:59:23] 
-- 우선 달력 테이블을 하나 미리 만들어 놓고
-- 다양한 용도로 사용하시는게 좋을 듯 합니다.
SELECT a.dt
     , 50 - COUNT(b.sdate) 잔여수
     , CASE WHEN COUNT(b.sdate) < 50 THEN '예약가능' ELSE '예약완료' END 비고
  FROM (-- 달력 테이블 --
        SELECT '2014.10.01' dt
        UNION ALL SELECT '2014.10.02'
        UNION ALL SELECT '2014.10.03'
        UNION ALL SELECT '2014.10.04'
        UNION ALL SELECT '2014.10.05'
        UNION ALL SELECT '2014.10.06'
        UNION ALL SELECT '2014.10.07'
        UNION ALL SELECT '2014.10.08'
        UNION ALL SELECT '2014.10.09'
        UNION ALL SELECT '2014.10.10'
        UNION ALL SELECT '2014.10.11'
        UNION ALL SELECT '2014.10.12'
        UNION ALL SELECT '2014.10.13'
        UNION ALL SELECT '2014.10.14'
        UNION ALL SELECT '2014.10.15'
        UNION ALL SELECT '2014.10.16'
        UNION ALL SELECT '2014.10.17'
        UNION ALL SELECT '2014.10.18'
        UNION ALL SELECT '2014.10.19'
        UNION ALL SELECT '2014.10.20'
        UNION ALL SELECT '2014.10.21'
        UNION ALL SELECT '2014.10.22'
        UNION ALL SELECT '2014.10.23'
        UNION ALL SELECT '2014.10.24'
        UNION ALL SELECT '2014.10.25'
        UNION ALL SELECT '2014.10.26'
        UNION ALL SELECT '2014.10.27'
        UNION ALL SELECT '2014.10.28'
        UNION ALL SELECT '2014.10.29'
        UNION ALL SELECT '2014.10.30'
        UNION ALL SELECT '2014.10.31'
        ) a
  LEFT OUTER JOIN
       (-- 예약 테이블 --
        SELECT 1 idx, 'a' id, '2014.10.01' sdate, '2014.10.05' edate
        UNION ALL SELECT 2, 'b', '2014.10.03', '2014.10.10'
        UNION ALL SELECT 3, 'c', '2014.10.01', '2014.10.25'
        UNION ALL SELECT 4, 'd', '2014.10.10', '2014.10.20'
        ) b
    ON a.dt BETWEEN b.sdate AND b.edate
 WHERE a.dt LIKE '2014.10%'
 GROUP BY a.dt
;

 

by 신이만든짝퉁 [2014.12.23 14:00:58]

앗, MYSQL인데다가 이미 마농님께서 답글을 다셨네요.

저는 오라클로 구현해 보았습니다. ^^;

WITH T1 AS
     (SELECT '20141001' ST, '20141005' ED
        FROM DUAL
      UNION ALL
      SELECT '20141003' ST, '20141010' ED
        FROM DUAL
      UNION ALL
      SELECT '20141001' ST, '20141025' ED
        FROM DUAL
      UNION ALL
      SELECT '20141010' ST, '20141020' ED
        FROM DUAL)
   , T2 AS
     (SELECT     TO_CHAR(LEVEL + TO_DATE('201410', 'YYYYMM') - 1, 'YYYYMMDD') YMD
            FROM DUAL
      CONNECT BY LEVEL <= TO_DATE('201411', 'YYYYMM') - TO_DATE('201410', 'YYYYMM'))
SELECT   T2.YMD, 50 - SUM(NVL2(T1.ST, 1, 0)) CNT
    FROM T1, T2
   WHERE T2.YMD BETWEEN T1.ST(+) AND T1.ED(+)
GROUP BY T2.YMD
ORDER BY T2.YMD;

 

by erea [2014.12.23 16:05:54]

다들 감사합니다

한수배우고 갑니다~

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