0809_Oracle : 분석함수, 데이터 정의언어

-- SUM() OVER() 함수
SELECT empNo, name, dept, pos, sal,
    SUM( sal ) OVER (ORDER BY empNo) 앞누적
FROM emp;

SELECT empNo, name, dept, pos, sal,
    SUM( sal ) OVER (ORDER BY dept) 부서별앞누적
FROM emp;

SELECT empNo, name, dept, pos, sal,
    SUM( sal ) OVER (PARTITION BY dept) 부서총급여
FROM emp;

SELECT empNo, name, dept, pos, sal,
    SUM( sal ) OVER (PARTITION BY dept ORDER BY empNo) 부서개인별누적
FROM emp;

SELECT empNo, name, dept, pos, sal,
    SUM( sal ) OVER (PARTITION BY dept ORDER BY pos) 부서직위별
FROM emp;

-- name, dept, sal, 부서총급여에대한백분율
SELECT name, dept, sal,
    ROUND ( SAL / SUM ( sal ) OVER( PARTITION BY DEPT) * 100 ) 부서별비율
FROM emp;

-- 부서명 성별 인원수 부서성별백분율
SELECT dept, DECODE( MOD( SUBSTR(rrn, 8, 1), 2), 0, '여자', '남자') 성별, 
    COUNT(*) 인원수,
    SUM( COUNT(*) ) OVER( PARTITION BY dept) 부서인원
FROM emp
GROUP BY dept, DECODE( MOD( SUBSTR(rrn, 8, 1), 2), 0, '여자', '남자');

SELECT dept, DECODE( MOD( SUBSTR(rrn, 8, 1), 2), 0, '여자', '남자') 성별, 
    COUNT(*) 인원수,
    ROUND( COUNT(*) / SUM( COUNT(*) ) OVER( PARTITION BY dept) * 100 ) ||'%'비율
FROM emp
GROUP BY dept, DECODE( MOD( SUBSTR(rrn, 8, 1), 2), 0, '여자', '남자');

-- AVG() OVER() 함수
SELECT name, dept, sal, ROUND( AVG(sal) OVER()) 전체평균
FROM emp;

SELECT name, dept, sal, sal - ROUND( AVG(sal) OVER()) "전체평균과 차이"
FROM emp;

SELECT name, dept, sal, ROUND( AVG(sal) OVER( ORDER BY empNo)) 
FROM emp; -- 처음부터 나까지 평균 // 처음부터 출력행까지의 평균 계속 바뀜

SELECT name, dept, sal, ROUND( AVG(sal) OVER( ORDER BY dept)) 
FROM emp; -- 처음부터 현재 부서까지 평균

SELECT name, dept, sal, ROUND( AVG(sal) OVER(PARTITION BY dept)) 
FROM emp; -- 부서별 평균
-- MAX() OVER()와 MIN() OVER() 함수
-- name, dept, sal, 부서별최대급여
SELECT name, dept, sal, MAX( sal ) OVER( PARTITION BY dept ) 부서최대
FROM emp;

-- name, dept, sal, 최대급여와 차이
SELECT name, dept, sal, MAX( sal ) OVER() - sal 최대와차이
FROM emp;

-- name, dept, sal, 최소급여와 차이
SELECT name, dept, sal, sal - MIN( sal ) OVER() 최대와차이
FROM emp;

-- RATIO_TO_REPORT() OVER() 함수: 비율
SELECT dept, COUNT(*)
FROM EMP
GROUP BY dept;
-- 부서별 인원

SELECT dept, ROUND( COUNT(*) / (SELECT COUNT(*) FROM emp ) * 100) 비율
FROM EMP
GROUP BY dept;
-- 전체인원 대비 부서별 인원 비율

SELECT dept, ROUND( RATIO_TO_REPORT( COUNT(*) ) OVER() * 100) 비율
FROM EMP
GROUP BY dept;

-- LISTAGG() WITHIN GROUP() 함수 : 각 값을 결합
SELECT dept, name
FROM emp
ORDER BY dept;

SELECT dept, LISTAGG(name, ',') WITHIN GROUP ( ORDER BY empNO) 부서사원명
FROM EMP
GROUP BY dept;

-- LAG() OVER() 함수와 LEAD() OVER() 함수
SELECT name, sal,
    LAG( sal, 1, 0 ) OVER ( ORDER BY sal DESC) lag
FROM emp;
-- LAG( sal, 1, 0 )
-- sal : 출력컬럼 
-- 1 : offset. 1줄씩 밀림. 3을 주면 3줄씩 밀림
-- 0 : 밀린 자리에 들어올 값

