📚03. 복수행 함수

📘GROUP 함수의 종류

GROUP BY : 공통적인 데이터 별로 묶어서 연산처리를 하고싶을때 사용

쉽게 말하자면 전체 회

📖예시

SELECT COUNT(*) 
     , COUNT(EMPNO)
     , COUNT(COMM)
     , SUM(COMM)
     , AVG(COMM)
     -- COMM이 NULL이 아닌 사람(4명) 만큼 나눠짐
     , AVG(NVL(COMM,0)) "NVL_AVG"
     -- NULL값을 0으로 하면 나누는 사람수가 많아지므로 달라짐
     , MAX(COMM)
     , MIN(COMM)
     , MAX(hiredate)
     , MIN(hiredate)
    FROM EMP;

SELECT STDDEV(COMM)
     , VARIANCE(COMM)
     FROM EMP;

📖GROUP BY

SELECT DEPTNO
     , AVG(SAL)
    FROM EMP
GROUP BY DEPTNO;

여기서 만약에 GROUP 대신 ORDER 사용시 오류가 발생한다.

SELECT DEPTNO
     , JOB
     , SAL
     FROM EMP
 ORDER BY DEPTNO;

SELECT DEPTNO
     , JOB
     , AVG(SAL)
     FROM EMP
 GROUP BY DEPTNO , JOB
 ORDER BY DEPTNO;
-- 만약에 GROUP BY DEPTNO , JOB 안하면 오류발생

SELECT DEPTNO NO
     , JOB
     , AVG(SAL)
     FROM EMP
  --   GROUP BY NO, SAL
     GROUP BY DEPTNO, JOB;

📖문제1.

평균 월급 2000 이상의 부서번호와 평균 연봉 출력

SELECT DEPTNO
     , ROUND(AVG(SAL),2)
     FROM EMP
 GROUP BY DEPTNO 
 HAVING AVG(SAL)>=2000;
-- 조건절 WHERE이 아니라 HAVING 사용

📖문제2.

CAL 테이블 이용해서 달력 만들기
(참고로 CAL 테이블은 아래와 같다)

SELECT MIN(DECODE(DAY,'SUN',DAYNO)) "SUN"
     , MIN(DECODE(DAY,'MON',DAYNO)) "MON"
     , MIN(DECODE(DAY,'TUE',DAYNO)) "TUE"
     , MIN(DECODE(DAY,'WED',DAYNO)) "WED"
     , MIN(DECODE(DAY,'THU',DAYNO)) "THU"
     , MIN(DECODE(DAY,'FRI',DAYNO)) "FRI"
     , MIN(DECODE(DAY,'SAT',DAYNO)) "SAT"
    FROM CAL
    GROUP BY WEEKNO
    ORDER BY WEEKNO;

(순서)

SELECT DECODE(DAY,'SUN',DAYNO) "SUN"
     , DECODE(DAY,'MON',DAYNO) "MON"
     , DECODE(DAY,'TUE',DAYNO) "TUE"
     , DECODE(DAY,'WED',DAYNO) "WED"
     , DECODE(DAY,'THU',DAYNO) "THU"
     , DECODE(DAY,'FRI',DAYNO) "FRI"
     , DECODE(DAY,'SAT',DAYNO) "SAT"
    FROM CAL;

이렇게 나오는데 null을 없애기 위해서 GROUP 함수 사용
(MIN, MAX 등 사용가능)

그래서 MIN을 씌우고 GROUP BY WEEKNO;추가

SELECT MIN(DECODE(DAY,'SUN',DAYNO)) "SUN"
     , MIN(DECODE(DAY,'MON',DAYNO)) "MON"
     , MIN(DECODE(DAY,'TUE',DAYNO)) "TUE"
     , MIN(DECODE(DAY,'WED',DAYNO)) "WED"
     , MIN(DECODE(DAY,'THU',DAYNO)) "THU"
     , MIN(DECODE(DAY,'FRI',DAYNO)) "FRI"
     , MIN(DECODE(DAY,'SAT',DAYNO)) "SAT"
    FROM CAL
    GROUP BY WEEKNO;

이라면 아래와 같이 나오는데,

왜 이렇게 나오는가?
그것은 CAL 테이블의 DAYNO가 VARCHAR이기 때문.
즉 문자열!!
그래서 아스키코드를 비교해서 출력된다.(그래서 엉망진창 순서임)

    select ascii('29') --50
    from dual;
    
    select ascii('8')--56
    from dual;

📖피벗 테이블 사용한 경우

