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 40752 게시물 읽기
No. 40752
원화환산 평잔 퀴즈입니다.
작성자
김흥수(protokhs)
작성일
2015-03-24 02:21
조회수
8,544

 다음과 같은 자료가 있습니다.

with 환율목록 as

(
          select to_date('2015-01-01','yyyy-mm-dd') 기준일자, 1096.56 환율 from dual
union all select to_date('2015-01-02','yyyy-mm-dd') 기준일자, 1050.69 환율 from dual
union all select to_date('2015-01-03','yyyy-mm-dd') 기준일자, 1066.7  환율 from dual
union all select to_date('2015-01-04','yyyy-mm-dd') 기준일자, 1149.34 환율 from dual
union all select to_date('2015-01-05','yyyy-mm-dd') 기준일자, 1106.53 환율 from dual
union all select to_date('2015-01-06','yyyy-mm-dd') 기준일자, 1063.22 환율 from dual
union all select to_date('2015-01-07','yyyy-mm-dd') 기준일자, 1097.55 환율 from dual
union all select to_date('2015-01-08','yyyy-mm-dd') 기준일자, 1102.09 환율 from dual
union all select to_date('2015-01-09','yyyy-mm-dd') 기준일자, 1144.63 환율 from dual
union all select to_date('2015-01-10','yyyy-mm-dd') 기준일자, 1066.87 환율 from dual
union all select to_date('2015-01-11','yyyy-mm-dd') 기준일자, 1123.65 환율 from dual
union all select to_date('2015-01-12','yyyy-mm-dd') 기준일자, 1066.7  환율 from dual
union all select to_date('2015-01-13','yyyy-mm-dd') 기준일자, 1086.19 환율 from dual
union all select to_date('2015-01-14','yyyy-mm-dd') 기준일자, 1073    환율 from dual
union all select to_date('2015-01-15','yyyy-mm-dd') 기준일자, 1117.14 환율 from dual
union all select to_date('2015-01-16','yyyy-mm-dd') 기준일자, 1068.09 환율 from dual
union all select to_date('2015-01-17','yyyy-mm-dd') 기준일자, 1138.49 환율 from dual
union all select to_date('2015-01-18','yyyy-mm-dd') 기준일자, 1083.84 환율 from dual
union all select to_date('2015-01-19','yyyy-mm-dd') 기준일자, 1104.65 환율 from dual
union all select to_date('2015-01-20','yyyy-mm-dd') 기준일자, 1117.41 환율 from dual
union all select to_date('2015-01-21','yyyy-mm-dd') 기준일자, 1089.92 환율 from dual
union all select to_date('2015-01-22','yyyy-mm-dd') 기준일자, 1078.02 환율 from dual
union all select to_date('2015-01-23','yyyy-mm-dd') 기준일자, 1146.27 환율 from dual
union all select to_date('2015-01-24','yyyy-mm-dd') 기준일자, 1117.6  환율 from dual
union all select to_date('2015-01-25','yyyy-mm-dd') 기준일자, 1111.24 환율 from dual
union all select to_date('2015-01-26','yyyy-mm-dd') 기준일자, 1097.71 환율 from dual
union all select to_date('2015-01-27','yyyy-mm-dd') 기준일자, 1133.58 환율 from dual
union all select to_date('2015-01-28','yyyy-mm-dd') 기준일자, 1114.34 환율 from dual
union all select to_date('2015-01-29','yyyy-mm-dd') 기준일자, 1128.46 환율 from dual
union all select to_date('2015-01-30','yyyy-mm-dd') 기준일자, 1131.25 환율 from dual
union all select to_date('2015-01-31','yyyy-mm-dd') 기준일자, 1124.28 환율 from dual
)
,거래기록 as
(
            select to_date('20150105','yyyymmdd') 거래일자 , 120 거래금액 from dual
union all   select to_date('20150108','yyyymmdd') 거래일자 , 400 거래금액 from dual
union all   select to_date('20150122','yyyymmdd') 거래일자 , -500 거래금액 from dual
union all   select to_date('20150124','yyyymmdd') 거래일자 , -20 거래금액 from dual
)
 

1월의 환산평잔을 구하십시오.

환산금액은 거래금액 * 환율로 구하시면 되고

1월 한달간의 잔액의 평균을 구하시면 됩니다.

최대한 연산 횟수를 줄이셔서 최적의 해로 구하시기 바랍니다.(단 거래 기록은 샘플이며 아주 많을 수 있습니다.)

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

어떤 결과가 나와야 하는지?

결과가 나오기 위한 계산절차(실제 수치 적용된)가 어떻게 되는지요?

마농(manon94)님이 2015-03-24 10:04에 작성한 댓글입니다.

 평잔 결과는

 

