#4 복수행 함수

행의 그룹에 대해 출력을 제공

---요약---

1. 집계함수

- 행들의 그룹에 대해 하나의 행 결과를 생성 ( 하나의 그룹 - 하나의 행 )

- group by 를 생략하면, 전체 테이블에 대해 집계를 수행

- 개별 행에는 수행되지 않음

- 그룹에 속한 행만큼 결과를 출력

- having 절로 그룹핑한 조건을 검색

 

- rollup: n+1 소계 -> n개의 컬럼 지정 (뒤의 컬럼을 하나씩 제거하면서(~grand total) 그룹 생성)

- cube: 2^n 모든 소계 -> 가능한 모든 컬럼의 조합을 grouping

- grouping sets: n개의 그룹 구성

 

2. 분석 함수

- 행들의 그룹에 대해 여러 행 결과를 생성 (하나의 그룹 - 여러행 결과 )

- 분석 함수에 추가 인자가 필요

- 분석 절이 over 필요

- 그룹에 속한 모든 행에 대해 집계를 수행해서 하나의 행을 출력

- 테이블에 존재하는 행마다 그룹에 대한 연산을 통해서 행마다 하나의 결과

 

-> 분석함수가 집계함수보다 빠르다

group by  없이 그룹에 대해 분석과정 수행 가능

 

pivot ( 집계함수 for 컬럼 in (리터럴) )

- 간접 group by : pivot 절에 등장하지 않은 컬럼들에 대해 그룹핑 됨

 (집계함수는 해당 그룹내에서 수행됨)

- 출력

  - 간접 group by 컬럼 먼저, in 에 정의한 리터럴들이 컬럼들로 출력, 각 컬럼의 값은 집계함수의 결과

 

unpivot ( 컬럼2 for 컬럼1 in (피벗 컬럼들) )

- 출력

  - 등장하지 않은 컬럼이 먼저

  - 컬럼1 이름으로 -> in 다음의 피벗컬럼들이 컬럼 값으로

  - 컬럼2 -> 피벗 테이블의 집계값이 컬럼값으로

---

1. 집계 함수

** NULL 값은 집계에서 제외됨

1) count()

 

2) sum()

-> 널이 아닌 값들의 합

 

3) AVG()

null 을 제외한 평균

 

4) max, min

 

5) STDDEV(): 표준편차

VARIANCE() : 분산

 

2. 특정 조건으로 세부적인 그룹화 하기

(GROUP BY 절 사용, 그루핑할 기준을 정함)

- 값이 같은 것들을 GROUP 으로 나누어 집계함수를 수행

 

** 정렬을 원하는 경우 ORDER BY 절로 직접 정렬

 

************************** GROUP BY 절 주의사항 *********************************

1) SELECT 절에 사용된 그룹 함수 이외의 컬럼이나 표현식은 반드시 GROUP BY 절에 사용되어야 한다.

** GROUP BY 절에 사용된 컬럼이 SELECT 절에 없는건 상관 없음

 

2) GROUP BY 절에는 반드시 컬럼명이 사용되어야 하며 컬럼 별칭은 사용할 수 없다.

 

컬럼 별칭 -> " "

그 외 -> ' '

3. HAVING 절을 사용해 그룹핑한 조건으로 검색

- WHERE 절에는 그룹함수를 이용하여 검사할 수 없다

** 그룹에 대한 조건은 HAVING절을 사용한다.

***** group by 같이 사용

 

4. 분석 함수

4.1 RollUP() 함수

- Group by 의 확장된 형태

- 자동으로 소계와 합계를 구해주는 함수

 

Group by Rollup ( DEPTNO, JOB );

-> M + 1개의 그룹이 생김

**순서**       ->   마지막 인자부터 생략하면서 남은 것들을 가지고 GROUP BY

1. DEPTNO, JOB 그룹을 집계

2. DEPTNO 그룹 집계

3. 전체 그룹을 집계

** 나열 순서가 중요

 

1) 부분 롤업

SELECT deptno, position, count(*), sum(pay)

from professor

group by position, rollup(deptno);

 

position 으로 먼저 그룹을 분류한 후, 같은 deptno가 있을 경우 Rollup() 함수를 사용하여 요약한 후 deptno별로 출력한다.

group by

- 1. position, deptno

- 2. position

-> grand total이 없음, group by 기본으로 position 되어있음

 

SELECT deptno, position, count(*), sum(pay)

from professor2

group by deptno, Rollup(position);

-> deptno로 먼저 그룹핑한 후 position별로 rollup

- postion, deptno

- deptno

 

4.2 CUBE() 함수

- 소계와 전체 합계까지 출력하는 함수

- 집계 컬럼들의 모든 경우에 대한 소계를 구하므로 순서가 바뀌어도 데이터는 같다.

