Exists 문제풀이, 집계함수/분석함수 RANK(),DENSE_RANK()차이점

<학습목표>
*1.rank(), rank() over
 2.rownum**, rowid (**실무多,페이징처리)
 3.ratio_to_repect(실무x) 
 4.rollup / cube/ grouping set <=> 집합과의 관계는? (시험 o, 실무x)
 5.row_number()over (**실무多,페이징처리) 
 6.lag/lead (시험 o, 실무x)

 

--EXISTS 복습 
--P.265
--EXISTS 심경
--두 집합의 중간에 AND EXISTS를 쓰고
--B 집합을 괄호로 묶은 후 
--속세의 연결고리를 연결해주면 
--교집합이 되느니라. 
 
--P.266
--2005년도 구매금액 2천만 이상 우수고객으로 지정하여 
--검색하시오 ?
--(Alias는 회원ID, 회원명, '우수고객’)
--(구매금액 : SUM(CART.CART_QTY * PROD.PROD_SALE))
SELECT M.MEM_NAME 회원명
     , M.MEM_ID 회원ID
     , '우수고객'
FROM MEMBER M
WHERE EXISTS 
(SELECT C.CART_MEMBER 회원ID
    ,  SUM(C.CART_QTY * P.PROD_SALE) 구매금액
FROM CART C, PROD P
WHERE P.PROD_ID = C.CART_PROD
AND SUBSTR(C.CART_NO,1,4) ='2005'
AND M.MEM_ID = C.CART_MEMBER
GROUP BY C.CART_MEMBER
HAVING SUM(C.CART_QTY * P.PROD_SALE) >= 20000000);
 
--상품분류 별 3개 이상인 상품을 '다중상품'으로 지정하여
--검색해보자.
--(Alias는 상품분류코드, 상품분류명, '다중상품’)
--(상품의수 : COUNT(PROD_ID))
--LPROD 및 PROD 테이블을 활용해보자
 
SELECT L.LPROD_GU 상품분류코드
     , L.LPROD_NM 상품분류명
     , '다중상품'
FROM LPROD L
WHERE EXISTS
(
SELECT P.PROD_LGU
    , COUNT(P.PROD_ID)
FROM PROD P
WHERE P.PROD_LGU = L.LPROD_GU
GROUP BY P.PROD_LGU
HAVING COUNT(P.PROD_ID)>=3
);
 
--SUBQUERY
SELECT P.PROD_LGU   상품분류코드
     , (SELECT L.LPROD_NM FROM LPROD L WHERE L.LPROD_GU = P.PROD_LGU)  상품분류명
     , '다중상품'
     , COUNT(P.PROD_ID)
FROM   PROD P
GROUP BY P.PROD_LGU
HAVING COUNT(P.PROD_ID) >= 3;
 
--JOIN
SELECT P.PROD_LGU   상품분류코드
     , L.LPROD_NM  상품분류명
     , '다중상품'
     , COUNT(P.PROD_ID)
FROM   PROD P, LPROD L
WHERE  P.PROD_LGU = L.LPROD_GU
GROUP BY P.PROD_LGU, L.LPROD_NM 
HAVING COUNT(P.PROD_ID) >= 10;
 
--EXISTS 문제)
--2005년도 매입금액 1천만원 이상 우수거래처로 지정하여 
--검색하시오 ?
--(Alias는 거래처코드, 거래처명, '우수거래처’)
--(구매금액 : SUM(BP.BUY_QTY * BP.BUY_COST))
 
SELECT B.BUYER_ID 거래처코드
     , B.BUYER_NAME 거래처명
     , '우수거래처'
FROM BUYER B
WHERE EXISTS
(
SELECT P.PROD_BUYER
     , SUM(BP.BUY_QTY * BP.BUY_COST)
FROM PROD P, BUYPROD BP
WHERE P.PROD_ID=BP.BUY_PROD
AND EXTRACT(YEAR FROM BP.BUY_DATE)='2005'
AND B.BUYER_ID=P.PROD_BUYER
GROUP BY P.PROD_BUYER
HAVING SUM(BP.BUY_QTY * BP.BUY_COST)>=10000000
);
 
 
--SUBQUERY
SELECT P.PROD_BUYER 거래처코드
     , (SELECT B.BUYER_NAME FROM BUYER B WHERE P.PROD_BUYER=B.BUYER_ID) 거래처명
     , SUM(BP.BUY_QTY * BP.BUY_COST) 매입금액
FROM PROD P, BUYPROD BP
WHERE P.PROD_ID=BP.BUY_PROD
AND EXTRACT(YEAR FROM BP.BUY_DATE)='2005'
GROUP BY P.PROD_BUYER
HAVING SUM(BP.BUY_QTY * BP.BUY_COST)>=10000000;
 

집계함수 / 분석함수 정리
- STDDEV() : 표본표준편차
- VARIANCE() : 분산
-* RANK() : 값의 그룹에서 값의 순위를 계산
-* DENSE_RANK() : 컬럼이나 표현식에 대하여 순위 계산. 1씩 증가
- GROUP_ID() : GROUP BY 절 결과로부터 중복된 그룹을 구별
- GROUPING() : 해당 컬럼이 그룹에서의 사용유무를 0,1로 반환
- GROUPING_ID() : 행과 관련되는 GROUPING에 대응되는 수치를 반환
- COME_DIST() : 값의 그룹에 있는 값의 누적 분포를 계산
- PERCENT_RANK() : 그룹 수에 대한 값의 순위 퍼센트 반환
- NTILE(N) : 출력결과를 지정한 그룹 수로 N으로 나누어 출력

** ROW_NUMBER() : PARTITION BY절의 정렬 결과에 순위를 부여
-* FIRST_VALUE() : ORDER BY 절의 정렬된 그룹에서 첫번째 값을 반환
-* LAST_VALUE() : ORDER BY 절의 정렬된 그룹에서 마지막 값을 반환
-* LAG(컬럼명, N) : 윈도우의 정렬된 값 중 N의 이전 행 값을 반환
-* LEAD(컬럼명, N) : 현재 행 기준으로 N이후 행 값을 반환

/*
RANK(), DENSE_RANK()차이점
 
DENSE_RANK() : RANK() 함수와 문법은 유사하나, 동일 값에 대한 순위 부여가 다름.
 - 그룹에서 행의 순위를 계산하며, ORDER BY 절에 사용된 컬럼이나 표현식에 대하여 순위를 부여함
   순위는 동일순위의 수와 상관없이 1 증가된 값을 돌려줌.(EX:3위가 2명이어도, 그 다음은 4위/RANK()는 5위)
*/
 
SELECT RANK('c001')
       WITHIN GROUP(ORDER BY CART_MEMBER) "RANK"
     , DENSE_RANK('c001') 
       WITHIN GROUP(ORDER BY CART_MEMBER) "DENSE_RANK"
FROM CART;