집계함수와 분석함수, 페이징처리

--집계함수와 분석함수

1) 집계함수
- COUNT, MAX, MIN, SUM, AVG
- STUDDEV, VARIANCE, RANK, DENSE_RANK
- GROUP_ID, GROUPING,GROUPING_ID
- COME_DIST, PERCENT_RANK
- FIRST_VALUE, LAST_VALUE
- LAG, LEAD 

집계함수명(expr,..) WIRHIN GROUP(
ORDER BY <<값 표현>[ASC|DESC]>,...
[NULL FIRST| NULL LAST]...)

예)
SELECT RANK('c001')
       WITHIN GROUP(ORDER BY CART_MEMBER) "RANK"
     , DENSE_RANK('c001') 
       WITHIN GROUP(ORDER BY CART_MEMBER) "DENSE_RANK"
FROM CART;

 

2)분석함수
- COUNT, MAX, MIN, SUM, AVG
- STUDDEV, VARIANCE, RANK, DENSE_RANK
- NTILE(n), ROW_NUMBER
- COME_DIST, PERCENT_RANK
- FIRST_VALUE, LAST_VALUE
- LAG, LEAD 

분석함수명(인수1...(생략가능))OVER(
<PARTITION BY 표현식> -> 그룹으로 묶는다
<ORDER BY 표현식2[ASC | DESC]>
<window절>)

예)
SELECT CART_MEMBER 회원ID
    ,  CART_QTY 구매수
    , RANK()OVER(PARTITION BY CART_MEMBER ORDER BY CART_QTY DESC) RANK
FROM CART;

SELECT CART_MEMBER 회원ID
    ,  CART_QTY 구매수
    , RANK()OVER(ORDER BY CART_QTY) RANK
    , DENSE_RANK()OVER(ORDER BY CART_QTY DESC) RANK_DESC
FROM CART;

--페이징처리
전체 행의 수 totalCount
페이지별 출력행수 paging
현재페이지 currPage
시작글번호 ((paging*currPage)-paging)+1
종료글번호 paging*currPage

 

--분석용 문법
--장바구니테이블에서 회원들의 아이디와 구매수, 구매수순위출력
SELECT CART_MEMBER 회원ID
    ,  CART_QTY 구매수
    , RANK()OVER(ORDER BY CART_QTY) RANK
    , DENSE_RANK()OVER(ORDER BY CART_QTY DESC) RANK_DESC
FROM CART;
 
--P268
SELECT CART_MEMBER 회원ID
    ,  CART_QTY 구매수
    , RANK()OVER(PARTITION BY CART_MEMBER ORDER BY CART_QTY DESC) RANK
FROM CART;
 
--집계용 문법
SELECT RANK('c001')
       WITHIN GROUP(ORDER BY CART_MEMBER) "RANK"
     , DENSE_RANK('c001') 
       WITHIN GROUP(ORDER BY CART_MEMBER) "DENSE_RANK"
FROM CART;
 
--ROWNUM : 오라클 내부적으로 처리하기 위한 
--         각 레코드에 대한 일련번호
-- JSP, 페이징처리할 때 많이 쓰임
SELECT ROWNUM RNUM, L.*
FROM LPROD L;
 
SELECT ROWNUM RNUM, L.*
FROM LPROD L
ORDER BY LPROD_NM ASC;
 
--위의 해결법 INLINE VIEW
 
SELECT ROWNUM RNUM,T.*
FROM(
    SELECT L.*
    FROM LPROD L
    ORDER BY LPROD_NM ASC
    )T;
 
--페이징처리
SELECT U.*
FROM(
    SELECT ROWNUM RNUM, T.*
    FROM
    (
    SELECT L.*
    FROM LPROD L
    ORDER BY LPROD_NM ASC
    )T
)U
WHERE U.RNUM BETWEEN 6 AND 10;
 
SELECT U.*
FROM(
    SELECT ROWNUM RNUM, T.*
    FROM
    (
    SELECT L.*
    FROM LPROD L
    ORDER BY LPROD_NM ASC
    )T
)U ;
-- ROW_NUMBER()함수(분석함수)
-- 정렬된 결과에 대하여 1로 시작해서 각 행에 유일한 순서를 할당
 
-- 위의 예제와 같은 결과 출력
SELECT ROW_NUMBER() OVER(ORDER BY LPROD_NM ASC) RNUM
     , L.LPROD_ID
     , L.LPROD_GU
     , L.LPROD_NM
FROM LPROD L;
 
--페이징처리
SELECT T.*
FROM
(
    SELECT ROW_NUMBER() OVER(ORDER BY LPROD_NM ASC) RNUM
     , L.LPROD_ID
     , L.LPROD_GU
     , L.LPROD_NM
    FROM LPROD L
)T
WHERE T.RNUM BETWEEN 1 AND 5;
 