- CUBE()에 지정된 컬럼들의 수가 N -> 2^N승의 소그룹이 생성됨

 

SELECT deptno, job, round(avg(sal),1) count(*)

from emp

group by CUBE(deptno, job)

order by deptno, job;

 

그룹

- DEPTNO 그룹 집계

- JOB 그룹 집계

- DEPTNO, JOB 그룹 집계

- 전체 그룹 집계

-> 모든 조합에 대한 GROUP BY

컬럼이 많아지면 계산 비용이 증가한다.

 

4.3 GROUPING SETS()

- 그룹핑 조건이 여러개일 경우

- 학년별로 학생들의 인원수 합계, 학과별로 인원수 합계를 구한다면

 

SELECT grade, deptno1, count(*)

FROM student

GROUP BY Grouping Sets(grade, deptno1);

-> grade 로 group by 를 한 결과 & deptno1으로 group by 를 한 결과

 

4.4 LISTAGG() 함수

- LISTAGG 함수에 나열하고 싶은 컬럼명을 적고, 컬럼 값들을 구분할 구분자를 '->' 표현, WITHIN GROUP() 사이에 가로로 나열하고 싶은 규칙을  ORDER BY 로 적어주면 된다.

- 만약 데이터를 구분할 구분자를 주지 않으면 모든 데이터가 한줄로 연결되어 출력된다.

- WITHIN GROUP () 에 아무런 조건을 주지 않으면 에러

- 데이터 구분자에 예약어도 사용될 수 없다.

 

SELECT deptno, SELECT DEPTNO, LISTAGG(ENAME, '->') WITHIN GROUP(ORDER BY hiredate) listagg
from emp
group by deptno;

 

 

- 각 deptno 별로 그룹핑, 한 그룹에 대해 ename 값 컬럼을 hiredate로 정렬된 채로 묶어서 출력

 

 

---------------------6주차-----------------

4.5 PIVOT 함수

- row 단위를 column단위로 변경해주는 함수

- UNPIVOT함수는 반대로 column 단위를 row단위로 변경해주는 기능을 한다.

 

날짜 정보를 가진 CAL 테이블

 

1) pivot을 사용하지 않고 decode 함수를 활용하여 달력 만들기

select max(decode(day,'SUN',dayno)) sun,

max(decode(day, 'MON',dayno)) mon,

max(decode(day, 'TUE', dayno)) tue,

max(decode(day, 'WED',dayno)) wed,

max(decode(day, 'THU',dayno)) thu,

max(decode(day, 'FRI', dayno)) fri,

max(decode(day, 'SUN', dayno)) sun

from cal;

 

실행 결과

 

-> decode(day, 'SUN', dayno) 구문은 day의 모든 행에 대해서 day 가 'SUN' 인 dayno를 가져온다.

-> 각 컬럼마다 decode를 통해 가져온 값들 중에서 최댓값을 가져오게 된다.

-> 여기서 dayno가 저장된 타입이 문자이므로 ASCII 코드 값으로 비교하는데, 두자리 이상인 경우 앞자리만 비교하게 되기 때문에 8, 9 같은 값들이 출력된 것이다.

 

 

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;

 

 

 

max를 사용하지 않으면 cal 테이블의 day 컬럼이 특정 요일과 같은지 비교하는 작업을 각 decode() 문마다 같은 행에 대해 한번씩 수행하므로 위와 같은 결과를 출력한다

- 즉, 위의 테이블에서 첫번째 행은 cal 테이블에서 dayno가 1인 행에 대해서 day 컬럼의 값과 특정 요일을 각자 한번씩 비교한 결과를 출력

(dayno가 1일때, decode()문이 각각 1번씩, 총 7번 실행된 결과가 한 행을 구성함)

(day, dayno의 한 행마다 요일별)7개의 컬럼을 계산한다.)

 

 

달력을 만들려면 weekno 값이 같은 행들에 대해서는 한 행으로 출력되어야 한다.

그러므로 group by를 사용해서 weekno가 같은 행들은 컬럼별로 max를 취하면 (null과 숫자 하나 뿐) 주별로 요일이 한 행에 출력된다.

 

- 그렇지만 정렬이 안되어 있으므로 weekno 별로 정렬을 수행하면 달력처럼 출력된다.

 

2) PIVOT 기능을 사용하여 달력 만들기

SELECT * FROM (select weekno week, day, dayno

                      from cal)

pivot

( max(dayno) for day in('SUN' sun, 'MON' mon, 'TUE' tue,

                               'WED' wed, 'THU' thu, 'FRI' fri, 'SAT' sat)

)

order by week;

 

- pivot 절에 max(dayno) 절은 decode 문장에서 사용되는 함수를 적으면 되고

  for 절에는 화면에 집계될 그룹핑할 컬럼을 적으면 된다.