SELECT name, sal,
    LEAD( sal, 1, 0 ) OVER ( ORDER BY sal DESC) lead -- 위로 한칸 올라감
FROM emp;

-- NTILE() OVER() 함수 : 그룹별 분리
-- 60개를 7그룹
-- (1~9 : 1그룹)
-- (10~18 : 2그룹)
-- (19~27 : 3그룹)
-- (28~36 : 4그룹) ...
SELECT name, sal, 
    NTILE( 7 ) OVER ( ORDER BY sal DESC )그룹
FROM emp;

-- 윈도우 절 
-- FIRST_VALUE() OVER()
SELECT name, dept, sal,
    FIRST_VALUE( sal) OVER (PARTITION BY dept ORDER BY sal DESC) 가장높은연봉
FROM emp;
-- 부서별로 내림차순 정렬시켰을 때 처음 값을 가져와라

-- 적은순에서 큰 수 : 기준이 현재행이 처음이며 마지막
SELECT name, dept, SAL,
    LAST_VALUE( sal ) OVER ( ORDER BY sal)
FROM emp;

-- SELECT * FROM emp의 마지막 값만 출력
SELECT name, dept, SAL,
    LAST_VALUE( sal ) OVER ()
FROM emp;

-- 가장 큰 값 출력 (ORDER BY sal로 인해)
SELECT name, dept, SAL,
    LAST_VALUE( sal ) OVER ()
FROM emp
ORDER BY sal;

-- 적은값에서 큰순
-- CURRENT ROW 현재가 시작과 끝점(기본)
SELECT  name,  dept,  sal,
    LAST_VALUE( sal ) OVER ( ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW   )
FROM emp
ORDER BY sal;

SELECT  name,  dept,  sal,
    LAST_VALUE( sal ) OVER ( ORDER BY sal  )
FROM emp
ORDER BY sal;

-- 가장 큰 값만
-- UNBOUNDED FOLLOWING : 마지막이 끝점 
SELECT  name,  dept,  sal,
    LAST_VALUE( sal ) OVER ( ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING   )
FROM emp
ORDER BY sal;

-- dept별 pos의 sal 소계, dept별 소계, 마지막에 총계 출력
-- dept + pos 소계
-- dept 소계
-- 전체
SELECT dept, pos, SUM(sal)
FROM EMP
GROUP BY ROLLUP(dept, pos);

SELECT pos, dept, SUM(sal)
FROM EMP
GROUP BY ROLLUP(pos, dept);

SELECT city, dept, pos, SUM(sal)
FROM EMP
GROUP BY ROLLUP(city, dept, pos); -- 4레벨 출력

-- dept별 pos의 sal 소계, dept별 소계 출력하며 마지막에 총계는 출력하지 않는다.
SELECT dept, pos, SUM(sal)
FROM EMP
GROUP BY dept, ROLLUP(pos);

-- 부서별 인원수와 마지막에 전체인원수
SELECT dept, COUNT(*)
FROM EMP
GROUP BY dept; -- 부서별 인원수

SELECT dept, COUNT(*)
FROM EMP
GROUP BY ROLLUP(dept); -- 인수가 1개면 2레벨

-- CUBE 절 예
-- dept 별 pos의 sal소계, dept 별 소계, pos별 소계, 마지막에 총계 출력
SELECT dept, pos, SUM(sal)
FROM EMP
GROUP BY CUBE( dept, pos)
ORDER BY dept, pos;

SELECT city, dept, pos, SUM( sal)
FROM EMP
GROUP BY CUBE(city, dept, pos)
ORDER BY city, dept, pos;

SELECT city, dept, pos, SUM( sal)
FROM EMP
GROUP BY city, CUBE(dept, pos)
ORDER BY city, dept, pos;

-- GROUPING 함수와 GROUP_ID 함수
SELECT dept, pos, GROUPING(dept), GROUPING(pos), COUNT(*)
FROM EMP
GROUP BY ROLLUP(dept, pos);

SELECT dept, pos, GROUPING(dept), GROUPING(pos), COUNT(*)
FROM EMP
GROUP BY ROLLUP(dept, pos)
HAVING GROUPING(pos) = 1;

SELECT dept, COUNT(*)
FROM EMP
GROUP BY ROLLUP(dept, pos)
HAVING GROUPING(pos) = 1;

SELECT dept, COUNT(*)
FROM EMP
GROUP BY ROLLUP(dept);

