Database 7일차

[ ROLLUP과 CUBE ]

▶ ROLLUP (expr1, expr2, ...)

● GROUP BY 절에서 사용됨
● expr로 명시한 표현식을 기준으로 집계한 결과, 추가 정보 집계
● expr을 기준으로 소연산을 진행
● expr로 명시한 표현식 수와 순서에 따라 레벨 별로 집계
● expr 개수가 n개 라면, n+1 레벨까지, 하위에서 상위 레벨 순으로 집계

- 소그룹간의 합계를 구하는 함수

• GROUP BY period, ROLLUP(gubun)은 기간별로 구분 합계까지만 보여주고 전체 합계는 없음.
• GROUP BY ROLLUP(period), gubun은 구분별로 기간 합계를 보여주며 마지막에 전체 총합까지 나옴.
• 즉, 첫 번째는 기간 중심 집계, 두 번째는 구분 중심 집계 + 전체 합계.
• NULL이 어디에 나오느냐가 차이를 만든다.
• 결과적으로 집계 관점과 총합 포함 여부가 다르다.

 

GROUP BY ROLLUP(period, gubun)은 두 컬럼을 함께 계층적으로 집계하는 방식

• 먼저 (period, gubun) 조합별 합계를 구함
• 그 다음 period별 전체 합계를 구함 (gubun이 NULL).
• 마지막으로 전체 총합까지 구함 (period, gubun 모두 NULL).

즉, 기간별·구분별 상세 → 기간별 합계 → 전체 합계까지 한 번에 나오는 구조

---

SELECT period, gubun, SUM(loan_jan_amt) totl_jan
    FROM kor_loan_status
WHERE period LIKE '2013%'
GROUP BY period, ROLLUP( gubun );

 

---

SELECT period, gubun, SUM(loan_jan_amt) totl_jan
    FROM kor_loan_status
WHERE period LIKE '2013%'
GROUP BY ROLLUP( period ), gubun;

---

SELECT period, gubun, SUM(loan_jan_amt) totl_jan
    FROM kor_loan_status
WHERE period LIKE '2013%'
GROUP BY ROLLUP( period, gubun);

 

[ ROLLUP과 CUBE ]

▶ CUBE (expr1, expr2, ...)

● GROUP BY 절에서 사용됨
● 명시한 표현식 개수에 따라 가능한 모든 조합별로 집계
● expr 개수가 3이면 2의 3승, 즉 총 8가지 종류로 집계됨

--CUBE
SELECT period, gubun, SUM(loan_jan_amt) totl_jan
    FROM kor_loan_status
WHERE period LIKE '2013%'
GROUP BY CUBE( period, gubun );

SELECT period, gubun, SUM(loan_jan_amt) totl_jan
    FROM kor_loan_status
WHERE period LIKE '2013%'
GROUP BY period, CUBE( gubun );

---

[ 집합 연산자  ]

▶ UNION  
● 집합의 합집합 개념  
● 두 개 이상의 개별 SELECT 쿼리를 연결  
● 개별 SELECT 쿼리 반환 결과가 중복될 경우 UNION 연산 결과는 한 로우만 반환됨

-- UNION
SELECT goods
    FROM exp_goods_asia
    WHERE country = '한국'
UNION
SELECT goods
    FROM exp_goods_asia
    WHERE country = '일본';

▶ UNION ALL

● UNION과 유사

● 개별 SELECT 쿼리 반환 결과가 중복될 경우, 중복되는 건까지 모두 반환

--UNION ALL
SELECT goods
    FROM exp_goods_asia
    WHERE country = '한국'
UNION ALL
SELECT goods
    FROM exp_goods_asia
    WHERE country = '일본';

▶ INTERSECT

● 집합의 교집합 개념

● 두 개 이상의 개별 SELECT 쿼리를 연결

● 개별 SELECT 쿼리 반환 결과 중 공통된 항목만 추출

-- INTERSECT
SELECT goods
    FROM exp_goods_asia
    WHERE country = '한국'
INTERSECT
SELECT goods
    FROM exp_goods_asia
    WHERE country = '일본';

▶ MINUS

● 집합의 차집합 개념

● 두 개 이상의 개별 SELECT 쿼리를 연결

● 개별 SELECT 쿼리 반환 결과 중 중복된 건을 제외한 선행 쿼리 결과 추출

