Oracle 15강 - 순위함수, 테이블 조인

GROUP BY절을 구성할 때
SELECT절은 집계함수를 제외한 일반 컬럼들이 GROUP BY절의 기준이 됨.
~별 앞에있는 글자로 SELECT절 구성하기

사용예)장바구니 테이블에서 2005년 월별,회원별,상품별 판매집계를 조회하시오
--2005년도는 WHERE절에서 추출

 (GROUP BY 절)
  SELECT SUBSTR(A.CART_NO,5,2) AS 월,
         A.CART_MEMBER AS 회원번호,
         A.CART_PROD AS 상품코드,
         SUM(A.CART_QTY) AS 판매수량,
         SUM(A.CART_QTY*B.PROD_PRICE) AS 판매금액합계
    FROM CART A, PROD B
   WHERE B.PROD_ID=A.CART_PROD
     AND CART_NO LIKE '2005%' --2005년에 '판매'된 상품이기때문에 CART에서 찾음
   GROUP BY SUBSTR(A.CART_NO,5,2),A.CART_MEMBER,A.CART_PROD
   ORDER BY 1, 2;
  
 (GROUP BY ROLLUP 절)
  SELECT SUBSTR(A.CART_NO,5,2) AS 월,
         A.CART_MEMBER AS 회원번호,
         A.CART_PROD AS 상품코드,
         SUM(A.CART_QTY) AS 판매수량,
         SUM(A.CART_QTY*B.PROD_PRICE) AS 판매금액합계
    FROM CART A, PROD B
   WHERE B.PROD_ID=A.CART_PROD
     AND CART_NO LIKE '2005%' --2005년에 '판매'된 상품이기때문에 CART에서 찾음
   GROUP BY ROLLUP(SUBSTR(A.CART_NO,5,2),A.CART_MEMBER,A.CART_PROD)
  --가장 왼쪽에 있는 레벨이 하위레벨
   ORDER BY 1, 2;    
  
 (부분 ROLLUP : 일부 분류기준 컬럼이 ROLLUP 밖에 기술된 경우) --ROLLUP절과 컬럼이 동등한 관계
  SELECT SUBSTR(A.CART_NO,5,2) AS 월,
         A.CART_MEMBER AS 회원번호,
         A.CART_PROD AS 상품코드,
         SUM(A.CART_QTY) AS 판매수량,
         SUM(A.CART_QTY*B.PROD_PRICE) AS 판매금액합계
    FROM CART A, PROD B
   WHERE B.PROD_ID=A.CART_PROD
     AND CART_NO LIKE '2005%' 
   GROUP BY SUBSTR(A.CART_NO,5,2),ROLLUP(A.CART_MEMBER,A.CART_PROD) 
  --전체집계를 제외한 구분이 나옴
   ORDER BY 1, 2;    
   

ROLLUP은 단계별 집계를 낼 때 유용하게 사용되는 함수
GROUP BY절에 사용되지 않는 일반컬럼은 서브쿼리를 사용해야 한다.
ex)SELECT절에 '월'이라는 컬럼이 오는데 GROUP BY절에 와서는 안된다면 서브쿼리를 써야함.

6) CUBE(col,[,col,...])

• 주어진 컬럼들을 조합하여 나올 수 있는 모든 경우의 가지수만큼 다양한 집계반환

• 사용된 컬럼의 수가 n개일 때 집계의 종류는 2^n 가지임

           SELECT SUBSTR(A.CART_NO,5,2) AS 월,
                   A.CART_MEMBER AS 회원번호,
                   A.CART_PROD AS 상품코드,
                   SUM(A.CART_QTY) AS 판매수량,
                   SUM(A.CART_QTY*B.PROD_PRICE) AS 판매금액합계
              FROM CART A, PROD B
             WHERE B.PROD_ID=A.CART_PROD
               AND CART_NO LIKE '2005%' 
             GROUP BY CUBE(SUBSTR(A.CART_NO,5,2),A.CART_MEMBER,A.CART_PROD) 
             --전체집계를 제외한 구분이 나옴
             ORDER BY 1, 2;

순위함수(분석함수)

• 특정 컬럼을 기준으로 크기에 따른 순위를 구하는 함수
• RANK() OVER, DENSE_RANK() OVER, ROW_NUMBER() OVER
  --동점자 처리를 어떻게 할 것인가가 다름
• 그룹내에서 순위는 RANK() OVER(PARTITION ~) 함수 사용

1)RANK() OVER
. 순위 부여시 중복값(같은값)이 발생되면 중복 값의 갯수만큼 건너 뛰고 다음 순위 부여
ex) 90,80,80,80,70 =>(1,2,2,2,5..)
. SELECT 절에 사용 (다른 절에서는 사용불가)
(사용형식)
RANK() OVER(ORDER BY 컬럼명 [ASC|DESC]) [AS 별칭]

