국비학원 15일차) ORACLE 예습 (다중행 함수, 그룹함수)

COALESCE NULL이 아닌 첫번째 칼럼의 값 반환 ,NVL과 똑같이 사용 가능!
COALESCE 함수에 입력된 인자 중에서 NULL이 아닌 첫 번째 칼럼의 값을 반환한다.

SELECT LAST_NAME, SALARY,
COALESCE(COMMISSION_PCT,0)
FROM EMPLOYEES;

COMMISSION_PCT가 NULL이면 0, 아니라면 그대로의 값 반환해줘


다중행 함수 - 하나의 열의 출력 결과를 담다

SUM 합계
COUNT 갯수
MAX 최대값
MIN 최소값
AVG 평균값


SUM 합계 (NULL 제외)
SUM(합계를 구할 데이터,칼럼)

SELECT
SUM(DISTINCT SAL),
SUM(ALL SAL),
SUM(SAL)
FROM EMP;

1.SAL 중복값은 제외하고 합해줘
2,3 SAL 전부 합한 값 출력해줘



COUNT 데이터 개수(NULL 제외)

SELECT COUNT(*)
FROM EMP;

테이블의 모든 행 수를 알려줘


COUNT+DISTINCT 이용하기

SELECT
COUNT(DISTINCT SAL),
COUNT(SAL)
FROM EMP;

1.SAL이 겹치는 행 빼면 총 몇 행이야?
2.SAL이 총 몇 행이야?



MAX 최댓값
MAX(DISTINCT나 ALL나 아무것도 선택안함,최대값을 구할 데이터)

SELECT MAX(SAL)
FROM EMP
WHERE DEPTNO = 10;

DEPTNO이 10인 사람 중에 SAL이 제일 높은 사람은?


MIN 최솟값
(DISTINCT나 ALL이나 아무것도 선택 안함,최솟값을 구할 데이터)

SELECT MIN(SAL)
FROM EMP
WHERE DEPTNO = 10;

DEPTNO이 10인 사람 중에 SAL이 제일 낮은 사람은?




MAX와 MIN은 날짜 데이터에도 이용이 가능하다!!!

SELECT MAX(HIREDATE)
FROM EMP
WHERE DEPTNO = 20;

DEPTNO이 20인 사람 중에 제일 최근 HIREDATE 값을 가진 사람은?



SELECT MIN(HIREDATE)
FROM EMP
WHERE DEPTNO = 20;

DEPTNO이 20인 사람 중에 HIREDATE가 제일 오래 된 사람은?



AVG 평균
AVG(DISTINCT나 ALL이나 아무값도 지정안함,평균 구할 데이터)

SELECT AVG(SAL)
FROM EMP
WHERE DEPTNO = 30;

DEPTNO이 30인 사람들의 SAL 평균 값은?



SELECT AVG(DISTINCT SAL)
FROM EMP
WHERE DEPTNO = 30;

DEPTNO이 30인 사람 중에 중복 값을 제거한 평균 SAL은?



GRUOP BY 의미 있는 데이터끼리 묶어서 그룹화 하다

SELECT 칼럼들
FROM 테이블
WHERE 조건식
GRUOP BY 그룹화 할 칼럼들 지정
ORDER BY 정렬
-WHERE 다음 순서가 GROUP BY



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

DEPTNO이 같은 것끼리 묶고,
그 그룹들의 SAL의 평균을 알려줘


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

일단 DEPTNO 기준으로 먼저 그룹을 묶어
'그 그룹 내'에서 JOB을 기준으로 다시 묶어
그 다음 그들의 SAL 평균을 구해줘


GROUP BY 쓸 때 주의할 점
다중행 함수를 사용하지 않은 일반 칼럼은
GROUP BY로 묶지 않는 이상, SELECT 절에서 사용 불가



HAVING -> GROUP BY의 조건
GROUP BY 다음 줄에 넣으면 된다


SELECT DEPTNO, JOB, AVG(SAL)
FROM EMP
GROUP BY DEPTNO, JOB
HAVING AVG(SAL)>=2000
ORDER BY DEPTNO, JOB;

DEPTNO을 기준으로 묶은 다음, 그 기준으로 JOB을 묶어
근데 SAL 평균이 2000 이상인 행만 출력해







---------그룹화와 관련 된 함수!



ROLLUP, CUBE, GROUPING SETS


ROLLUP, CUBE 그룹화 데이터의 합계

ROLLUP(그룹화 칼럼들 지정) //그룹화한 하나의 기준으로만 출력할게

SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB);

DEPTNO 기준으로, 다시 JOB을 기준으로
DEPTNO, JOB, COUNT(해당하는 인원 세기), 최고SAL,합계SAL,평균SAL 구할거야
그리고 DEPTNO, JOB을 기준으로 전체 데이터도 알려주고
그 전체 데이터의 합(?)한 것도 알려줄게



CUBE(그룹화 칼럼들 지정) //모든 기준 조합 전부 출력할게

SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB)
ORDER BY DEPTNO,JOB;

DEPTNO 기준으로, 다시 JOB을 기준으로
DEPTNO, JOB, COUNT(해당하는 인원 세기), 최고SAL,합계SAL,평균SAL 구할거야
먼저 DEPTNO와 JOB을 기준으로 한 전체 데이터 보여줄 거고
다음엔 JOB기준으로 한 전체 데이터 보여줄게^^



ㄴ얘네 두개 모르겠음...이해 안 감