- 서브쿼리에 포함된 컬럼 중, PIVOT절에서 사용되지 않은 컬럼으로 GROUP BY 한다. (간접적 Grouping)

- MAX(DAYNO) -> WEEKNO 그룹에 속한 DAYNO 중에서 MAX

 

3) PIVOT -EMP 테이블에서 직급별 인원수 표시하기

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

 

SELECT DEPTNO, COUNT(DECODE(JOB, 'CLERK',1)) CLERK,
COUNT(DECODE(JOB, 'MANAGER',1)) MANAGER,
COUNT(DECODE(JOB, 'PRESIDENT',1)) PRESIDENT,
COUNT(DECODE(JOB, 'ANALYST',1)) ANALYST,
COUNT(DECODE(JOB, 'SALESMAN',1)) SALESMAN
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;

 

- PIVOT

집계 결과들이 행 단위로 되어있는 것들을 컬럼 단위로 요약 정보나 리포트를 낼 때 의미있는 경우에 사용한다.

기준이 되는 컬럼에 등장하는 값들을 컬럼화 해서 출력

(원래 행으로 되어있는 것들을 컬럼화하고, 그 컬럼을 기준으로 요약 정보를 집계하여 최종 보고서를 뿌릴 때 유용함)

 

* 집계 함수를 여러개 사용할 수 있다.

SELECT * FROM (SELECT DEPTNO, JOB, SAL FROM EMP)
PIVOT
( COUNT(JOB) AS COUNT,
  SUM(NVL(SAL,0)) AS SUM FOR JOB IN('CLERK' C, 'MANAGER' M, 'PRESIDENT' P, 
  'ANALYST' A, 'SALESMAN' S)
)
ORDER BY DEPTNO;

 

**** 집계 함수를 2개 이상 사용할 때는 반드시 별칭 부여

 

4.6 UNPIVOT

컬럼화 되어있는 것을 다시 ROW로 변환

 

SELECT * FROM UPIVOT
UNPIVOT(
EMPNO FOR JOB IN(CLERK, MANAGER, PRESIDENT, ANALYST, SALESMAN)

);

CLERK, MANAGER,,, -> UPIVOT에서 컬럼명으로 존재하므로 ' ' 로 묶으면 안됨, 값이 아님

*** IN안에 나열된 컬럼명들을 JOB이라는 신규 컬럼의 값으로 가져옴

- 각 컬럼에 있던 값들을 UPIVOT 테이블에서 DEPTNO 와 각 컬럼을 좌표로 해서 거기에 해당하는 값을 EMPNO 신규 컬럼에 값으로 넣어줌

 

 

4.7 LAG

LAG(출력할 컬럼명, OFFSET, 기본 출력값)

    OVER (QUERY_PARTITION 구문, ORDER BY 정렬할 컬럼)

: 현재 행 값을 기준으로 OFFSET 만큼의 이전 행의 값을 가져와서 출력

- 없으면 기본 출력값을 출력함

 

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

 

OFFSET

= 1: 1행 이전의 값

= 2: 2행 이전의 값

기본 출력값

- 가져올 OFFSET에 해당하는 값이 없을 때 출력할 값

 

OVER()

-> PARTITION BY | ORDER BY 구문 생략 가능 (둘 중 하나만)

ORDER BY col

-> col 컬럼을 기준으로 오름차순 정렬한 후, offset을 기준으로 출력할 컬럼에 대해서 이전 값을 출력

 

4.8 LEAD

LAG와 동일한 문법

- OFFSET 만큼 뒤에 있는 행의 값을 가져옴

 

4.9 RANK

: 순위 출력 함수

 

-> 특정 데이터의 순위를 확인

RANK(조건값) WITHIN GROUP ( ORDER BY 조건 값의 컬럼명 )

**** 조건값(상수)과 ORDER BY 뒤에 나오는 데이터는 같은 컬럼이어야 한다.

(ORDER BY 뒤의 컬럼에서 조건 값의 순위를 구함)

 

-> 전체 순위 보기

RANK( ) OVER (PARTITION BY 그룹핑할 컬럼 ORDER BY 조건컬럼명)

**동점인 경우 같은 등수로 출력 (동점자가 생기는 경우 동일한 순위가 되어 비연속적인 순위, 등수 생략됨)

 

** PARTITION BY : 그룹핑을 하는 것

RANK() OVER (PARTITION BY deptno order by sal desc)

-> deptno별로 그룹을 나눈 후 그룹별로 sal 컬럼을 기준으로 내림차순 정렬

 

4.10 DENSE_RANK

: 순위 함수

** 동일한 순위를 하나의 건수로 취급하여 연속된 순위를 보여줌

- 생략되는 등수가 없음

 

 

RANK와 DENSE_RANK 비교

 