선행 쿼리문의 MINUS 뒤의 커리문을 빼기 때문에 순서에 따라 결과 값이 달라짐

-- MINUS
SELECT goods
    FROM exp_goods_asia
    WHERE country = '한국'
MINUS
SELECT goods
    FROM exp_goods_asia
    WHERE country = '일본';

SELECT goods
    FROM exp_goods_asia
    WHERE country = '일본'
MINUS
SELECT goods
    FROM exp_goods_asia
    WHERE country = '한국';

---

▶  집합 연산자 제한사항

● 개별 SELECT 쿼리의 SELECT 리스트 개수와 데이터 타입이 일치해야 함

● ORDER BY 절은 맨 마지막 개별 SELECT 쿼리에만 명시 가능함

● BLOB, CLOB, BFILE 같은 LOB 타입 컬럼은 집합 연산자 사용 불가

● UNION, INTERSECT, MINUS 연산자는 LONG형 컬럼에는 사용 불가

--집합 연산자 제한사항
SELECT seq
    FROM exp_goods_asia
    WHERE country = '한국'
UNION
SELECT goods
    FROM exp_goods_asia
    WHERE country = '일본';
-- SELECT문의 선택한 컬럼 두개가 달라서 오류가 난다

SELECT seq
    FROM exp_goods_asia
    WHERE country = '한국'
UNION
SELECT seq, goods
    FROM exp_goods_asia
    WHERE country = '일본';
-- SELECT문의 선택한 컬럼 개수가 달라서 오류

SELECT goods
    FROM exp_goods_asia
    WHERE country = '한국'
UNION
SELECT goods
    FROM exp_goods_asia
    WHERE country = '일본'
    ORDER BY goods;
    -- 마지막 쿼리문에 ORDER BY절을 붙여야 오류가 나지 않는다

 

-- GROUPPING SETS

-- GROUPPING SETS
SELECT period, gubun, SUM(loan_jan_amt) totl_jan
    FROM kor_loan_status
    WHERE period LIKE '2013%'
    GROUP BY GROUPING SETS(period, gubun);

SELECT period, gubun, SUM(loan_jan_amt) totl_jan
    FROM kor_loan_status
    WHERE period LIKE '2013%'
    AND region IN ('서울', '경기')
    GROUP BY GROUPING SETS(period, (gubun,region));

---

ex_user

-----------------------------------------------------------------
-----------------------------------------------------------------    
--chap05_ex2
-----------------------------------------------------------------
-----------------------------------------------------------------
--1. 모든 사원의 급여 최고액, 최저액, 총액 및 평균 급여를 출력하세요. 칼럼의 명칭은 최고액 (Maximun) 
--최저액(Minimun), 총액(Sum), 평균급여(Average)로 지정하고 평균에 대해서는 정수로 반올림 하세요
SELECT * FROM EMPLOYEE;

SELECT 
        MAX(SALARY) AS Maximum, 
        MIN(SALARY) AS Minimum,
        SUM(SALARY) AS Sum,
        ROUND(AVG(SALARY)) AS Average
    FROM employee;


--2. 각 담당업무 유형별로 급여 최고액, 최저액, 총액 및 평균액을 출력하세요. 칼럼의 명칭은 최고액(Maximun) 
--최저액(Minimun), 총액(Sum), 평균 급여(Average)로 지정하고 평균에 대해서는 정수로 반올림 하세요.
SELECT 
        MAX(SALARY) AS Maximum, 
        MIN(SALARY) AS Minimum,
        SUM(SALARY) AS Sum,
        ROUND(AVG(SALARY)) AS Average
    FROM employee
    GROUP BY JOB;
    
--3. Count(*)함수를 이용해서 담당업무가 동일한 사원의 수를 출력하세요
SELECT JOB, COUNT(JOB) AS "사원수"
    FROM employee
    GROUP BY JOB;
    
--4. 관리자의 수를 나열하세요. 칼럼의 별칭은 MANAGER로 출력하세요.
SELECT COUNT(JOB) AS MANAGER
    FROM employee
    WHERE job = 'MANAGER';

--5. 급여 최고액, 급여 최저액의 차액을 출력하세요. 칼럼의 별칭 DIFFERENCE로 지정하세요
SELECT MAX(SALARY) - MIN(SALARY) AS "DIFFERENCE"
    FROM employee;
    
