3. SQL 복수행 함수(그룹함수) _연습문제다시해보기

 3장. SQL복수행함수(그룹함수)

 

1. Group함수

1) COUNT : 입력되는 데이터들의 건수 출력

2) SUM : 입력되는 데이터들의 합계값을 출력.

3) AVG함수

 - 주의 : 특정 칼럼을 지정할 경우 해당 칼럼에 NULL 값이 있다면 자동으로 NULL값은 제외하기 때문에, 의도하지 않은 오류가 나므로 주의해야 한다.

 : 잘못된계산

 : 옳은 계산  

 

4) MAX, MIN 함수 _ 시간이 오래 걸리므로 거의 쓰지 말 것.

5) STDDEV 함수 / VARIANCE 함수 : STDDEV 함수는 표준편차를 구하는 함수이고, VARIANCE함수는 분산을 구하는 함수.

 

2. 특정 조건으로 세부적인 그룹화 하기(GROUP BY절)

: 전체 값을 조회하는 것이 아니라, 특정 조건을 주고 더 세부적인 결과를 출력.(학과별로, 직급별로 정렬..)

 -주의사항 

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

2) Group By 절에 사용된 칼럼은 select절에 사용되지 않아도 된다.

3) Group by 절에는 반드시 칼럼명이 사용되어야 하며, 칼럼 Alias는 사용하면 안된다.

 

GROUP BY 절과 정렬(SORT)와의 관계

Oracle 10g R1 버전까지는 GROUP BY 결과는 늘  Sort 되어서 출력되었습니다. 그러나  10g R2 버전부터 Hash 기반의 New in-Memory Sort Algorithm 방식으로 변경되면서 더 이상 정렬이 되지 않고 출력이 됩니다.  정렬이 되지 않기에 기존 방법보다 성능 향상이 많이 이루어 졌으나 정렬을 원하는 경우 반드시 ORDER BY 절을 함께 사용해야만 합니다.   만약 GROUP BY 후 자동으로 정렬되었던 이전 방식으로 돌아가고 싶다면 초기화  파라미터  파일  (pfile  이나  spfile )에  "_gby_hash_aggregation_enabled"=FALSE 로 설정해 주시면 새로운 방식을 사용하지 않고 이전 방식을 사용하게 되어 GROUP BY 절 사용 시에 자동으로 정렬이 되어서 나오게 됩니다.  그러나 전체적인 GROUP BY 성능이 저하되므로 추천하는 방법은 아닙니다.

 

 

3. 조건을 주고 검색하기 (HAVING절 사용하기)

: 그룹함수를 조건으로 사용하고 싶을 경우에는 WHERE절이 아니라 HAVING절을 사용한다. (HAVING절의 위치는 GROUP BY 절 이전이든 이후이든 상관없음.)

 

4. 자동으로 소계/합계를 구해주는 함수

1) ★ROLLUP 함수 : 주어진 데이터들의 소계(부분합계)를 구해줌. (GROUP BY 함수 안에 적어준다.)

 

: position별로 그룹핑하고, deptno가 같은것 끼리 합계를 구해줌.          = deptno별로 그룹핑하고, 같은 position을 합계 구해준다.

 

2) CUBE 함수 : 다차원 정보의 데이터 요약.. (속도가 느림)(group by 안에 써준다)

(정리가 덜 됐습니다.)

5. 실전 그룹핑 관련 함수

1) GROUPING SETS함수 : 그룹핑 조건이 여러 개 일 경우(같은 테이블의 같은 함수일 경우만)

문법 : GROUP BY GROUPING SETS(그룹핑 원하는 칼럼1, 2);

: 학년별로 학생들의 인원수 합계와,  학과별로 인원수의 합계를 구해야 하는 경우.

 

: 학년별로 학과별로, 인원수와 키의 합계를 동시에 출력.

2) LISTAGG 함수 (11G에서 추가됨) : 같은 그룹을 가로로 나열하여 그룹핑 해주는 함수

문법 : LISTAGG(칼럼, ‘구분할구분자’) WITHIN GROUP(ORDER BY 정렬규칙칼럼)

 

3) ★ PIVOT함수 : row(가로)단위를 column(세로)단위로 변경해줌. (11g 버전에서 추가된 함수)

