08 집합연산자, 그룹화, 계층검색

ryunyryuny 2023. 10. 17. 17:51

[문제] 최고 급여를 받는 사원의 정보, 부서 이름 정보를 출력해주세요.

1) SUBQUERY, INLINE VIEW, JOIN
SELECT *
FROM  hr.employees e JOIN hr.departments d
ON e.department_id = d.department_id
WHERE salary = (SELECT max(salary)
                FROM hr.employees);

# SUBQUERY
SELECT *
FROM hr.employees
WHERE salary = 최고급여;        -- WHERE절 : 행제한, 행추출, 그룹함수X
+
SELECT max(salary)
FROM hr.employees;
||
SELECT *
FROM hr.employees
WHERE salary = (SELECT max(salary)
                FROM hr.employees); -> 최고급여를 받는 사원정보
↓
# INLINE VIEW = 가상테이블로 만들기
SELECT *
FROM (SELECT *
        FROM hr.employees
        WHERE salary = (SELECT max(salary)
                        FROM hr.employees));
↓
# JOIN = 부서테이블과 연결
SELECT *
FROM (SELECT *
      FROM hr.employees
      WHERE salary = (SELECT max(salary)
                      FROM hr.employees)) e, hr.departments d
WHERE e.department_id = d.department_id;

2) SUBQUERY, SCALAR SUBQUERY
# SUBQUERY
SELECT *
FROM hr.employees
WHERE salary = (SELECT max(salary)
                FROM hr.employees);
+
# SCALAR SUBQUERY 조인을 하지않고 묶기
SELECT e.*, 부서이름
FROM hr.employees e
WHERE salary = (SELECT max(salary)
                FROM hr.employees);
+
SELECT department_name
FROM hr.departments
WHERE department_id = e.department_id;  -- 단일행, 단일값
↓
SELECT e.*, (SELECT department_name     -- SELECT에 입력
             FROM hr.departments
             WHERE department_id = e.department_id) dept_name
FROM hr.employees e
WHERE salary = (SELECT max(salary)
                FROM hr.employees);

■ 집합연산자
- UNION, UNION ALL, INTERSECT, MINUS
합집합 교집합 차집합
- SELECT 절의 컬럼의 갯수가 일치해야 한다.
- 첫번째 SELECT 절에 대응되는 두번째 SELECT 절 컬럼의 데이터 타입이 일치해야한다.
- UNION, INTERSECT, MINUS 연산자는 중복을 제거한다.(내부적으로 정렬이 발생한다.)
- UNION ALL 연산자는 중복되는 데이터도 포함하기 때문에 내부적으로 정렬이 발생하지 않는다.
- 집합연산자에서 ORDER BY 절은 제일 마지막에 작성해야 한다.
- ORDER BY 절에는 첫번째 SELECT 절의 컬럼이름, 별칭, 위치표기법을 사용한다.

1. 합집합
- UNION : 중복을 제거
- UNION ALL : 중복을 포함

SELECT employee_id, job_id, salary
FROM hr.employees;
+
SELECT employee_id, job_id
FROM hr.job_history;
↓
SELECT employee_id, job_id, salary
FROM hr.employees
UNION
SELECT employee_id, job_id, NULL
FROM hr.job_history; -- 정렬 -> 중복제외

SELECT employee_id, job_id, salary      -- salary 숫자형
FROM hr.employees
UNION
SELECT employee_id, job_id, 0           -- 0 숫자형
FROM hr.job_history;

SELECT employee_id, job_id, salary      -- salary 숫자형
FROM hr.employees
UNION
SELECT employee_id, job_id, '무급'       -- '무급' 문자형
FROM hr.job_history;                    --> 오류, 데이터 타입 불일치
↓
SELECT employee_id, job_id, to_char(salary) -- to_char(salary) 문자형
FROM hr.employees
UNION
SELECT employee_id, job_id, '무급'            -- '무급' 문자형
FROM hr.job_history;

SELECT employee_id as id, job_id job, to_char(salary) sal -- 상위 칼럼명을 기준으로 노출
FROM hr.employees
UNION
SELECT employee_id, job_id, '무급'
FROM hr.job_history;

SELECT employee_id
FROM hr.employees
UNION -- 107행, 중복 제외
SELECT employee_id
FROM hr.job_history;

SELECT employee_id
FROM hr.employees
UNION ALL           -- 117행, 중복 포함, 정렬X
SELECT employee_id
FROM hr.job_history;
↓
SELECT employee_id id
FROM hr.employees
UNION ALL           -- 117행, 중복 포함
SELECT employee_id
FROM hr.job_history
ORDER BY 1;         -- 정렬하기