사용예)사원테이블에서 80번 부서직원 중 입사년도가 가장 빠른 직원부터 순위를 부여하여 조회하시오
Alias는 사원번호,사원명,입사일자,순위

  SELECT EMPLOYEE_ID AS 사원번호,
         EMP_NAME AS 사원명,
         HIRE_DATE AS 입사일자,
         RANK() OVER(ORDER BY HIRE_DATE ASC) AS 순위
    FROM HR.EMP
   WHERE DEPARTMENT_ID=80;

ROW_NUMBER() OVER : 순차적으로 순위가 부여됨(동점자여도 순서에 따라 순위가 다름)

• 중복값을 아예 무시하고 순위를 부여

2)DENSE_RANK() OVER
. 중복 순위 발생 후 다음 순위를 연속된 값을 부여
ex)90,80,80,80,70 => 1,2,2,2,3,...

사용예)상품테이블에서 매출가격 순으로 등수를 DENSE_RANK()함수 형식으로
부여하시오.

  SELECT PROD_ID AS 상품코드,
         PROD_NAME AS 상품명,
         PROD_PRICE AS 판매가,
         RANK() OVER(ORDER BY PROD_PRICE DESC) AS 순위1,
         DENSE_RANK() OVER(ORDER BY PROD_PRICE DESC) AS 순위2
    FROM PROD;   

의사컬럼 SUDO컬럼 : 시스템이 가지고 있는 가상 컬럼

3)ROW_NUMBER() OVER --제일 많이 사용
. 중복값에 관계없이 SEQUENCE(순차적인 순위 값) 값을 반환 --차례대로 반환
ex)90,80,80,80,70 => 1,2,3,4,5,...

사용예)상품테이블에서 매출가격 순으로 등수를 ROW_NUMBER()함수 형식으로 부여하시오.

  SELECT PROD_ID AS 상품코드,
         PROD_NAME AS 상품명,
         PROD_PRICE AS 판매가,
         RANK() OVER(ORDER BY PROD_PRICE DESC) AS 순위1,
         DENSE_RANK() OVER(ORDER BY PROD_PRICE DESC) AS 순위2, 
         ROW_NUMBER() OVER(ORDER BY PROD_PRICE DESC) AS 순위3 
    FROM PROD;     

4)그룹내 순위
. 그룹별로 순위를 구하기 위해 사용되는 순위함수
(사용형식)
RANK() OVER(PARTITION BY 컬럼명1,[,컬럼명2,...] ORDER BY 컬럼명11[,컬럼명12,...][ASC|DESC]
- '컬럼명1[,컬럼명2,...]' : 그룹화의 기준 컬럼명
- '컬럼명11[,컬럼명12,..]' : 정렬의 기준 컬럼명

사용예)상품테이블에서 분류별로 상품의 매출가격 순으로 등수를 부여하시오.

SELECT PROD_ID AS 상품코드,
         PROD_NAME AS 상품명,
         PROD_LGU AS 분류코드,
         PROD_PRICE AS 가격,
         RANK() OVER(PARTITION BY PROD_LGU ORDER BY PROD_PRICE DESC)순위
    FROM PROD;
    
    
    

테이블 조인

• 관계형 데이터베이스의 주요 연산중 하나
• 다수개의 테이블에 분산된 자료를 테이블 간에 설정된 관계를 이용하여 참조하는 연산
• 구분
  . 일반조인, ANSI 조인 (둘 다 배워야 함)
  --일반조인 : 자사의 DBMS에서만 실행됨 / ANSI조인 : 해당 DBMS가 어떤 것이든 다 사용가능
  . 내부조인, 외부조인
  -- 내부조인 : 조인조건을 만족하는 부분만 내보내는 조인 / 외부조인 : 조인조건 외의 테이블에
  . 동등조인,세미조인,안티조인,카타시안조인,등등
  --동등조인 : 동등한관계, 거의 90%임
  --외부조인은 실무에서 거의 사용안함.
  --안티조인 : 조인조건에서 '=' 부호가 아닌 다른 부호가 사용되는 조인
  --카타시안조인 : 조인 조건을 잘못썼을 경우 나오는 조인, 절대 사용해서는 안되는 조인
  관계가 없으면 조인도 없다.
  

FROM절에 테이블, 뷰만 와야함, 서브쿼리도 올 수 있는데 독립적으로 실행되어야 함.

1. 일반조인과 ANSI 조인

• 일반조인은 DBMS 회사별로 제공되는 조인 형식
• ANSI 조인 : 미국표준위원회에서 제정한(89년 기준안) 조인문 형식

2. 내부조인
   1) 동등조인(Equi-JOIN)
   • 조인조건에 '='연산자가 사용된 조인
   • 가장 일반적인 조인 형식

