수업내용/SQL

[2022.10.31.월] 분석함수, 계층 검색

주니어주니 2022. 10. 31. 19:34

 

 

1. TOP-N 분석

 

  • 조건에 맞는 최상위 레코드 N개 혹은 최하위 레코드 N개를 조회하는 쿼리
    (ex. 급여를 가장 많이 받는 사원 3명, 판매가 가장 많이 된 도서 10종류 조회)

  • ROWNOM 의사컬럼 사용하기 (최상위 N개) 
    : 조회된 행에 행번호를 1번부터 순서대로 부여하는 의사컬럼 
    -> 중간에 자를 수 없음
    -> 인라인뷰로 정렬 먼저 

 

 

 

1) 급여를 가장 많이 받는 사원 3명 조회하기

-- 급여를 가장 많이 받는 사원 3명 조회하기
SELECT ROWNUM, EMPLOYEE_ID, FIRST_NAME, SALARY
FROM (SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
      FROM EMPLOYEES
      ORDER BY SALARY DESC)
WHERE ROWNUM <= 3;

 

 

 

2) 가장 최근에 입사한 직원 10명 조회하기

-- 가장 최근에 입사한 직원 10명 조회하기
SELECT ROWNUM, EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM (SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
      FROM EMPLOYEES
      ORDER BY HIRE_DATE DESC)
WHERE ROWNUM <= 10;

 

 

 

3) 가장 사원수가 많은 부서 3곳 조회하기

-- 가장 사원수가 많은 부서 3곳 조회하기
SELECT ROWNUM, DEPARTMENT_ID, CNT
FROM (SELECT DEPARTMENT_ID, COUNT(*) CNT
        FROM EMPLOYEES
        WHERE EMPLOYEE_ID IS NOT NULL
        GROUP BY DEPARTMENT_ID
        ORDER BY CNT DESC)
WHERE ROWNUM <= 3;

 

 

 

 

 

2. 분석함수

 

  • 테이블의 데이터를 특정 기준으로 분석하여 결과를 조회하는 함수

 

2-1. 순위 분석함수 : RANK(), DENSE_RANK(), ROW_NUMBER()

 

  • RANK(), DENSE_RANK() 
    : 순위에 따른 조회 개수가 가변적 (10위까지 조회했을 때 조회 결과가 10개 이상일 수 있다.)

  • ROW_NUMBER()
    : 순위에 따른 조회 개수가 고정적 (10위까지 조회하면 조회결과가 항상 10개)  
    -> 중간 N개 ~ N개

  • SELECT 분석함수( [컬럼명] OVER ( [PARTITION BY  컬럼명] [ORDER BY 컬럼명 정렬기준] )
    FROM 테이블명

 

 

 

 

1) 급여를 기준으로 순위 분석함수를 사용해서 조회하기

-- 급여를 기준으로 순위 분석함수를 사용해서 조회하기
SELECT RANK() OVER (ORDER BY SALARY DESC) SALARY_RANKING, 
       DENSE_RANK() OVER (ORDER BY SALARY DESC) SALARY_DENSE_RANKING, 
       ROW_NUMBER() OVER (ORDER BY SALARY DESC) SALARY_ROW_NUMBER, 
       EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES;

 

 

* RANK -> 같은 순위가 여러개일 수 있음

  ROWNUM -> 그냥 행번호를 붙인 것 (같은 번호 X) 

 

 

 

2) 급여를 기준으로 직원들을 정렬했을 때 급여 순위가 1 ~ 10에 해당하는 직원 10명 조회하기

-- 급여를 기준으로 직원들을 정렬했을 때 급여 순위가 1 ~ 10에 해당하는 직원 10명 조회하기
SELECT ROWNUMBER, EMPLOYEE_ID, FIRST_NAME, SALARY
FROM (SELECT ROW_NUMBER() OVER (ORDER BY SALARY DESC) ROWNUMBER, EMPLOYEE_ID, FIRST_NAME, SALARY
      FROM EMPLOYEES)
