⏰ 2024.10.28 (D+13)
1. OVER함수
🔖 중요]
- GROUP BY, ORDER BY를 이용한 서브 쿼리를 개선하기 위해 나온 함수
- OVER함수의 인자로 PARTITION BY를 사용하여 그룹으로 묶어서 쿼리를 수행
- 순위 분석 함수 사용시 OVER함수의 인자로 ORDER BY절을 함께 사용하여 위대로 정렬된 결과셋 가능
- COUNT(), MAX(), MIN(),SUM(), AVG()등의 일반 집계 분석함수 와 RANK(),ROW_NUMBER()등의 순위 분석함수와 함께 사용
- GROUP BY 절을 사용하지 않고 검색된 각 행에 파티션(그룹)으로 집계된 값을 표시할 때 OVER 절과 함께 PARTITION BY 절을 사용
ORDER BY절 사용 순위 미 포함 예시 📖]
SELECT
ename,
sal,
ROWNUM
FROM
emp
ORDER BY sal DESC;2) OVER()함수로 순위 포함 예시 📖]
SELECT
ENAME,
SAL,
FROM EMP;
💡 순위 분석함수인 ROW_NUMBER()와 RANK()차이
1) ROW_NUMBER() OVER (ORDER BY sal DESC) 연봉순위
: 각 PARTITION 내에서 ORDER BY절에 의해 정렬된 순서를 기준으로 고유한 값을 반환하는 함수
2) RANK() OVER (ORDER BY sal DESC) 연봉순위
: RANK 함수는 동일한 값이면 중복 순위를 부여하고, 다음 순위는 해당 개수만큼 건너뛰고 반환하는 함수
3) DENSE_RANK() OVER (ORDER BY sal DESC) 연봉순위
: DENSE_RANK 함수는 동일한 값이면 중복 순위를 부여하고, 다음 순위는 중복 순위와 상관없이 순차적으로 반환하는 함수
문제 📙] 모든 사원들을 대상으로 그 사원이 속한 부서의 연봉 최소, 최대, 총 연봉,연봉 평균, 연봉 순위를 출력
서브쿼리를 이용한 예시 📖]
SELECT
ENAME,
DEPTNO,
SAL,
(SELECT MIN(SAL) FROM EMP WHERE DEPTNO=E.DEPTNO) 최소,
(SELECT MAX(SAL) FROM EMP WHERE DEPTNO=E.DEPTNO) 최대,
(SELECT SUM(SAL) FROM EMP WHERE DEPTNO=E.DEPTNO) 총연봉,
(SELECT AVG(SAL) FROM EMP WHERE DEPTNO=E.DEPTNO) 부서내_평균연봉
FROM EMP E
ORDER BY ENAME;OVER() 함수를 이용한 예시 📖]
SELECT
ENAME,
DEPTNO,
SAL,
MIN(SAL) OVER (PARTITION BY DEPTNO) 최소,
MAX(SAL) OVER (PARTITION BY DEPTNO) 최대,
SUM(SAL) OVER (PARTITION BY DEPTNO) 총연봉,
AVG(SAL) OVER (PARTITION BY DEPTNO) 평균연봉,
RANK() OVER (ORDER BY SAL DESC) 회사내연봉순위,
RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) 부서내연봉순위
FROM EMP E
ORDER BY ENAME;
GROUP BY를 사용하는 예시 📖]
SELECT
DEPTNO,
MIN(SAL),
ENAME,
JOB
FROM EMP
GROUP BY DEPTNO, ENAME, JOB;OVER()함수를 사용하는 예시 📖]
SELECT
E.*,SUM(SAL) OVER (PARTITION BY JOB) AS total_SAL
FROM
EMP E;
2. 계정 생성 및 권한부여
1. SYSTEM 계정에서 신규 계정 USER01 생성
CREATE USER USER01 IDENTIFIED BY USER01;2. USER01 권한 부여
GRANT connect, resource TO USER01;3. USER01 할당량 부여
ALTER USER USER01 QUOTA UNLIMITED on Users;
3. 테이블 및 데이터 생성 및 삭제
1. 테이블 생성
CREATE TABLE MEMBER(
USERNAME VARCHAR2(10) CONSTRAINT PK_MEMBER PRIMARY KEY,
PASSWORD VARCHAR2(10) NOT NULL,
NAME NVARCHAR2(10));2. 테이블 조회
SELECT * FROM MEMBER;
3. 데이터 삽입
INSERT INTO MEMBER VALUES('KIM','1234','김길동'); //정상적 데이터 삽입[O]
INSERT INTO MEMBER VALUES('KIM','1234','이길동'); //중복된 데이터 삽입[X]
INSERT INTO MEMBER(PASSWORD, NAME) VALUES('1234','이길동'); //NULL인 데이터 삽입[X]4. 테이블 삭제
DROP TABLE MEMBER;
📖 TABLE PRIMARY KEY 키 설정 예시 ]
CREATE TABLE MEMBER(
USERNAME VARCHAR2(10) PRIMARY KEY,
PASSWORD VARCHAR2(10) NOT NULL,
NAME NVARCHAR2(10));CREATE TABLE COMPOSITKEY(
COL1 NUMBER,
COL2 VARCHAR2(10),
COL3 VARCHAR2(10),
CONSTRAINT PK_COMPOSITKEY PRIMARY KEY(COL1,COL2));🖥️ COMPOSITKEY TABLE 데이터 삽입하는 방법 ]
INSERT INTO compositkey VALUES(1,'DATA1','CALDATA1');
INSERT INTO compositkey VALUES(1,'DATA2','CALDATA2');
INSERT INTO compositkey VALUES(2,'DATA1','CALDATA2');🔔 오류 원인 ]
INSERT INTO compositkey VALUES(1,'DATA2','CALDATA2');
[X] 동일한 데이터를 이미 삽입했기 때문에 오류 발생
