양이의 기술블로그

ROLLUP

롤업구문은 GROUP BY 절과 같이 사용된다.

GROUP BY절에 의해서 그룹지어진 집합 결과에 대해서 좀 더 상세한 정보를 얻을수 있다.

SELECT 절에 ROLLUP을 사용하면 데이터의 총계를 구할수 있다.

TEST_JOB 데이터로 예제를 만들어 보자

SELECT
*
FROM TEST_JOB
ORDER BY JOB;

TEST_JOB 조회시 결과

GROUP BY 를 사용해서 직업별로 급여 합계를 구해보자

SELECT
   JOB
  ,SUM(SAL)
FROM TEST_JOB
GROUP BY JOB;

결과를 보면 JOB별 SAL 의 합계가 조회된다.

ROLLUP 을 사용해서 JOB별 SAL합계와 총계를 구해보자.

SELECT
  JOB
  ,SUM(SAL)
FROM TEST_JOB
GROUP BY ROLLUP (JOB);

결과를 보면 급여합계의 총계가 추가되었다.

ROLLUP을 이용하여 부서별 SAL의 소계와 총계를 구해보자.

SELECT
  DEPTNO
  ,JOB
  ,SUM(SAL)
FROM TEST_JOB
GROUP BY ROLLUP (DEPTNO,JOB);

결과 

GROUPING()

소계,합계를 구분하고자 할때 GROUPING(컬럼) 또는 GROUPING_ID(컬럼1,컬럼2)를 사용한다. 

GROUPING()함수는 해당 컬럼이 집합에 참여하지 않으면 1을 반환, 집계계산에 참여하면 0을 반환한다.

SELECT
  DEPTNO
  ,JOB
  ,SUM(SAL)
  ,GROUPING(DEPTNO)
  ,GROUPING(JOB)
  ,GROUPING_ID(DEPTNO,JOB)
FROM TEST_JOB
GROUP BY ROLLUP (DEPTNO,JOB);

결과

oracle sequence 생성,조회

생성 옵션값에 대해 알아보자.

CREATE SEQUENCE 시퀀스명

START WITH n[초기화 값]

INCREMENT BY n[증가값]

MAXVALUE n[최대값]  또는 NOMAXVALUE  [무한대값]

MINVALUE n [최소값]  또는 NOMINVALUE [무한대값]

그럼 생성해보자

시퀀스명 = test_seq 

초기값 = 1

증가값 = 1

최대값 = 500

create sequence test_seq 

start with 1 

increment BY 1

maxvalue 10000

생성된 test_seq 사용하는 방법

insert into test (id_seq, name )

values(test_seq.nextval, 'HI' );

insert문 실행후 최종(현재) 시퀀스값 조회

select 시퀀스명.currval from dual;

insert문 실행 없이 최종 시퀀스값 조회

select * from user_sequences

where sequence_name=upper('시퀀스명')

결과 데이터에서 last_number 가 현재 시퀀스값을 의미함.

시퀀스 초기화 할시 현재 시퀀스를 삭제후 다시 생성해야함.

oracle 인덱스 조회

SELECT A.uniqueness,
         b.*
FROM ALL_INDEXES a,
       ALL_IND_COLUMNS b
WHERE a.index_name = b.index_name
AND a.table_name=upper('TABLE_NAME');   // 테이블명

ORACLE PIVOT

PIVOT 기능은 11g부터 사용가능하다.

이하버전은 DECODE 이용하여 ROW 을 컬럼으로 변경하는 작업을 했다.

PIVOT 를 이용하면 DECODE 의 복잡하고 비직관적인 코드를 좀더 직관적으로 작성할 수 있다.

주의할점 !

PIVOT 사용시 해당하는 컬럼을 미리 정의해야된다.

상황에 따라 DECODE , PIVOT 사용한다.

예제 !

가상테이블 이용해 2017년 DATE 구하여 PIVOT,DECODE을 각각 사용하여 요일갯수 SUM 해봄.  

* 가상테이블

WITH TEMP_TABLE AS (

SELECT (TO_DATE('20140101','YYYYMMDD') + LEVEL-1) DTE

      FROM DUAL

      CONNECT BY LEVEL-1 <= TO_DATE('20171231','YYYYMMDD') - TO_DATE('20170101','YYYYMMDD')

)

* PIVOT

SELECT *

FROM (

  SELECT to_char(DTE,'MM') || '월' MON

              , decode(to_char(DTE,'D'),'1','일','2','월','3','화','4','수','5','목','6','금','7','토') WEEK

              , DTE

  FROM TEMP_TABLE

)

PIVOT

(

COUNT(DTE)

FOR WEEK IN ('일','월','화','수','목','금','토')

)

ORDER BY MON;

* DECODE

SELECT to_char(DTE,'MM') || '월' MON

  , sum(decode(to_char(DTE,'D'),'1',1,0)) "일"

  , sum(decode(to_char(DTE,'D'),'2',1,0)) "월"

  , sum(decode(to_char(DTE,'D'),'3',1,0)) "화"

  , sum(decode(to_char(DTE,'D'),'4',1,0)) "수"

  , sum(decode(to_char(DTE,'D'),'5',1,0)) "목"

  , sum(decode(to_char(DTE,'D'),'6',1,0)) "금"

  , sum(decode(to_char(DTE,'D'),'7',1,0)) "토"

FROM TEMP_TABLE

GROUP BY to_char(DTE,'MM')

ORDER BY MON;

* 결과값 !

MON 일    월     화     수      목     금      토

01월 4 4 4 5 5 5 4

02월 4 4 4 4 4 4 4

03월 5 5 4 4 4 4 5

04월 4 4 5 5 4 4 4

05월 4 4 4 4 5 5 5

06월 5 5 4 4 4 4 4

07월 4 4 5 5 5 4 4

08월 5 4 4 4 4 5 5

09월 4 5 5 4 4 4 4

10월 4 4 4 5 5 5 4

11월 5 4 4 4 4 4 5

12월 4 5 5 5 4 4 4

 ALTER TABLE 

-기존테이블에 컬럼추가시

ALTER TABLE 테이블명

ADD(컬럼명 데이터형식)

ALTET TABLE TEST

ADD(addr varchar2)

- 기존테이블에 컬럼추가 및 데이터 추가시

ALTER TABLE 테이블명

ADD(컬럼명 AS 넣을 데이터)

ALTER TABLE TEST

ADD(addr AS addr1+addr2)

위 쿼리를 사용하면 하나의 테이블로 반환됨

주의사항

- 각 Select 문의 열 수는 같아야함, 

- 각 Select 문의 표현식과 집계 함수도 같아야 한다.

- Select문의 순서는 중요하지 않다. 순서는 결과에 영향을 미치지 않는다.

- 유니온의 결과에서 중복값은 하나로 나오는 것이  기본 설정

- 열의 데이터 타입은 같거나 서로 변환 가능한 값이어야함

- 중복값을 보고 싶을땐 UNION ALL 사용함

- Order By의 경우 유니온의 맨 끝에 하나만 받을 있음

UINON 으로 테이블 만들기

출쳐 : http://warmz.tistory.com/278