WHERE ROWNUMBER >= 1 AND ROWNUMBER <= 10;

 

 

※ 이게 안되는 이유 : 실행 순서가 FROM -> WHERE -> SELECT 순

   ( FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY ) 

     SELECT문의 별칭, 함수는 ORDER BY 에서만 쓸 수 있음 

 

 

 

3) 급여를 기준으로 직원들을 정렬했을 때 급여 순위가 11 ~ 20에 해당하는 직원 10명 조회하기

-- 급여를 기준으로 직원들을 정렬했을 때 급여 순위가 11 ~ 20에 해당하는 직원 10명 조회하기
SELECT ROWNUMBER, EMPLOYEE_ID, FIRST_NAME, SALARY
FROM (SELECT ROW_NUMBER() OVER (ORDER BY SALARY DESC) ROWNUMBER, EMPLOYEE_ID, FIRST_NAME, SALARY
      FROM EMPLOYEES)
WHERE ROWNUMBER >= 11 AND ROWNUMBER <= 20;

 

 

 

4) 부서별로 구분해서(PARTITION BY DEPARTMENT_ID) 해당 부서내에서 급여를 기준으로 순번 부여하기

-- 부서별로 구분해서(PARTITION BY DEPARTMENT_ID) 해당 부서내에서 급여를 기준으로 순번 부여하기
SELECT ROW_NUMBER() OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) ROWNUMBER, 
       EMPLOYEE_ID, FIRST_NAME, DEPARTMENT_ID, SALARY
FROM EMPLOYEES
ORDER BY DEPARTMENT_ID, ROWNUMBER;

 

 

 

5) 부서별로 가장 급여를 많이 받는 사원 조회하기

-- 부서별로 가장 급여를 많이 받는 사원 조회하기
SELECT DEPARTMENT_ID, EMPLOYEE_ID, FIRST_NAME, SALARY
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) ROWNUMBER,
             DEPARTMENT_ID, EMPLOYEE_ID, FIRST_NAME, SALARY
      FROM EMPLOYEES
      WHERE DEPARTMENT_ID IS NOT NULL)
WHERE ROWNUMBER = 1
ORDER BY DEPARTMENT_ID, SALARY;

 

 

 

 

 

2-2. 집계 분석함수 : SUM(), MIN(), MAX(), AVG(), COUNT()

 

  • 그룹함수와 분석함수
    • 그룹함수 : 테이블 전체 혹은 집합 그룹당 결과 하나 반환
      SELECT SUM(컬럼명)                  -> 다른 컬럼과 같이 쓰러면 조회하려는 컬럼을 모두 그룹화 해야함 
      FROM EMPLOYEES

    • 분석함수 : 행마다 결과 반환 
      SELECT SUM(컬럼명) OVER() 
      FROM EMPLOYEES

 

1) 집계 분석함수를 사용해서 직원정보와 각 부서별 평균급여 조회하기

-- 집계 분석함수를 사용해서 직원정보와 각 부서별 평균급여 조회하기
SELECT DEPARTMENT_ID, EMPLOYEE_ID, FIRST_NAME, SALARY, AVG(SALARY) OVER(PARTITION BY DEPARTMENT_ID)
FROM EMPLOYEES;

 

 

 

2) 집계 분석함수를 사용해서 직원정보와 해당 직원이 소속된 부서의 사원수, 총급여, 평균급여 조회하기

-- 집계 분석함수를 사용해서 직원정보와 해당 직원이 소속된 부서의 사원수, 총급여, 평균급여 조회하기
SELECT DEPARTMENT_ID, EMPLOYEE_ID, FIRST_NAME, SALARY,
      COUNT(SALARY) OVER (PARTITION BY DEPARTMENT_ID) CNT,
      SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID) SALARY_SUM,
      TRUNC(AVG(SALARY) OVER (PARTITION BY DEPARTMENT_ID)) SALARY_AVERAGE
FROM EMPLOYEES;

 

 

 

 