▶ 달력 만들기

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

 

▷decode함수를 활용하는 기존방법 이용시. ★★

decode 원리이해↓ 

1) decode 수행결과 부분을 한단계씩 수행해서 살펴보면, 먼저 decode만 했을 때 각 요일에 해당되는 숫자가 길게 전부 다 출력된다.   2) 두번째 단계로 MAX나 MIN 또는 AVG 함수를 사용한다. (어느 것을 써도 결과는 동일하다.)    위 결과에서의 숫자는 아라비아숫자가 아니라 아스키코드라는 문자이다. 문자의 크기를 비교하라고 하면 오라클은 문자를 ASCII코드값으로 변환을 해서 나오는 값으로 비교를 한다. 또한 ASCII코드로 변환할 때 두 글자 이상의 경우에는 가장 앞에 있는 글자만 변환해서 비교를 한다. 그래서 2나 29가 같은 ASCII코드 값을 갖게 된다. 예를들면 위의 결과에서 SUN은 1,8,15,22,29 이므로 MAX를 하면 29가 나와야 하지만, 2가 나온 이유는 아래처럼 2와 29의 아스키코드 값이 동일하므로 2가 출력된 것이다.         3) 주(weekno)별로 그룹핑을 하면, 정렬이 안된 상태로 그룹핑된다.   4)그래서 마지막으로 주별로 정렬을 해주면 완성된다.

 

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

▷PIVOT 함수 이용시

 : 아래의 decode함수와 비교시 pivot 부분에 → decode 함수에서 사용했던 그룹함수(count함수)를 쓰고, FOR부분에 → 그룹핑을 할 컬럼 이름을 쓰고, IN 뒷부분에 → 분류할 목록을 적어주는 것을 알 수 있다.

 

▷DECODE 함수 이용시 (0이라는 숫자는 의미없다.)

 

DECODE 수행 원리 ↓

 

1) job이 clerk이라면 0을 출력해라 라는 식으로 출력.   2) 위 결과를 deptno별로 다 더하면 총인원수가 나오므로 count해주고, deptno로 group by 해주면 된다.

 

 

4) UNPIVOT : column단위를 row(가로)단위로 변경해줌. (합져 있는 것을 풀어서 보여주는 역할)

 i) 테스트를 위해 아래와 같이 테이블을 생성해보겠습니다.

 ii) UNPIVOT으로 위의 합쳐진 결과를 풀어 보겠습니다.

5) LAG함수 : 이전 행 값 가져올 때 사용하는 함수.

문법 :   LAG(출력할칼럼명, OFFSET , 기본출력값) ★  -- OFFSET: 몇칸씩 밀건지.

OVER(쿼리파티션구문, order by 정렬할 칼럼)

 → 이 문법을 활용하는 예) 어제오늘/일주일/x개월 실적,판매량 비교시

 

6) LEAD : LAG함수의 반대로 이후의 값을 가져옴. ( 문법은 동일하나, 결과화면에서 offset값이 가장 마지막에 보인다.)

 

7) RANK함수 (순위출력함수)

* 특정 데이터의 순위(집계용) :  RANK(순위매길조건값) WITHIN GROUP (ORDER BY 조건값 칼럼명 [ASC | DESC] )

  -- 주의할 점 : RANK뒤에 나오는 데이터와 ORDER BY 뒤에 나오는 데이터는 같은 컬럼이어야 한다.

: 송도권교수가 8번째순위이다.

* 전체 순위(분석용) : RANK( ) OVER (ORDER BY 조건칼럼명 [ASC|DESC] )

    → 그룹핑할때는 RANK ( ) OVER (PARTITION BY 조건칼럼명  ORDER BY 칼럼명 [asc|desc] )

-  PARTITION BY  (그룹핑할 칼럼을 적어주면 됨) (GROUP BY 대신)  : 부서번호내에서 직업별로 그룹핑하고, sal의 순위 구한다.

* 기본 or ASC : 작은거부터 1등

  DESC : 큰거부터 1등

 

8) SUM( 칼럼명 ) OVER ★★ : 누계 구하기.(누적합계) 

: 제품코드별로 분류한 후 누적판매금액 (100코드번호의 누적판매금액 4000)

