[SIST] Oracle_days08

오늘도 여러 문제를 풀면서 새로운 개념을 배웠다.

응용되는 문제는 git에 업로드 하였고, 새롭게 배운 내용들만 정리하였다.

---

1. FIRST_VALUE, LAST_VALUE 분석 함수

FIRST_VALUE는 분석 함수로 정렬된 값중에서 [현재 행까지의 첫 번째 값]을 반환한다.
만약 첫번째가 NULL이라면, IGNORE NULLS를 지정하지 않았다면 NULL을 반환하게 된다.
LAST_VALUE 함수도 분석 함수로 정렬된 값중에서 [현재 행까지의 마지막 값]을 반환하는 함수이다.

 

【형식】
FIRST_VALUE ¦ LAST_VALUE (expr [IGNORE NULLS] )
OVER (

  [PARTITION BY expr2] [,...]

   ORDER BY expr3 [collate_clause] [ASC ¦ DESC] [NULLS FIRST ¦ NULLS LAST])
) 

 

예시)

> 위의 결과를 보면 LAST_VALUE() 함수는 현재 행의 마지막 값을 출력하고 있어 결과가 계속 바뀌는 것을 확인할 수 있다.

---

2. GROUP BY 절과 HAVING 절

- HAVING 절은 GROUP BY 절과 꼭 같이 사용해야된다.

- HAVING 절은 GROUP BY 절로 묶여진 결과 내에서 조건을 주는 절이다.

- 주의! SELECT 절에 있는 집계함수 외에 모든 것들은 GROUP BY 절에 넣어야 한다.

 

예시)

 

문제1) insa테이블에서 여자인원수가 5명 이상인 부서만 출력

 

문제2) emp 테이블에서 20번, 40번 부서는 제외 후 그 외 부서의 사원수를 계산하고 그 사원수가 4명 이상인 부서정보를 부서번호, 부서명, 사원수 출력

 

문제3) insa 테이블에서 남자사원들만 부서별로 사원수를 구해서 6명 이상인 부서만 출력

---

3. GROUP BY 절에서 ROLLUP과 CUBE 조건

1) ROLLUP 연산자
        (1) ROLLUP은 GROUP BY 절의 그룹 조건에 따라 전체 행을 그룹화 하고
        (2) 각 그룹에 대해 부분합을 구하는 연산자이다.

2) CUBE 연산자
     (1) ROLLUP 연산자를 수행한 결과에 더해 GROUP BY 절에 기술된 조건에 따라 모든 가능한 그룹핑 조합에 대한 결과를 출력한다.

 

GROUP BY 조건이 1개면 다를게 없지만 조건이 여러 개이면 결과 값이 달라진다.
GROUP BY 뒤에 기술한 컬럼이 2개일 경우 ROLLUP은 n+1에서 3개의 그룹별 결과가 출력되고, CUBE는 2*n에서 2*2=4개의 결과 셋이 출력된다.
        예) buseo, jikwi 조건 2개
            ROLLUP = 2+1 (부서별 사원수, 직위별 사원수, 총사원수)
            CUBE = 2*2 (부서+직위 사원수, 부서별 사원수, 직위별 사원수, 총사원수)

 

문제1) insa 테이블에서 남자사원수, 여자사원수를 출력

> 그룹화한 부분합을 따로 구해서 UNION을 사용할 필요 없이 ROLLUP 연산자를 사용하면 부분합이 구해진다.

 

아래는 CUBE 연산자를 사용한 결과이다. 자세한 차이점은 다른 예제를 통해서 살펴보자~

 

문제2) insa 테이블에서 아래와 같이 부서별 사원수를 구하고 전체 사원수 구하기

[ROLLUP 연산자 사용]

 

[CUBE 연산자 사용]

 

ROLLUP 연산자와 CUBE 연산자를 사용해서 출력한 결과를 보면 다른 점이 있다.

ROLLUP은 조건을 준 부서와 직위에 대해서만 적용이 되어 부서별 합, 부서 내의 직위별 합, 총합을 구하고

CUBE 연산자는 모든 가지 수에 대해서 합을 구하기 때문에 직위별 합도 출력하게 된다.

---

4. PIVOT 함수

 1) Oracle 11g 버전부터 제공하는 함수
 2) 행과 열을 뒤집는 함수
 3) 반대로 뒤집는 건 언피벗이라고 한다.

 

[PIVOT 형식]
    SELECT * 
    FROM (피벗 대상 쿼리문) -- 서브쿼리
    PIVOT (그룹함수(집계컬럼) FOR 피벗컬럼 IN(피벗컬럼 값 AS 별칭...))

 

예시1) emp 테이블에서 job 별로 총 사원수 구하는 쿼리

 

예시2) emp 테이블에서 job 별 1월~12월에 입사한 사원의 수 구하는 쿼리

 

문제1) insa 테이블에서 성별로 사원수를 조회

---

5. TRIM 함수

[형식] TRIM('특정문자' FROM 문자열)

- Java의 trim 함수와 같이 특정문자를 앞뒤로 제거하는 함수이다.

 

예시)

---

6. NLS 란??

- National Language Support의 약자

- NLS parameter는 SESSION, CLIENT, SERVER의 세 가지로 분류된다.

- 이 세 가지 분류의 우선 순위는 다음과 같다.
   SESSION > CLIENT > SERVER

 

- server, client, session의 환경이 서로 다르다면, session에서 설정한 환경을 따라 가게 된다.

예) 오라클 서버가 미국에 있고(서버) 나는 한국에서 접속(클라이언트)해서 날짜를 찍어보면 한국형 날짜가 찍힌다(클라이언트) 만약, 로그인하고 로그아웃할 때 까지 날짜형식을 일본날짜로 했으면 세션동안 일본날짜가 찍힌다.

 

예시)

nls_session_parameters의 종류는 아래와 같다.