3) 집계분석함수와 그룹함수 비교 

-- 직원아이디, 이름, 급여, 부서 평균 급여, 직원 급여와의 차이 조회하기 (집계 분석함수 사용) 
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, 
       TRUNC(AVG(SALARY) OVER(PARTITION BY DEPARTMENT_ID)) AVG_SALARY, 
       TRUNC(SALARY - AVG(SALARY) OVER(PARTITION BY DEPARTMENT_ID)) SALARY_GAP
FROM EMPLOYEES
ORDER BY EMPLOYEE_ID;

-- 직원아이디, 이름, 급여, 부서 평균 급여, 직원 급여와의 차이 조회하기 (그룹함수 사용) 
SELECT B.EMPLOYEE_ID, B.FIRST_NAME, B.SALARY, A.AVG_SALARY, B.SALARY - A.AVG_SALARY SALARY_GAP
FROM (SELECT DEPARTMENT_ID, TRUNC(AVG(SALARY)) AVG_SALARY
      FROM EMPLOYEES
      GROUP BY DEPARTMENT_ID) A, EMPLOYEES B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID;

 

 

 

 

 

 

3. 집합 연산자

 : UNION ALL(합집합), UNION(합집합), INTERSECT(교집합), MINUS(차집합)

 : 2개 이상의 SQL문 실행결과에 대한 집합연산(합집합, 교집합, 차집합)을 지원한다. 

 

 

 

 

- SQL문의 컬럼 개수가 동일해야 한다.

- SQL문의 각 컬럼별 데이터타입이 서로 동일한 순서로 같아야 한다.

- SQL문의 각 컬럼별 이름이 동일할 필요는 없다. 

 

- 아래 SQL문에 없는 컬럼 -> NULL이나 ' ', 0 으로 컬럼 개수 맞추기
- 차집합, 교집합일 때는 값이 모두 똑같아야 함

 

 

 

1) EMPLOYEES 테이블과 JOB_HISTORY 테이블 - 직종이 변경될 적이 있는 직원의 아이디 조회하기

-- EMPLOYEES 테이블과 JOB_HISTORY 테이블 - 직종이 변경될 적이 있는 직원의 아이디 조회하기
--> EMPLOYEES 테이블에서 조회한 결과와 JOB_HISTORY 테이블에서 조회한 결과에 모두 포함된 값을 조회한다.(교집합)
SELECT EMPLOYEE_ID
FROM EMPLOYEES
INTERSECT
SELECT EMPLOYEE_ID
FROM JOB_HISTORY;

 

 

 

2) EMPLOYEES 테이블과 JOB_HISTORY 테이블 - 직종이 한번도 변경된 적이 없는 직원의 아이디, 이름, 급여 조회하기

-- EMPLOYEES 테이블과 JOB_HISTORY 테이블 - 직종이 한번도 변경된 적이 없는 직원의 아이디, 이름, 급여 조회하기
-- EMPLOYEES 테이블의 조회결과에서 JOB_HISTORY 테이블의 조회결과를 제외한 값을 조회한다. (차집합) 
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE EMPLOYEE_ID IN (SELECT EMPLOYEE_ID
                      FROM EMPLOYEES
                      MINUS
                      SELECT EMPLOYEE_ID
                      FROM JOB_HISTORY);

SELECT A.EMPLOYEE_ID, B.FIRST_NAME, B.SALARY
FROM (SELECT EMPLOYEE_ID
      FROM EMPLOYEES
      MINUS
      SELECT EMPLOYEE_ID
      FROM JOB_HISTORY) A, EMPLOYEES B
WHERE A.EMPLOYEE_ID = B.EMPLOYEE_ID
ORDER BY A.EMPLOYEE_ID;

 

 

 

3) 직원의 매니저 혹은 부서의 매니저 조회하기 (중복 포함) 