SELECT *
FROM (SELECT employee_id as id, job_id job, to_char(salary) sal
        FROM hr.employees
        UNION ALL
        SELECT employee_id, job_id, '무급'
        FROM hr.job_history
        ORDER BY 3 DESC)
WHERE sal = '무급';

2. 교집합 INTERSECT

SELECT employee_id
FROM hr.employees
INTERSECT
SELECT employee_id
FROM hr.job_history;

SELECT employee_id, job_id
FROM hr.employees
INTERSECT
SELECT employee_id, job_id
FROM hr.job_history;

3. 차집합 MINUS
SELECT employee_id      -- 첫번째 SELECT 기준
FROM hr.employees
MINUS
SELECT employee_id      -- 여기와 겹치는 것을 제외한다.
FROM hr.job_history;    -- 100건

SELECT employee_id      -- 첫번째 SELECT 기준
FROM hr.job_history
MINUS
SELECT employee_id      -- 여기와 겹치는 것을 제외한다.
FROM hr.employees;      -- 0건

SELECT employee_id, job_id
FROM hr.employees
MINUS
SELECT employee_id, job_id
FROM hr.job_history;    -- 105건

[문제] job_id를 한번이라도 바꾼 사원들의 정보를 출력해주세요.

SELECT *
FROM hr.employees
WHERE employees_id 비교연산자 (job_id = 한번이상 바꾼 사원 사원번호);
+
(SELECT employee_id
FROM hr.job_history)
↓
SELECT *
FROM hr.employees
WHERE employee_id IN (SELECT employee_id
                      FROM hr.job_history);
+                      ㄴ 중복값 보유
SELECT employee_id
FROM hr.employees
INTERSECT
SELECT employee_id
FROM hr.job_history;
↓
SELECT *
FROM hr.employees
WHERE employee_id IN (SELECT employee_id
                      FROM hr.employees
                      INTERSECT
                      SELECT employee_id
                      FROM hr.job_history);     -- 비효율적
↓
SELECT *
FROM hr.employees e
WHERE EXISTS (SELECT 'X'
              FROM hr.job_history
              WHERE employee_id = e.employee_id);   -- 최적화

[문제] job_id를 한번도 바꾸지 않은 사원들의 정보를 출력해주세요.

SELECT *
FROM hr.employees
WHERE employee_id NOT IN (SELECT employee_id
                          FROM hr.job_history);

SELECT *
FROM hr.employees
WHERE employee_id NOT IN (SELECT employee_id
                          FROM hr.employees
                          INTERSECT
                          SELECT employee_id
                          FROM hr.job_history);

SELECT *
FROM hr.employees
WHERE employee_id IN (SELECT employee_id
                      FROM hr.employees
                      MINUS
                      SELECT employee_id
                      FROM hr.job_history);

SELECT *
FROM hr.employees e
WHERE NOT EXISTS (SELECT 'X'
                  FROM hr.job_history
                  WHERE employee_id = e.employee_id);	--> 최적화

[문제] 부서가 소재하지 않은 국가 리스트가 필요하다. country_id, country_name 출력.

SELECT country_id, country_name
FROM hr.countries                       -- 전체목록에서
MINUS
SELECT c.country_id, c.country_name     -- 부서지정한 목록을 정렬 후 제외
FROM hr.departments d, hr.locations l, hr.countries c
WHERE d.location_id = l.location_id
AND l.country_id = c.country_id;        -- 큰데이터 2개를 정렬 후 비교 >> 속도저하
||
SELECT country_id, country_name
FROM hr.countries c
WHERE NOT EXISTS (SELECT 'X'
                  FROM hr.departments d, hr.locations l
                  WHERE d.location_id = l.location_id   -- JOIN
                  AND l.country_id = c.country_id)      -- country_id 체크
ORDER BY 1;                             -- 비교 후 정렬 >> 최적화

SELECT e.employee_id, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id(+) = d.department_id(+);  -- 오류
↓
SELECT e.employee_id, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id(+)
UNION                                           -- 비효율, 테이블 2번 엑세스
SELECT e.employee_id, d.department_name         --        정렬이 수행된다
FROM hr.employees e, hr.departments d
WHERE e.department_id(+) = d.department_id;

↓
[문제] UNION -> UNION ALL + NOT EXISTS 변환

SELECT e.employee_id, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id(+)
UNION ALL                                   -- hr.employees 2번 엑세스
SELECT NULL, department_name
FROM hr.departments d
WHERE NOT EXISTS (SELECT 'X'
                  FROM hr.employees
                  WHERE department_id = d.department_id);
↓
SELECT e.employee_id, d.department_name
FROM hr.employees e FULL OUTER JOIN hr.departments d    -- 최적화
ON e.department_id = d.department_id;

[문제]
1) department_id, job_id, manager_id 기준으로 총액 급여를 출력

