IT 지식 쌓기

예제 테이블

CREATE TABLE TEST(

A VARCHAR2(10),

B VARCHAR2(10)

);

INSERT INTO TEST VALUES ('2','516958');

INSERT INTO TEST VALUES ('1','123458');

INSERT INTO TEST VALUES ('1','123458');

INSERT INTO TEST VALUES ('1','458512');

INSERT INTO TEST VALUES ('2','468521');

INSERT INTO TEST VALUES ('2','735196');

INSERT INTO TEST VALUES ('1','794528');

RANK() 함수와 ROW_NUMBER() 함수 정의

RANK() 정의

RANK() 함수는 레코드 단위로 순차적으로 순위 (1부터 출력)를 부여하고 

레코드 단위로 같은 값에 대해서는 동일한 순위를 부여한다.

PARTIRION BY 를 사용하면 전체를 한 그룹으로 보는것이 아니라 PARTITION  BY 에 사용된

컬럼을 지준으로 다르게 그룹을 나누어 순위를 부여한다.

오라클 8i 부터 지원하는 분석함수

공동순위가 있으므로 순위에 비는 숫자가 생깁니다.

중복 값을 허용 하면서 비는 숫자를 없애려면

DENSE_RANK() 사용하면 된다.

RANK() 사용 예제

SELECT A, B, RANK() OVER(ORDER BY A, B) 순위 FROM TEST;

전체

A           B         순위

1     123458      1

1   123458      1

1   458512      3

1   794528      4

2   468521      5

2   516958      6

2   735196      7

SELECT A, B, RANK() OVER(PARTITION BY A ORDER BY A, B) 순위 FROM TEST;

PARTITION BY  적용  A 컬럼을 기준으로 그룹을 나눔

A           B         순위

1   123458      1

1   123458      1

1   458512      3

1   794528      4

2   468521      1

2   516958      2

2   735196      3

ROW_NUMBER() 정의

ROW_NUMBER() 는 레코드 단위로 동일한 값이라도 매번 새로운  순위를 부여한다.

ROW_NUMBER() 함수는 각 PARTITION 내에서 ORDER BY 절에 의해 

정렬된 순서로 유일한 값을 돌려주는 함수이면 ROWNUM 과는 관계가 없습니다.

오라클 8i 부터 지원하는 분석 함수

중복 순위가 없고 모든 값은 유일값을 가지게 된다.

SELECT A, B, ROW_NUMBER () OVER (ORDER BY A, B) 순번 FROM TEST;

전체

A           B         순번

1   123458     1

1   123458     2

1   458512     3

1   794528     4

2   468521     5

2   516958     6

2   735196     7

SELECT A, B, ROW_NUMBER() OVER(PARTITION BY A ORDER BY A, B) 순번 FROM TEST;

PARTITION BY  적용  A 컬럼을 기준으로 그룹을 나눔

A           B         순번

1   123458     1

1   123458     2

1   458512     3

1   794528     4

2   468521     1

2   516958     2

2   735196     3

NTILE () 정의

NTILE 함수는 쿼리의 결과를 N개의 그룹으로 분류하는 기능을 제공

SELECT A, B,

NTILE(2) OVER (ORDER BY A) 그룹1,

NTILE(3) OVER (ORDER BY A) 그룹2,

NTILE(5) OVER (ORDER BY A) 그룹3

FROM TEST;

결과 값 : 

* OVER() 함수는 필수 입니다.

* OVER() 함수안의 PARTITION BY 는 미필수 이지만 ORDER BY는 필수 이다.

* PARTITION BY 를 사용하면 출력결과를 하나의 그룹으로 보는것이 아니라

PARTITION BY 에 사용된 컬럼을 기준으로 그룹을 나누어지게 된다.

(순위도 그룹별로 별개로 1부터 부여된다.)

ROW_NUMBER() 응용 예제

A, B 그룹별 A, B 의 출력을 상위 2개만 출력하기

SELECT A, B FROM(

SELECT A, B, ROW_NUMBER() OVER (PARTITION BY A ORDER BY B) 전체 FROM TEST) 

WHERE 전체 <= 2;

결과 값 :

A        B

1 123458

1 123458

2 468521

2 516958