SELECT * FROM
(SELECT WEEKNO
     , DAY
     , DAYNO
    FROM CAL
)
PIVOT --피벗테이블
(MAX(DAYNO)
 FOR DAY IN ('SUN'
           , 'MON'
           , 'TUE'
           , 'WED'
           , 'THU'
           , 'FRI'
           , 'SAT'
           )
)
ORDER BY WEEKNO;

📖문제3.

EMP 테이블에서 부서별로 각 직급별 인원이 몇명인지 계산해서 출력

SELECT  DEPTNO
      , COUNT(DECODE(JOB,'CLERK','CLERK')) "CLERK"
      , COUNT(DECODE(JOB,'MANAGER','MANAGER')) "MANAGER"
      , COUNT(DECODE(JOB,'PRESIDENT','PRESIDENT')) "PRESIDENT"
      , COUNT(DECODE(JOB,'ANALYST','ANALYST')) "ANALYST"
      , COUNT(DECODE(JOB,'SALESMAN','SALESMAN')) "SALESMAN"
    FROM EMP 
    group by DEPTNO 
    ORDER BY DEPTNO;

--강사님 방법
SELECT  DEPTNO
      , SUM(DECODE(JOB,'CLERK','1','0')) "CLERK"
      , SUM(DECODE(JOB,'MANAGER','1','0')) "MANAGER"
      , SUM(DECODE(JOB,'PRESIDENT','1','0')) "PRESIDENT"
      , SUM(DECODE(JOB,'ANALYST','1','0')) "ANALYST"
      , SUM(DECODE(JOB,'SALESMAN','1','0')) "SALESMAN"
    FROM EMP 
    group by DEPTNO 
    ORDER BY DEPTNO;


SELECT  DEPTNO
      , COUNT(DECODE(JOB,'CLERK','1AFDSF')) "CLERK"
      , COUNT(DECODE(JOB,'MANAGER','1AASD')) "MANAGER"
      , COUNT(DECODE(JOB,'PRESIDENT','1DA')) "PRESIDENT"
      , COUNT(DECODE(JOB,'ANALYST','1A')) "ANALYST"
      , COUNT(DECODE(JOB,'SALESMAN','DF')) "SALESMAN"
    FROM EMP 
    group by DEPTNO 
    ORDER BY DEPTNO;      
    -- 아무거나 넣어도 COUNT여서 갯수를 샌다.(결과 변화 없음)
    --NULL만 아니면 됨

📖피벗테이블 사용한 경우

SELECT * FROM
(SELECT DEPTNO
     , JOB
     , EMPNO
    FROM EMP 
)    
PIVOT (COUNT(EMPNO) FOR JOB IN('CLERK'
                             , 'MANAGER'
                             , 'PRESIDENT'
                             , 'ANALYST'
                             , 'SALESMAN'
                             ))
ORDER BY DEPTNO;

📖문제4.

위와 같이 출력하시오

SELECT DEPTNO
     , COUNT(DECODE(JOB,'CLERK','A')) "'CLERK'_COUNT"
     , SUM(DECODE(JOB,'CLERK',SAL)) "'CLERK'_SUM"
     , COUNT(DECODE(JOB,'MANAGER','A')) "'MANAGER'_COUNT"
     , SUM(DECODE(JOB,'MANAGER',SAL)) "'MANAGER'_SUM"
     , COUNT(DECODE(JOB,'PRESIDENT','A')) "'PRESIDENT'_COUNT"
     , SUM(DECODE(JOB,'PRESIDENT',SAL)) "'PRESIDENT'_SUM"
     , COUNT(DECODE(JOB,'ANALYST','A')) "'ANALYST'_COUNT"
     , SUM(DECODE(JOB,'ANALYST',SAL)) "'ANALYST'_SUM"
     , COUNT(DECODE(JOB,'SALESMAN','A')) "'SALESMAN'_COUNT"
     , SUM(DECODE(JOB,'SALESMAN',SAL)) "'SALESMAN'_SUM"
    FROM EMP
 GROUP BY DEPTNO

📖피벗테이블 사용한 경우

SELECT * FROM
(SELECT DEPTNO
     , JOB
     , EMPNO
     ,SAL
    FROM EMP 
)    
PIVOT (COUNT(EMPNO)AS COUNT, SUM(SAL) AS SUM FOR JOB IN('CLERK'
                                                      , 'MANAGER'
                                                      , 'PRESIDENT'
                                                      , 'ANALYST'
                                                      , 'SALESMAN'
                                                       ))
GROUP BY DEPTNO                                                      

📖LAG,LEAD

이전행, 다음행 값 가져오는 함수

