퇴근5분전

피봇(pivot)과 언피봇(unpivot) 응용

# 7) 데이타베이스/오라클2019. 11. 3. 23:09

환경 : 오라클 12C

 

피봇은 주로 사용해왔으니... 원래 알던 개념이었다.

언피봇은 무조건 피봇의 반대라고 생각했었는데...

 

율촌 프로젝트 하면서 공부를 다시 하게 되었다.

언피봇과 피봇을 합치면 멋진 결과가 나오더라.

 - MES 생산결과를 뽑아 낼때 투입대비, 생산량을 뽑아내는 쿼리에 적용했었다.

 -- 탭키 간격이 안먹넹...

 

SELECT *
FROM
(
SELECT *
FROM
(
SELECT '데이타' A, 1 K, 10 DATA1, 20 DATA2
,'NO 1의 설명' NO1_DESC
,'NO 2의 설명' NO2_DESC
FROM DUAL
UNION ALL
SELECT '데이타' A, 2 K, 30 DATA1, 40 DATA2
,'NO 1의 설명' NO1_DESC
,'NO 2의 설명' NO2_DESC
FROM DUAL
UNION ALL
SELECT '데이타' A, 3 K, 50 DATA1, 60 DATA2
,'NO 1의 설명' NO1_DESC
,'NO 2의 설명' NO2_DESC
FROM DUAL
) A
UNPIVOT
(
( NO_DESC, DATA ) FOR
NO IN
(
(NO1_DESC, DATA1) AS 1,
(NO2_DESC, DATA2) AS 2
)
)
) T
PIVOT
(
MIN( DATA ) AS DATA FOR
K IN ( 1 , 2, 3 )
)
;

 

언피봇 한 결과
위 결과를 피봇한 결과

 

** 음... mssql이랑 다른가? 이상하게 동작하네...

   오라클은 위 문법에 맞춰서 작성하면 잘 되던뎅...

동일한 결과로 mssql로 만들려면 다르게 작성해야 한다.

 

unpivot 도 내가 원래 알던대로 mssql은 동작하고...

오라클로 테스트를 못해보니까 아쉽네...

뭔가 좀 다르게 동작하는 듯 한데... 어떻게 보면 같은것 같기도 하고...

다음에 오라클 쓸때가 오면 다시 비교해봐야겠다.

 

'# 7) 데이타베이스 > 오라클' 카테고리의 다른 글

집계함수 MIN, MAX에 해당하는 다른 컬럼값은?  (0) 2019.11.03
GROUP 콤마구분자로 연결하기  (0) 2019.11.03
달력2  (0) 2019.11.03
달력 1  (0) 2019.11.03
[오라클] 동적쿼리  (0) 2010.10.24

집계함수 MIN, MAX에 해당하는 다른 컬럼값은?

# 7) 데이타베이스/오라클2019. 11. 3. 22:57

환경 : 오라클 12C

 

-- 해석 : K가 가장 높은 값의 V는 뭐지?,

--          K가 가장 낮은 값의 V는 뭐지?

SELECT MAX( V ) KEEP ( DENSE_RANK FIRST ORDER BY K DESC ) MAX_K_V

        , MIN( V ) KEEP ( DENSE_RANK LAST ORDER BY K DESC ) MIN_K_V

FROM

(

SELECT 1 K, 'A' V FROM DUAL UNION ALL

SELECT 0 K, 'B' V FROM DUAL UNION ALL

SELECT 3 K, 'C' V FROM DUAL UNION ALL

SELECT 9 K, 'D' V FROM DUAL UNION ALL

SELECT 5 K, 'E' V FROM DUAL UNION ALL

SELECT 6 K, 'F' V FROM DUAL

) Z

'# 7) 데이타베이스 > 오라클' 카테고리의 다른 글