--6. 직급별 사원의 최저 급여를 출력하세요. 관리자를 알 수 없는 사원 및 
--최저 급여가 2000미만인 그룹은 제외시키고
--최저급여에 대한 내림차순으로 정렬하여 출력하세요.
SELECT MANAGER, JOB, MIN(SALARY)
    FROM employee
    WHERE MANAGER IS NOT NULL
    GROUP BY MANAGER, JOB
    HAVING MIN(SALARY) >= 2000
    ORDER BY 3 DESC0;

--7. 각 부서에 대해 부서번호, 사원수, 부서내의 모든 사원의 평균급여를 출력하시오, 칼럼의 별칭은
--부서번호(DNO), 사원수(Number of PeoPle), 평균급여(Salary)로 지정하고 
--평균급여는 소수점 2째 자리에서 반올림 하세요.
SELECT  DNO AS DNO,
            count(DNO) AS "Number of PeoPle", 
            ROUND(AVG(SALARY),  2) AS Salary
        FROM employee
        GROUP BY DNO;
        
--8. 각 부서에 대해 부서번호 이름, 지역명, 사원수,부서내의 모든 사원의 평균 급여를 출력하시오. 
--칼럼의 별칭은 부서번호이름(DName), 지역명(Location), 사원수(Number of PeoPle),
--평균급여(Salary)로 지정하고 평균급여는 정수로 반올림하세요.
--부서번호이름(DName), 지역명(Location),
--: decode로 번호와 이름 매칭
SELECT  DNO AS DNO,
            count(DNO) AS "Number of PeoPle", 
            ROUND(AVG(SALARY),  2) AS Salary,
    DECODE(DNO, 
           10, 'ACCOUNTING', 
           20, 'RESEARCH', 
           30, 'SALES', 
           40, 'OPERATIONS') AS DName,
    DECODE(DNO, 
           10, 'NEW YORK', 
           20, 'DALLAS', 
           30, 'CHICAGO', 
           40, 'BOSTON') AS Location
    FROM employee
    GROUP BY DNO;

--9. 업무를 표시한 다음 해당 업무에 대해 부서번호별 급여 및 부서 10,20,30의 급여 총액을 각각 출력하시오. 
--각 칼럼의 별칭은 각각 job, 부서10,부서20, 부서30, 총액으로 지정하세요.
--부서10,부서20, 부서30
--: decode로 번호와 합계 매칭
SELECT  
    JOB AS job,
        SUM(DECODE(DNO, 10, SALARY, 0)) AS "부서10",
        SUM(DECODE(DNO, 20, SALARY, 0)) AS "부서20",
        SUM(DECODE(DNO, 30, SALARY, 0)) AS "부서30",
        SUM(SALARY) AS 총액
    FROM employee
    GROUP BY JOB;

---

ora_user_quiz

-----------------------------------------------------------------------------
--DB05 26/1/29
-----------------------------------------------------------------------------
--chap05_ex1
SELECT * FROM employees;
--1. 사원테이블 (employees) 에서 입사년도별 사원수를 구하는 쿼리를 작성해보자.
SELECT 
  TO_CHAR(HIRE_DATE, 'YYYY') AS "입사년도별", COUNT(*)
FROM EMPLOYEES
GROUP BY TO_CHAR(HIRE_DATE, 'YYYY')
ORDER BY TO_CHAR(HIRE_DATE, 'YYYY');

--2. kor_loan_status 테이블에서 2012년도 월별, 지역별 대출 총 잔액을 구하는 쿼리를 작성하라.
SELECT * FROM kor_loan_status;

SELECT period, region, SUM(loan_jan_amt)
    FROM kor_loan_status
WHERE period LIKE '2012%'
GROUP BY period, region
ORDER BY period, region;
    
    
--3. 아래의 쿼리는 분할 ROLLUP을 적용한 쿼리이다.
SELECT period, gubun, SUM(loan_jan_amt) totl_jan
FROM kor_loan_status
WHERE period LIKE '2013%'
GROUP BY period, ROLLUP( gubun );
--이 쿼리를 ROLLUP을 사용하지 않고, 집합연산자를 사용해서 동일한 결과가 나오도록 쿼리를 작성해보자.

    SELECT period, gubun, SUM(loan_jan_amt) totl_jan
        FROM kor_loan_status
    WHERE period LIKE '2013%'
    GROUP BY period, gubun