GROUPING SETS 그룹화 열이 여러개 일 때, 각 열별 그룹화 결과값
GROUPING SETS(그룹화 할 칼럼들)

SELECT DEPTNO, JOB, COUNT(*)
FROM EMP
GROUP BY GROUPING SETS(DEPTNO, JOB)
ORDER BY DEPTNO, JOB;

DEPTNO을 그룹화해서 갯수가 몇인지,
JOB을 그룹화해서 갯수가 몇인지,
각각 따로 알려줄게




GROUPING 그룹화 한 상태로 결과 집계 되었는지 확인
현재 출력 되는 데이터가 어떤 칼럼의 그룹화를 통해 나온 것인가?

GROUPING(GROUP BY절에 ROLLUP 또는 CUBE에 명시한 칼럼 이름)
그룹화 되었다면 0, 그룹화 안됐다면 1 반환


SELECT DEPTNO, JOB, COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL),
GROUPING(DEPTNO),
GROUPING(JOB)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB)
ORDER BY DEPTNO, JOB;

DEPTNO이랑 JOB이 CUBE형식으로 그룹화 되었는지 확인해줘(0,1로)



DECODE+GROUPING

SELECT
DECODE(GROUPING(DEPTNO),
1,'ALL_DEPT',
DEPTNO)
AS DEPTNO,
DECODE(GROUPING(JOB),
1, 'ALL_JOB',
JOB)
AS JOB,
COUNT(*), MAX(SAL), SUM(SAL), AVG(SAL)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB)
ORDER BY DEPTNO, JOB;

DEPTNO을 그루핑하는데,
값이 1이면(없으면) 'ALL_DEPT'라고 뜨게 해주고
0이면(있으면) DEPTNO 그대로 출력해줘

JOB을 그루핑 할 건데
값이 1이면(없으면) ALL_JOB 이라고 뜨게 해주고
0이면(있으면) JOB 그대로 출력해줘




GROUPING_ID 한번에 여러 칼럼 그룹화됐는지 확인
GROUPING_ID(그룹화 확인할 여러 칼럼)


SELECT DEPTNO, COUNT(*), SUM(SAL),
GROUPING(DEPTNO),
GROUPING(JOB),
* GROUPING_ID(DEPTNO,JOB)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB)
ORDER BY DEPTNO, JOB;

* DEPTNO과 JOB이 그룹화 됐는지 확인할 거야
전부 그룹화 됐으면 0
홀수(DEPTNO) 그룹이 안 됐으면 1
짝수(JOB) 그룹이 안 됐으면 2
둘다 안 됐으면 3 이라고 출력해줘



LISTAGG 가로 나열

LISTAGG(나열할 칼럼, 칼럼 사이의 구분자)
WITHIN GROUP(ORDER BY 나열할 정렬 기준 칼럼)


SELECT DEPTNO,
LISTAGG(ENAME,',')
WITHIN GROUP(ORDER BY SAL DESC) AS ENAMES
FROM EMP
GROUP BY DEPTNO;

DEPTNO을 그룹화하고
ENAME을 , 구분자로 가로로 출력할 건데 그 칼럼은 ENAMES 이거야
근데 가로 나열할 때 SAL을 내림차순으로 하는 기준으로 출력해줘



PIVOT 행을 열로 바꿔!!
UNPIVOT 열을 행으로 바꿔!!

PIVOT 행->열

SELECT *
FROM (SELECT DEPTNO, JOB, SAL FROM EMP)
PIVOT (MAX(SAL)FOR DEPTNO IN (10,20,30))
ORDER BY JOB;

모든걸 가져올 거야
(EMP에서 가져온 DEPTNO, JOB, SAL 모두)
JOB을 기준으로 겹치는 DEPTNO을 합쳐서 각 JOB의 MAX(SAL)을 가져올 건데,
DEPTNO의 10, 20, 30 순이고, 각 칼럼 이름도 10,20,30 으로 할래

 

 

 

 

 

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

설명이 어렵다....결과로 대신

 

 

 

SELECT DEPTNO,
MAX(DECODE(JOB, 'CLERK', SAL)) AS "CLERK",
MAX(DECODE(JOB, 'SALESMAN', SAL)) AS "SALESMAN",
MAX(DECODE(JOB, 'PRESIDENT', SAL)) AS "PRESIDENT",
MAX(DECODE(JOB, 'MANAGER', SAL)) AS "MANAGER",
MAX(DECODE(JOB, 'ANALYST', SAL)) AS "ANALYST"
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;

 

JOB의 'CLERK'는 SAL이 될 거야< 그거의 MAX치를 가져오고, 그 칼럼 이름은 "CLERK"이거야

.

.

.

DEPTNO 그룹으로 묶고, DEPTNO 오름차순으로 출력해줘

 

 

 

 

UNPIVOT <- 반대 -> PIVOT


SELECT *
FROM( SELECT DEPTNO,
MAX(DECODE(JOB, 'CLERK', SAL)) AS "CLERK",
MAX(DECODE(JOB, 'SALESMAN', SAL)) AS "SALESMAN",
MAX(DECODE(JOB, 'PRESIDENT', SAL)) AS "PRESIDENT",
MAX(DECODE(JOB, 'MANAGER', SAL)) AS "MANAGER",
MAX(DECODE(JOB, 'ANALYST', SAL)) AS "ANALYST"
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO)
UNPIVOT(SAL FOR JOB IN(CLERK, SALESMAN, PRESIDENT, MANAGER, ANALYST))
ORDER BY DEPTNO, JOB;