피봇(pivot)과 언피봇(unpivot) 응용  (0) 2019.11.03
GROUP 콤마구분자로 연결하기  (0) 2019.11.03
달력2  (0) 2019.11.03
달력 1  (0) 2019.11.03
[오라클] 동적쿼리  (0) 2010.10.24

GROUP 콤마구분자로 연결하기

# 7) 데이타베이스/오라클2019. 11. 3. 22:54

환경 : 오라클 12C

 

 

SELECT LISTAGG( GROUP_KEY || '_V, ' || GROUP_KEY || '_R' , ', ') WITHIN GROUP (ORDER BY GROUP_KEY) V1

FROM (

SELECT 1 GROUP_KEY FROM DUAL UNION ALL

SELECT 2 GROUP_KEY FROM DUAL UNION ALL

SELECT 3 GROUP_KEY FROM DUAL UNION ALL

SELECT 4 GROUP_KEY FROM DUAL

) A;

 

결과 : 1_V, 1_R, 2_V, 2_R, 3_V, 3_R, 4_V, 4_R

 

 

반대로

 

select TRIM(REGEXP_SUBSTR( COLS, '[^,]+', 1, 1, 'i')) COL1

,TRIM(REGEXP_SUBSTR( COLS, '[^,]+', 1, 2, 'i')) COL2

,TRIM(REGEXP_SUBSTR( COLS, '[^,]+', 1, 3, 'i')) COL3

,TRIM(REGEXP_SUBSTR( COLS, '[^,]+', 1, 4, 'i')) COL4

from

(

SELECT 'A,B,C,D' COLS

FROM DUAL

) a

;

'# 7) 데이타베이스 > 오라클' 카테고리의 다른 글

피봇(pivot)과 언피봇(unpivot) 응용  (0) 2019.11.03
집계함수 MIN, MAX에 해당하는 다른 컬럼값은?  (0) 2019.11.03
달력2  (0) 2019.11.03
달력 1  (0) 2019.11.03
[오라클] 동적쿼리  (0) 2010.10.24

달력2

# 7) 데이타베이스/오라클2019. 11. 3. 22:50

환경 : 오라클 12C

 

 일하다보면 스케쥴관리를 구현할때가 있는데...

그 샘플 쿼리이다.

 

 

WITH MONTH_DAY

AS

(

SELECT ( TRUNC( TO_DATE( :AV_DAY , 'YYYYMMDD'), 'MM') -1 * ( TO_CHAR( TRUNC( TO_DATE(:AV_DAY, 'YYYYMMDD'), 'MM'), 'D' ) - 1 )) + LEVEL - 1 DD

FROM DUAL

CONNECT BY LEVEL <= ( ( LAST_DAY( TO_DATE(:AV_DAY, 'YYYYMMDD') ) + ( 7 - TO_CHAR( LAST_DAY( TO_DATE(:AV_DAY, 'YYYYMMDD') ), 'D' ))) -- 종료일

- ( TRUNC( TO_DATE(:AV_DAY, 'YYYYMMDD'), 'MM') -1 * ( TO_CHAR( TRUNC( TO_DATE(:AV_DAY, 'YYYYMMDD'), 'MM'), 'D' ) - 1 )) + 1 ) -- 시작일

), DATA_TMP

AS

(

SELECT LEVEL - 1 LV

FROM DUAL

CONNECT BY LEVEL <= 5

)

, DATA_CONT

AS

(

SELECT A.LV, B.DD, B.VAL1

FROM DATA_TMP A

,

(

SELECT TO_DATE( '20190303', 'YYYYMMDD') DD, 1 LV, '값1' VAL1 FROM DUAL UNION

SELECT TO_DATE( '20190303', 'YYYYMMDD') DD, 2 LV, '값2' VAL1 FROM DUAL UNION

SELECT TO_DATE( '20190403', 'YYYYMMDD') DD, 1 LV, '0403' VAL1 FROM DUAL UNION

SELECT TO_DATE( '20190303', 'YYYYMMDD') DD, 3 LV, '332' VAL1 FROM DUAL

)B

WHERE A.LV = B.LV(+)

)