: 1000번 지점의 대출일자, 대출코드, 대출금액, 누적대출금액 출력.

: 1000번 대리점의 제품코드별로 분류 한 후, 판매일자, 제품코드, 판매량, 판매금액, 누적판매금액을 출력.

9) RATIO_TO_REPORT : 판매비율

문법 : ROUND ((RATIO_TO_REPORT(SUM(칼럼명)) OVER())*100,소수점x째자리까지)    (round는 반올림을 의미.)

                                                              →그룹핑해서 계산하고 싶을 땐 OVER(PARTITON BY 칼럼명)

 

 

6) LAG함수를 활용한 차이 구하기.

 

: 100번 판매점의 일자 별 판매내역과 금액과 전일 판매수량과 금액 차이를 출력.

 

 

7. 그룹함수 연습문제

 

 

답)

  1  select max(pay+nvl(bonus, 0)) "max",
  2     min(pay+nvl(bonus,0)) "min",
  3     round(avg(pay+nvl(bonus,0)),1) "avg"
  4* from professor

 

 

답) ★★혼자못해결함★★ 해결했을때 취소선 지우자!

  1  select max(nvl2(bonus, pay+bonus, pay*0)) "max",
  2     min(nvl2(bonus, pay+bonus, pay*0)) "min",
  3     round(avg(nvl2(bonus, pay+bonus, pay*0)),1) "avg"
  4* from professor

 

 

답) ★★혼자못해결함★★ 해결했을때 취소선 지우자!

  1  select count(*) "합계",
  2     count(decode(to_char(birthday,'MM'), '01', 1)) "1월",
  3     count(decode(to_char(birthday,'MM'), '02', 1)) "2월",
  4     count(decode(to_char(birthday,'MM'), '03', 1)) "3월",
  5     count(decode(to_char(birthday,'MM'), '04', 1)) "4월",
  6     count(decode(to_char(birthday,'MM'), '05', 1)) "5월",
  7     count(decode(to_char(birthday,'MM'), '06', 1)) "6월",
  8     count(decode(to_char(birthday,'MM'), '07', 1)) "7월",
  9     count(decode(to_char(birthday,'MM'), '08', 1)) "8월",
 10      count(decode(to_char(birthday,'MM'), '09', 1)) "9월",
 11      count(decode(to_char(birthday,'MM'), '10', 1)) "10월",
 12      count(decode(to_char(birthday,'MM'), '11', 1)) "11월",
 13      count(decode(to_char(birthday,'MM'), '12', 1)) "12월"
 14* from student

 

:'명'도 같이 출력

답)  ★★혼자못해결함★★ 해결했을때 취소선 지우자!

select count(*)||'명' "합계",
  2     count(decode(to_char(hiredate,'MM'),'01',0))||'명' "1월",
  3     count(decode(to_char(hiredate,'MM'),'02',0))||'명' "2월",

  4     count(decode(to_char(hiredate,'MM'),'03',0))||'명' "3월",

  5     count(decode(to_char(hiredate,'MM'),'04',0))||'명' "4월",

  6     count(decode(to_char(hiredate,'MM'),'05',0))||'명' "5월",

  7     count(decode(to_char(hiredate,'MM'),'06',0))||'명' "6월",

  8     count(decode(to_char(hiredate,'MM'),'07',0))||'명' "7월",

  9     count(decode(to_char(hiredate,'MM'),'08',0))||'명' "8월",

  10     count(decode(to_char(hiredate,'MM'),'09',0))||'명' "9월",

  11     count(decode(to_char(hiredate,'MM'),'10',0))||'명' "10월",

  12     count(decode(to_char(hiredate,'MM'),'11',0))||'명' "11월",

  13     count(decode(to_char(hiredate,'MM'),'12',0))||'명' "12월"

  14 from emp;

 

 

답)

select count(*) "합계",
  2      count(decode(substr(tel,1, instr(tel,')')-1),'02',0)) "서울",
  3      count(decode(substr(tel,1, instr(tel,')')-1),'031',0))"경기",
  4      count(decode(substr(tel,1, instr(tel,')')-1),'051',0))"부산",
  5      count(decode(substr(tel,1, instr(tel,')')-1),'052',0))"울산",
  6      count(decode(substr(tel,1, instr(tel,')')-1),'053',0))"대구",
  7      count(decode(substr(tel,1, instr(tel,')')-1),'055',0))"경남"
  8*    from student

 

