*join 은 두가지로 (내부/외부)
내부는 조인조건을 만족하지않는 모든 조건을 다 무시해버림
외부는 조인조건이 부족하면 거기에 null을 채워넣는다.
ex 2005-5의 판매정보를 조회
안팔린거빼고 팔린것만 조회 - 내부조인
안팔렸어도 거래코드있는거 다 0으로 하는게 외부조인
count는 *을 쓰면 null이어도 0이 아니고 1로 센다.
그래서 외부조인에서 count쓸때는 반드시 *대신 컬럼명을 써야한다.
부서별 급여합계를 구하면 마지막 전체합계를 어케구하나?
쿼리하나에서는 방법이없음 전체합은 그룹바이를 안쓰고 전체를 하나의 그룹으로 묶어야하기때문
그래서 부분합계를 전체합계로 되돌려주는 함수 : rollup, cube
ROLLUP
- 주어진 컬럼명을 기준으로 레벨별로 구분하여 집계를 반환
- 전체 합계 반환
*사용형식
ROLLUP(컬럼명1 [,컬럼명2,...])
- ROLLUP은 GROUP BY절에 기술
- 기술한 컬럼명의 수와 순서(오른쪽→왼쪽)에 따라 레벨별 집계를 반환
- 기술된 컬럼의 수가 n개이면 n+1레벨(+1:전체집계를 의미)까지 하위레벨에서 상위레벨 순으로 집계를 반환 함
-2개 컬럼이라고 치면 컬럼2를 가지고 집계낸 후 컬럼1로 집계내고, 그다음에 전체를 가지고 집계를 낸다.
EX
KOR_LOAN_STATUS테이블에서 2013년도 지역별, 구분별 대출잔액을 조회하시오
1)ROLLUP 없이
SELECT SUBSTR(PERIOD,1,4) 년도,
REGION 지역,
GUBUN 대출구분,
SUM(LOAN_JAN_AMT) 잔액합계
FROM KOR_LOAN_STATUS
GROUP BY SUBSTR(PERIOD,1,4),
REGION,
GUBUN
ORDER BY 1,2;
2) ROLLUP을 적용해보자
SELECT SUBSTR(PERIOD,1,4) 년도,
REGION 지역,
GUBUN 구분,
SUM(LOAN_JAN_AMT) 잔액합계
FROM KOR_LOAN_STATUS
GROUP BY ROLLUP(SUBSTR(PERIOD,1,4),
REGION,
GUBUN)
ORDER BY 1,2;
젤 하위레벨인 구분을 가지고 집계를 먼저 내준다.
그담엔 지역가지고 집계
그래서 롤업으로 3개컬럼을 사용했다? 하위레벨>중간거>상위레벨 집계내고 맨마지막 전체집계(+1)이 된다
**부분 ROLLUP
GROUP BY 절에서 ROLLUP밖에 컬럼이 기술되어지는 경우
GROUP BY expr1, ROLLUP(expr2,expr3)인 경우
(expr1, expr2, expr3), (expr1, expr2), (expr1) 순으로 집계 (expr1은 롤업바깥에 있으니까 계속 붙어다님)
결국 전체 집계를 제공되지 않음
즉 ROLLUP(expr1,expr2,expr3)과 똑같은데 단, 부분롤업은 전체집계만 안나온다는 점이 다르다
GROUP BY ROLLUP (1,2)3 은
(1,2,3),(1,3),(3) 이런식으로 만들어짐
GROUP BY SUBSTR(PERIOD,1,4),
ROLLUP(
REGION,
GUBUN)이렇게 기간을 밖으로 뺴면 그룹바이절엔 두개의 항목이 써진거나 다름없다.
왜 전체집계만 빠질까?
기간이 아까 가장 상위레벨이었는데 빼는것도 롤업 왼쪽으로 뺐으니까 롤업보다도 더 먼저 구분되어지는 컬럼임
2. CUBE
- 조합가능 한 모든 집계를 다 낸다 즉 3개컬럼? 2^3가지의 집계종류
- ROLLUP과 같이 다양한 집계 제공함수
- 기술된 컬럼들의 조합 가능한 모든 조합별 집계를 반환
- 기술된 컬럼이 n개이면 집계 결과의 종류는 2의 n승 가지만큼 반환
- CUBE도 GROUP BY정레서 기술 한다.
*사용형식
CUBE(컬럼명 [,컬럼명,...])
ROLLUP 사용한경우
컬럼이 2개면 3가지가 나옴 (각각하나씩+전체집계)
큐브는 4개
1로집계 2로집계 두개로 집계 전체를 합해서 집계
3개로 써보자
ROLLUP의 경우
SELECT SUBSTR(PERIOD,1,4) 년도,
REGION 지역,
GUBUN 구분,
SUM(LOAN_JAN_AMT) 잔액합계
FROM KOR_LOAN_STATUS
GROUP BY ROLLUP(
SUBSTR(PERIOD,1,4),
REGION,
GUBUN
)
ORDER BY 1,2;
CUBE의 경우
SELECT SUBSTR(PERIOD,1,4) AS 년도,
REGION 지역,
GUBUN 구분,
SUM(LOAN_JAN_AMT) 잔액합계
FROM KOR_LOAN_STATUS
GROUP BY CUBE(
SUBSTR(PERIOD,1,4),
REGION,
GUBUN)
ORDER BY 1,2;
행의 갯수에 따라 집계종류가 기하급수적으로 늘어남
NULL 처리함수
- 데이터 중 NULL값에 따라 연산결과가 달라짐
- 오라클의 모든 컬럼은 자료가 입력되지 않으면 모두 NULL으로 초기화 됨
- IS [NOT]NULL, NVL, NVL2, NULLIF 등이 제공
1. IS [NOT]NULL
클래스안, 메소드 밖에서 선언되어지는 변수가 인스턴스변수. (지역변수가 아닌 변수들)은 사용자가 굳이 초기화 시키지 않아도 됨. 자바와 달리 오라클은 빈값? 에 전부 NULL이 들어감 (숫자형 문자형 어쩌구 구분없음)
문자형일때 뿐만 아니라 숫자나 날짜형일때도 NOT NULL 쓸수있음 반대로 얘기하면 숫자나문자날짜에 데이터가 들어오지않앗을때 자동적으로 집어넣어지는값이 NULL임
이 NULL이 연산에 사용되어지면 무조건 결과가 NULL임
자구 헷갈리는거! = NULL은 안됨! NULL에는 =못씀 널여부는 IS를 써줘야 함
- 계산의 결과나 컬럼에 저장된 값이 NULL인지 여부 판단
- NULL은 '='연산자로 동등성 여부를 판단할 수 없음
- IS [NOT] NULL, NVL(특정컬럼값이나 계산식의 값이 널인지여부에따라 널이면 사용자가 정의한 두번째 매개변수값 가져오고 널아니면 그냥 그값가져옴), NVL2(NVL 기능확장. NVL은 매개변수 두개 2는 3개 컬럼값을 판단해서 널아니면 마지막 매개변수 널이면 중간거), NULLIF(몇번째가 널인지 어떤 컬럼값이 널인지) 등이 제공
*사용형식
컬럼명 IS [NOT] NULL
EX
사원테이블에서 영업실적 코드(COMMISSION_PCT) 가 NULL이 아닌 사원을 조회하시오
SELECT EMPLOYEE_ID 사원번호,
EMP_NAME 사원명,
DEPARTMENT_ID 부서코드,
COMMISSION_PCT 영업실적
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL;
2. NVL (가장 많이 씀)
- NULL 여부에 따라 다른 값을 반환할 때 사용
*사용형식
NVL(c, val) C는 우리가 사용하는 수식이나 컬럼, VAL은 값 (C의 타입과 VAL의 타입이 같아야함)
- 'c'가 NULL이 아니면 c값을, NULL이면 val값을 반환
- 'c'의 데이터 타입과 val의 데이터 타입은 동일해야 함
EX
상품코드테이블 (PROD)에서 상품의 색상(PROD_COLOR)에 저장된 값이 없으면 (NULL이면) 그 값을 '색상정보 없음'으로 대차하시오
SELECT PROD_ID 상품코드,
PROD_NAME 상품이름,
NVL(PROD_COLOR,'색상정보없음') 색상코드
FROM PROD;
만약 이걸 업데이트 (색상정도 널만 색상정보없음으로 업데이트)하라고 한다면?
우선
컬러가 NULL인 상품만 출력
SELECT PROD_ID 상품코드,
PROD_NAME 상품이름
FROM PROD
WHERE PROD_COLOR IS NULL;
UPDATE PROD
SET PROD_COLOR=NVL(PROD_COLOR,'색상정보없음');
다시 검사
SELECT PROD_ID 상품코드,
PROD_NAME 상품이름
FROM PROD
WHERE PROD_COLOR IS NULL;
*상품테이블에서 분류코드 'P201' 전체 재고량(PROD_TOTALSTOCK)의 값을 NULL로 바꾸시오.
UPDATE PROD
SET PROD_TOTALSTOCK=NULL
WHERE UPPER(PROD_LGU)='P201';여기서 =NULL은 '같다'는 뜻이 아님 '할당'이므로 사용가능
근데 TOTALSTOCK 의 디폴트값때문에 적용오류임 NULL이 아닌 다른 값으로는 가능하다
EX
사원테이블에서 부서코드가 NULL인 사원을 조회하시오
Alias 는 사원번호, 사원명, 부서코드, 비고
**함정!! c와 val은 데이터타입이 맞아야한다!!!
DEPARTMENT_ID는 넘버타입, 우리가 바꾸려는건 문자열 ('임시직')이니까 TO_CHAR로 바꿔줘야함
SELECT EMPLOYEE_ID 사원번호,
EMP_NAME 사원명,
NVL(TO_CHAR(DEPARTMENT_ID),'임시직') 비고
FROM EMPLOYEES;
EMPLOYEES 테이블엔 사원의 직속상관 사원번호(MANAGER_ID)가 같이 있다. (별도의 테이블을 만들지않고)
마찬가지로 DEPARTMENT테이블에도 상부부서번호가 같이 써있다
"재귀"
5! (펙토리얼)은 5*4*3*2*1 즉 5와 자기자신보다 하나 작은값을 곱해준다.
오라클은 자바에서 나오는것처럼 재귀를 이용한 매소드는 없지만
EX 부서코드의 PK 는 DEPARTMENT_ID인데, 본인이 본인하고 관계를 맺음 (재귀, 자기참조)
EX
2005년 6월 모든 상품에 대한 판매실적을 조회하시오(모든 : OUTER JOIN)
(판매됐든 안됐든 간에 모든 상품에 대해 판매실적을 조회해봐라) 상품하나가 여러번 팔렸으면 여러번을 합쳐서 하나로 내보내라.는얘기 >> ~별이 안들어갔다 할지라도 GROUP BY 가 필요함
>내부조인으로는 절대못푼다
Alias는 상품명, 판매수량합계, 판매금액합계
상품판매에 관련된 정보는 CART 상품명은 CART에 없음 PROD가 필요함..
매출뒤져봐도 상품테이블에있는 상품이 하나씩다팔렸으면 둘이 같지만 안팔린상품이잇다면 카트가 더 적을수잇음
즉 어떤경우에도 상품에 대한 정보가 더 많은 테이블은 PROD테이블임
SELECT B.PROD_NAME 상품명,
SUM(A.CART_QTY) 판매수량합계,
SUM(A.CART_QTY * B.PROD_PRICE) 판매금액합계
FROM CART A
RIGHT OUTER JOIN PROD B ON(A.CART_PROD=B.PROD_ID
AND A.CART_NO LIKE '200506%')
GROUP BY B.PROD_NAME
ORDER BY 1;
이건 잠깐 확인
--2005.6월에 판매된 상품의 종류
SELECT DISTINCT CART_PROD
FROM CART
WHERE CART_NO LIKE '200506%';
SELECT B.PROD_NAME 상품명,
SUM(NVL(A.CART_QTY,0)) 판매수량합계,
SUM(A.CART_QTY * B.PROD_PRICE) 판매금액합계이렇게 바꿔주면
SELECT B.PROD_NAME 상품명,
COUNT(A.CART_QTY) 판매횟수,
SUM(NVL(A.CART_QTY,0)) 판매수량합계,
SUM(A.CART_QTY * B.PROD_PRICE) 판매금액합계COUNT를 넣어서 판매ㅅ횟수도 세보면
SELECT B.PROD_NAME 상품명,
COUNT(A.CART_QTY) 판매횟수,
SUM(NVL(A.CART_QTY,0)) 판매수량합계,
NVL(SUM(A.CART_QTY * B.PROD_PRICE),0) 판매금액합계
NVL을 하나하나 넣어주기 힘들면 맨 바깥괄호에 NVL 해도 됨
'학원수업_Oracle' 카테고리의 다른 글
| 201022 JOIN (0) | 2020.10.22 |
|---|---|
| 201022 Null처리 함수 NVL2 (0) | 2020.10.22 |
| 201020 Oracle (0) | 2020.10.20 |
| 201019 Oracle (0) | 2020.10.19 |
| 201016 Oracle 형변환연산자 (CAST, TO_CHAR, TO_NUMBER, TO_DATE) (0) | 2020.10.16 |