UNION
    SELECT period, '', SUM(loan_jan_amt) totl_jan
        FROM kor_loan_status
    WHERE period LIKE '2013%'
    GROUP BY period;

--4. 다음 쿼리를 실행해서 결과를 확인한 후, 집합 연산자를 사용해 동일한 결과를 추출하도록 쿼리를 작성해 보자.
SELECT period,
CASE WHEN gubun = '주택담보대출' THEN SUM(loan_jan_amt) ELSE 0 END 주택담보대출액,
CASE WHEN gubun = '기타대출' THEN SUM(loan_jan_amt) ELSE 0 END 기타대출액
FROM kor_loan_status
WHERE period = '201311'
GROUP BY period, gubun;

    SELECT period,
           SUM(loan_jan_amt) AS 주택담보대출액,
           0 AS 기타대출액
    FROM kor_loan_status
    WHERE period = '201311'
      AND gubun = '주택담보대출'
    GROUP BY period, gubun
UNION ALL
    SELECT period,
           0 AS 주택담보대출액,
           SUM(loan_jan_amt) AS 기타대출액
    FROM kor_loan_status
    WHERE period = '201311'
      AND gubun = '기타대출'
    GROUP BY period, gubun;
    
--5. 다음과 같은 형태, 즉 지역과 각 월별 대출총잔액을 구하는 쿼리를 작성해 보자.
-----------------------------------------------------------------------------------------
--지역 201111 201112 201210 201211 201212 203110 201311
-----------------------------------------------------------------------------------------
--서울
--부산
--...
--...
-----------------------------------------------------------------------------------------
--SELECT FROM ( SELECT FROM ) GROUP BY ORDER BY ;이 형식으로쓰도록
SELECT * FROM kor_loan_status;

SELECT 
    REGION,
    SUM("201111") AS "201111",
    SUM("201112") AS "201112",
    SUM("201210") AS "201210",
    SUM("201211") AS "201211",
    SUM("201212") AS "201212",
    SUM("201310") AS "201310",
    SUM("201311") AS "201311"
FROM (
    SELECT 
        REGION,
        DECODE(PERIOD, '201111', LOAN_JAN_AMT, 0) AS "201111",
        DECODE(PERIOD, '201112', LOAN_JAN_AMT, 0) AS "201112",
        DECODE(PERIOD, '201210', LOAN_JAN_AMT, 0) AS "201210",
        DECODE(PERIOD, '201211', LOAN_JAN_AMT, 0) AS "201211",
        DECODE(PERIOD, '201212', LOAN_JAN_AMT, 0) AS "201212",
        DECODE(PERIOD, '201310', LOAN_JAN_AMT, 0) AS "201310",
        DECODE(PERIOD, '201311', LOAN_JAN_AMT, 0) AS "201311"
    FROM kor_loan_status
    WHERE PERIOD IN ('201111','201112','201210','201211','201212','201310','201311')
)
GROUP BY REGION
ORDER BY REGION;

 

 

-- GROUPPING SETS 사용
SELECT 
    REGION,
    SUM(DECODE(PERIOD, '201111', LOAN_JAN_AMT, 0)) AS "201111",
    SUM(DECODE(PERIOD, '201112', LOAN_JAN_AMT, 0)) AS "201112",
    SUM(DECODE(PERIOD, '201210', LOAN_JAN_AMT, 0)) AS "201210",
    SUM(DECODE(PERIOD, '201211', LOAN_JAN_AMT, 0)) AS "201211",
    SUM(DECODE(PERIOD, '201212', LOAN_JAN_AMT, 0)) AS "201212",
    SUM(DECODE(PERIOD, '201310', LOAN_JAN_AMT, 0)) AS "201310",
    SUM(DECODE(PERIOD, '201311', LOAN_JAN_AMT, 0)) AS "201311"
FROM kor_loan_status
WHERE PERIOD IN ('201111','201112','201210','201211','201212','201310','201311')
--GROUP BY REGION
GROUP BY GROUPING SETS ( (REGION) , () )
ORDER BY REGION;