(사용형식:일반조인)
SELECT 컬럼list
FROM 테이블명1 [별칭1], 테이블명2 [별칭2],테이블명3 [별칭3],...] --별칭 반드시 사용하기
WHERE 조인조건
[AND 일반조건]
. '조인조건' : 사용된 테이블들 간 동일한 데이터를 가진 컬럼명을 '='연산자로 연결한 조건
. 조인조건의 갯수는 적어도 테이블의 수-1개 이상이어야 함
. '일반조건'과 '조인조건'의 기술 순서는 수행과 관계없음

(사용형식:ANSI조인)
SELECT 컬럼list
FROM 테이블명1 [별칭1]
INNER JOIN 테이블명2 [별칭2] ON(조인조건1 [AND 일반조건1])
[INNER JOIN 테이블명3 [별칭3] ON(조인조건2 [AND 일반조건2])]
:
[WHERE 일반조건n]
.'테이블명1'과 '테이블명2'는 반드시 직접 조인 가능해야함
.'테이블명3'은 '테이블명1','테이블명2' 조인 결과와 조인
.'일반조건1'은 '테이블명1','테이블명2'에만 관련된 조건
.'일반조건n'은 모든 테이블에 공통으로 적용되는 조건

사용예)사원테이블에서 2005년 이후 입사한 사원들을 조회하시오.
Alias는 사원번호, 사원명, 부서명, 입사일이다.

 (일반조인) 
  SELECT A.EMPLOYEE_ID AS 사원번호, 
         A.EMP_NAME AS 사원명, 
         B.DEPARTMENT_NAME AS 부서명, 
         A.HIRE_DATE AS 입사일
    FROM HR.EMP A, HR.DEPT B  
   WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID
     AND EXTRACT(YEAR FROM A.HIRE_DATE)>=2008
   ORDER BY 3; --일반조건

  (ANSI조인) 
  SELECT A.EMPLOYEE_ID AS 사원번호, 
         A.EMP_NAME AS 사원명, 
         B.DEPARTMENT_NAME AS 부서명, 
         A.HIRE_DATE AS 입사일
    FROM HR.EMP A
   INNER JOIN HR.DEPT B ON(A.DEPARTMENT_ID=B.DEPARTMENT_ID
         AND EXTRACT(YEAR FROM A.HIRE_DATE)>=2008)
   ORDER BY 3;

사용예)미국내에 있는 부서별 인원수를 조회하시오
Alias는 부서코드,부서명,인원수

  (일반)
  SELECT B.DEPARTMENT_ID AS 부서코드,
         A.DEPARTMENT_NAME AS 부서명,
         COUNT(*) AS 인원수
    FROM HR.DEPT A, HR.EMP B, HR.LOCATIONS C 
   WHERE A.LOCATION_ID=C.LOCATION_ID
     AND A.DEPARTMENT_ID=B.DEPARTMENT_ID
     AND UPPER(C.COUNTRY_ID)='US'  --여기에 !를 붙이면 미국 외에 있는 부서
   GROUP BY B.DEPARTMENT_ID,A.DEPARTMENT_NAME   
   ORDER BY 1;
   
  (ANSI)
  SELECT B.DEPARTMENT_ID AS 부서코드,
         A.DEPARTMENT_NAME AS 부서명,
         COUNT(*) AS 인원수
    FROM HR.DEPT A
   INNER JOIN HR.EMP B ON(A.DEPARTMENT_ID=B.DEPARTMENT_ID)
   INNER JOIN HR.LOCATIONS C ON(A.LOCATION_ID=C.LOCATION_ID AND C.COUNTRY_ID='US')
   GROUP BY B.DEPARTMENT_ID,A.DEPARTMENT_NAME
   ORDER BY 1;

사용예)2005년도 5월 거래처별 매입현황을 조회하시오
Alias는 거래처코드,거래처명,매입수량,매입금액이다.

  (일반)
  SELECT C.BUYER_ID AS 거래처코드,
         C.BUYER_NAME AS 거래처명,
         SUM(A.BUY_QTY) AS 매입수량,
         SUM(A.BUY_QTY*B.PROD_COST) AS 매입금액
    FROM BUYPROD A, PROD B, BUYER C
   WHERE A.BUY_PROD=B.PROD_ID
     AND B.PROD_BUYER=BUYER_ID
     AND A.BUY_DATE BETWEEN TO_DATE('20050501') AND TO_DATE('20050531')
   GROUP BY C.BUYER_ID,C.BUYER_NAME
   ORDER BY 1;
  
  (ANSI)
  SELECT C.BUYER_ID AS 거래처코드,
         C.BUYER_NAME AS 거래처명,
         SUM(A.BUY_QTY) AS 매입수량,
         SUM(A.BUY_QTY*B.PROD_COST) AS 매입금액
    FROM BUYPROD A
   INNER JOIN PROD B ON(A.BUY_PROD=B.PROD_ID)
   INNER JOIN BUYER C ON(B.PROD_BUYER=BUYER_ID 
          AND A.BUY_DATE BETWEEN TO_DATE('20050501') AND TO_DATE('20050531'))
   GROUP BY C.BUYER_ID,C.BUYER_NAME
   ORDER BY 1;