답)★★혼자못해결함★★ 해결했을때 취소선 지우자!   (소계도 구해야되!!)

  1  select deptno,
  2  sum(decode(job, 'CLERK', sal, 0)) "clerk",
  3  sum(decode(job, 'MANAGER',sal, 0)) "manager",
  4  sum(decode(job, 'PRESIDENT',sal, 0)) "president",
  5  sum(decode(job, 'ANALYST',sal, 0)) "analyst",
  6  sum(decode(job, 'SALESMAN', sal, 0))"salesman",
  7  sum(nvl2(job, sal, 0))"합계"
  8  from emp
  9* group by rollup(deptno)

 

 

* 데이터삭제

delete from emp
where ename='홍길동';

delete from emp
where ename='일지매';

 

 

 

답)★★혼자못해결함★★ 해결했을때 취소선 지우자!

select deptno "부서번호", ename "이름", sal "급여",
  2  sum(sal)over(order by sal) "누적급여금액"
  3  from emp;

 

 

답)★★혼자못해결함★★ 해결했을때 취소선 지우자!

  1  select max(sum(decode(name, 'lemon',100)))레몬,
  2  max(sum(decode(name,'apple',200)))사과,
  3  max(sum(decode(name,'grape',300)))포도
  4  from fruit
  5* group by name

: decode만 수행하면 해당하는 숫자가 아래로 길게 출력되는데, 이를 sum합해서, 최대값max로 뽑아 한줄(가로)로 출력.

group by 해줘야됨.

 

 

답) ★★혼자못해결함★★ 해결했을때 취소선 지우자!
select count(name)||'명('|| count(name)/count(name)*100 ||'%)' "합계",

count(decode(substr(tel,1,instr(tel,')')-1), '02','서울'))||'명('||
(count(decode(substr(tel,1,instr(tel,')')-1),'02','서울'))/count(name)*100)||'%)' "서울",

count(decode(substr(tel,1,instr(tel,')')-1), '031','경기'))||'명('||
(count(decode(substr(tel,1,instr(tel,')')-1),'031','경기'))/count(name)*100)||'%)' "경기",

count(decode(substr(tel,1,instr(tel,')')-1), '051','부산'))||'명('||
(count(decode(substr(tel,1,instr(tel,')')-1),'051','부산'))/count(name)*100)||'%)' "부산",

count(decode(substr(tel,1,instr(tel,')')-1), '052','울산'))||'명('||
(count(decode(substr(tel,1,instr(tel,')')-1),'052','울산'))/count(name)*100)||'%)' "울산",

count(decode(substr(tel,1,instr(tel,')')-1), '053','대구'))||'명('||
(count(decode(substr(tel,1,instr(tel,')')-1),'053','대구'))/count(name)*100)||'%)' "대구",

count(decode(substr(tel,1,instr(tel,')')-1), '055','경남'))||'명('||
(count(decode(substr(tel,1,instr(tel,')')-1),'055','경남'))/count(name)*100)||'%)' "경남",

from student;

 

 

 

답) ★★혼자못해결함★★ 해결했을때 취소선 지우자!

  1  select deptno, ename, sal, sum(sal)over(partition by deptno order by sal) "부서별 누적 급여액"
  2  from emp
  3* order by deptno, sal

 

 

 

 

답)

  1  select deptno, ename, sal"급여", sum(sum(sal))over()"전체비중",
  2  round((ratio_to_report(sum(sal))over())*100,2) "급여비중-%"
  3  from emp
  4  group by deptno, ename, sal
  5* order by sal desc

 

 

 

답)

select deptno, ename, sal, sum(sum(sal))over(partition by deptno) "부서급여합계",
round((ratio_to_report(sum(sal))over(partition by deptno))*100,2) "부서내급여비중%"
from emp
group by deptno, ename, sal
order by deptno

 

 

 

 

 

[TIP] partition by 와 order by

 

Partition by deptno

Order by deptno

Partition by deptno Order by pay