-- 직원의 매니저 혹은 부서의 매니저 조회하기 (부서의 매니저는 부서당 하나니까 중복제거할 필요 X ) 
-- 중복 제거한 직원의 매니저와 부서의 매니저를 모두 합쳐버리기 (직원매니저와 부서매니저의 중복 있음)
SELECT DISTINCT MANAGER_ID
FROM EMPLOYEES
WHERE MANAGER_ID IS NOT NULL
UNION ALL
SELECT MANAGER_ID
FROM DEPARTMENTS
WHERE MANAGER_ID IS NOT NULL;

 

 

 

4) 직원의 매니저 혹은 부서의 매니저 조회하기 (중복 제거) 

-- 직원의 매니저 혹은 부서의 매니저 조회하기
-- 직원매니저와 부서매니저 중복 없음
SELECT MANAGER_ID
FROM EMPLOYEES
WHERE MANAGER_ID IS NOT NULL
UNION
SELECT MANAGER_ID
FROM DEPARTMENTS
WHERE MANAGER_ID IS NOT NULL;

 

 

 

 

 

4. 계층 검색 

 

  • 계층형 쿼리를 이용해서 트리 구조로 데이터를 조회하는 것 
  • 조직도, 메뉴, 카테고리 등은 대부분 트리구조를 가지고 있다.

  • 계층 검색의 예
    : 특정 데이터의 하위 데이터, 상위 데이터 조회하기

  • 계층형 쿼리의 형식
    SELECT [LEVEL, ] COLUMN, COLUMN, ...
    FROM 테이블
    [WHERE 조건식]
    START WITH 조건식
    CONNECT BY PRIOR 조건식

    * LEVEL : 조회되는 각 행에 대한 레벨을 표시한다. 최상위행은 1, 그 하위행은 2, 그 하위행은 3을 반환. 
    * START WITH  : 계층 검색의 시작지점을 지정한다. 
    * CONNECT BY : 상위행, 하위행 간의 관계를 지정한다. 

 

 

*  상위행에서 하위행으로 검색하기
-- SELECT LEVEL, 컬럼명, 컬럼명
-- FROM 테이블
-- START WITH 조건식
-- CONNECT BY PRIOR 부모컬럼(기본키 컬럼) = 자식컬럼(외래키 컬럼) (-> 부모컬럼에서 자식컬럼으로 찾아감)

   (CONNECT BY PROIR : 뭐를 우선시 할거냐) 

 

1) 100번 직원 및 부하직원 조회하기 (조직도 형태) 

-- 100번 직원 및 부하직원 조회하기 (조직도 형태) 
SELECT LEVEL, EMPLOYEE_ID, LPAD(' ', (LEVEL-1)*5, ' ') || FIRST_NAME, MANAGER_ID 
FROM EMPLOYEES
START WITH EMPLOYEE_ID = 100
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

 

 

 

2) 101번 직원 및 부하직원 조회하기 (조직도 형태) 

-- 101번 직원 및 부하직원 조회하기 (조직도 형태) 
SELECT LEVEL, EMPLOYEE_ID, LPAD(' ', (LEVEL-1)*5, ' ') || FIRST_NAME, MANAGER_ID 
FROM EMPLOYEES
START WITH EMPLOYEE_ID = 101
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

 

 

 

* 하위행에서 상위행으로 검색하기
-- SELECT LEVEL, 컬럼명, 컬럼명
-- FROM 테이블
-- START WITH 조건식
-- CONNECT BY PRIOR 자식컬럼(외래키 컬럼) = 부모컬럼(기본키 컬럼)

 

3) 206번 직원의 상관을 조회하기

-- 206번 직원의 상관을 조회하기
SELECT LEVEL, EMPLOYEE_ID, FIRST_NAME, MANAGER_ID
FROM EMPLOYEES
START WITH EMPLOYEE_ID = 206
CONNECT BY PRIOR MANAGER_ID = EMPLOYEE_ID
ORDER BY LEVEL DESC;

 

 

 

 

* LEVEL과 CONNECT BY를 이용해서 연속된 숫자, 날짜 생성하기

 