SELECT T.*
FROM
(
    SELECT ROW_NUMBER() OVER(ORDER BY LPROD_NM ASC) RNUM
     , L.LPROD_ID
     , L.LPROD_GU
     , L.LPROD_NM
    FROM LPROD L
)T
--WHERE T.RNUM BETWEEN ((paging*currPage)-paging)+1 AND paging*currPage *****
WHERE T.RNUM BETWEEN ((5*1)-5)+1 AND (5*1);
 
 
--ROWID : 테이블의 특정 레코드로 랜덤하게 접근하기 위한 논리적인 주소값
--        -테이블에 한 행을 입력하면 오라클 서버는 입력된 행(ROW)식별자
--         정보로 ROWID를 생성해서 부여함
--        -ROWID는 시스템 데이터로 저장되며 데이터베이스 전체에서 유일한 값
--        (중복되지 않는 고유의 값)을 가짐.
 
--인덱스에 쓰임
--AAAE8X : 데이터객체정보
--AAB : 연관파일번호
--AAALJX : 블록번호
--AAA : 행번호
 
SELECT LPROD_GU
     , LPROD_NM
 
--RATIO_TO_REPORT : 전체대비 해당 ROW의 값이 차지하는 비율을 구해줌
/*
UNION : 합집합(중복1회, 자동정렬O)
UNION ALL : 합집합(중복ALL, 자동정렬X) --컬럼갯수, 자료형이 일치되어야 함
INTERSECT : 교집합(EXISTS, IN)
MINUS : 차집합(NOT EXISTS)
*/
 
SELECT T1.VAL
     , RATIO_TO_REPORT(T1.VAL) OVER () * 100|| '%'
    
FROM
(
    SELECT 10 VAL FROM DUAL
    UNION ALL
    SELECT 20 VAL FROM DUAL
    UNION ALL
    SELECT 30 VAL FROM DUAL
    UNION ALL
    SELECT 40 VAL FROM DUAL
)T1;
 
--위와 같은 결과, 다른 방법
WITH T1 AS(
    SELECT 10 VAL FROM DUAL
    UNION ALL
    SELECT 20 VAL FROM DUAL
    UNION ALL
    SELECT 30 VAL FROM DUAL
    UNION ALL
    SELECT 40 VAL FROM DUAL
)SELECT T1.VAL
     , RATIO_TO_REPORT(T1.VAL) OVER () * 100|| '%'
FROM T1;
 
--a001회원이 구입한 상품의 내역을 활용하여
--구매개수(CART_QTY) 대비 해당 구매개수 값이
--차지하는 비율을 구하기
--ALIAS : 회원ID, 상품코드, 구매수, 차지비율
 
SELECT CART_MEMBER 회원ID
    ,  CART_PROD 상품코드
    ,  CART_QTY 구매수
    , ROUND(RATIO_TO_REPORT(CART_QTY) OVER(PARTITION BY CART_MEMBER) *100,2)||'%'
 
FROM CART;
 
 
--회원별 구매개수(CART_QTY) 대비 해당 구매개수 값이
--차지하는 비율을 구하기
--ALIAS : 회원ID, 상품코드, 구매수, 차지비율
 
SELECT CART_MEMBER 회원ID
    ,  SUM(CART_QTY) 구매수
FROM CART
GROUP BY CART_MAMBER;
 
 
 
--회원별 구매금액 대비 해당 구매개수 값이
--차지하는 비율을 구하기
--ALIAS : 구매순위 회원ID, 구매비율
WITH T AS(
SELECT C.CART_MEMBER 
    ,  SUM(P.PROD_SALE *C.CART_QTY) SUM_QTY
FROM CART C, PROD P
WHERE P.PROD_ID=C.CART_PROD
GROUP BY C.CART_MEMBER
)
SELECT ROW_NUMBER() OVER(ORDER BY T.SUM_QTY DESC) RNUM 
      ,T.CART_MEMBER 회원ID
      ,ROUND(RATIO_TO_REPORT(T.SUM_QTY) OVER()*100,2)||'%' RTO
FROM T, MEMBER M
WHERE T.CART_MEMBER = M.MEM_ID;
 
--예제 테이블생성
create table t_groupby_test(
 userid varchar2(10),
 position varchar2(30),
 age number(10),
 sal number(10)
);
 
