블로그 이미지
SITD

카테고리

분류 전체보기 (34)
1.DB (4)
2.OS (3)
3.PROGRAMMING (14)
4.학업 (0)
5.영어 (0)
6.KSIT (5)
7.증권 (1)
8.EXCEL (0)
9.Graduate (2)
기타 (5)
Total
Today
Yesterday

달력

« » 2024.3
1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31

공지사항

태그목록

최근에 올라온 글

'1.DB'에 해당되는 글 4건

  1. 2011.11.14 분석함수 및 KEEP을 정리해보자.
  2. 2011.05.17 아우터 조인의 원리는?
  3. 2011.04.25 NULLS FIRST/LAST
  4. 2011.04.22 오라클 ROWNUM
가장 기본적인 EMP테이블에서 부서별로 MAX와 MIN 값을 구해보자

<분석함수 버전>

SELECT A.deptno, A.empno, A.sal, B.empno, B.sal
  FROM (SELECT t.*,
  ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal DESC) AS rn
      FROM EMP T
) A,
(SELECT t.*,
  ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal ASC) AS rn
      FROM EMP T
) B
 WHERE A.deptno = B.deptno
  AND A.rn = 1
  AND B.rn = 1


10 7839 5000 7934 1300
20 7788 3000 7369 800
30 7698 2850 7900 950



<MAX KEEP 버전> 

SELECT deptno,
MAX(empno) KEEP(DENSE_RANK FIRST  ORDER BY sal desc) as empno,
MAX(sal) AS max_sal,
MIN(empno) KEEP(DENSE_RANK FIRST  ORDER BY sal Asc) as empno,
min(sal) AS min_sal
  FROM EMP
 GROUP BY deptno


10 7839 5000 7934 1300
20 7902 3000 7369 800
30 7698 2850 7900 950
9001 9000