, MONTH_DAY_WEEK

AS

(

SELECT A.DD

, TRUNC( (TO_NUMBER( A.DD - ( TRUNC( TO_DATE(:AV_DAY, 'YYYYMMDD'), 'MM') -1 * ( TO_CHAR( TRUNC( TO_DATE(:AV_DAY, 'YYYYMMDD'), 'MM'), 'D' ) ))) - 1 )/ 7) WEEK

, TO_CHAR( A.DD, 'DY' ) DAYOFWEEK

, B.LV

, CASE WHEN B.LV = 0 THEN TO_CHAR( A.DD, 'DD' ) ELSE C.VAL1 END VAL1

FROM MONTH_DAY A, DATA_TMP B, DATA_CONT C

WHERE A.DD = C.DD(+)

AND B.LV = C.LV(+)

)

 

select *

from

(

SELECT TO_CHAR( MIN(DD), 'YYYYMMDD') || '~' || TO_CHAR( MAX( DD ), 'YYYYMMDD') FROMTO

, WEEK

, LV

, MAX("'일'") "SUN"

, MAX("'월'") "MON"

, MAX("'화'") "TUE"

, MAX("'수'") "WED"

, MAX("'목'") "THU"

, MAX("'금'") "FRI"

, MAX("'토'") "SAT"

FROM MONTH_DAY_WEEK

PIVOT

(

MAX( VAL1 ) FOR DAYOFWEEK IN ( '일', '월', '화', '수', '목', '금', '토' )

) A

GROUP BY WEEK, LV

ORDER BY WEEK, LV

)

;

 

스케쥴이 포함된 달력 표시

 

'# 7) 데이타베이스 > 오라클' 카테고리의 다른 글

집계함수 MIN, MAX에 해당하는 다른 컬럼값은?  (0) 2019.11.03
GROUP 콤마구분자로 연결하기  (0) 2019.11.03
달력 1  (0) 2019.11.03
[오라클] 동적쿼리  (0) 2010.10.24
[오라클] 패키지 만드는 구문..  (0) 2010.10.23

달력 1

# 7) 데이타베이스/오라클2019. 11. 3. 22:47

환경 : 오라클 12C

 

WITH MONTH_DAY

AS

(

SELECT ( TRUNC( TO_DATE( :AV_DAY , 'YYYYMMDD'), 'MM') -1 * ( TO_CHAR( TRUNC( TO_DATE(:AV_DAY, 'YYYYMMDD'), 'MM'), 'D' ) - 1 )) + LEVEL - 1 DD

FROM DUAL

CONNECT BY LEVEL <= ( ( LAST_DAY( TO_DATE(:AV_DAY, 'YYYYMMDD') ) + ( 7 - TO_CHAR( LAST_DAY( TO_DATE(:AV_DAY, 'YYYYMMDD') ), 'D' ))) -- 종료일

- ( TRUNC( TO_DATE(:AV_DAY, 'YYYYMMDD'), 'MM') -1 * ( TO_CHAR( TRUNC( TO_DATE(:AV_DAY, 'YYYYMMDD'), 'MM'), 'D' ) - 1 )) + 1 ) -- 시작일

)

, MONTH_DAY_WEEK

AS

(

SELECT DD

, TRUNC( (TO_NUMBER(DD - ( TRUNC( TO_DATE(:AV_DAY, 'YYYYMMDD'), 'MM') -1 * ( TO_CHAR( TRUNC( TO_DATE(:AV_DAY, 'YYYYMMDD'), 'MM'), 'D' ) ))) - 1 )/ 7) WEEK

, TO_CHAR( DD, 'DY' ) DAYOFWEEK

, TO_CHAR( DD, 'DD' ) VAL1

FROM MONTH_DAY

)