SELECT department_id, job_id, manager_id, sum(salary)
FROM hr.employees
GROUP BY department_id, job_id, manager_id;

2) department_id, job_id 기준으로 총액급여를 출력

SELECT department_id, job_id, sum(salary)
FROM hr.employees
GROUP BY department_id, job_id;

3) department_id 기준으로 총액급여를 출력

SELECT department_id, sum(salary)
FROM hr.employees
GROUP BY department_id;

4) 전체 총액 급여를 출력

SELECT sum(salary)
FROM hr.employees;

1),2),3),4)를 한꺼번에 출력해주세요.

SELECT department_id, job_id, manager_id, sum(salary)
FROM hr.employees
GROUP BY department_id, job_id, manager_id
UNION ALL
SELECT department_id, job_id, NULL, sum(salary)
FROM hr.employees
GROUP BY department_id, job_id
UNION ALL
SELECT department_id, NULL, NULL, sum(salary)
FROM hr.employees
GROUP BY department_id
UNION ALL
SELECT NULL, NULL, NULL, sum(salary)
FROM hr.employees;
↓

■ ROLLUP 그룹화
- GROUP BY 절에 지정된 열 리스트를 오른쪽에서 왼쪽방향으로 이동하면서 그룹화를 만드는 연산자

예시)
SELECT a, b, c, sum(sal)
FROM test
GROUP BY a, b, c;

sum(sal) = {a,b,c}
↓
SELECT a, b, c, sum(sal)
FROM test
GROUP BY ROLLUP(a, b, c);

sum(sal) = {a,b,c}
         = {a,b}
         = {a}
         = {}

SELECT department_id, job_id, manager_id, sum(salary)
FROM hr.employees
GROUP BY ROLLUP(department_id, job_id, manager_id);

sum(salary) = {department_id, job_id, manager_id}
            = {department_id, job_id}
            = {department_id}
            = {}

■ CUBE
- ROLLUP 연산자 기능을 포함하고 모든 그룹화를 할 수 있도록 만드는 연산자.
- 모든 경우의 수

SELECT a, b, c, sum(sal)
FROM test
GROUP BY CUBE(a, b, c);

sum(sal) = {a,b,c}
         = {a,b}
         = {a,c}
         = {b,c}
         = {a}
         = {b}
         = {c}
         = {}

SELECT department_id, job_id, manager_id, sum(salary)
FROM hr.employees
GROUP BY CUBE(department_id, job_id, manager_id);

sum(salary) = {department_id, job_id, manager_id}
            = {department_id, job_id}
            = {department_id, manager_id}
            = {job_id, manager_id}
            = {department_id}
            = {job_id}
            = {manager_id}
            = {}

예)
sum(sal) = {a,b}
= {a,c}
↓
SELECT a, b, NULL, sum(sal)
FROM test
GROUP BY a, b
UNION ALL
SELECT a, NULL, c, sum(sal)
FROM test
GROUP BY a, c;
↓

■ GROUPING SETS
- 내가 원하는 그룹을 만드는 연산자

SELECT a, b, C, sum(sal)
FROM test
GROUP BY GROUPING SETS((a, b),(a,c),());

sum(sal) = {a,b}
= {a,c}
         = {}

SELECT department_id, job_id, manager_id, sum(salary)
FROM hr.employees
GROUP BY GROUPING SETS((department_id, job_id),(department_id,manager_id),());

sum(salary) = {department_id, job_id}
            = {department_id,manager_id}
            = {}

[문제] 년도 분기별 총액을 구하세요. 행의 합과 열의 합도 구하세요. ★★★★★

1. 연도별 분기별 출력 + GROUP BY CUBE로 그룹별 합계 출력
SELECT to_char(hire_date,'yyyy') year, to_char(hire_date, 'q') quater, sum(salary) sumsal
FROM hr.employees
GROUP BY CUBE (to_char(hire_date,'yyyy'), to_char(hire_date, 'q'));

2. InlineView + nvl(quater,0) null값을 칼럼으로 활용가능하게 데이터로 변경
SELECT year, nvl(quater,0) quater, sumsal
FROM (SELECT to_char(hire_date,'yyyy') year, to_char(hire_date, 'q') quater, sum(salary) sumsal
      FROM hr.employees
      GROUP BY CUBE (to_char(hire_date,'yyyy'), to_char(hire_date, 'q')));

3. InlineView + 세로형 테이블로 변환
SELECT *
FROM (SELECT year, nvl(quater,0) quater, sumsal
      FROM (SELECT to_char(hire_date,'yyyy') year, to_char(hire_date, 'q') quater, sum(salary) sumsal
            FROM hr.employees
            GROUP BY CUBE (to_char(hire_date,'yyyy'), to_char(hire_date, 'q'))))
