데이터베이스 질문과 답변

join문 질문입니다 0 2 1,161

by 조새롬 [SQL Query] [2014.02.24 14:18:00] 

SELECT REPLACE(LPAD(b.lv,2, '0'), '24', '00') AS PV_DATE,NVL(PV_COUNT,0) AS PV_COUNT, SPEC_DATE, ID_SITE
   FROM(  
   SELECT COUNT(*) AS PV_COUNT, TO_CHAR ( a.action_time, 'HH24' ) AS PV_DATE, TO_CHAR(a.action_time, 'YY/MM/DD') AS SPEC_DATE, ID_SITE
   FROM ci_visitor_actions a
   WHERE a.action_time BETWEEN TO_DATE ('2014/02/20' , 'YYYY/MM/DD' )
   AND TO_DATE ( '2014/02/20' , 'YYYY/MM/DD' ) + 0.99999   
   GROUP BY TO_CHAR ( a.action_time, 'HH24' ),TO_CHAR ( a.action_time, 'YY/MM/DD' ), ID_SITE  
   )a
   ,(SELECT LEVEL lv FROM DUAL CONNECT BY 24 >= LEVEL
   )b
   WHERE a.PV_DATE (+) = B.lv
   ORDER BY PV_DATE,ID_SITE


쿼리를 조회하면 
PV_DATE ,  PV_COUNT  , SPEC_DATE,  ID_SITE
00 0 (null) (null)
01 0 (null) (null)
02 0 (null) (null)
03 0 (null) (null)
04 0 (null) (null)
05 0 (null) (null)
06 0 (null) (null)
07 0 (null) (null)
08 0 (null) (null)
09 13 14/02/19 2
09 9 14/02/20 2
10 19 14/02/19 2
11 3 14/02/20 1
11 45 14/02/20 2
12 22 14/02/20 2
12 1 14/02/19 2
13 2 14/02/20 2
13 6 14/02/19 2
14 14 14/02/20 2
14 8 14/02/19 2
15 7 14/02/20 2
15 18 14/02/19 2
16 5 14/02/19 2
16 4 14/02/20 2
17 9 14/02/20 1
17 2 14/02/20 2
17 28 14/02/19 2
18 2 14/02/19 1
18 11 14/02/19 2
18 7 14/02/20 2
19 4 14/02/20 1
19 10 14/02/20 2
19 8 14/02/19 2
20 0 (null) (null)
21 0 (null) (null)

이렇게 결과가 나옵니다

제가 원하는 결과는 


해당 날짜에 00~23까지 모두 나오고
(14/02/19 = 00~23)
(14/02/20 = 00~23)

그리고 또 ID_SITE 가 1,2가 있는데
그렇다면 
해당날짜에 00~23까지 1,2 모두 나오는 것입니다.

(14/02/19 = 00~23) = 1
(14/02/19 = 00~23) = 2
(14/02/20 = 00~23) = 1
(14/02/20 = 00~23) = 2


PV_DATE ,  PV_COUNT  , SPEC_DATE,  ID_SITE
00 0 14/02/20 1
00 0 14/02/20 201 0 14/02/20 1
01 0 14/02/20 2
02 0 14/02/20 1
02 0 14/02/20 2
03 0 14/02/20 1
03 0 14/02/20 2
04 0 14/02/20 1
04 0 14/02/20 2
05 0 14/02/20 1
05 0 14/02/20 2
06 0 14/02/20 1
06 0 14/02/20 2
07 0 14/02/20 1
07 0 14/02/20 2
08 0 14/02/20 1
08 0 14/02/20 2
09 0 14/02/20 1
09 9 14/02/20 2
10 0 14/02/20 1
10 0 14/02/20 2
11 3 14/02/20 1
11 45 14/02/20 2
12 0 14/02/20 1
12 22 14/02/20 2
13 0 14/02/20 1
13 2 14/02/20 2
14 0 14/02/20 1
14 14 14/02/20 2
15 0 14/02/20 1
15 7 14/02/20 2
16 0 14/02/20 1
16 4 14/02/20 2
17 9 14/02/20 1
17 2 14/02/20 2
18 0 14/02/20 1
18 7 14/02/20 2
19 4 14/02/20 1
19 10 14/02/20 2
20 0 14/02/20 1
20 0 14/02/20 2
21 0 14/02/20 1
21 0 14/02/20 2
22 0 14/02/20 1
22 0 14/02/20 2
23 0 14/02/20 1
23 0 14/02/20 2

00 0 14/02/19 1
00 0 14/02/19 201 0 14/02/19 1
01 0 14/02/19 2
02 0 14/02/19 1
02 0 14/02/19 2
03 0 14/02/19 1
03 0 14/02/19 2
04 0 14/02/19 1
04 0 14/02/19 2
05 0 14/02/19 1
05 0 14/02/19 2
06 0 14/02/19 1
06 0 14/02/19 2
07 0 14/02/19 1
07 0 14/02/19 2
08 0 14/02/19 1
08 0 14/02/19 2
09 0 14/02/19 1
09 9 14/02/19 2
10 0 14/02/19 1
10 0 14/02/19 2
11 3 14/02/19 1
11 45 14/02/19 2
12 0 14/02/19 1
12 22 14/02/19 2
13 0 14/02/19 1
13 2 14/02/19 2
14 0 14/02/19 1
14 14 14/02/19 2
15 0 14/02/19 1
15 7 14/02/19 2
16 0 14/02/19 1
16 4 14/02/19 2
17 9 14/02/19 1
17 2 14/02/19 2
18 0 14/02/19 1
18 7 14/02/19 2
19 4 14/02/19 1
19 10 14/02/19 2
20 0 14/02/19 1
20 0 14/02/19 2
21 0 14/02/19 1
21 0 14/02/19 2
22 0 14/02/19 1
22 0 14/02/19 2
23 0 14/02/19 1
23 0 14/02/19 2


질문이 조금 헷갈립니다.
제가 조회하는 모든 결과는 null없이 ID_SITE별 날짜별로 24시간 모두나오게 하고싶은 것입니다.

도와주세요 ㅠ 
by 마농 [2014.02.24 15:45:24] 
-- 1~24 로 조인 후 24만 0으로 바꾸는 것은 0시 자료를 누락시키는 오류입니다.
-- 처음부터 0~23 으로 조인해야죠.
-- 파티션아우터조인을 이용하세요.
SELECT b.lv AS pv_date
     , NVL(pv_count, 0) AS pv_count
     , spec_date
     , id_site
  FROM (SELECT LPAD(LEVEL - 1, 2, '0') lv FROM DUAL CONNECT BY LEVEL <= 24) b
  LEFT OUTER JOIN
       (SELECT id_site
             , TO_CHAR(a.action_time, 'yy/mm/dd') AS spec_date
             , TO_CHAR(a.action_time, 'hh24') AS pv_date
             , COUNT(*) AS pv_count
          FROM ci_visitor_actions a
         WHERE a.action_time BETWEEN TO_DATE('2014/02/20', 'yyyy/mm/dd')
                                 AND TO_DATE('2014/02/20', 'yyyy/mm/dd') + 0.99999
         GROUP BY id_site
             , TO_CHAR(a.action_time, 'yy/mm/dd')
             , TO_CHAR(a.action_time, 'hh24')
        ) a
 PARTITION BY (a.id_site, a.spec_date)
    ON a.pv_date = b.lv
 ORDER BY spec_date DESC, pv_date, id_site
;
by 조새롬 [2014.02.24 17:17:20]

정말 대단하십니다
존경스럽네요
너무 감사합니다^^
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입