SELECT dept, empNo, name, SUM(sal)
FROM EMP
GROUP BY ROLLUP (dept, (empNo, name));

SELECT dept, empNo, name, GROUP_ID(), SUM(sal)
FROM EMP
GROUP BY dept, ROLLUP (dept, (empNo, name));

SELECT dept, empNo, name, GROUP_ID(), 
    DECODE( GROUP_ID(), 0, NVL(name, '합계'), '평균') name,
    DECODE( GROUP_ID(), 0, SUM(sal), ROUND( AVG(sal))) sal
FROM EMP
GROUP BY dept, ROLLUP (dept, (empNo, name))
ORDER BY dept, GROUP_ID(), empNo;

SELECT dept, empNo,
    DECODE( GROUP_ID(), 0, NVL(name, '합계'), '평균') name,
    DECODE( GROUP_ID(), 0, SUM(sal), ROUND( AVG(sal))) sal
FROM EMP
GROUP BY dept, ROLLUP (dept, (empNo, name))
ORDER BY dept, GROUP_ID(), empNo;

-- 문제풀이
SELECT dept, pos, COUNT(*)
FROM emp
GROUP BY CUBE(dept, pos)
ORDER BY dept;

SELECT dept, COUNT(DECODE(pos, '부장', 1, 0)) 부장,
COUNT(DECODE(pos, '과장', 1, 0)) 과장
FROM emp
GROUP BY dept, CUBE(pos);

SELECT city, MAX ( COUNT (*)) 인원수
FROM emp
WHERE COUNT(DECODE( MOD ( SUBSTR( rrn, 8, 1) , 2), 0, '여자') )
GROUP BY city;

SELECT city, COUNT (*) 인원수
FROM emp
WHERE MOD ( SUBSTR( rrn, 8, 1) , 2) = 0
GROUP BY city
HAVING COUNT(*) = 
(SELECT MAX( COUNT(*)) FROM emp WHERE MOD(SUBSTR(rrn, 8, 1),2) = 0 
GROUP BY city);

SELECT dept, COUNT(DECODE(SUBSTR(pos, 1, 2), '부장', 1, 0)) 부장, 
FROM emp
GROUP BY dept;

SELECT dept, pos, COUNT(*) 인원수
FROM emp
GROUP BY pos, ROLLUP(dept)
ORDER BY dept;

SELECT dept, 
COUNT(DECODE(pos, '부장', 1) ) 부장, 
COUNT(DECODE(pos, '과장', 1) ) 과장, 
COUNT(DECODE(pos, '대리', 1) ) 대리, 
COUNT(DECODE(pos, '사원', 1) ) 사원
FROM emp
GROUP BY dept;

SELECT dept, 
COUNT(DECODE(pos, '부장', 1) ) 부장, 
COUNT(DECODE(pos, '과장', 1) ) 과장, 
COUNT(DECODE(pos, '대리', 1) ) 대리, 
COUNT(DECODE(pos, '사원', 1) ) 사원
FROM emp
GROUP BY ROLLUP(dept);
-- =========================================

CREATE TABLE test
(
    num NUMBER(10) PRIMARY KEY,
    name VARCHAR2(30) NOT NULL, -- 최대 한글 10자
    birth DATE, -- DATE는 크기주지않음.
    city VARCHAR2(30)
);

-- 테이블 목록 확인
SELECT * FROM tab;
SELECT * FROM tabs; -- 자세히 나옴

-- 테이블 컬럼 확인
SELECT * FROM col WHERE tname = 'TEST'; -- ' '안에는 꼭 대문자
SELECT * FROM cols WHERE table_name = 'TEST'; -- 자세히 나옴
DESC test;

-- 테이블 내용 확인
SELECT * FROM emp;
SELECT * FROM test; -- 아직 구조만 만들었기 때문에 아무것도 출력되지 않음.

-- 존재하는 테이블을 이용하여 새로운 테이블 만들기 : 구조 및 데이터 복사
SELECT empNo, name, sal, bonus, sal+bonus FROM emp;
CREATE TABLE emp1 AS SELECT empNo, name, sal, bonus, sal+bonus FROM emp;
-- 에러. 컬럼명 규칙 위반. 컬럼의 이름에는 +가 들어갈 수 없다.
CREATE TABLE emp1 AS SELECT empNo, name, sal, bonus, sal+bonus pay FROM emp;
-- 테이블 구조 및 데이터도 복사. NOT NULL을 제외한 제약조건은 복사되지 않음.
SELECT * FROM tab;
DESC emp1;
SELECT * FROM col WHERE tname = 'EMP1';