PIVOT (max(sumsal) FOR quater IN (1 Q1, 2 Q2, 3 Q3, 4 Q4, 0 합))
ORDER BY 1;

4. InlineView + 데이터 내 NULL값 변환
SELECT nvl(year,'합') 년도, nvl(Q1,0) "1분기", nvl(Q2,0) "2분기", nvl(Q3,0) "3분기", nvl(Q4,0) "4분기", 합
FROM (SELECT *
      FROM (SELECT year, nvl(quater,0) quater, sumsal
            FROM (SELECT to_char(hire_date,'yyyy') year, to_char(hire_date, 'q') quater, sum(salary) sumsal
                  FROM hr.employees
                  GROUP BY CUBE (to_char(hire_date,'yyyy'), to_char(hire_date, 'q'))))
      PIVOT (max(sumsal) FOR quater IN (1 Q1, 2 Q2, 3 Q3, 4 Q4, 0 합))
      ORDER BY 1);

■ 계층검색(hierarchical query)
- 조직도, 게시판 연결글, 탐색기

- CONNECT BY PRIOR ~

SELECT *
FROM hr.employees
START WITH employee_id = 101			-- 시작점, 첫번째 무조건 출력
CONNECT BY PRIOR employee_id = manager_id;	-- 연결고리 조건
            ㄴ 전단계 사원번호를 상사번호로 연결하여 해당 행을 출력, 위에서 밑으로 검색

1. 101번 사원 출력
2. 101번을 manager_id로 갖고 있는 사원의 행 출력 -> 108번 사원 출력
3. 108번을 manager_id로 갖고 있는 사원의 행 출력 -> 없음 -> 전단계
4. 101번을 manager_id로 갖고 있는 사원의 행 출력 -> 109번 사원 출력
...

- 시작점부터 연결된 하위 자료 모두 출력

- CONNECT BY ~ PRIOR ~

SELECT *
FROM hr.employees
START WITH employee_id = 101			-- 시작점, 무조건 출력
CONNECT BY employee_id = PRIOR manager_id;	-- 연결고리 조건
                          ㄴ 전단계 상사번호를 사원번호로 연결하여 해당 행을 출력, 밑에서 위로 검색

1. 101번 사원 출력
2. 101번 사원의 manager_id를 employee_id에서 찾기 -> 100번 사원 출력
3. 100번 사원의 manager_id를 employee_id에서 찾기 -> 없다 -> 종료

- 시작점부터 연결된 상위 자료만 출력

SELECT level, last_name
FROM hr.employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id;
↓
SELECT level, lpad(' ',level*2-2,' ')||last_name
FROM hr.employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id;

SELECT level, lpad(' ',level*2-2,' ')||last_name
FROM hr.employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;    -- 칼럼명 사용

- 계층검색에서 정렬을 수행하려면 ORDER SIBLINGS BY 로 작성해야 한다.
- ORDER SIBLINGS BY 에서는 위치표기법, 열별칭을 사용할 수 없다.

SELECT level, lpad(' ',level*2-2,' ')||last_name
FROM hr.employees
WHERE employee_id != 101                    -- 101번 행 제외 : WHERE 절에 작성
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;

SELECT level, lpad(' ',level*2-2,' ')||last_name
FROM hr.employees
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id
AND employee_id != 101                      -- 101번 조직 제외 : CONNECT BY 다음 AND에서 작성
ORDER SIBLINGS BY last_name;

# SELECT문으로 번호만들기
- CONNECT BY
- 시작 기본값 1

[문제] SELECT문을 이용해서 1~100 출력해주세요.

SELECT level
FROM dual
CONNECT BY level <= 100;

[문제] SELECT문을 이용해서 2단을 출력해주세요.

SELECT '2 * ' || level || ' = ' || 2 * level "2단"
FROM dual
CONNECT BY level < 10;

[문제] SELECT문을 이용해서 2단~9단까지 출력해주세요.

SELECT dan ||' * '|| num ||' = '|| dan*num 구구단
FROM (SELECT level + 1 dan
      FROM dual
      CONNECT BY level <= 8), -- 메인반복문
     (SELECT level num        
      FROM dual
      CONNECT BY level <= 9); -- 서브반복문

'Oracle SQL' 카테고리의 다른 글

09-2 권한, 테이블, INSERT, UPDATE, DELETE (0)	2023.10.18
09 WITH (0)	2023.10.18
07 PIVOT, UNPIVOT, SCALAR SUBQUERY (0)	2023.10.16
06 SUBQUERY (0)	2023.10.13
05 JOIN (0)	2023.10.12

'Oracle SQL'의 다른글

현재글08 집합연산자, 그룹화, 계층검색

Record.

08 집합연산자, 그룹화, 계층검색

'Oracle SQL' 카테고리의 다른 글

티스토리툴바