안녕하세요.
이용건수 대비 평균 이용시간을 구해야 하는데요.
어떻게 해야하는지 도와주세요. ^^;;
테이블
: A1_APNO VARCHAR(4) 고객번호
: A1_INDA VARCHAR(8) 입장일자
: A1_OUDA VARCHAR(8) 퇴장일자
: A1_INTI VARCHAR(6) 입장시간
: A1_OUTI VARCHAR(6) 퇴장시간
: A1_USTI VARCHAR(6) 이용시간
CREATE TABLE A1 ( A1_APNO VARCHAR(4) , A1_INDA VARCHAR(8) , A1_OUDA VARCHAR(8) , A1_INTI VARCHAR(6) , A1_OUTI VARCHAR(6) , A1_USTI VARCHAR(6) )
INSERT INTO A1_INFO VALUES ( 'M1' , '20120102' , '20120102' , '100101' , '120101' , '020101' )
INSERT INTO A1_INFO VALUES ( 'M3' , '20120102' , '20120102' , '120101' , '140101' , '020101' )
INSERT INTO A1_INFO VALUES ( 'M2' , '20120102' , '20120102' , '140101' , '160101' , '020101' )
INSERT INTO A1_INFO VALUES ( 'M2' , '20120103' , '20120103' , '100101' , '120101' , '020101' )
INSERT INTO A1_INFO VALUES ( 'M1' , '20120103' , '20120103' , '110101' , '140101' , '030101' )
결과
구분 일자 이용객 평균이용시간
1월 2일 3 020101
1월 3일 2 023001
. . . .
. . . .
평균이용시간의 형식은 상관없습니다.
02:10:20 또는 02시10분20초 등... 관계없구요...
Comment 10
-
건우아빠
2013.07.10 18:17
-
아톰
2013.07.11 09:42
MS-SQL 2000 이라서 그런것인지는 모르겠지만...
주신쿼리로 실행해 본 결과입니다.
메시지 156, 수준 15, 상태 1, 줄 1
'WITH' 키워드 근처의 구문이 잘못되었습니다.이렇게 "쿼리가 완료되었으나 오류가 발생했습니다." 라고 나옵니다.
제가 이렇게 저럭헤 만들어보고, 나머지 산식을 대입해보려고 했으나 잘 안된 쿼리리는 아래와 같습니다.
건우아빠님께서 만들어주신 WITH 의 첫번째 SELECT 결과가 더 간단합니다만 결과가 위에서처럼 나오기에...
도움말을 보고 있습니다만... 잘 안되네요. ^^;;
한번만 더 봐주세요... ^^;;
여쭙는길에 하나만 더 말씀드리면... 가로로 출력도 될까요?
가로결과 보기는... ( 일자별 평균시간만... )
구분 1 2 3 4 ~ ~ ~ ~ ~ ~ ~ 31
2012-01월 02:11:11 02:10:10
-
건우아빠
2013.07.11 10:49
인라인뷰로 처리하시면 됩니다.
SELECT A1_INDA [일자]
, CNT [이용객]
, RIGHT('0'+CONVERT(VARCHAR, SEC / 3600 ),2) + ':'
+ RIGHT('0'+CONVERT(VARCHAR, (SEC % 3600) / 60) ,2) + ':'
+ RIGHT('0'+CONVERT(VARCHAR, (SEC % 3600) % 60) ,2) [평균이용시간]
FROM ( SELECT A1_INDA
, COUNT(*) CNT
, ( SUM(CAST( LEFT(A1_USTI,2) AS INT ) * 3600 )
+ SUM(CAST( SUBSTRING(A1_USTI,3,2) AS INT ) * 60)
+ SUM(CAST( RIGHT(A1_USTI,2) AS INT )) ) / COUNT(*) SEC
FROM #A1_INFO
GROUP BY A1_INDA ) RES -
아톰
2013.07.11 11:32
처음 주신 쿼리로 이렇게 저렇게 해봤는데... (위 쿼리처럼 비슷하게... ^^;;)
잘 안되더니...
건우아빠님께서 주신것 보니 제가 만들어둔 쿼리가 턱없이 모자라네요. ^^;;
감사합니다. ^^
가로로 출력되는건 제 힘으로 함 해볼께요.
맛점 하세요... ^_^
-
건우아빠
2013.07.11 13:50
가로로 출력하는건 2000이시면 프로시저로 하시는게 좋을듯 합니다.
물론 인라인뷰로 해서 한방으로 가능은 하지만 프로시저로 인라인뷰에 해당하는 부분을 임시 테이블로 저장하고
그 임시 테이블을 가지고 처리하시는 방법도 나쁘지는 않습니다.
건수가 별로 없으면 이렇게 하셔도 성능에는 큰차이가 없을듯 하구요.
나중에 수정등이 편할수도 있습니다.
-
아톰
2013.07.11 16:42
건우아빠님께 정말 죄송한데요...
가로로 출력하는것 좀 해주심 안될까요~?
4시간째 헤메고 있네요... ^^;;
인원을 뺀 상태로는 되는데요... 인원을 추가하려니 어렵네요. ^^;;
구분1 구분2 1 2 3 4 ~ ~ ~ ~ ~ ~ ~ 31
2012-01월 평균시간 02:11:11 02:10:10
인원 2 3
2012-02월
2013-03월
-
건우아빠
2013.07.11 18:59
1일~31일 까지 고정적으로 표현하시는 방법이고 동적으로 구현을 하실려면 동적쿼리를 이용하셔야 합니다.
어플단에서 월에 따라 없는 일자를 안보이게 처리하시는게 편할듯 합니다.
, (CASE PIV.GB
WHEN 1 THEN '평균이용시간'
WHEN 2 THEN '이용객'
END) GB
, MAX(CASE WHEN RIGHT( RESULT.[일자] ,2) = '01' THEN
(CASE PIV.GB
WHEN 1 THEN CONVERT(VARCHAR(10) , [평균이용시간] )
WHEN 2 THEN CONVERT(VARCHAR(10) , [이용객] )
END)
END) [01]
, MAX(CASE WHEN RIGHT( RESULT.[일자] ,2) = '31' THEN
(CASE PIV.GB
WHEN 1 THEN CONVERT(VARCHAR(10) , [평균이용시간] )
WHEN 2 THEN CONVERT(VARCHAR(10) , [이용객] )
END )
END) [31]
FROM (
SELECT A1_INDA [일자]
, CNT [이용객]
, RIGHT('0'+CONVERT(VARCHAR, SEC / 3600 ),2) + ':'
+ RIGHT('0'+CONVERT(VARCHAR, (SEC % 3600) / 60) ,2) + ':'
+ RIGHT('0'+CONVERT(VARCHAR, (SEC % 3600) % 60) ,2) [평균이용시간]
FROM ( SELECT A1_INDA
, COUNT(*) CNT
, ( SUM(CAST( LEFT(A1_USTI,2) AS INT ) * 3600 )
+ SUM(CAST( SUBSTRING(A1_USTI,3,2) AS INT ) * 60)
+ SUM(CAST( RIGHT(A1_USTI,2) AS INT )) ) / COUNT(*) SEC
FROM #A1_INFO
GROUP BY A1_INDA ) RES ) AS RESULT
( SELECT 1 GB UNION ALL SELECT 2 GB ) PIV
ORDER BY LEFT( RESULT.[일자] ,6) , PIV.GB
-
아톰
2013.07.16 13:38
만들어주신 쿼리로 업무처리는 마무리 했습니다만...
이해를 했다하더라도 시간지나서 까먹을텐데... ^^;;
업무처리했다고 소홀히 하지 않고 찬찬히 살펴보고 이해하도록 하겠습니다.
바쁘신 와중에도 신경써 주셔서 감사합니다. ^^
-
등빛
2014.07.17 16:29
select Mon,day,visit,
RIGHT('0'+CONVERT(VARCHAR, SEC / 3600 ),2) + ':'
+ RIGHT('0'+CONVERT(VARCHAR, (SEC % 3600) / 60) ,2) + ':'
+ RIGHT('0'+CONVERT(VARCHAR, (SEC % 3600) % 60) ,2) as time
from
(
select
substring (right(a1_inda,3),1,1) as Mon
,substring (right(a1_inda,3),3,1) as day
,count(a1_inda) as visit
,(SUM(CAST( LEFT(A1_USTI,2) AS INT ) * 3600 )
+ SUM(CAST( SUBSTRING(A1_USTI,3,2) AS INT ) * 60)
+ SUM(CAST( RIGHT(A1_USTI,2) AS INT )) ) / COUNT(*) SEC
from A1_INFO
group by A1_INDA
) as b
공부하는겸해서 댓글답니다 -
한태
2016.03.10 16:56
select DATEPART(MONTH, a.A1_INDA) as 구분,
DATEPART(day,a.a1_inda) as 일자,
a.cnt as 이용객,
Convert(varchar(50),dateadd(second,Convert(int,a.t_sum),'00:00'),108) as 평균이용시간
from
(
select CAST(a1_inda as datetime) A1_INDA,
count(*) cnt,
sum(convert(int,SUBSTRING(a1_usti,1,2))*3600+ convert(int,SUBSTRING(a1_usti,3,2))*60+ convert(int,SUBSTRING(a1_usti,5,2)))/COUNT(*) t_sum
from A1_info
group by a1_inda
) a
평균이용시간을 좀 더 간편하게 찾아보려 고민해보았습니다.
시간이 문자형이라
WITH RES
AS (
SELECT A1_INDA
, COUNT(*) CNT
, ( SUM(CAST( LEFT(A1_USTI,2) AS INT ) * 3600 )
+ SUM(CAST( SUBSTRING(A1_USTI,3,2) AS INT ) * 60)
+ SUM(CAST( RIGHT(A1_USTI,2) AS INT )) ) / COUNT(*) SEC
FROM #A1_INFO
GROUP BY A1_INDA
)
SELECT A1_INDA [일자]
, CNT [이용객]
, RIGHT('0'+CONVERT(VARCHAR, SEC / 3600 ),2) + ':'
+ RIGHT('0'+CONVERT(VARCHAR, (SEC % 3600) / 60) ,2) + ':'
+ RIGHT('0'+CONVERT(VARCHAR, (SEC % 3600) % 60) ,2) [평균이용시간]
FROM RES