database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 37839 게시물 읽기
No. 37839
TimeStamp 의 group by..
작성자
이영찬(leeyc09)
작성일
2010-08-09 14:04
조회수
3,675

날짜 별 체류시간을 구하는 쿼리를 짜고 있습니다.

TBLVISITMASTER 테이블에 VarChar2 형식으로 입점,퇴점시간(INTIME,OUTTIME)이

2009-03-11 11:44:39 이와 같은 형식으로 저장 되어있습니다.

SELECT
  t4.LOCATIONGROUPNAME --지역명
  ,t5.USERNAME -- 담당자 명
  ,t6.FRANCHISEFORMNAME --가맹점형태
  ,t7.FRANCHISETYPENAME -- 가맹점종류
  ,t8.CUSTOMERSECTIONNAME -- 가맹점 구분
  ,T9.VISITPERIODNAME
  ,t1.CUSTOMERCODE
  ,t3.CUSTOMERNAME -- 거래처 명
  ,t1.VISITDATE CDATE
  ,to_timestamp(to_char(TO_DATE(t1.OUTTIME, 'YYYY-MM-DD HH24:MI:SS'),'yyyymmddHH24miss'), 'YYYYMMDDHH24MISSFF6')
  - to_timestamp(to_char(TO_DATE(t1.INTIME, 'YYYY-MM-DD HH24:MI:SS'),'yyyymmddHH24miss'), 'YYYYMMDDHH24MISSFF6')
as WORKTIME
    
  FROM
   TBLVISITMASTER T1
   LEFT JOIN TBLCUSTOMERMASTER t3 ON ( t1.CUSTOMERCODE = t3.CUSTOMERCODE)
   LEFT JOIN TBLLOCATIONGROUPCODE t4 ON ( t3.LOCATIONGROUPCODE = t4.LOCATIONGROUPCODE)
   LEFT JOIN TBLUSERMASTER t5 ON ( t1.USERID = t5.USERID )
   LEFT JOIN TBLFRANCHISEFORMCODE t6 ON ( t3.FRANCHISEFORMCODE = t6.FRANCHISEFORMCODE)
   LEFT JOIN TBLFRANCHISETYPECODE t7 ON ( t3.FRANCHISETYPECODE = t7.FRANCHISETYPECODE)
   LEFT JOIN TBLCUSTOMERSECTIONCODE  t8 ON ( t3.CUSTOMERSECTIONCODE = t8.CUSTOMERSECTIONCODE )
   LEFT JOIN TBLVISITPERIODCODE  T9 ON ( T3.VISITPERIOD = T9.VISITPERIODCODE )
  WHERE
   VISITDATE BETWEEN '2010-02-19' AND '2010-02-21'
  GROUP BY
   t4.LOCATIONGROUPNAME --지역명
   ,t5.USERNAME -- 담당자 명
   ,t6.FRANCHISEFORMNAME --가맹점형태
   ,t7.FRANCHISETYPENAME -- 가맹점종류
   ,t8.CUSTOMERSECTIONNAME -- 가맹점 구분
   ,T9.VISITPERIODNAME
   ,t1.CUSTOMERCODE
   ,t3.CUSTOMERNAME -- 거래처 명
   ,t1.VISITDATE
   ,t1.USERID
   ,t1.OUTTIME
   ,t1.inTIME

위와 같은 쿼리로

거래처 및 일자별 체류 시간을 구했습니다.그런데 한 거래처를 같은 날 두번 반복하는 것 때문에 WORKTIME을 거래처 별로 더해줘야하는데

TimeStamp 형식 더하는 법을 모르겠습니다.

,SUM(NVL(LPAD(EXTRACT(DAY FROM WORKTIME)*24 + EXTRACT(HOUR FROM WORKTIME),2,0),'00'))       
  ||':'|| SUM(NVL(LPAD(EXTRACT(MINUTE FROM WORKTIME),2,0),'00'))             
  ||':'|| SUM(NVL(LPAD(EXTRACT(SECOND FROM WORKTIME),2,0),'00'))  AS WORKTIME

무식한 방법으로 이런식으로 추출했더니 72분 116초 라는 결과가 나와버리네요.

timestamp 나 date 형식을 sum 할 수 있는 방법이 있나요??

 

전체 쿼리 입니다.

Select
 LOCATIONGROUPNAME --지역명
 ,USERNAME -- 담당자 명
 ,FRANCHISEFORMNAME --가맹점형태
 ,FRANCHISETYPENAME -- 가맹점종류
 ,CUSTOMERSECTIONNAME -- 가맹점 구분
 ,VISITPERIODNAME
 ,CUSTOMERCODE
 ,CUSTOMERNAME -- 거래처 명
 ,NVL(MAX( DECODE( CDATE, '2010-02-19', WORKTIME )),'00:00:00') "2010-02-19"
 ,NVL( MAX( DECODE( CDATE, '2010-02-20', WORKTIME )),'00:00:00') "2010-02-20"
 ,NVL( MAX( DECODE( CDATE, '2010-02-21', WORKTIME )),'00:00:00') "2010-02-21"
 ,cnt