SELECT ENAME
      ,HIREDATE
      ,SAL
      ,LAG(SAL, 1, 0) OVER(ORDER BY HIREDATE)"LAG"
      ,LAG(SAL, 2, 0) OVER(ORDER BY HIREDATE)"LAG"
      ,LAG(SAL, 2) OVER(ORDER BY HIREDATE)"LAG"
      ,LAG(SAL) OVER(ORDER BY HIREDATE)"LAG"
      ,LEAD(SAL,1,0) OVER(ORDER BY HIREDATE)"LEAD"
      ,LEAD(SAL,2,0) OVER(ORDER BY HIREDATE)"LEAD"
      ,LEAD(SAL,2) OVER(ORDER BY HIREDATE)"LEAD"
      ,LEAD(SAL) OVER(ORDER BY HIREDATE)"LEAD"
FROM EMP;

📖Rank

: 순위를 매길때 사용하는 함수

SELECT RANK('SMITH') WITHIN GROUP(ORDER BY ENAME)"RANK"
    FROM EMP
    ORDER BY ENAME;

SELECT EMPNO
     , ENAME
     , SAL
     , RANK() OVER(ORDER BY SAL) AS RANK_ASC
     , RANK() OVER(ORDER BY SAL DESC) AS RANK_DESC
    FROM EMP;

SELECT EMPNO
     , ENAME
     , SAL
     , DEPTNO
     , JOB
     , RANK()OVER (ORDER BY SAL desc) "RANK"
     , DENSE_RANK()OVER (ORDER BY SAL desc) "RANK"
     -- 랭크가 중복되더라도 숫자를 지나가지 않고 그대로 9,9,10,11 이런식으로 랭킹
     , ROW_NUMBER()OVER (ORDER BY SAL desc) "RANK"
     -- 중복 되지 않고,  모든 행의 번호를 고유하게 순차적으로 지정
    FROM EMP;

📖문제5.

위의 값대로 출력하시오

SELECT EMPNO
     , ENAME
     , SAL
     , DEPTNO
     , JOB
     , RANK()OVER ( PARTITION BY DEPTNO, JOB ORDER BY SAL desc) "RANK"
    FROM EMP;

SELECT EMPNO
     , ENAME
     , SAL
     , DEPTNO
     , RANK()OVER ( ORDER BY SAL DESC ) AS RANK
    FROM EMP
    WHERE DEPTNO =10
UNION ALL
SELECT EMPNO
     , ENAME
     , SAL
     , DEPTNO
     , RANK()OVER ( ORDER BY SAL DESC ) AS RANK
    FROM EMP
    WHERE DEPTNO =20
UNION ALL
SELECT EMPNO
     , ENAME
     , SAL
     , DEPTNO
     , RANK()OVER ( ORDER BY SAL DESC ) AS RANK
    FROM EMP
    WHERE DEPTNO =30;

📖문제6.

SELECT P_DATE
     , P_CODE
     , P_QTY
     , P_TOTAL
     , SUM(P_TOTAL)OVER(ORDER BY P_TOTAL) "TOTAL"
    FROM PANMAE
    WHERE P_STORE = 1000;

📖문제7.

SELECT P_DATE
     , P_CODE
     , P_QTY
     , P_TOTAL
     , SUM(P_TOTAL)OVER(PARTITION BY P_CODE ORDER BY P_TOTAL) "TOTAL"
    FROM PANMAE
    WHERE P_STORE = 1000;

📖문제8.

--강사님  방법
SELECT P_CODE
     , P_STORE
     , P_DATE
     , P_QTY
     , P_TOTAL
     , SUM(P_TOTAL)OVER(PARTITION BY P_CODE, P_STORE  ORDER BY P_DATE) "TOTAL"
    FROM PANMAE;

📖추가 문제 및 예시

SELECT P_CODE
     , P_STORE
     , P_QTY
     , SUM(P_QTY) OVER () "TOTAL_QLY"
     , ROUND(P_QTY/SUM(P_QTY) OVER ()*100,2) "QLY_%"
     , ROUND(RATIO_TO_REPORT(SUM(P_QTY)) OVER()*100,2) "QLY_%"
     , ROUND(RATIO_TO_REPORT(P_QTY) OVER()*100,2) "QLY_%"
    FROM PANMAE
    WHERE P_CODE =100
    group by P_CODE
           , P_STORE
           , P_QTY ;

(강사님이 내주신 문제와 답의 사진이 달라서 번외로만 참고하기)

(04.는 없었다!)

📚05. DDL 명령과 Dictionary

📘다양한 오라클 명령어 종류들

---

📖새로운 테이블 생성

create table사용

CREATE TABLE NEW_TABLE
(NO NUMBER (3)--숫자로 된 3자리까지
,NAME VARCHAR2(10)--문자로 된 10자리까지
,BIRTH DATE
);