SELECT A, B FROM (

SELECT A, B, ROW_NUMBER() OVER (PARTITION BY A ORDER BY A, B) "중복 제외"

FROM TEST GROUP BY A, B) WHERE "중복 제외" <= 2;

결과 값 :

A        B

1 123458

1 458512

2 468521

2 516958

ROW_NUMBER  응용예제 2

직업별로 급여 합계를 계산해서 급여 합계가 많은 순으로 가장 많은 직업 3개만 출력하기

ROW_NUMBER 사용 안할시

SELECT JOB, "급여 합계" FROM 

(

SELECT JOB, SUM(SAL) "급여 합계" FROM EMP

GROUP BY JOB

ORDER BY "급여 합계" DESC

) 

WHERE ROWNUM < 4;

ROW_NUMBER  사용

SELECT JOB, "급여 합계" FROM

(

SELECT JOB, SUM(SAL) "급여 합계", 

ROW_NUMBER() OVER (ORDER BY SUM(SAL) DESC) NUM

FROM EMP

GROUP BY JOB

)

WHERE NUM < 4;

결과 값 : 

    JOB          결과 합계

MANAGER        8275

ANALYST        6000

SALESMAN        5600

PARTITION BY

PARTITION BY는 select한 결과를 특정 column을 기준으로 나누는 역할을 한다. 여기서는 userid를 기준으로 partition을 나눴다. 그리고 나뉜 partition은 score column을 기준으로 내림차순으로 정렬을 하고, 정렬된 내용을 RANK() OVER를 통해 순서를 매긴다. 마지막으로 한 번 더 select하여 상위 3개를 가져온다

테스트 환경

CREATE TABLE STUDENT (

"SID" NUMBER,

"NAME" VARCHAR2(32),

"EMAIL" VARCHAR2(32)

);

INSERT INTO STUDENT VALUES (582812, '한놈', 'a@a.a');

INSERT INTO STUDENT VALUES (582812, '한놈', 'hannom@example.com');

INSERT INTO STUDENT VALUES (582812, '한놈', 'b@b.b');

INSERT INTO STUDENT VALUES (241234, '두식이', 'dusigi@example.com');

INSERT INTO STUDENT VALUES (241234, '두식이', '');

INSERT INTO STUDENT VALUES (372342, '석삼', 'seoksam@example.com');

문제 상황

기본키가 없는 테이블 STUDENT 에서 SID 를 기본키로 하고 싶다.

ALTER TABLE STUDENT ADD CONSTRAINT SID_PK PRIMARY KEY (SID);

SQL 오류: ORA-02437: (SCOTT.SID_PK)을 검증할 수 없습니다 - 잘못된 기본 키입니다.

중복값 확인

SELECT * 

FROM (SELECT SID, COUNT(SID) CNT FROM STUDENT GROUP BY SID)

A WHERE CNT >1;

  SID       CNT

582812      3

241234      2

* SID 에 중복 값이 있다.

중복값 제거

방법 1) 최근 것을 남김

SID 별 입력 역순 조회

SELECT ROWID, 

RANK() OVER(PARTITION BY SID ORDER BY ROWID DESC) RNK,

STUDENT.* 

FROM STUDENT;

      ROWID                RNK      SID       NAME           EMAIL

AAASSdAAEAAAAKtAAE 1 241234 두식이 NULL

AAASSdAAEAAAAKtAAD 2 241234 두식이 dusigi@example.com

AAASSdAAEAAAAKtAAF 1 372342 석삼         seoksam@example.com

AAASSdAAEAAAAKtAAC 1 582812 한놈        b@b.b

AAASSdAAEAAAAKtAAB 2 582812 한놈        hannom@example.com

AAASSdAAEAAAAKtAAA 3 582812 한놈        a@a.a

RNK 가 2이상인 것듯을 삭제할 것이다.

삭제 대상 확인

SELECT * FROM STUDENT

WHERE ROWID IN 

(SELECT ROWID FROM(SELECT ROWID, 

  RANK() OVER (PARTITION BY SID ORDER BY ROWID DESC)RNK, 

  STUDENT.* FROM STUDENT)

  WHERE RNK > 1);

   SID      NAME            EMAIL

241234 두식이 dusigi@example.com

582812 한놈      hannom@example.com