FROM (
 SELECT
  LOCATIONGROUPNAME --지역명
  ,USERNAME -- 담당자 명
  ,FRANCHISEFORMNAME --가맹점형태
  ,FRANCHISETYPENAME -- 가맹점종류
  ,CUSTOMERSECTIONNAME -- 가맹점 구분
  ,VISITPERIODNAME
  ,CUSTOMERCODE
  ,CUSTOMERNAME -- 거래처 명
  ,CDATE
  ,SUM(NVL(LPAD(EXTRACT(DAY FROM WORKTIME)*24 + EXTRACT(HOUR FROM WORKTIME),2,0),'00'))       
  ||':'|| SUM(NVL(LPAD(EXTRACT(MINUTE FROM WORKTIME),2,0),'00'))             
  ||':'|| SUM(NVL(LPAD(EXTRACT(SECOND FROM WORKTIME),2,0),'00'))  AS WORKTIME
  ,count(WORKTIME) as cnt
  from(
  SELECT
  t4.LOCATIONGROUPNAME --지역명
  ,t5.USERNAME -- 담당자 명
  ,t6.FRANCHISEFORMNAME --가맹점형태
  ,t7.FRANCHISETYPENAME -- 가맹점종류
  ,t8.CUSTOMERSECTIONNAME -- 가맹점 구분
  ,T9.VISITPERIODNAME
  ,t1.CUSTOMERCODE
  ,t3.CUSTOMERNAME -- 거래처 명
  ,t1.VISITDATE CDATE
  ,to_timestamp(to_char(TO_DATE(t1.OUTTIME, 'YYYY-MM-DD HH24:MI:SS'),'yyyymmddHH24miss'), 'YYYYMMDDHH24MISSFF6')
  - to_timestamp(to_char(TO_DATE(t1.INTIME, 'YYYY-MM-DD HH24:MI:SS'),'yyyymmddHH24miss'), 'YYYYMMDDHH24MISSFF6') as WORKTIME
    
  FROM
   TBLVISITMASTER T1
   LEFT JOIN TBLCUSTOMERMASTER t3 ON ( t1.CUSTOMERCODE = t3.CUSTOMERCODE)
   LEFT JOIN TBLLOCATIONGROUPCODE t4 ON ( t3.LOCATIONGROUPCODE = t4.LOCATIONGROUPCODE)
   LEFT JOIN TBLUSERMASTER t5 ON ( t1.USERID = t5.USERID )
   LEFT JOIN TBLFRANCHISEFORMCODE t6 ON ( t3.FRANCHISEFORMCODE = t6.FRANCHISEFORMCODE)
   LEFT JOIN TBLFRANCHISETYPECODE t7 ON ( t3.FRANCHISETYPECODE = t7.FRANCHISETYPECODE)
   LEFT JOIN TBLCUSTOMERSECTIONCODE  t8 ON ( t3.CUSTOMERSECTIONCODE = t8.CUSTOMERSECTIONCODE )
   LEFT JOIN TBLVISITPERIODCODE  T9 ON ( T3.VISITPERIOD = T9.VISITPERIODCODE )
  WHERE
   VISITDATE BETWEEN '2010-02-19' AND '2010-02-21'
  GROUP BY
   t4.LOCATIONGROUPNAME --지역명
   ,t5.USERNAME -- 담당자 명
   ,t6.FRANCHISEFORMNAME --가맹점형태
   ,t7.FRANCHISETYPENAME -- 가맹점종류
   ,t8.CUSTOMERSECTIONNAME -- 가맹점 구분
   ,T9.VISITPERIODNAME
   ,t1.CUSTOMERCODE
   ,t3.CUSTOMERNAME -- 거래처 명
   ,t1.VISITDATE
   ,t1.USERID
   ,t1.OUTTIME
   ,t1.inTIME
  ) 
   group by LOCATIONGROUPNAME,
  USERNAME -- 담당자 명
  ,FRANCHISEFORMNAME --가맹점형태
  ,FRANCHISETYPENAME -- 가맹점종류
  ,CUSTOMERSECTIONNAME -- 가맹점 구분
  ,VISITPERIODNAME
  ,CUSTOMERCODE
  ,CUSTOMERNAME -- 거래처 명
  ,CDATE
 )