SELECT * FROM emp1;

CREATE TABLE emp1 AS SELECT empNo, name, dept, pos FROM emp WHERE dept = '개발부';
-- 에러. ORA-00955 : 기존의 객체가 이름을 사용하고 있습니다. 
-- emp1 이라는 객체를 이미 만들었으므로 다른 이름을 줘야한다.
CREATE TABLE emp2 AS SELECT empNo, name, dept, pos FROM emp WHERE dept = '개발부';

SELECT * FROM tab;
SELECT * FROM emp2;

SELECT empNo, name, TO_DATE(SUBSTR(rrn, 1, 6), 'RRMMDD'), 
    DECODE(MOD(SUBSTR(rrn, 8, 1), 2), 1, '남자', '여자')
FROM emp;

-- 컬럼명을 지정해서 구조 및 데이터 복사
CREATE TABLE emp3 (eno, name, birth, gender) AS 
    SELECT empNo, name, TO_DATE(SUBSTR(rrn, 1, 6), 'RRMMDD'), 
        DECODE(MOD(SUBSTR(rrn, 8, 1), 2), 1, '남자', '여자')
    FROM emp;

SELECT * FROM tab;
DESC emp3;

SELECT * FROM emp3;

-- 테이블의 구조만 복사 ( NOT NULL을 제외한 제약 조건은 복사 안됨)
SELECT * FROM emp WHERE 1 = 1;

SELECT * FROM emp WHERE 1 = 2;

CREATE TABLE emp4 AS
    SELECT * FROM emp WHERE 1= 2;

SELECT * FROM tab;
DESC emp4;
SELECT * FROM col WHERE tname = 'EMP4';

SELECT * FROM emp4;

-- 기존 테이블에 새로운 컬럼을 추가
-- 새로 추가되는 컬럼은 마지막에 추가 된다.

DESC test;

-- 컬럼 추가
ALTER TABLE test ADD ( 
    dept VARCHAR2(30),
    sal NUMBER(3) NOT NULL );

DESC test;

-- emp1 테이블에 다음의 컬럼을 추가
ALTER TABLE emp1 ADD(
    dept VARCHAR2(30) NOT NULL);   
-- 에러. 이미 데이터가 존재하는 경우 NOT NULL 속성의 컬럼을 추가 할 수 없다.
ALTER TABLE emp1 ADD(
    dept VARCHAR2(30) );
DESC emp1; 
SELECT * FROM emp1;

-- ALTER TABLE 테이블명 MODIFY (컬럼명 자료형(크기));
-- 테이블에 존재하는 컬럼의 타입, 폭 등을 수정한다.
-- 데이터가 존재하면 데이터 타입 변경은 불가능하고,
-- 폭은 데이터 길이보다 크거나 같아야 한다.
ALTER TABLE test MODIFY (
    sal NUMBER(8) );
DESC test;

ALTER TABLE emp1 MODIFY (
    name VARCHAR2(8) );
DESC test; -- 에러 한글3글자 9바이트
-- 에러. 데이터가 존재하면 폭은 데이터 길이보다 크거나 같아야 한다.

-- ALTER TABLE 테이블명 RENAME COLUMN 컬럼명 TO 새로운컬럼명
-- 존재하는 컬럼이름 변경하기
DESC test;

ALTER TABLE test RENAME COLUMN num TO empNo;
DESC test;

-- ALTER TABLE 테이블명 DROP COLUMN 컬럼명
-- 존재하는 컬럼 제거
DESC test;

ALTER TABLE test DROP COLUMN dept;
DESC test;

SELECT * FROM emp1;
ALTER TABLE emp1 DROP COLUMN name;
SELECT * FROM emp1;
-- 데이터가 존재하면 데이터도 삭제된다.

-- ALTER TABLE ~ SET UNUSED
-- 컬럼은 삭제하지 않지만 논리적으로 접근하지 못하도록 제한. 즉, 논리적으로 삭제
SELECT * FROM emp3;

ALTER TABLE emp3 SET UNUSED( name );
DESC emp3; -- name 컬럼은 보이지 않음
SELECT * FROM emp3; -- name 컬럼은 보이지 않음
-- UNUSED된 컬럼 개수 확인
SELECT * FROM USER_UNUSED_COL_TABS;
-- UNUSED된 컬럼 제거
ALTER TABLE emp3 DROP UNUSED COLUMNS;

SELECT * FROM USER_UNUSED_COL_TABS;