582812 한놈       a@a.a

삭제 후 결과 확인

DELETE FROM STUDENT

WHERE ROWID IN 

(SELECT ROWID FROM(SELECT ROWID, 

  RANK() OVER(PARTITION BY SID ORDER BY ROWID DESC) RNK,

  STUDENT.* FROM STUDENT)

  WHERE RNK > 1);

* 3개행이 삭제 되었습니다.

SELECT * FROM STUDENT;

  SID       NAME         EMAIL

582812 한놈       b@b.b

241234 두식이      NULL

372342 석삼       seoksam@example.com

방법2) 잘  입력된 것을 남김

EMAIL 이 잘 입력된 순으로 조회

SELECT ROWID,

RANK() OVER(PARTITION BY SID ORDER BY LENGTH(EMAIL) DESC NULLS LAST,

ROWID DESC) RNK, STUDENT.* FROM STUDENT;

      ROWID                RNK      SID       NAME           EMAIL

AAASSfAAEAAAAK1AAD 1 241234 두식이 dusigi@example.com

AAASSfAAEAAAAK1AAE 2 241234 두식이 NULL

AAASSfAAEAAAAK1AAF 1 372342 석삼    seoksam@example.com

AAASSfAAEAAAAK1AAB 1 582812 한놈       hannom@example.com

AAASSfAAEAAAAK1AAC 2 582812 한놈       b@b.b

AAASSfAAEAAAAK1AAA 3 582812 한놈       a@a.a

RNK 가 2이상인 것들을 삭제할 것이다.

삭제 대상 확인

SELECT * FROM STUDENT

WHERE ROWID IN (SELECT ROWID FROM(SELECT ROWID,

  RANK() OVER(PARTITION BY SID ORDER BY LENGTH(EMAIL) DESC NULLS LAST,

  ROWID DESC) RNK, STUDENT.* FROM STUDENT)

  WHERE RNK > 1);

  SID       NAME       EMAIL

582812 한놈        a@a.a

241234 두식이      NULL

582812 한놈        b@b.b

삭제 후 결과 확인

DELETE FROM STUDENT

WHERE ROWID IN (SELECT ROWID FROM(SELECT ROWID,

  RANK() OVER(PARTITION BY SID ORDER BY LENGTH(EMAIL) DESC NULLS LAST,

  ROWID DESC) RNK, STUDENT.* FROM STUDENT)

  WHERE RNK >1 );

*3개 행이 삭제 되었습니다.

SELECT * FROM STUDENT;

  SID       NAME         EMAIL

582812 한놈    hannom@example.com

241234 두식이 dusigi@example.com

372342 석삼       seoksam@example.com

PK 제약조건 생성 테스트

이제 중복값이 없어 아래 SQL 문을 오류없이 실행할수 있다.

ALTER TABLE STUDENT ADD CONSTRAINT SID_PK PRIMARY KEY (SID);

* table STUDENT이(가) 변경되었습니다.

출처 : http://zetawiki.com/

DDL (Data Definition Language) : 데이터와 그 구조 정의 = 테이터 정의어

TABLE 정의

행과 열로 구성된 기본적인 저장매체의 단위

TABLE CREATE 주의사항

1. 테이블 이름을 지정하고 각 컬럼들은 괄호 ( ) 로 묶어 지정합니다.

2. 컬럼뒤에 데이터 타입은 꼭 지정되어야 합니다.

3. 각 컬럼들은 콤마  , 로 구분되고, 항상 끝은 세미콜론 ; 으로 끝납니다.

4. 한 테이블 안에서 컬럼이름은 같을 수  없을며 다른 테이블에서의 컬럼 이름과는 같을수 있습니다.

5. 테이블명을 쌍따옴표 " " 로 감싸지 않으면 기본적으로 대문자로 생성되며 

감싸면 입력한 그대로 생성이 된다.

TABLE CREATE 특이사항

1. 오라클에서는 테이블 생성시 컬럼을 1000개까지 생성이 가능하다.

2. 오라클에서는 테이블 생성 후 커밋 커리된다.

TABLE CREATE 명령

CHECK 제약 조건 : 컬럼에 입력되는 값을 체크함

컬럼 CHAR(1) CHECK (컬럼 = '0' OR 컬럼 = '1') ;