출처 : 오라클 KEEP
         (http://tortoiselfish.springnote.com/pages/5897823?print=1

keep(dense_rank {last|first} order by <>) , over(partition by XX)

 먼저 keep(dense_rank first order by )함수

A 컬럼 기준 정렬 후 지정 된 행의 B 컬럼 값 추출 하기

first나 last로 order by 한 로우에서 첫번째 혹은 마지막로우를 가져온다

 

MIN(B) (DENSE_RANK FIRST ORDER BY A [ASC|DESC])

MAX(B) (DENSE_RANK LAST ORDER BY A [ASC|DESC])

 

오라클 클럽에서 발췌한 퀴즈를 풀면서 알게 되었다는... 스칼라서브쿼리를 생각했었는데 저런 방법도 있었구나.. ㅎ

참 배울게 많다

 

--참고자료(http://3030.tistory.com/88)
구문
【형식】

집합함수 KEEP (
 DENSE_RANK FIRST ORDER BY
    expr [DESC|ASC][NULL{FIRST|LAST}],...)

 

field1    field2
1    c
2    b
3    a

SQL> select MIN(field1), MIN(field2) KEEP(DENSE_RANK FIRST ORDER BY field1) AS field2 from table1
결과>1, c

SQL> select MAX(field1), MAX(field2) KEEP(DENSE_RANK LAST ORDER BY field1) AS field2 from table1
결과> 3 , a

 

 

--참고자료(http://tong.nate.com/rahxephon/35087891)

COUNT(*) OVER () : 전체행 카운트

COUNT(*) OVER (PARTITION BY 컬럼) : 그룹단위로 나누어 카운트

MAX(컬럼) OVER() : 전체행 중에 최고값

MAX(컬럼) OVER(PARTITION BY 컬럼) : 그룹내 최고값

MIN(컬럼) OVER () : 전체행 중에 최소값

MIN(컬럼) OVER (PARTITION BY 컬럼) : 그룹내 최소값

SUM(컬럼) OVER () : 전체행 합

SUM(컬럼) OVER (PARTITION BY 컬럼) : 그룹내 합

AVG(컬럼) OVER () : 전체행 평균

AVG(컬럼) OVER (PARTITION BY 컬럼) : 그룹내 평균

STDDEV(컬럼) OVER () : 전체행 표준편차

STDDEV(컬럼) OVER (PARTITION BY 컬럼) : 그룹내 표준편차

RATIO_TO_REPORT(컬럼) OVER () : 현재행값/SUM(전체행값) 퍼센테이지로 나타낼경우 100곱하면 됩니다.

RATIO_TO_REPORT(컬럼) OVER (PARTITION BY 컬럼) : 현재행값 / SUM(그룹행값) 퍼센테이지로 나타낼경우 100곱하면 됩니다.

 

오라클 클럽 문제

[퀴즈] 분석함수의 이해

구분1, 구분2, 날짜, 값으로 구성된 테이블이 있습니다.
구분1, 구분2 그룹으로 최종날짜와 금액합계를 구하고,
구분1별 최종날짜와 그에 해당하는 구분2값과 금액합계를 구하시오.

WITH t AS
(
SELECT 'A' gb1, '01' gb2, '20100601' dt, 10 v FROM dual
UNION ALL SELECT 'A', '01', '20100604', 20 FROM dual
UNION ALL SELECT 'A', '02', '20100603', 30 FROM dual
UNION ALL SELECT 'A', '03', '20100602', 40 FROM dual
UNION ALL SELECT 'B', '01', '20100601', 50 FROM dual
UNION ALL SELECT 'B', '02', '20100605', 60 FROM dual
UNION ALL SELECT 'B', '03', '20100603', 70 FROM dual
UNION ALL SELECT 'B', '04', '20100602', 80 FROM dual
)
SELECT * FROM t
;

 

 

 


 

==정답

SELECT gb1
    , gb2
    , MAX(dt) dt
    , SUM(v) v
    , MAX(MAX(dt)) OVER(PARTITION BY gb1) max_dt
    , MAX(gb2) KEEP(DENSE_RANK LAST ORDER BY MAX(dt)) OVER(PARTITION BY gb1) max_dt_gb2
    , MAX(SUM(v)) KEEP(DENSE_RANK LAST ORDER BY MAX(dt)) OVER(PARTITION BY gb1) max_dt_v
  FROM t
 GROUP BY gb1, gb2
 ORDER BY gb1, gb2
;

 

with t as
(
select 'A' bg1, '01' gb2 , '20100601' dt, 10 v from dual
union all select 'A','01','20100604', 20 from dual
union all select 'A','02','20100603', 30 from dual
union all select 'A','03','20100602', 40 from dual
union all select 'B','01','20100601', 50 from dual
union all select 'B','02','20100605', 60 from dual
union all select 'B','03','20100603', 70 from dual
union all select 'B','04','20100602', 80 from dual
)
select bg1, gb2, max(dt), sum(v),
(select max(dt) from t where bg1 =a.bg1) max_dt,
max(max(dt)) over(partition by bg1),
min(gb2) keep(dense_rank last order by max(dt)) over(partition by bg1) max_dt_gb2,
max(sum(v)) keep(dense_rank last order by max(dt)) over(partition by bg1) max_dt_v,
(select sum(v) from t where dt = (select max(dt) from t where bg1 =a.bg1)) nax_dt_v from t a
group by  bg1, gb2














 

'1.DB > SQL' 카테고리의 다른 글

NULLS FIRST/LAST  (0) 2011.04.25
Posted by SITD
, |
추가 by 2012.07.13
NVL로 아우터를 따라할 수 있지만 제약이 있다. 만약 조인을 할 때 (+)가 하나도 없이 NVL로만 할 경우...해당 테이블을 찾아가지 못해서 결과가 안나오는 듯 하다.

아래는 거 쿼리이다.

SELECT A.acnt_no,
B.acnt_no,
C.item_cd
  FROM WR_TB415 A,
  AC_TBM_ACNT B,
WR_TB504 C
 WHERE A.acnt_no = B.acnt_no
   AND A.acnt_no = C.acnt_no (+)
   AND A.item_cd = NVL(C.item_cd, A.item_cd)
   AND A.acnt_no IN ('00011222201')
/
SELECT A.acnt_no,
B.acnt_no,
C.item_cd
  FROM WR_TB415 A,
  AC_TBM_ACNT B,
WR_TB504 C
 WHERE A.acnt_no = B.acnt_no
   AND A.acnt_no = C.acnt_no (+)
   AND A.item_cd = C.item_cd (+)
   AND A.acnt_no IN ('00011222201')


=> 결과 동일
SELECT A.acnt_no,
B.item_cd
  FROM WR_TB415 A,
  WR_TB300 B
 WHERE A.item_cd = B.item_cd (+)
   AND A.acnt_no = '10006000101'
/
SELECT A.acnt_no,
B.item_cd
  FROM WR_TB415 A,
  WR_TB300 B
 WHERE A.item_cd = NVL(B.item_cd, A.item_cd)
   AND A.acnt_no = '10006000101'
=> 결과가 상이함.





(+)의 기능에 대해
다음과 같은 쿼리가 있을 때,

SELECT A.item,
A.item_name,
A.dept,
A.dept_nm
  FROM ITEM_CODE A,
  DEPT_CODE B
 WHERE A.dept = B.dept (+)
   AND B.dept_nm (+) = 'ASCII'
/

이 쿼리의 처리 순서가 네스티드 루프 기준으로 ITEM_CODE테이블을 풀 스캔해서 해당 테이블의 로우 1건을 가져온 후, A.dept를 ITEM_CODE에서 가져온 로우의 값으로 치환,
이후 DEPT_CODE테이블 검색시 실제론 A.dept에 들어있는 값을 이용해서 찾는다고 알고 있습니다.

이때 '해당 B.dept에 (+)이 있으면 B의 값이 없더라도 A로우를 출력'하는 거 같던데,

이렇게 이해해도 될까요?

즉, (+)가 붙는 조건은 조인 혹은 검색시 조회가 안되더라도 값 없음으로 해당 로우 자체를 조건에서 제외하는게 아니라, 그대로 진행(즉, B테이블 못 읽음)한다라고 이해하는게 맞을까요?
이는 조인 조건이 아닌 AND B.dept_nm (+) = 'ASCII'의 경우에도 똑같이 이해해도 될까 궁금합니다.

답변 부탁드립니다 m(_ _)m


답변 by ProDBA 아바롱님.


네.. 큰 맥락에서 그렇게 이해하시는게 맞습니다.
다만 아우터 조인의 개념에 조금 더 부연을 드리자면
 
(+) 기호가 붙지 않은 쪽(OUTER TABLE, DRIVING TABLE)의 테이블 건수 이상이 출력된다는 것입니다.
즉, 위와 같은 예제에서는 A 테이블에 아무런 조건이 없으므로 최소한 A의 모든 ROW가 출력됩니다.
물론 조인 조건의 컬럼에 따라 B 테이블에서 1:M으로 조인되어지면 A의 모든 ROW가 M의 배수 만큼 중복으로 출력됩니다.
그러므로, 최소한 A의 건수이상 출력됩니다.
 
또한, 이해하고 계신 개념을 조금만 보정 하자면 B 테이블을 읽지 않는 것이 아니고
A에는 존재하는 데이터가 B에서 조인되어지지 않거나 혹은 조인되어 지더라도 B의 필터링 조건에서 제외되더라도
A를 출력시키는 개념입니다.
일반적인 INNER JOIN은 위와 반대로 조인되어 지지 않거나 필터링 조건에서 제외되면 A를 출력시키지 않는 것이죠.
 
도움이 되시길 바랍니다.


추가. 
아우터는 
이너 조인 
UNION ALL
안티 조인
으로, 이너 조인에 의한 교집합 + 안티조인에 의한 A에서 B를 제외한 여집합과 같은 결과를 리턴한다.

그리고 위와 같은 원리이기 때문에,
SELECT A.item,
A.item_name,
A.dept,
A.dept_nm
  FROM ITEM_CODE A,
  DEPT_CODE B
 WHERE A.dept = B.dept (+)
   AND NVL(B.dept_nm, 'ASCII') = 'ASCII'
 
등으로 (+) 대신 널일때를 대비한 처리를 해주면 쿼리를 변경시켜도 아우터는 유지된다. 


'1.DB > ORACLE' 카테고리의 다른 글

오라클 ROWNUM  (0) 2011.04.22
Posted by SITD
, |

NULLS FIRST/LAST

1.DB/SQL / 2011. 4. 25. 17:28

SELECT *
  FROM (SELECT '0' aa from dual UNION ALL
SELECT '1' aa from dual UNION ALL
SELECT '2' aa from dual UNION ALL
SELECT '3' aa from dual UNION ALL
SELECT NULL   FROM DUAL)
 ORDER BY aa desc nulls first
-- ORDER BY aa desc nulls last

-- 널의 위치를 지정한다. 

'1.DB > SQL' 카테고리의 다른 글

분석함수 및 KEEP을 정리해보자.  (0) 2011.11.14
Posted by SITD
, |

오라클 ROWNUM

1.DB/ORACLE / 2011. 4. 22. 10:18
우선 로우넘은 슈도컬럼이다.
보이지 않지만 편의를 위해 존재하는 컬럼. 이런게 몇개 있어요 rowid, rownum, ora_rowscn같은 것들..


rownum을 할당하는데는 순서가 있는데,

현재까지 이해하고 있는 걸론 연산 후 결과출력값 순서대로 값을 준다.
맨처음 리턴된 값에 1
그 다음 번 값에 2
이런 순서로 로우에 넘버를 주기 때문에 rownum = 2같은 경우엔 조건이 성립이 될수가 없다. 대상으로부터 첫 로우를 받기 전엔 rownum은 무조건 1인 상태니까.

로우넘을 설정하는 순서는
1. 소팅 연산이 없을 경우 = 디비에서 가져온 값 순서대로 rownum

2. 소팅 연산이 있을 경우 = 소팅의 결과로 가져온 값 순서대로 rownum
1) 디비에서 값을 가져온다 = 이때 디비에서 읽은 순서대로 rownum 설정
2) 디비에서 가져온 값을 정렬한다. = 정렬결과 리턴된 값 순서대로 다시 rownum설정. 이때 상단의 rownum은 사라지는건지 덮어씌어지는건진 모르겠지만 이미 사라진 이후. 



3. 특이 케이스라고 할 수 있는게 인덱스.
인덱스의 특징은 바로 정렬.
데이터를 정렬해야 하는데 해당 데이터의 정렬키가 인덱스와 동일하다면?
디비에서 가져올 때부터 인덱스 순서대로 정렬된 순서로 가져오면 되지 굳이 디비에서 값을 가져온 후 정렬 시키는 오버헤드를 할 필요가 없다 
그렇기 때문에 디비에서 가져오는 순서 = 정렬된 순서
가 되어 로우넘이 인덱스 순서대로 부과가 된다. 
물론, 오더바이 순서를 바뀌는 순간 로우넘은 뒤죽박죽. 


즉 1단계(order by)전과 후의 rownum이 다르다.
왜 이런가, 바로 ORDER BY의 수행 순서 때문이다.order by는 모든 대상을 찾은 다음에 수행되고 DB에서 읽어온 데이터의 rownum은 where 절을 기반으로 가져온 데이터 순서대로 이미 주어져 있기 때문이다.
그래서 인라인 뷰로 묶은 다음에 외부에서 로우넘을 읽어와야 정렬된 rownum을 얻을 수가 있다.

'1.DB > ORACLE' 카테고리의 다른 글

아우터 조인의 원리는?  (0) 2011.05.17
Posted by SITD
, |

최근에 달린 댓글

글 보관함