GROUP BY
LOCATIONGROUPNAME --지역명
,USERNAME -- 담당자 명
,FRANCHISEFORMNAME --가맹점형태
,FRANCHISETYPENAME -- 가맹점종류
,CUSTOMERSECTIONNAME -- 가맹점 구분
,VISITPERIODNAME
,CUSTOMERCODE
,CUSTOMERNAME -- 거래처 명
,cnt
order by
LOCATIONGROUPNAME,USERNAME,FRANCHISETYPENAME,CUSTOMERCODE

 

이 글에 대한 댓글이 총 1건 있습니다.

date 타입으로 변환하여 차감하면 수치가 나오므로 이를 sum하시면 됩니다.
합계는 하루를 넘지 않을것으로 예상되어 sssss 포멧을 응용했습니다.
24시간 이상 나온다면 조금만 더 응용해 보시면 될듯 합니다.
잘 안되시면 다시 질문하세요.
모든 테이블을 다 조인후 Group by 하게 되면 일량만 많아집니다.
최소한의 테이블로 Group by 하여 집합을 최대한 줄인뒤
나머지 테이블들과 조인하시면 됩니다.


SELECT t4.locationgroupname   -- 지역명
     , t5.username            -- 담당자 명
     , t6.franchiseformname   -- 가맹점형태
     , t7.franchisetypename   -- 가맹점종류
     , t8.customersectionname -- 가맹점구분
     , t9.visitperiodname     --
     , t1.customercode        -- 거래처
     , t3.customername        -- 거래처 명
     , t1.visitdate  cdate    -- 방문일
     , NVL("2010-02-19", '00:00:00') "2010-02-19"
     , NVL("2010-02-20", '00:00:00') "2010-02-20"
     , NVL("2010-02-21", '00:00:00') "2010-02-21"
     , cnt
  FROM (SELECT customercode, userid
             , MIN(DECODE(visitdate,'2010-02-19',tm)) "2010-02-19"
             , MIN(DECODE(visitdate,'2010-02-20',tm)) "2010-02-20"
             , MIN(DECODE(visitdate,'2010-02-21',tm)) "2010-02-21"
             , SUM(cnt) cnt
          FROM (SELECT customercode, userid, visitdate
                     , TO_CHAR(TO_DATE(ROUND(
                       SUM( TO_DATE(outtime, 'yyyy-mm-dd hh24:mi:ss')
                          - TO_DATE( intime, 'yyyy-mm-dd hh24:mi:ss')
                          ) * 24*60*60
                       ), 'sssss'), 'hh24:mi:ss') AS tm
                     , COUNT(*) cnt
                  FROM tblvisitmaster
                 WHERE visitdate BETWEEN '2010-02-19' AND '2010-02-21'
                 GROUP BY customercode, userid, visitdate
                )
         GROUP BY customercode, userid
        ) t1
  LEFT JOIN tblcustomermaster      t3
    ON t1.customercode           = t3.customercode
  LEFT JOIN tbllocationgroupcode   t4
    ON t3.locationgroupcode      = t4.locationgroupcode
  LEFT JOIN tblusermaster          t5
    ON t1.userid                 = t5.userid
  LEFT JOIN tblfranchiseformcode   t6
    ON t3.franchiseformcode      = t6.franchiseformcode
  LEFT JOIN tblfranchisetypecode   t7
    ON t3.franchisetypecode      = t7.franchisetypecode
  LEFT JOIN tblcustomersectioncode t8
    ON t3.customersectioncode    = t8.customersectioncode
  LEFT JOIN tblvisitperiodcode     t9
    ON t3.visitperiod            = t9.visitperiodcode
 ORDER BY locationgroupname, username, franchisetypename, customercode
;

마농(manon94)님이 2010-08-09 15:32에 작성한 댓글입니다.
이 댓글은 2010-08-09 15:44에 마지막으로 수정되었습니다.
[Top]
No.
제목
작성자
작성일
조회
37843프로시져 호출시 가끔 세션이 쌓이면서 딜레이가 생기네요. [3]
개발자A
2010-08-11
2693
37842데이타베이스 표준?? [1]
박종덕
2010-08-11
2366
37840쿼리 질문 드립니다. 도움좀 부탁드리겠습니다. [2]
개발자A
2010-08-09
2885
37839TimeStamp 의 group by.. [1]
이영찬
2010-08-09
3675
37837oracle 학사 DB 연동 [1]
김경원
2010-08-09
2932
37836전에 nc소프트 면접볼때요 db 연결 방식에 대해 묻던데요. [1]
이근호
2010-08-09
3124
37835주민번호 확인시 에러 처리 [2]
임경모
2010-08-06
3666
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.018초, 이곳 서비스는
	PostgreSQL v16.2로 자료를 관리합니다