[오라클] 간단한 쿼리인데 3개의 Row에 대해 계산식 적용하여 한행 추가
[MS-SQL] 로 작업했던 내용인데...
동일한 결과에 대한 오라클 쿼리에 대해 기록해본다.
이 쿼리는 원래 오라클이 먼저 생성된것임.
FKT 머 MS-SQL 쪽을 보면 된다.
아래는 옆으로 늘려서 계산하게 되는 쿼리임.
PS: 여기서 좀 골때렸던게...
3, 5 년 차 쿼리를 만들었던게 있었는데...
WHERE 조건에 YYMM 컬럼에 대한 것이었다.
YYMM 은 YYYY-MM 데이타 포멧으로 년월이 저장된 컬럼인데
아래는 교체했던 쿼리이고.
YYMM
BETWEEN
TO_CHAR(TO_DATE( P_ANAL_YEAR ||'0101' , 'YYYYMMDD' ) - (p_yearRange-1) * 365, 'YYYY-MM')
AND
TO_CHAR( TO_DATE( P_ANAL_YEAR||'1231' , 'YYYYMMDD' ) , 'YYYY-MM')
잘못된 쿼리
YYMM
BETWEEN
TO_CHAR(TO_DATE( :ANAL_YEAR , 'YYYY' ) - 3*365, 'YYYY')||'01'
AND
:ANAL_YEAR||'12'
잘못되었다기보다... 어찌되었든 결과가 나왔었는데... 몇가지 찾아보니 참 거시기 하더라..
TO_DATE( '2010' , 'YYYY') 하니까 2010-01-01 이 아니고 10월 몇일로 뜨길래.. 당황스러웠다..
거기에 3년 을 빼고 년도에 01을 붙였으나 위에 언급했듯 기본 포멧이 '-' 이 들어간 데이타였는데...
데이타가 1(올해), 3년전, 5년전 데이타가 정상 쿼리 된걸 보면...
헛다리 짚었던 BETWEEN 때문이 아니었을까?
이문제는 MS-SQL로 테스트를 해보아야 겠다...
아무튼 바뀐 쿼리 조건으로 테스트 해보니 쿼리를 2개를 만들려했던게 1개로 처리가 되어버리더라...
물론 하위 쿼리 <-- 때문에 시간이 2SEC 미만으로 나오긴 하는데 ... 느린건 뭐.. 어쩔수 없나보다
히스토리에서 데이타를 가져다가 테이블 저장없이 누적 계산과 더블어 데이타를 뽑아서 계산 수식을 적용해야 되다보니... ( 한방 쿼리로 아직 안되는건 머..... )
-- 쿼리 소스!!
SELECT
'C',
ROUND( SQRT( (2*SF1*ST1)/SK1 ), ROUNDCNT),
ROUND( SQRT( (2*SF2*ST2)/SK2 ), ROUNDCNT),
ROUND( SQRT( (2*SF3*ST3)/SK3 ), ROUNDCNT)
FROM
(
SELECT SUM( DECODE( HEADER , 'F' , AVG_DISTINCTION , 0 )) AS SF1,
SUM( DECODE( HEADER , 'K' , AVG_DISTINCTION , 0 )) AS SK1,
SUM( DECODE( HEADER , 'T' , AVG_DISTINCTION , 0 )) AS ST1,
SUM( DECODE( HEADER , 'F' , INI_DISTINCTION , 0 )) AS SF2,
SUM( DECODE( HEADER , 'K' , INI_DISTINCTION , 0 )) AS SK2,
SUM( DECODE( HEADER , 'T' , INI_DISTINCTION , 0 )) AS ST2,
SUM( DECODE( HEADER , 'F' , MAX_DISTINCTION , 0 )) AS SF3,
SUM( DECODE( HEADER , 'K' , MAX_DISTINCTION , 0 )) AS SK3,
SUM( DECODE( HEADER , 'T' , MAX_DISTINCTION , 0 )) AS ST3
FROM
(
SELECT 'CC' AS KEY, 'F' HEADER , AVG_DISTINCTION, INI_DISTINCTION , MAX_DISTINCTION
FROM
(
-- 중복 --
SELECT
AVG( round( OUTPUT_SUM - INPUT_SUM ,roundCnt) ) AS AVG_DISTINCTION,
SUM( DECODE( SUBSTR( YYMM , 6 ), '01',OUTPUT_SUM - INPUT_SUM, 0 ) ) AS INI_DISTINCTION,
MAX( round( OUTPUT_SUM - INPUT_SUM ,roundCnt) ) AS MAX_DISTINCTION
FROM
(
SELECT YYMM, ( SELECT SUM( INPUT_SUM_AMT ) FROM PLAN_RESULTS
WHERE GIGUM_CODE = P_GIGUM_CODE
AND ANAL_YEAR = P_ANAL_YEAR AND YYMM LIKE P_ANAL_YEAR || + '%'
AND YYMM <= A.YYMM ) INPUT_SUM, -- 수입 누계 누적
( SELECT SUM( OUTPUT_SUM_AMT ) FROM PLAN_RESULTS
WHERE GIGUM_CODE = P_GIGUM_CODE
AND ANAL_YEAR = P_ANAL_YEAR AND YYMM LIKE P_ANAL_YEAR || + '%'
AND YYMM <= A.YYMM ) OUTPUT_SUM -- 지출 누계 누적
FROM PLAN_RESULTS A
WHERE GIGUM_CODE = P_GIGUM_CODE
AND ANAL_YEAR = P_ANAL_YEAR
AND YYMM LIKE P_ANAL_YEAR || + '%'
)
-- 중복 --
)year0
union
SELECT 'CC' AS KEY, 'K' , ROUND( P_OP/12/100, ROUNDCNT ) , ROUND( P_OP/12/100, ROUNDCNT ) ,ROUND( P_OP/12/100, ROUNDCNT ) FROM DUAL
-- K쿼리
UNION
SELECT --year0.yymm,
'CC' AS KEY,
'T' HEADER ,
AVG_DISTINCTION * ROUND( P_OP2 / 12/ 100, ROUNDCNT ),
INI_DISTINCTION * ROUND( P_OP2 / 12/ 100, ROUNDCNT ),
MAX_DISTINCTION * ROUND( P_OP2 / 12/ 100, ROUNDCNT )
FROM
(
-- 중복 --
SELECT AVG( round( OUTPUT_SUM - INPUT_SUM ,roundCnt) ) AS AVG_DISTINCTION,
SUM( DECODE( SUBSTR( YYMM , 6 ), '01',OUTPUT_SUM - INPUT_SUM, 0 ) ) AS INI_DISTINCTION,
MAX( round( OUTPUT_SUM - INPUT_SUM ,roundCnt) ) AS MAX_DISTINCTION
FROM
(
SELECT YYMM, ( SELECT SUM( INPUT_SUM_AMT ) FROM PLAN_RESULTS
WHERE GIGUM_CODE = P_GIGUM_CODE
AND ANAL_YEAR = P_ANAL_YEAR AND YYMM LIKE P_ANAL_YEAR || + '%'
AND YYMM <= A.YYMM ) INPUT_SUM, -- 수입 누계 누적
( SELECT SUM( OUTPUT_SUM_AMT ) FROM PLAN_RESULTS
WHERE GIGUM_CODE = P_GIGUM_CODE
AND ANAL_YEAR = P_ANAL_YEAR AND YYMM LIKE P_ANAL_YEAR || + '%'
AND YYMM <= A.YYMM ) OUTPUT_SUM -- 지출 누계 누적
FROM PLAN_RESULTS A
WHERE GIGUM_CODE = P_GIGUM_CODE
AND ANAL_YEAR = P_ANAL_YEAR
AND YYMM LIKE P_ANAL_YEAR || + '%'
)
-- 중복 --
)
) GROUP BY KEY
)
'# 7) 데이타베이스 > 오라클' 카테고리의 다른 글
| [오라클] 오브젝트 이름의 길이는 30 문자 이하로 제한됩니다 (0) | 2010.10.23 |
|---|---|
| [ 토드 9.5 ]KEY 단축키 (0) | 2010.10.23 |
| 날 이틀동안 열폭하게 만든 스크립트.. (0) | 2010.09.10 |
| 임시테이블 (0) | 2010.09.10 |
| 오라클 프로시져 실행 (0) | 2010.09.09 |
날 이틀동안 열폭하게 만든 스크립트..
DECLARE
DT1 DATE := TO_DATE( '201001' , 'YYYYMM');
DT2 DATE := TO_DATE( '201002' , 'YYYYMM');
CNT INT := MONTHS_BETWEEN( DT2, DT1) + 1;
IDX INT := 0;
DT DATE;
BEGIN
WHILE IDX <= CNT LOOP
DBMS_OUTPUT.PUT_LINE( IDX );
DT := ADD_MONTHS( DT1, IDX );
INSERT INTO 임시테이블
VALUES ( DT );
IDX := IDX + 1;
EXIT WHEN IDX > CNT;
END LOOP;
SELECT YMD FROM 임시테이블; COMMIT; -- INTO를 요구한다.
END;
SELECT YMD FROM 임시테이블; COMMIT; -- 정상적인 위치
위 쿼리는 작업 중 MS-SQL에 익숙해진 생각에서 시작했다.
항목별 기간 데이타를 만들기 위해 사용 될 기간테이블로 항목별 테이블과 크로스 조인을 통해 데이타를 생성하려는 목적으로 임시테이블을 토드에서 테스트 하기 위해 만들어진 스크립트 임.
팀장님께서 말씀하시는 데로는 INTO요구하는 곳에서 나오는것은 맞다고 한다. 어딘가 담아야 된다고?? 하심.
오라클이 1개의 쿼리만을 허용한다고 얘기는 얼핏 들었지만... 위 같은 상황일거란 생각은...
정상적인 위치로 옮겼을경우 F5를 통해 결과를 얻을수 있지만. F9를 통해서는 여전히 문법 오류를 반환한다.
아 특이해...
이번 플젝 성패는 오라클을 얼마나 빨리 습득하느냐에 따라 결판날듯 한뎁... 오라클 검색하면 왜케 어렵게 보이는지...
MS-SQL이 쉽다는걸 다시 느꼈다.
'# 7) 데이타베이스 > 오라클' 카테고리의 다른 글
| [ 토드 9.5 ]KEY 단축키 (0) | 2010.10.23 |
|---|---|
| [오라클] 간단한 쿼리인데 3개의 Row에 대해 계산식 적용하여 한행 추가 (0) | 2010.10.23 |
| 임시테이블 (0) | 2010.09.10 |
| 오라클 프로시져 실행 (0) | 2010.09.09 |
| [오라클] 내부 테이블 정보관련... (0) | 2010.09.06 |
임시테이블
DROP TABLE 임시테이블명 CASCADE CONSTRAINTS;
CREATE GLOBAL TEMPORARY TABLE 임시테이블명
(
YMD DATE
)
ON COMMIT DELETE ROWS -- Commit 시 임시 데이타는 모두 사라짐.
NOCACHE;
임시테이블 만드는 스트립트임.
옵션이 하나 더 있는데 오라클 임시테이블 치면 블로그 글이 많이 뜸.
'# 7) 데이타베이스 > 오라클' 카테고리의 다른 글
| [ 토드 9.5 ]KEY 단축키 (0) | 2010.10.23 |
|---|---|
| [오라클] 간단한 쿼리인데 3개의 Row에 대해 계산식 적용하여 한행 추가 (0) | 2010.10.23 |
| 날 이틀동안 열폭하게 만든 스크립트.. (0) | 2010.09.10 |
| 오라클 프로시져 실행 (0) | 2010.09.09 |
| [오라클] 내부 테이블 정보관련... (0) | 2010.09.06 |
오라클 프로시져 실행
프로시져 실행시... F5나 F9로 제대로 실행이 안되서 토드에서 번개아이콘 클릭해서 파라미터 설정후에 콜했었으나...
프로시져명( 1 ,:VAR ) [ 이건 되는걸 확인했음 ]
쿼리문 선택후 F5나 F9 또는 Ctrl + Enter를 클릭시 파라미터를 설정하는 다일로그 하나가 뜬다.
Var는 Cursor 타입으로 지정하면 된다.
: 김영삼씨 덕분에 알게 되었음... 감솨!
'# 7) 데이타베이스 > 오라클' 카테고리의 다른 글
| [ 토드 9.5 ]KEY 단축키 (0) | 2010.10.23 |
|---|---|
| [오라클] 간단한 쿼리인데 3개의 Row에 대해 계산식 적용하여 한행 추가 (0) | 2010.10.23 |
| 날 이틀동안 열폭하게 만든 스크립트.. (0) | 2010.09.10 |
| 임시테이블 (0) | 2010.09.10 |
| [오라클] 내부 테이블 정보관련... (0) | 2010.09.06 |
[오라클] 내부 테이블 정보관련...
오라클은 아래 링크에서 확인 가능함.
http://blog.naver.com/kmymk?Redirect=Log&logNo=110082928906
'# 7) 데이타베이스 > 오라클' 카테고리의 다른 글
| [ 토드 9.5 ]KEY 단축키 (0) | 2010.10.23 |
|---|---|
| [오라클] 간단한 쿼리인데 3개의 Row에 대해 계산식 적용하여 한행 추가 (0) | 2010.10.23 |
| 날 이틀동안 열폭하게 만든 스크립트.. (0) | 2010.09.10 |
| 임시테이블 (0) | 2010.09.10 |
| 오라클 프로시져 실행 (0) | 2010.09.09 |