272114.52258064516129032258065

입니다.

 

 

sum(일자별 잔액 * 환율) / 기간으로 계산합니다.

 
김흥수(protokhs)님이 2015-03-24 16:39에 작성한 댓글입니다.

SELECT AVG(환산잔액) 환산평잔
  FROM (SELECT SUM(NVL(b.거래금액,0)) OVER(ORDER BY a.기준일자) * a.환율 환산잔액
          FROM 환율목록 a
             , 거래기록 b
         WHERE a.기준일자 = b.거래일자(+)
        )
;

마농(manon94)님이 2015-03-24 17:41에 작성한 댓글입니다.
이 댓글은 2015-03-24 17:42에 마지막으로 수정되었습니다.

 문제를 살짝 바꿔서

고객별 평잔으로

거래기록 as
(
            select '1' 고객번호, to_date('20150105','yyyymmdd') 거래일자 , 120 거래금액 from dual
union all   select '1' 고객번호, to_date('20150108','yyyymmdd') 거래일자 , 400 거래금액 from dual
union all   select '1' 고객번호, to_date('20150122','yyyymmdd') 거래일자 , -500 거래금액 from dual
union all   select '1' 고객번호, to_date('20150124','yyyymmdd') 거래일자 , -20 거래금액 from dual
union all   select '2' 고객번호, to_date('20150108','yyyymmdd') 거래일자 , 400 거래금액 from dual
union all   select '2' 고객번호, to_date('20150122','yyyymmdd') 거래일자 , -500 거래금액 from dual
union all   select '2' 고객번호, to_date('20150124','yyyymmdd') 거래일자 , -20 거래금액 from dual
)
 
으로 하고 
원하는 결과는 
 
고객번호 평잔액
1 272114.52258064516129032258065
2 156633.02580645161290322580645
 
이렇게 하고 효율적으로...
김흥수(protokhs)님이 2015-03-24 17:59에 작성한 댓글입니다.

SELECT 고객번호
     , AVG(환산잔액) 환산평잔
  FROM (SELECT 고객번호
             , SUM(NVL(b.거래금액, 0)) OVER(
               PARTITION BY b.고객번호 ORDER BY a.기준일자
               ) * a.환율 AS 환산잔액
          FROM 환율목록 a
          LEFT OUTER JOIN 거래기록 b
          PARTITION BY (b.고객번호)
            ON a.기준일자 = b.거래일자
        )
 GROUP BY 고객번호
 ORDER BY 고객번호
;

마농(manon94)님이 2015-03-24 19:08에 작성한 댓글입니다.

마농님 답변 감사합니다.

 

제가 마농님 방법대로 한번 테스트 해보았습니다.

제 pc에서 다음과 같이 살짝 바꿔서 데이타를 10만 고객으로 뻥튀기 하고 해보았거든요...

,거래기록 as
(
    select
        b.lvl 고객번호
        ,a.거래일자
        ,a.거래금액
    from
        (
                    select '1' 고객번호, to_date('20150105','yyyymmdd') 거래일자 , 120 거래금액 from dual
        union all   select '1' 고객번호, to_date('20150108','yyyymmdd') 거래일자 , 400 거래금액 from dual
        union all   select '1' 고객번호, to_date('20150122','yyyymmdd') 거래일자 , -500 거래금액 from dual
        union all   select '1' 고객번호, to_date('20150124','yyyymmdd') 거래일자 , -20 거래금액 from dual
        ) a
        , (
            select
                level lvl
            from    dual
            connect by level <= 100000
        ) b
)
 
그랬더니
실행전 : 2015-03-25 01:24:19 458000
실행후 : 2015-03-25 01:24:40 070000
 
대략 20초 정도 걸립니다.
 
그런데 이것 보다 2배 정도 빠르게 결과가 나오는 방법이 있습니다.
 기간에 비해 거래 건이 희박할수록 상대적인 성능이 더 좋은 방법입니다.
 
 
김흥수(protokhs)님이 2015-03-25 01:31에 작성한 댓글입니다.
이 댓글은 2015-03-25 01:32에 마지막으로 수정되었습니다.

-- 제 PC 에선 9.2 초 걸리네요.
-- 다음 쿼리로 변경 후 3.8 초
SELECT a.고객번호
     , SUM(a.잔액 * b.환율) / 31 AS 환산잔액
  FROM (SELECT 고객번호
             , 거래일자
             , LEAD(거래일자-1, 1, TO_DATE('20150131', 'yyyymmdd'))
               OVER(PARTITION BY 고객번호 ORDER BY 거래일자) 종료일자
             , SUM(거래금액) OVER(PARTITION BY 고객번호 ORDER BY 거래일자) 잔액
          FROM 거래기록
        ) a
     , 환율목록 b
 WHERE b.기준일자 BETWEEN a.거래일자 AND a.종료일자
   AND a.잔액 > 0
 GROUP BY a.고객번호
 ORDER BY a.고객번호