--DESCRIBE(스키마구조를 묘사)
--SCHEMA ? 컬럼명, 자료형(문자, 숫자, 날짜...)
--         크기(BYTES), 제약사항(CONSTRAINTS)->P.K, F.K, U.K, N.N, CHECK)
DESC T_GROUPBY_TEST;
/
SELECT * FROM T_GROUPBY_TEST;
/
begin
  for i in 1..100
  loop
    if i <= 30 then
      if i <= 15 then
        insert into t_groupby_test
        values('user'||i, '사원', 24,  2200);       
      else
        insert into t_groupby_test
        values('user'||i, '사원', 27,  2400);      
      end if;
    elsif i > 30 and i <= 60 then
      if i <= 45 then
        insert into t_groupby_test
        values('user'||i, '대리', 28, 3300);         
      else
        insert into t_groupby_test
        values('user'||i, '대리', 31, 3600);      
      end if;
    else
      if i <= 80 then
        insert into t_groupby_test
        values('user'||i, '과장', 32, 4500);         
      else
        insert into t_groupby_test
        values('user'||i, '과장', 35, 5000);      
      end if;   
    end if;
  end loop;
  
  commit;
end;
 
/
 
--1)
SELECT POSITION 직급
     , SAL 연봉
     , SUM(SAL) 연봉합계
FROM T_GROUPBY_TEST
GROUP BY POSITION,SAL
UNION ALL
--2)
SELECT POSITION 직급
     , 0 연봉
     , SUM(SAL) 연봉합계
FROM T_GROUPBY_TEST
GROUP BY POSITION,SAL
UNION ALL
--3)
SELECT '' 직급
     , 0 연봉
     , SUM(SAL) 연봉합계
FROM T_GROUPBY_TEST;
 
-----ROLLUP
 
SELECT POSITION 직급
     , SAL 연봉
     , SUM(SAL) 연봉합계
FROM T_GROUPBY_TEST
GROUP BY ROLLUP(POSITION,SAL);
 
/
 
--1)
SELECT POSITION 직급
     , SAL 연봉
     , SUM(SAL) 연봉합계
FROM T_GROUPBY_TEST
GROUP BY POSITION,SAL
UNION ALL
--2)
SELECT POSITION 직급
     , 0 연봉
     , SUM(SAL) 연봉합계
FROM T_GROUPBY_TEST
GROUP BY POSITION,SAL
--2-2)
UNION ALL
SELECT '' 직급
     , SAL 연봉
     , SUM(SAL) 연봉합계
FROM T_GROUPBY_TEST
GROUP BY SAL
UNION ALL
--3)
SELECT '' 직급
     , 0 연봉
     , SUM(SAL) 연봉합계
FROM T_GROUPBY_TEST;
 
--CUBE
SELECT POSITION 직급
     , SAL 연봉
     , SUM(SAL) 연봉합계
FROM T_GROUPBY_TEST
GROUP BY CUBE(POSITION,SAL);
 
--GROUPING SET
SELECT POSITION 직금
     , SAL 연봉
    ,SUM(SAL) 연봉합계
FROM T_GROUPBY_TEST
GROUP BY GROUPING SETS(POSITION, SAL)
ORDER BY POSITION, SAL;
 
/
--***총 정리
GROUP BY ROLLUP(DNAME, JOB) = 
GROUP BY DNAME, JOB
UNION ALL
GROUP BY DNAME
UNION ALL
--모든 집합 그룹 결과
 
--***
GROUP BY CUBE(DNAME, JOB) =
GROUP BY DNAME, JOB
UNION ALL
GROUP BY DNAME
UNION ALL
GROUP BY JOB
 
--모든 집합 그룹 결과
 
--***
GROUP BY GROUPING SET(DNAME, JOB) = 
GROUP BY DNAME
UNION ALL
GROUP BY JOB
 
--***
GROUPING SETS(A, B, C) = 
GROUP BY A
UNION ALL
GROUP BY B
UNION ALL
GROUP BY C
 
--------------------------
--CRUD
--INSERT 심화
--> BOOM UP INSERT SKILL
 
CREATE TABLE REMAIN
(
  REMAIN_YEAR CHAR(4) NOT NULL,     -- 해당 년도
  REMAIN_PROD VARCHAR2(10) NOT NULL, -- 상품 코드
  REMAIN_J_00 NUMBER(5),             -- 전년 재고
  REMAIN_I    NUMBER(5),             -- 입고
  REMAIN_O    NUMBER(5),             -- 출고
  REMAIN_J_99 NUMBER(5),             -- 현재고
  REMAIN_DATE DATE,                  -- 처리일자
  CONSTRAINT PK_REMAIN PRIMARY KEY(REMAIN_YEAR, REMAIN_PROD),
  CONSTRAINT FR_REMAIN_PROD FOREIGN KEY(REMAIN_PROD) REFERENCES PROD(PROD_ID)
);