-- 데이터 삽입시 값을 체크해서 거짓이면 에러 발생

예제1)

CREATE TABLE TEST1

(AA CHAR(1) CHECK (AA = '0' OR AA = '1'));

-- 테이블 생성후 AA 컬럼에 문자열 2를 입력하면 에러 발생

예제2)

A CHAR(13) CHECK (컬럼 LIKE '____-__-__')

-- A 컬럼에 'YYYY-MM-DD' 형식으로만 입력가능

DEFAULT 제약조건 : 컬럼에 값이 입력 안될떄 자동으로 입력되는 값

예제1)

CREATE TABLE TEST3

(

A DATE DEFAULT SYSDATE,

B CHAR(10)

);

COMMIT;

INSERT INTO TEST3 (B) VALUES ('A') --날짜, A 입력됨

INSERT INTO TEST3 (A, B) VALUES (NULL, 'A') -- NULL 값, A 입력됨

INSERT INTO TEST3 (A, B) VALUES ('', 'A') -- NULL값, A 입력됨

TABLE CREATE EXAMPLE

예제1) PRIMARY KEY 생성 하지 않을시

CREATE TABLE A(

ID NUMBER(10),

NAME VARCHAR2(30)

);

예제2) PRIMARY KEY 생성 

CREATE TABLE B(

ID NUMBER(10) CONSTRAINT B_ID PRIMARY KEY,

NAME VARCHAR(30)

);

-- CONSTRAINT 이용해서 PRIMARY KEY 이름을 정할수 있다.

이름을 임으로 정하고 싶다면 적지 않아도 된다.

예제3) 제약 조건을 이용하지 않고 PRIMARY KEY 생성

CREATE TABLE C(

ID NUMBER(10),

NAME VARCHAR(30),

CONSTRAINT C_ID PRIMARY KEY (ID)

);

TABLE COPY

같은 계정에서의 TABLE COPY

TABLE A 가 있고 TABLE  B 를 생성하면서 복사하는 방법, 

하지만 제약조건(PRIMARY KEY, UNIQUE) 등은 별도로 해줘야 한다.

CREATE TABLE B AS SELECT * FROM A;

* B TABLE 을 만든 후 A TABLE 의 모든 데이터를 B TABLE 에 입력한다.

같은 서버에서 다른 계정으로 TABLE COPY (SYSTEM 계정에서 실행해야 한다.)

제약조건(PRIMARY KEY, UNIQUE) 등은 별도로 해줘야 한다.

CREATE TABLE SCOTT.B AS SELECT * FROM SC.A;

My SQL과 다르게 오라클에서는 Auto_Increment 명령어가 없다

그러므로 이 기능을 사용하기 위해서는 시퀀스를 생성해야한다.

Auto_Increment(인서트 자동증가) 시퀀스

CREATE SEQUENCE AAA START WITH 1 INCREMENT BY 1 MAXVALUE 100 CYCLE NOCACHE;

 밑줄 그어진 부분은 시퀀스 명이니 자유롭게 사용해도 된다.

사용방법

CREATE TABLE A (

id number(10),

name VARCHAR2(200)

);

라는 테이블을 만들었고 

이 테이블의 id라는 컬럼이 인서트가 될떄마다 자동 증가 되게 하고 싶다면

INSERT INTO board(id, name) values(AAA.NEXTVAL, 'aaa');

INSERT INTO board(id, name) values(AAA.NEXTVAL, 'bbb');

INSERT 문을 이렇게 구성하면 된다.

* 주의사항 :  시퀀스를 사용할 테이블은 시퀀스 보다 먼저 생성되어 있어야한다.

(시퀀스 생성 후의 시퀀스 사용 테이블을 생성하면 INSERT 시 카운트가 2부터 잡히는 경우가 발생)

초기화 방법

1) 시퀀스의 현재값

SELECT LAST_NUMBER FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'AAA';

결과값 : 7

2) 시퀀스의 INCREMENT 를 현재 값만큼 빼도록 설정 (시퀀스의 맥스 값 을 만들어 줘야한다.)

(최대값이 100일 경우 현재 값이 7이 라면 -7을 하여 최대값으로 바꿔준다)

ALTER SEQUENCE AAA INCREMENT BY -7;