SELECT TO_CHAR( MIN(DD), 'YYYYMMDD') || '~' || TO_CHAR( MAX( DD ), 'YYYYMMDD') FROMTO

, WEEK

, MAX("'일'") "SUN"

, MAX("'월'") "MON"

, MAX("'화'") "TUE"

, MAX("'수'") "WED"

, MAX("'목'") "THU"

, MAX("'금'") "FRI"

, MAX("'토'") "SAT"

FROM MONTH_DAY_WEEK

PIVOT

(

MAX( VAL1 ) FOR DAYOFWEEK IN ( '일', '월', '화', '수', '목', '금', '토' )

) A

GROUP BY WEEK

ORDER BY WEEK

;

 

:AV_DAY := '20191001'

 

달력이 표시됨.

 

'# 7) 데이타베이스 > 오라클' 카테고리의 다른 글

GROUP 콤마구분자로 연결하기  (0) 2019.11.03
달력2  (0) 2019.11.03
[오라클] 동적쿼리  (0) 2010.10.24
[오라클] 패키지 만드는 구문..  (0) 2010.10.23
[토드9.5] 쿼리 테스트 할때 구문 작성법...  (0) 2010.10.23

[오라클] 동적쿼리

# 7) 데이타베이스/오라클2010. 10. 24. 02:08


'select 1 from dual'

위처럼 문자열로 쿼리를 조합하여 문장을 만들어서 쿼리 할때가 있다.

오라클에서는



begin
   open :vcr for 'select 1 from dual';
end;

이러면 쉽게 가능하다.
저작자표시 비영리 동일조건

'# 7) 데이타베이스 > 오라클' 카테고리의 다른 글

달력2  (0) 2019.11.03
달력 1  (0) 2019.11.03
[오라클] 패키지 만드는 구문..  (0) 2010.10.23
[토드9.5] 쿼리 테스트 할때 구문 작성법...  (0) 2010.10.23
[오라클] 오브젝트 이름의 길이는 30 문자 이하로 제한됩니다  (0) 2010.10.23

[오라클] 패키지 만드는 구문..

# 7) 데이타베이스/오라클2010. 10. 23. 22:20


MS에서는 { 프로시져명 : 번호 } 같은걸로 했던게 기억나는데 맞는지 모르겠다.

 

패키지내에서는 프로시져들을 그룹화 할 수 있다. 
 
 
-- 패키지 헤더 템플릿.
CREATE OR REPLACE PACKAGE {스키마명}.{패키지명} AS
 
TYPE {커서명} IS REF CURSOR; 
 
/******************************************************************************
기  능       :   주석... 
*****************************************************************************/
PROCEDURE {프로시져 이름}
(
    {파라미터명}        IN  {파라미터 데이타 타입}
);
 
END {패키지명};
/
 
 
-- 패키지 Body 선언 템플릿
CREATE OR REPLACE PACKAGE BODY {스키마명}.{패키지명} IS
 
 
/******************************************************************************
기  능       :   주석...    
******************************************************************************/
PROCEDURE {프로시져 이름}
(
    {파라미터명}        IN  {파라미터 데이타 타입}
)
IS
 
 { 프로시져 내에서 사용될  타입/ 변수 선언}
 
BEGIN
 
       {프로시져 쿼리내용}
 
END;
 
END {패키지명};
/

저작자표시 비영리 동일조건

'# 7) 데이타베이스 > 오라클' 카테고리의 다른 글

달력 1  (0) 2019.11.03
[오라클] 동적쿼리  (0) 2010.10.24
[토드9.5] 쿼리 테스트 할때 구문 작성법...  (0) 2010.10.23
[오라클] 오브젝트 이름의 길이는 30 문자 이하로 제한됩니다  (0) 2010.10.23
[ 토드 9.5 ]KEY 단축키  (0) 2010.10.23

[토드9.5] 쿼리 테스트 할때 구문 작성법...

# 7) 데이타베이스/오라클2010. 10. 23. 22:12