- RANK는 동일 SAL 값을 각각의 건수로 취급하여 밀린 순위인 11을 출력 (9,9 -> 11)

- DENSE_RANK는 동점자가 있더라도 다음 데이터에 대한 순위가 +1 되어 출력됨 (9,9 -> 10)

 

4.11 ROW_NUMBER() 순위 함수

- 동률이 없음 (내부 ROWID로 동률을 처리) **** -> ROW ID가 바뀔 경우 동률의 순위가 바뀔 수 있다 (데이터 삭제후 다시 삽입)

- 동점자가 있더라도 무조건 유일한 순위로 출력함

 

문법은 RANK OVER, DENSE_RANK와 동일

 

4.12 SUM() OVER  누계 구하기

SELECT P_CODE, P_QTY, P_TOTAL, SUM(p_total) over( order by p_total )

from panmae

where p_store=1000;

- total 컬럼에서 이전 행까지의 total의 총합과 현재 total의 합계를 계산

- order by로 정렬된 순서로 누적 합 계산

 

SELECT P_CODE, P_QTY, P_TOTAL, SUM(p_total) over(partition by p_code order by p_total)

from panmae

where p_store=1000;

-> p_store=1000인 행에 대해서, p_code 별로 p_total을 기준으로 정렬하여 p_total의 누적합을 계산

★ order by 안하면 누적합이 아니라 합을 출력함

 

**** SUM() OVER

-> (정렬 기준 컬럼에)중복되는 값이 있는 경우 중복 값들의 합을 미리 구해놓고 미리 한번에 더해줌

 

**** sum(sal) over()

 -> over() 에 아무것도 안주면 괄호 안의 SAL 의 총합을 출력함

 

4.13 RATIO_TO_REPORT() OVER( )

- 전체 합에 대해서 각각의 행이 차지하는 비율

 

- 판매 비율 구하기

SELECT p_code, p_store, round(ratio_to_report(sum(p_qty)) over()*100,2)
from panmae
where p_code=100
group by p_code, p_store;

 

-> p_qty의 그룹별 합계/p_qty의 총합 -> ratio_to_report() over()의 결과

 

4.14 LAG 함수를 활용한 차이 구하기

OFFSET 만큼 떨어진 행과 현재 행과의 차이를 계산

 

SELECT p_store, p_date, p_code, p_qty,
lag(p_qty,1) over(order by p_date) "D-1 QTY",
p_qty - lag(p_qty,1) over(order by p_date) "DIFF-QTY"
from panmae
where p_store=1000;

 

 

- 파티션이 된 그룹마다 정렬한 후 LAG 함수를 이용하여 차이를 계산할 수 있다.

 

-------------- 연습문제 -----------------

-- 2) birthday 컬럼을 참조해서 월별 생일자수 출력

SELECT COUNT(BIRTHDAY)||'EA' TOTAL,
  COUNT(DECODE(SUBSTR(BIRTHDAY,4,2),'01',1))||'EA' JAN,
  COUNT(DECODE(SUBSTR(BIRTHDAY,4,2),'02',1))||'EA' FEB,
  COUNT(DECODE(SUBSTR(BIRTHDAY,4,2),'03',1))||'EA' MAR,
  COUNT(DECODE(SUBSTR(BIRTHDAY,4,2),'04',1))||'EA' APR,
  COUNT(DECODE(SUBSTR(BIRTHDAY,4,2),'05',1))||'EA' MAY,
  COUNT(DECODE(SUBSTR(BIRTHDAY,4,2),'06',1))||'EA' JUN,
  COUNT(DECODE(SUBSTR(BIRTHDAY,4,2),'07',1))||'EA' JUL,
  COUNT(DECODE(SUBSTR(BIRTHDAY,4,2),'08',1))||'EA' AUG,
  COUNT(DECODE(SUBSTR(BIRTHDAY,4,2),'09',1))||'EA' SEP,
  COUNT(DECODE(SUBSTR(BIRTHDAY,4,2),'10',1))||'EA' OCT,
  COUNT(DECODE(SUBSTR(BIRTHDAY,4,2),'11',1))||'EA' NOV,
  COUNT(DECODE(SUBSTR(BIRTHDAY,4,2),'12',1))||'EA' DEC
  FROM STUDENT;

 

--4)

SELECT DEPTNO,
SUM(DECODE(JOB,'CLERK',SAL,0)) CLERK, SUM(DECODE(JOB,'MANAGER',SAL,0)) MANAGER,
SUM(DECODE(JOB,'PRESIDENT',SAL,0)) PRESIDENT,
SUM(DECODE(JOB,'ANALYST',SAL,0)) ANALYST,
SUM(DECODE(JOB,'SALESMAN',SAL,0)) SALESMAN,
SUM(SAL) TOTAL
FROM EMP_3
GROUP BY ROLLUP(DEPTNO);