1) 1 ~ 12까지 연속된 숫자 조회하기

-- 1 ~ 12까지 연속된 숫자 조회하기
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= 12;

 

 

 

2) 2004년에 입사한 직원들의 월별 입사자 수를 조회하기 (입사자가 없는 달은 조회되지 않는다.)

-- 2004년에 입사한 직원들의 월별 입사자 수를 조회하기 (입사자가 없는 달은 조회되지 않는다.)
SELECT TO_NUMBER(TO_CHAR(HIRE_DATE, 'MM')) MONTH, COUNT(*)
FROM EMPLOYEES
WHERE HIRE_DATE >= '2004-01-01' AND HIRE_DATE < '2005-01-01'
GROUP BY TO_CHAR(HIRE_DATE, 'MM')
ORDER BY MONTH ASC;

 

 

 

3) 2004년에 입사한 직원들의 월별 입사자 수를 조회하기 (입사자가 없는 달은 0으로 조회된다.)

-- 2004년에 입사한 직원들의 월별 입사자 수를 조회하기 (입사자가 없는 달은 0으로 조회된다.)
SELECT B.MONTH, NVL(A.CNT, 0) CNT
FROM (SELECT TO_NUMBER(TO_CHAR(HIRE_DATE, 'MM')) MONTH, COUNT(*) CNT
      FROM EMPLOYEES
      WHERE HIRE_DATE >= '2004-01-01' AND HIRE_DATE < '2005-01-01'
      GROUP BY TO_NUMBER(TO_CHAR(HIRE_DATE, 'MM'))) A,
      (SELECT LEVEL MONTH
      FROM DUAL
      CONNECT BY LEVEL <= 12) B
WHERE A.MONTH(+) = B.MONTH
ORDER BY B.MONTH ASC;

 

 

 

4) 시작일부터 종료일 사이의 날짜 생성하기

-- 시작일부터 종료일 사이의 날짜 생성하기
SELECT TO_DATE('2022-10-01') + LEVEL - 1
FROM DUAL
CONNECT BY LEVEL <= TO_DATE('2022-10-31') - TO_DATE('2022-10-01') + 1;

 

 

 

5) 지정된 월의 시작일부터 종료일 사이의 날짜 생성하기

-- 지정된 월의 시작일부터 종료일 사이의 날짜 생성하기
SELECT TO_DATE('2022-02-01') + LEVEL - 1
FROM DUAL
CONNECT BY LEVEL <= LAST_DAY(TO_DATE('2022-02-01')) - TO_DATE('2022-02-01') + 1;

 

 

***  LEVEL이 1부터 시작하기 때문에 0부터 시작하게 하려고 LEVEL - 1 

10월 1일부터 + LEVEL을 하면 2부터 시작되니까 -1을 해줌

 

=> LEVEL이 1부터 31이 될때까지 

 

 

 

6) 10월달 일별 회원 가입 현황 조회하기

-- 10월달 일별 회원 가입 현황 조회하기
SELECT B.DAY, NVL(A.CNT, 0) CNT
FROM (SELECT TO_NUMBER(TO_CHAR(USER_CREATED_DATE, 'DD')) DAY, COUNT(*) CNT
      FROM SAMPLE_USERS
      WHERE USER_CREATED_DATE >= '2022-10-01' AND USER_CREATED_DATE <= LAST_DAY(TO_DATE('2022-10-01'))
      GROUP BY TO_NUMBER(TO_CHAR(USER_CREATED_DATE, 'DD'))) A, 
     (SELECT TO_NUMBER(TO_CHAR(TO_DATE('2022-10-01') + LEVEL - 1, 'DD')) DAY
      FROM DUAL
      CONNECT BY LEVEL <= LAST_DAY(TO_DATE('2022-10-01')) - TO_DATE('2022-10-01') + 1) B
WHERE A.DAY(+) = B.DAY
ORDER BY B.DAY;

 

 

 

 

 

 

 

 

저작자표시 비영리 변경금지 (새창열림)