MS-SQL에서는 쿼리에 대한 테스트는 툴에서 쉽게 작성하고 테스트해볼수도 있고

해서 편했으나... 토드는 그렇지 않았다.

문제는 BEGIN  { 테스트에 사용할 쿼리!!   }  END;


 토드를 처음 접했을때 가장 난감했던

간단한 쿼리인데 왜 BEGIN ~ END; 사이에 쿼리는 INTO를 요구하는지는 아직도 잘 모르겠으나

BEGIN 과 END; 사이에 쿼리가 존재시  한개의 결과값은

 SELECT 결과 INTO :V FROM DUAL   이 형태로 INTO로 데이타를 :V 에 담을수 있고

이를 보기위해서는

DBMS_OUTPUT.PUT_LINE 을 통해 확인할 수 있다.

그리고 결과 집합이 단일 값이 아닌 여러행이 리턴될때 그리드로 확인해야되는데

이는

SELECT 컬럼 INTO :V_CR  FROM 테이블;

:V_CR 의 타입을 커서로 지정해주고 실행하면

하단 상태창에 데이타 그리드에 쿼리 결과가 확인이 된다.

프로시져 실행 할 때도 물론 가능하다. 

 

기본쿼리 테스트에는 아래처럼 그냥 사용하면 된다.

BEGIN ~ END;  사이에 있는 것 들은 아래처럼 사용해야 한다. 
 
저작자표시 비영리 동일조건

'# 7) 데이타베이스 > 오라클' 카테고리의 다른 글

[오라클] 동적쿼리  (0) 2010.10.24
[오라클] 패키지 만드는 구문..  (0) 2010.10.23
[오라클] 오브젝트 이름의 길이는 30 문자 이하로 제한됩니다  (0) 2010.10.23
[ 토드 9.5 ]KEY 단축키  (0) 2010.10.23
[오라클] 간단한 쿼리인데 3개의 Row에 대해 계산식 적용하여 한행 추가  (0) 2010.10.23

[오라클] 오브젝트 이름의 길이는 30 문자 이하로 제한됩니다

# 7) 데이타베이스/오라클2010. 10. 23. 21:53

패키지 이름 지정할 때 애먹었던 것임.

30자가 넘어가니 예외가 뜨는데

토드에서의 예외는 정말 난해한 메세지들이 뜬다...

오라클을 헤매게 만든 가장 큰 요인이었던것 같다.

이에 대한 지원이 미흡해서 이번 프로젝트(근로복지공단)가 거지같은 종말을 맞이하고 있다.
저작자표시 비영리 동일조건

'# 7) 데이타베이스 > 오라클' 카테고리의 다른 글

[오라클] 패키지 만드는 구문..  (0) 2010.10.23
[토드9.5] 쿼리 테스트 할때 구문 작성법...  (0) 2010.10.23
[ 토드 9.5 ]KEY 단축키  (0) 2010.10.23
[오라클] 간단한 쿼리인데 3개의 Row에 대해 계산식 적용하여 한행 추가  (0) 2010.10.23
날 이틀동안 열폭하게 만든 스크립트..  (0) 2010.09.10

[ 토드 9.5 ]KEY 단축키

# 7) 데이타베이스/오라클2010. 10. 23. 21:48

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


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

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

저작자표시 비영리 동일조건

'# 7) 데이타베이스 > 오라클' 카테고리의 다른 글

[토드9.5] 쿼리 테스트 할때 구문 작성법...  (0) 2010.10.23
[오라클] 오브젝트 이름의 길이는 30 문자 이하로 제한됩니다  (0) 2010.10.23
[오라클] 간단한 쿼리인데 3개의 Row에 대해 계산식 적용하여 한행 추가  (0) 2010.10.23
날 이틀동안 열폭하게 만든 스크립트..  (0) 2010.09.10
임시테이블  (0) 2010.09.10

티스토리툴바