3) 시퀀스에서 다음 값을 가져 온다

SELECT AAA.NEXTVAL FROM DUAL;

결과값 : 100

4) 시퀀스 INCREMENT 값을 1로 설정한다.

ALTER SEQUENCE AAA INCREMENT BY 1;

Outer Join 이란?

- Equi Join은 조인을 생성하려는 두 개의 테이블의 한쪽 컬럼에서 값이 없다면 테이터를 반환하지 못한다.

- 동일 조건에서 조인 조건을 만족하는 값이 없는 행들을 조회하기 위해 Outer Join을 사용 한다.

- Outer Join 연산자는 "(+)" 이다.

- 조인시 값이 없는 조인측에 "(+)"를 위치 시킨다.

- Outer Join 연산자는 표현식의 한 편에만 올 수 있다.

Outer Join 예제

Equi Join과 Outer Join의 비교.

-- Equi Join 으로 부서 번호를 조회하는 예제

SELECT DISTINCT(e.deptno), d.deptno, d.dname

  FROM emp e, dept d

 WHERE e.deptno = d.deptno;

 DEPTNO     DEPTNO

------ ----------

    10         10

    20         20

    30         30

====================================================================== 

-- Outer Join 으로 부서 번호를 조회하는 예제

SELECT DISTINCT(e.deptno), d.deptno

  FROM emp e, dept d

 WHERE e.deptno(+) = d.deptno;

DEPTNO  DEPTNO

 ------- --------

     10       10

     20       20

     30       30

              40

Outer Join을 사용하는 테이블에 추가로 조건절이 있다면 (+)연산자를 모두 해야 한다.

======================================================================

-- ename LIKE 조건절에 (+)연산자가 누락된 경우

SELECT DISTINCT(a.deptno), b.deptno

  FROM emp a, dept b

 WHERE a.deptno(+) = b.deptno

   AND a.ename LIKE '%';

DEPTNO     DEPTNO

---------- ----------

        10         10

        20         20

        30         30

====================================================================== 

-- ename LIKE 조건절에 (+)연산자를 추가해야 정상적으로 데이터가 조회 된다. 

SELECT DISTINCT(a.deptno), b.deptno

  FROM emp a, dept b

 WHERE a.deptno(+) = b.deptno

   AND a.ename(+) LIKE '%';

DEPTNO  DEPTNO

 ------- --------

     10       10

     20       20

     30       30

              40

======================================================================

LEFT, RIGHT, FULL Outer Join

Oracle9i 부터는 ANSI/ISO SQL 표준인 LEFT OUTER JOIN , RIGHT OUTER JOIN, FULL OUTER JOIN를 지원 한다.

LEFT OUTER JOIN

LEFT OUTERL JOIN은 오른쪽 테이블(아래 예제에서 emp테이블)에 조인시킬 컬럼의 값이 없는 경우 사용한다.

-- LEFT OUTER JOIN 조인 예제

SELECT DISTINCT(e.deptno), d.deptno

  FROM dept d 

  LEFT OUTER JOIN emp e

  ON d.deptno = e.deptno;  

RIGHT OUTER JOIN

RIGHT OUTERL JOIN은 왼쪽 테이블(아래 예제에서 emp테이블)에 조인시킬 컬럼의 값이 없는 경우 사용한다.

DEPTNO  DEPTNO

 ------- --------

     10       10

     20       20

     30       30

              40

======================================================================

-- RIGHT OUTER JOIN 조인 예제

SELECT DISTINCT(e.deptno), d.deptno

  FROM emp e 

 RIGHT OUTER JOIN dept d

    ON e.deptno = d.deptno;

FULL OUTER JOIN

FULL OUTERL JOIN은 양쪽 테이블 모두 Outer Join걸어야 하는 경우 사용 한다.

DEPTNO  DEPTNO

 ------- --------

     10       10

     20       20

     30       30

              40

======================================================================

-- FULL OUTER JOIN 조인 예제

SELECT DISTINCT(e.deptno), d.deptno

  FROM emp e 

  FULL OUTER JOIN dept d

    ON e.deptno = d.deptno;

DEPTNO  DEPTNO

 ------- --------

     10       10

     20       20

     30       30

              40

출처 : 구루비