SQL-9) group by, having , 함수(function) - 5

안녕하세요 이승혁입니다.

 

이번 포스팅은 복수행 함수에 대한 실습 내용입니다.

복수행함수는 여러 행을 입력받아 그 행들 중 가장 높은 값, 작은 값, 평균, 총합 등 하나의 값을 출력해주는 함수입니다.

데이터를 하나의 기준으로 묶어주는 group by 절, group 함수의 결과에 대한 조건을 부여하는 having 절과 함께 사용합니다.

 

※ 함수

데이터로부터 의미있는 결과를 도출해 내기 위해 보다 더 정교한 데이터 검색을 할 수 있도록 도와주는 기

 

※함수의 종류 2가지 

1. 단일행 함수 : 문자, 숫자, 날짜, 변환, 일반

2. 복수행 함수 : max, min, avg, sum, count

 

ㆍ 복수행함수,그룹함수 - max

 

     최대 값을 출력하는 함수

     max(숫자데이터)

 

실습 1. max , 최대 월급 출력해보기

select max(sal)
from emp;

실습 2. group by + max, 각 부서별 가장 높은 월급 출력해보기

select deptno,max(sal)
from emp
group by deptno;

※ group by 절

데이터를 그룹핑해 줍니다.

 

select 검색하려는 칼럼명

from   검색하려는 테이블명

where 검색 조건

group by 그룹핑할 컬럼명

order by 정렬할 컬럼명

 

내부 실행 순서

  from ->    where ->      group by  -> select     -> order by

테이블 -> 조건맞는 행 ->  그룹핑     -> 칼럼선택 -> 정렬

 

실습 3. group by + order by + max, 최대 월급이 높은 순서대로 출력해보기

select deptno,max(sal)
from emp
group by deptno
order by max(sal);

ㆍ 복수행함수,그룹함수 - min

 

     최소값을 출력하는 함수

     min(숫자데이터)

 

실습 4. group by + min, 직업별 최소 월급을 출력해보기

select job, min(sal)
from emp
group by job;

실습 5. 성씨 별 최소 나이 출력해보기

select substr(ename,1,1), min(age)
from emp11
group by substr(ename,1,1);

 

ㆍ 복수행함수,그룹함수 - avg

 

     평균값을 출력하는 함수

 

실습 6. group by + order by + min, 부서 번호 별 평균 월급을 오름차순으로 출력해보기

select deptno,round(avg(sal),2)
from emp
group by deptno
order by 2;

order by 는 칼럼 순서대로 1, 2 와 같이 나타 낼 수 있습니다. 

해당 코드는 평균 월급 ( round(avg(sal),2) ) 를 숫자 2로 나타낼 수 있습니다.

 

※ group 함수에 대한 조건문

 

having절을 사용합니다.

 

select       : 보고싶은 컬럼명

from        : 데이터가 있는 테이블명

where      : 검색 조건

group by  : 그룹핑할 컬렴명을 기술

having     : 그룹함수로 조건을 줄 때

order by   :  정렬할 컬럼을 기술

 

코딩 순서

 

select -> from -> where -> group by -> having -> order by

 

실행 순서

 

from -> where -> group by -> having -> select -> order by

 

※ group 함수의 특징

 

null 값을 무시한다.

 

ex)

1. nvl 함수 사용 x 

select avg(comm)
from emp;

null 값인 행은 평균에 포함을하지 않는다.

2. nvl 함수 사용

select avg(nvl(comm,0))
from emp;

null값을 0으로 바꾸면 값이 달라진다.

 

ㆍ 복수행함수,그룹함수 - sum

 

     토탈 값을 출력하는 함수

     sum(숫자 데이터)

 

실습 7. group by + sum, 직업별 토탈 월급을 확인해보기

select job,sum(sal)
from emp
group by job;

 

실습 8. group by + having + sum, 직업별 토탈 월급이 6000 이상인 직업만 출력해보기

select job,sum(sal)
from emp
group by job
having sum(sal)>=6000;

 

ㆍ 복수행함수,그룹함수 - count

 

     건수, 행의 개수를 세는 함수

     count(데이터칼럼명)

 

     테이블의 전체 개수를 셀 때는 특정 칼럼보다는 '*' 사용하는것이 좋다.

     그룹 함수는 null을 무시하기 때문이다.

 

     ex)

--1.
select count(*)
from emp;
--2.
select count(comm)
from emp;

1.

2.

 

emp 테이블의 전체 행 개수는 14개 이지만, comm 칼럼을 사용하게 된다면 null이 아닌 4개만 나온다.

 

실습 9. group by + count, 통신사별 인원수 출력하기

select telecom, count(telecom) 이용자수
from emp11
group by telecom;

실습 10. 가장 많이 사용하는 도메인 출력하기

 

1. 도메인별 이용자수 구하기

select rtrim(substr(email,instr(email,'@')+1),'.com''.net') 도메인, count(*) 이용자수
from emp11
group by rtrim(substr(email,instr(email,'@')+1),'.com''.net')
order by 2 desc;

2. Sub Query 사용

 

위에서 구한 데이터를 하나의 테이블로 생각해서 from 절에 넣어 사용합니다.

select *
from (
       select rtrim(substr(email,instr(email,'@')+1),'.com''.net') 도메인, count(*) 이용자수
        from emp11
        group by rtrim(substr(email,instr(email,'@')+1),'.com''.net')
        order by 2 desc
)
where rownum=1;

rounum - 행 번호로 1번 행을 출력해 줍니다.

 

ㆍ 복수행함수,그룹함수 - variance

 

     분산을 구하는 함수

     분산 :  데이터의 퍼짐 정도 , 평균을 중심으로 데이터가 퍼져있는 정도

     variance(sal)

 

실습 11. group by + variance , 부서 번호 별 분산 구해보기

select deptno, round(variance(sal),2)
from emp
group by deptno;

 

ㆍ 복수행함수,그룹함수 - stddev

 

     표준편차를 구하는 함수

     표준편차는 분산값에 루트를 씌운 값입니다.

     실제 데이터와 평균값의 차이 입니다.

     stddev(숫자데이터)

     루트 함수 : sqrt(데이터)

 

실습 12. 분산 루트값과 stddev 값 비교해보기

select sqrt(variance(sal)),stddev(sal)
from emp;

 

이번 포스팅에서는 그룹함수에 대해서 실습을 진행해 보았습니다.

다음 포스팅에서는 데이터 분석을 용이하게 해주는 데이터 분석 함수에 대해 실습을 진행해 보도록 하겠습니다.

읽어주셔서 감사합니다.