퇴근5분전


Shortcut Key Function
F1 Windows Help File
F2 Toggle Full screen Editor
F3 Find Next Occurrence
<SHIFT> <F3> Find Previous Occurrence
F4 Describe Object at cursor. Describe Table, View, Procedure, Function, or Package in popup window
F5 Execute as Script
F6 Toggle between Editor and Results tabs
F7 Clear All Text
F8 Recall previous SQL statement
F9 Execute statement
<CTRL> F9 Describes statement at cursor
<SHIFT> F9 Execute snippet at cursor
F10 Popup Menu
F11 Execute code without using the Debugger
<CTRL> A Select All Text
<CTRL> C Copy
<CTRL> E Execute Explain Plan on the Current Statement
<CTRL> F Find Text
<CTRL> G Goto Line
<CTRL> H Highlight snippet
<CTRL>I Init caps for highlighted code.
<CTRL> L Converts Text to Lowercase
<CTRL> M Make Code Statement
<CTRL> N Recall Named SQL Statement
<CTRL> O Opens File
<CTRL> P Strip Code Statement
<CTRL> R Find and Replace
<CTRL> S Save File
<SHIFT> <CTRL> S Save File As
<CTRL> T Columns Dropdown
<CTRL> U Converts Text to Uppercase
<CTRL> V Paste
<CTRL> X Cut
<CTRL> Z Undo Last Change
<CTRL>. Display popup list of matching tablenames
<SHIFT> <CTRL> Z Redo Last Undo
<ALT> <UP> Display Previous Statement
<ALT> <DOWN> Display Next Statement (after <ALT> <UP>)
<ALT> <PageUP> Navigate to the previous tab in the editor
<ALT> <PageDOWN> Navigate to the next tab in the editor
<CTRL> <ALT> <PageUP> Navigate to the previous results panel tab
<CTRL> <ALT> <PageDOWN> Navigate to the next results panel tab
<CTRL><HOME> In the data grids, goes to the top of the recordset
<CTRL><END> In the data grids, goes to the end of the recordset
<CTRL><SPACE> Display the code template pick list
<CTRL><TAB> Cycles through the collection of MDI Child windows


토드 도움말 찾아보니 뜨더랑...

그닥 사용하는건 좀 적긴할텐데...


[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
        )


 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