[오라클] 간단한 쿼리인데 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 |
[ MS-SQL ] 간단한 쿼리인데 3개의 Row에 대해 계산해서 한행 추가
C# 에 심취해서 쿼리에 대한 내용을 블로깅 해두지 않았었는데...
오라클을 다루다 보니 작업처리 방법등은 기록해두는것이 괜찮을듯 하다.
아래 색 처리한 부분은
F 3609565874.83333
K 0.002083333
T 369980.502170417
요런 데이타에
= SQRT( ( 2 * F * T ) * K ) 라는 결과값을 한행 더 추가해야 될 경우
위 세개의 데이타를 옆으로 펼치면..
F_AVG, T_AVG, K_AVG, F_BASE, T_BASE, K_BASE, F_UPPER, T_UPPER, K_UPPER
형태로 변환해서 계산식을 적용해주어야 한다.
이때 위 쿼리에 컬럼을 하나 추가하여 그룹바이로 통합시켜주면 1개의 쿼리로 바꿀수 있게 된다.
방법 1)
--USE [TestDB]
--GO
/****** 개체: Table [dbo].[Plan_Results] 스크립트 날짜: 10/23/2010 19:50:45 ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
--SET ANSI_PADDING ON
--GO
--CREATE TABLE [dbo].[Plan_Results](
-- [Gigum_Code] [char](1) COLLATE Korean_Wansung_CI_AS NOT NULL,
-- [YYYYMM] [char](7) COLLATE Korean_Wansung_CI_AS NOT NULL,
-- [Year0_SumAmt] [numeric](18, 6) NULL,
-- [Year3_SumAmt] [numeric](18, 6) NULL,
-- [Year5_SumAmt] [numeric](18, 6) NULL,
-- CONSTRAINT [PK_Plan_Results] PRIMARY KEY CLUSTERED
--(
-- [Gigum_Code] ASC,
-- [YYYYMM] ASC
--)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
--) ON [PRIMARY]
--
--GO
--SET ANSI_PADDING OFF
--insert into Plan_Results values ( 1 , '2010-01', 881728083,- 21455788882,- 26296054501 )
--insert into Plan_Results values ( 1 , '2010-02', 1663456166,- 93932311442,- 67724751121 )
--insert into Plan_Results values ( 1 , '2010-03', 10535184250,- 39727311735,- 27162544453 )
--insert into Plan_Results values ( 1 , '2010-04', 10916912333,- 37168124598,- 24826804875 )
--insert into Plan_Results values ( 1 , '2010-05', 10798640416,- 332748211345,- 200422034557 )
--insert into Plan_Results values ( 1 , '2010-06', 12360368500,- 546349938762,- 336875250117 )
--insert into Plan_Results values ( 1 , '2010-07', 8342096583,- 526987602935,- 321423734623 )
--insert into Plan_Results values ( 1 , '2010-08', 3223824666,- 395509716668,- 245811417447 )
--insert into Plan_Results values ( 1 , '2010-09', 5355552750,- 281646305135,- 157115087823 )
--insert into Plan_Results values ( 1 , '2010-10',- 562719166,- 233716812282,- 136073606419 )
--insert into Plan_Results values ( 1 , '2010-11',- 7580991083,- 193765514755,- 109664461049 )
--insert into Plan_Results values ( 1 , '2010-12',-12619263000,- 176147709331,- 95575440266 )
DECLARE @OP FLOAT
DECLARE @OP2 FLOAT
SET @OP = 2.50 /100 /12 -- 적용금리
SET @OP2 = 0.123 /100 /12 -- 운용수수료
SELECT 'F' AS [HEADER] ,
AVG( YEAR0_SUMAMT ) AS [AVG],
SUM( CASE WHEN RIGHT(YYYYMM,2)= '01' THEN YEAR0_SUMAMT ELSE 0 END ) AS [BASE],
MAX( YEAR0_SUMAMT ) AS [UPPER]
FROM PLAN_RESULTS
GROUP BY GIGUM_CODE
UNION
SELECT 'K', @OP, @OP, @OP
UNION
SELECT 'T' AS [HEADER] ,
AVG( YEAR0_SUMAMT ) * @OP2 AS [AVG],
SUM( CASE WHEN RIGHT(YYYYMM,2)= '01' THEN YEAR0_SUMAMT ELSE 0 END ) * @OP2 AS [BASE],
MAX( YEAR0_SUMAMT ) * @OP2 AS [UPPER]
FROM PLAN_RESULTS
GROUP BY GIGUM_CODE
UNION
SELECT 'C', SQRT( ( 2* SUM(CASE WHEN HEADER = 'F' THEN [AVG] ELSE 0 END ) * -- AVG1,
SUM(CASE WHEN HEADER = 'T' THEN [AVG] ELSE 0 END ) ) / -- AVG2,
SUM(CASE WHEN HEADER = 'K' THEN [AVG] ELSE 0 END ) ), -- AVG3,
SQRT( ( 2 * SUM(CASE WHEN HEADER = 'F' THEN [BASE] ELSE 0 END ) * -- BASE1,
SUM(CASE WHEN HEADER = 'T' THEN [BASE] ELSE 0 END ) )/ -- BASE2,
SUM(CASE WHEN HEADER = 'K' THEN [BASE] ELSE 0 END ) ), --BASE3,
SQRT( (2 * SUM(CASE WHEN HEADER = 'F' THEN [UPPER] ELSE 0 END ) *-- UPPER1,
SUM(CASE WHEN HEADER = 'T' THEN [UPPER] ELSE 0 END ) )/ -- UPPER2,
SUM(CASE WHEN HEADER = 'K' THEN [UPPER] ELSE 0 END ) ) -- UPPER3
FROM
(
SELECT 0 [KEY], 'F' AS [HEADER] ,
AVG( YEAR0_SUMAMT ) AS [AVG],
SUM( CASE WHEN RIGHT(YYYYMM,2)= '01' THEN YEAR0_SUMAMT ELSE 0 END ) AS [BASE],
MAX( YEAR0_SUMAMT ) AS [UPPER]
FROM PLAN_RESULTS
GROUP BY GIGUM_CODE
UNION
SELECT 0, 'K', @OP, @OP, @OP
UNION
SELECT 0, 'T' AS [HEADER] ,
AVG( YEAR0_SUMAMT ) * @OP2 AS [AVG],
SUM( CASE WHEN RIGHT(YYYYMM,2)= '01' THEN YEAR0_SUMAMT ELSE 0 END ) * @OP2 AS [BASE],
MAX( YEAR0_SUMAMT ) * @OP2 AS [UPPER]
FROM PLAN_RESULTS
GROUP BY GIGUM_CODE
) T
GROUP BY [KEY]
UNION
SELECT '_C', SQRT( ( 2* SUM(CASE WHEN HEADER = 'F' THEN [AVG] ELSE 0 END ) * -- AVG1,
SUM(CASE WHEN HEADER = 'T' THEN [AVG] ELSE 0 END ) ) / -- AVG2,
SUM(CASE WHEN HEADER = 'K' THEN [AVG] ELSE 0 END ) ) -- AVG3,
+ SUM( CASE WHEN HEADER = 'F' THEN [BASE] ELSE 0 END ),
SQRT( ( 2 * SUM(CASE WHEN HEADER = 'F' THEN [BASE] ELSE 0 END ) * -- BASE1,
SUM(CASE WHEN HEADER = 'T' THEN [BASE] ELSE 0 END ) )/ -- BASE2,
SUM(CASE WHEN HEADER = 'K' THEN [BASE] ELSE 0 END ) ) --BASE3,
+ SUM( CASE WHEN HEADER = 'F' THEN [BASE] ELSE 0 END ),
SQRT( (2 * SUM(CASE WHEN HEADER = 'F' THEN [UPPER] ELSE 0 END ) *-- UPPER1,
SUM(CASE WHEN HEADER = 'T' THEN [UPPER] ELSE 0 END ) )/ -- UPPER2,
SUM(CASE WHEN HEADER = 'K' THEN [UPPER] ELSE 0 END ) ) -- UPPER3
+ SUM( CASE WHEN HEADER = 'F' THEN [BASE] ELSE 0 END )
FROM
(
SELECT 0 [KEY], 'F' AS [HEADER] ,
AVG( YEAR0_SUMAMT ) AS [AVG],
SUM( CASE WHEN RIGHT(YYYYMM,2)= '01' THEN YEAR0_SUMAMT ELSE 0 END ) AS [BASE],
MAX( YEAR0_SUMAMT ) AS [UPPER]
FROM PLAN_RESULTS
GROUP BY GIGUM_CODE
UNION
SELECT 0, 'K', @OP, @OP, @OP
UNION
SELECT 0, 'T' AS [HEADER] ,
AVG( YEAR0_SUMAMT ) * @OP2 AS [AVG],
SUM( CASE WHEN RIGHT(YYYYMM,2)= '01' THEN YEAR0_SUMAMT ELSE 0 END ) * @OP2 AS [BASE],
MAX( YEAR0_SUMAMT ) * @OP2 AS [UPPER]
FROM PLAN_RESULTS
GROUP BY GIGUM_CODE
) T
GROUP BY [KEY]
'# 7) 데이타베이스 > Ms-Sql' 카테고리의 다른 글
| [MS-SQL] 누적 계산에 사용한 쿼리... (0) | 2010.10.23 |
|---|---|
| [MS-SQL] SELECT 쿼리.. (0) | 2010.10.23 |
| [MS-SQL] 피벗 .. (0) | 2010.08.24 |
| 하루 종일 쌩쑈하다. (0) | 2009.08.13 |
| MS-SQL 에서의 한글 자음으로의 검색... (0) | 2009.06.26 |
날 이틀동안 열폭하게 만든 스크립트..
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 |
[MS-SQL] 피벗 ..
간만에 쿼리 쓸라니 기억이 안나서 어렵군...
갑자기 피벗 쓸일이 있었는데... 가물 가물한 기억땜시롱...
간단한 예제로 써놓는답.
SELECT y, sum( case c when 'A' then i else 0 end ) AS AColumn ,sum( case A.c when 'B' then i else 0 end ) AS BColumn
FROM
(
SELECT '200812' AS Y , 'A' AS C, '1' AS I
UNION
SELECT '200812', 'B', '2'
)a
group by A.y
결과
Y AColumn BColumn
200812 1 2
Sum이 기억이 안나서 무진장 헤맸뉑...
CTE는 왜이리 어렵누..
추가 ~~~ : 반대로 할경우!!!
SELECT A.A, CASE B.[K] WHEN 1 THEN A.[1]
WHEN 2 THEN A.[2]
WHEN 3 THEN A.[3]
WHEN 4 THEN A.[4]
WHEN 5 THEN A.[5] END AS M
FROM
(
SELECT 'A' [A], 1 [1], 2 [2],3 [3],4 [4],5 [5]
)A
,
(
SELECT 1 K
UNION ALL
SELECT 2 K
UNION ALL
SELECT 3 K
UNION ALL
SELECT 4 K
UNION ALL
SELECT 5 K
)B
'# 7) 데이타베이스 > Ms-Sql' 카테고리의 다른 글
| [MS-SQL] 누적 계산에 사용한 쿼리... (0) | 2010.10.23 |
|---|---|
| [MS-SQL] SELECT 쿼리.. (0) | 2010.10.23 |
| [ MS-SQL ] 간단한 쿼리인데 3개의 Row에 대해 계산해서 한행 추가 (0) | 2010.10.23 |
| 하루 종일 쌩쑈하다. (0) | 2009.08.13 |
| MS-SQL 에서의 한글 자음으로의 검색... (0) | 2009.06.26 |
하루 종일 쌩쑈하다.
오늘 한 삽질을 기록하다.
테이블1
(
BankNum, -- PK
Sno -- FK Referance [테이블1].[Sno]
)
테이블2
(
Sno -- PK
)
두개의 테이블이 있다.
[테이블1]에는 5000개의 데이타가 이미 들어가 존재하고
[테이블2]는 다른 정보로 가득 차있다.
두테이블은 Sno로 연계관계가 성립한다.
이건 기본적으로 [테이블1]을 바탕으로 해서 넣기 때문에
[테이블2]에서는 Sno를 얻어다가 업데이트 하게 되는 형태로 이때 Sno갯수는 가변적이다.
여기서 나의 왕고집이 또한번 빛을 발휘한다.
"원샷 쿼리로 어찌 어찌 해보자..."
우선 삽질과 함께 헤매이게된 조건을 기록해보면.
난 커서를 모른다. ( 공부만 했지 적용은 안해봐서.. 응용에 대해 무지하다. )
그리고 테스트중인 Sql서버가 Ms-Sql 2000 이었다. ( 줸장! )
-> 2000이란 존재는 내가 다뤘던 2005보다는 좀더 쿼리가 복잡해진다. 쑈를 더한다는거...
-> 내가 기억하는 쿼리로 아무리 풀려해도 오류가 떠서 확인해보니 2000 이었더라..
-> 난 2005인줄 알고 있었을 뿐이고~
테이블1에 데이타를 넣어야 되는데 Sno null이고 테이블2는 테이블1과 연계될게 없었는데
난 오늘 이걸 종일 매치 시키려고 애썼따.
결과는 물론 나왔지만 성능상 몹쓸쿼리가 되었음에 고집을 접었다.
잘못된 판단으로의 접근 :
1. 내가 생각했던 방법은 테이블1 - 테이블2 에서 서로 매치되는게
없으므로 테이블2에서 선택된 Sno 값들에 대해 임시 테이블을 만들면서 번호를 붙이고
2. 테이블1도 임시테이블로 만들면서 행번호를 만들어서
3. 서로 두 임시 테이블을 조인 후
4. 그 테이블로 테이블1에 업데이트를 시도한다!
첨엔 그럴싸 했고 업데이트문을 하나로 할수 있어보여서 왠지... 더욱더 해보자라는 강한 의지가...
단, 임시테이블이 걸리는데 ...
<< 삽질 쿼리 >>
SELECT SNO
INTO #TABLE1
FROM [STUDENT](NOLOCK)
WHERE SNO IN ('2007A131 ','20052109 ');
SELECT (SELECT COUNT(*) FROM #TABLE1 AS B WHERE B.SNO <= A.SNO) AS NUM, A.SNO
INTO #TABLE3
FROM #TABLE1 AS A;
SELECT BANKNUM, BANKCODE
INTO #TABLE2
FROM BANKNO AS A(NOLOCK)
WHERE SNO IS NULL;
SELECT ( SELECT COUNT( * ) FROM #TABLE2 AS B WHERE B.BANKNUM <= A.BANKNUM ) AS NUM, A.BANKNUM, A.BANKCODE
INTO #TABLE4
FROM #TABLE2 AS A;
UPDATE [BANKNO] SET
[BANKNO].[SNO] = [SOURCE].[SNO],
[BANKNO].[SDATE] = CONVERT(CHAR(10), GETDATE(), 120),
[BANKNO].[FDATE] = CONVERT(CHAR(10), GETDATE(), 120)
FROM
(
SELECT TB1.NUM, TB1.BANKNUM, TB2.SNO, TB1.BANKCODE
FROM #TABLE3 AS TB1
INNER JOIN #TABLE4 AS TB2 ON TB1.NUM = TB2.NUM
)AS [SOURCE]
WHERE [BANKNO].BANKNUM = [SOURCE].BANKNUM
AND [BANKNO].BANKCODE = [SOURCE].BANKCODE;
DROP TABLE #TABLE1; DROP TABLE #TABLE2; DROP TABLE #TABLE3;
일반적으로 사용하는 1개 행씩업데이트 하는 구문 말고, 어떤 대상을 가지고 업데이트 하는 방법을 찾아
Update문을 찾아서 확인.
Form절 이 있었고 이를 적용해보았다. ( 임시테이블을 잘못 써서 삽질한 얘기는 패쑤 ~ )
대략 기록하자면 아래 와 같다.
------------------------------------------------------
Update 테이블 Set
컬럼 = 값
From 대상
where 테이블.조건 컬럼 = 대상.조건컬럼
------------------------------------------------------
여기서 Where문에 걸어줄 조건 땜시롱 위에서 얘기한 로직으로 만들었는데...
동작은 되는데 임시테이블 5000개에 번호를 붙이다 보니 이게 성능상 문제가 되어버렸다.
너무 길어져서 결론을 쓴다.
1개행씩 처리하고 쿼리는 더욱 간단해졌다.
순서 :
1. 선택된 Sno 목록화 하고
2. 루프를 돌면서 1개행씩 업데이트 하되 대상은 Sno가 빈 BankNum로 한다.
구문은
UPDATE BANKNO SET
SNO = '2007A131'
FROM ( SELECT TOP 1 BANKNUM, BANKCODE FROM BANKNO(NOLOCK) WHERE SNO IS NULL )AS [SOURCE]
WHERE BANKNO.BANKNUM = SOURCE.BANKNUM AND BANKNO.BANKCODE = SOURCE.BANKCODE
참 짧게 되었찌! 췌.. 이걸 6시간동안 고민하고 생각하고 ..;;
요새 슬럼프라 일도 안되고 재미도 없고 ...
다시 내린 결론은 어떤 문제에 대한 해결방법은 " 쉽게 생각하자." 였다.
내가 굳이 어렵게 생각할 필요는 없으니 ~ !!
아는 한도내에서 쉽게 쉽게...
'# 7) 데이타베이스 > Ms-Sql' 카테고리의 다른 글
| [MS-SQL] 누적 계산에 사용한 쿼리... (0) | 2010.10.23 |
|---|---|
| [MS-SQL] SELECT 쿼리.. (0) | 2010.10.23 |
| [ MS-SQL ] 간단한 쿼리인데 3개의 Row에 대해 계산해서 한행 추가 (0) | 2010.10.23 |
| [MS-SQL] 피벗 .. (0) | 2010.08.24 |
| MS-SQL 에서의 한글 자음으로의 검색... (0) | 2009.06.26 |
MS-SQL 에서의 한글 자음으로의 검색...
declare @hangle_jaeum varchar(100)
declare @startchar char(2)
declare @endchar char(2)
set @hangle_jaeum = 'ㄱㄴㄷㄹㅁㅂㅅㅇㅈㅊㅋㅌㅍㅎ'
set @startchar = @WORD
set @endchar = SUBSTRING( @hangle_jaeum, charindex( @startchar, @hangle_jaeum, 0 )+1, 1 )
print ( SUBSTRING( @hangle_jaeum, charindex( @startchar, @hangle_jaeum, 0 )+1,1 ) )
print ('s:'+ @startchar + ' e:' + @endchar)
SELECT *
FROM [테이블]
WHERE [NAME] BETWEEN @startchar AND @endchar
SQL 문을 도와주다가 이름을 ㄱ~ ㅎ으로 찾는 주소록 관련해서
쿼리를 살짝 도와주고 CASE문으로 조합하여 해당 쿼리를 완성해서 준적이 있었으나.
위 BETWEEN으로 된다고 하여 신기해서 블로그에 적어둠.
SQL에서의 어떤 속성값이 지원하는지? 아님 기본으로 지원이 되는것인지는 모르겠으나.
BETWEEN 'ㄱ' AND 'ㄴ' 하게 되면
ㄱ 으로 시작하는 첫글자를 대상으로 조회 해왔다
ㄴ이 오지는 않았다. ㄴ대신 ㄷ 을 넣게 되면 ㄱ , ㄴ 으로 시작하는 첫글자들을 결과집합으로
쿼리해오더라
'# 7) 데이타베이스 > Ms-Sql' 카테고리의 다른 글
| [MS-SQL] 누적 계산에 사용한 쿼리... (0) | 2010.10.23 |
|---|---|
| [MS-SQL] SELECT 쿼리.. (0) | 2010.10.23 |
| [ MS-SQL ] 간단한 쿼리인데 3개의 Row에 대해 계산해서 한행 추가 (0) | 2010.10.23 |
| [MS-SQL] 피벗 .. (0) | 2010.08.24 |
| 하루 종일 쌩쑈하다. (0) | 2009.08.13 |