;

마농(manon94)님이 2015-03-25 11:38에 작성한 댓글입니다.

 훌륭하시네요.

 

발상을 바꾸셨네요..

김흥수(protokhs)님이 2015-03-25 11:59에 작성한 댓글입니다.

 만약 외화환산이 아니라 그냥 평잔만을 구한다면 다음과 같이 하는 것도 가능합니다.

 
sum(거래금액 * 기간종료일까지의기간) / 평잔산출기간
 
왜냐면 평잔이 일자를 x축으로 하고 잔액을 y축으로 했을 때 만들어지는 그래프의 면적을 기간으로 나눈
평균 높이로 볼 수 있으니까.
잔액의 기간 적수를 산출할 때 sum(잔액 변동분 * 잔여기간)으로 구해도 되는 거죠...
 
면적을 y축에 평행하게 잘라 붙이느냐 아니면 x축에 평행하게 잘라 붙이느냐의 차이인데
기간에 비해 거래 건수가 적으면 위의 계산식이 거래가 없는 구간의 잔액을 생성하지 않아도 되니 더 유리합니다.
 
대부분 재무나 기타 돈관련 어플리케이션을 짜시는 경력이 좀 있으신 분들은 이런 내용은 아시더라구요.
 
그런데 그런 분들도 외화 환산이 되어야 하면 환율이 일자별로 바뀌기 때문에 일자별 잔액을 산출하고 환율을 곱해서 
평잔을 산출하는 방법 밖에 없다고들 알고 계십니다.
(인터넷에서 뒤져봐도 잘 없더군요)
 
그러나 외화 환산이 필요한 경우도 위의 산식을 응용하여 일자별 잔액을 만들지 않고도 평잔을 산출하는 방법이 있어서
그 내용을 공유하려고 퀴즈를 낸 것입니다.
 
그 산식은 
 
sum(거래금액 * 기간종료일까지의 기간 * 잔여기간 평균환율 ) / 평잔산출기간
 
입니다.
 
여기서 핵심은 잔여기간 평균환율을 환율 대신 곱하면 된다는 것입니다.
 
그리고 잔여기간 평균 환율은 다음과 같이 간단히 구해집니다.
 
    select
        a.기준일자
        , a.환율
        , avg(a.환율) over ( order by a.기준일자 desc ) 잔여평균환율
    from    환율목록 a
 
    
이 산식을 반영한 sql은 다음과 같습니다.
 
-- 환산 없는 그냥 평잔
select
    a.고객번호
    , sum((to_date('20150201','yyyymmdd') - 거래일자) * 거래금액) / (to_date('20150201','yyyymmdd') - to_date('20150101','yyyymmdd'))평잔
from    거래기록 a
group by
    a.고객번호
/
 
, 잔여평균환율 as
(
    select
        a.기준일자
        , a.환율
        , avg(a.환율) over ( order by a.기준일자 desc ) 잔여평균환율
    from    환율목록 a
)
select
    a.고객번호
    ,sum((to_date('20150201','yyyymmdd') - a.거래일자) * a.거래금액 * b.잔여평균환율 ) /  (to_date('20150201','yyyymmdd') - to_date('20150101','yyyymmdd')) 평잔
from    거래기록 a
        , 잔여평균환율 b
where   a.거래일자 = b.기준일자
group by
    a.고객번호
/
 
꼭 이 케이스 아니더라도 변환율이 있는 것의 누적 평균 구하는 것들은 다 비슷한 원리가 있으니
돈 관련 프로그램 개발하시는 분들은 참조하시기 바랍니다.
김흥수(protokhs)님이 2015-03-25 16:33에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
40755쿼리 퀴즈입니다.(시계맞추기) [7]
김흥수
2015-03-30
9753
40754초보자 쿼리 짜는것좀 도와주세요 ㅠㅠ [2]
첼시리우
2015-03-26
6473
40753TYPE object 를 만들고 다른 디비에서 디비 링크로 사용 할 수 없나요? [2]
안녕하세요
2015-03-26
6469
40752원화환산 평잔 퀴즈입니다. [9]
김흥수
2015-03-24
8544
40750계층형구조에서 하위 데이터 카운트 [1]
이혜미
2015-03-20
10208
40749OCP 자격 취득
정용석
2015-03-19
6397
40748오라클 tree 구조 질문좀 드립니다. [6]
ka
2015-03-19
7963
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.018초, 이곳 서비스는
	PostgreSQL v16.2로 자료를 관리합니다