날짜 : 2021-06-28
태그 : oracle SQL 그룹함수 groupby 함수
개요
그룹함수는 다음그림과 같이 여러개의입력이 하나의 출력으로 나오는것으로 이해하면된다.
메모
아래에 설명된 모든 예제는 oracle 기준으로 작성되었습니다.
select sum(distinct sal),
sum(all sal),
sum(sal)
from emp;
--distinct로 sal의 중복값을 없앤값과 , all을 적용한 sum 값, 원본그대로의 sum 값은 다를것이라고 예상할 수 있다.
select sum(sal),round(avg(sal),0)
from emp;
--sum과 avg동시사용도가능하다
select max(sal),min(sal)
from emp;
-- max와 min 동시사용
select max(ename),min(ename)
from emp; -- 문자도가능하다
select max(hiredate),min(hiredate)
from emp;
-- 날짜도 최대 최소값을 구할 수 있다.
select count(ename),count(comm)
from emp;
--count는 행의 개수를 세어주는 함수이다.
-- * 넣으면 null 포함 전체행의개수가 카운트됨
select count(job),count(distinct job)
from emp;
select count(*)
from emp;
--모든레코드 개수인12가나온다.
select avg(sal),max(sal),min(sal),sum(sal)
from emp
where job like 'SAL%';
--sal% 와일드카드를 이용하여 조건을 건 후 해당조건에 해당하는 각각의 합계와 최대값 등등을 추출할 수 있다.
select count(*) c_인원 ,
count(comm) c_커미션 ,
avg(comm) a_커미션,
avg(nvl(comm,0)) n_커미션,
count(deptno) c_dept ,
count(distinct deptno) c_distinct
from emp;
--c는 카운트 ,a는 평균,n은 nvl약어로 쓴거임 . nvl 을포함해서 평균을내면 값이작아지는것에 주목
**2.GROUP BY**
****
select deptno, sum(sal)
from emp
group by deptno
order by 1;
--group by 함수를 써야하는이유는 아까말했듯 sum 함수는 그룹함수이기때문에 1개의 결과만을 출력해야한다.
하지만 select deptno의 개별컬럼은 12개정도의 행을 가지고있는데 12개의 결과를 출력해야하는 것과 1개의 결과를 출력해야하는 sum 함수와의 충돌이 일어나서 그룹으로 단일컬럼을 묶지않으면 오류가뜨게된다. 그렇기 때문에 그룹 by 라는 개념이 출현한 것이다.
-- 개별컬럼을 쓰려면 group by 안에 묶은 컬럼만 선택할수있다.
select deptno 부서번호 , avg(sal) 평균월급
from emp
group by deptno
order by 1;
select deptno 부서번호 , max(sal) 최대월급 , min (sal) 최소월급
from emp
group by deptno
order by 1;
select to_char(hiredate,'YYYY') 년,
to_char(hiredate,'MM') 월 , sum(sal)
from emp
group by to_char(hiredate,'YYYY') , to_char(hiredate,'MM')
order by 년 asc;
--그루핑할때 순서를 주의해야함 년도별로 먼저 그다음에 월별로 .
--내가 뭘 먼저 그루핑할지를 정해야한다.
select deptno , count(*) , avg(sal),
min(sal),
max(sal),
sum(sal)
from emp
group by deptno
order by sum(sal) desc;
--이렇게 sum을 이용하여 정렬도할수있다.
HAVING
해빙은 group by 의 where 절이라고 생각하면된다.
/*
용어설명
deptno : 사원번호
ename : 사원이름
mgr : 사원의관리자번호
job : 직업
sal : 연봉
hiredate : 고용일자
emp : 사원정보 테이블
dept : 부서정보 테이블
dname : 부서의이름
loc : 부서의위치
*/
having 전에 이전에 했던 그룹by를 복습해보자.
gropu by 예제1
select deptno, job, count(*), avg(sal),sum(sal)
from emp
group by deptno, job;
-- 단일컬럼인 deptno 와 job별로 그루핑을 해주었다.group by 예제 2
select to_char(hiredate,'YYYY')년 ,to_char(hiredate,'MM') , sum(sal)
from emp
group by to_char(hiredate,'YYYY') , to_char(hiredate,'MM')
order by 1 asc;
--년도, 월별로 급여합계를 구하는 코드having 예제1
select deptno,sum(sal)
from emp
group by deptno
having sum(sal) > 9000;
--having 으로 조건을 추가하여 검색함. where 절의 조건처럼 이해하면쉽다.having 예제2
select deptno,sum(sal)
from emp
where sal > 800
group by deptno
having sum(sal) > 9000;having 예제3
select deptno,avg(sal),sum(sal)
from emp
group by deptno
having max(sal) > 2900;
--having 문에 max가 select절에 없어도 사용가능한걸볼수있다.having 예제4
select job ,avg(sal) , sum(sal)
from emp
group by job
having avg(sal) >=3000;
--연봉평균이 3천 이상인 데이터를 job별로 그루핑하여 볼수있다.having 예제5
select job,sum(sal) payroll
from emp
where job not like 'SALE%'
group by job
having sum(sal) > 5000
order by sum(sal) desc;
--wher 절과 having 절의 혼합
--판매원을 제외하고 특정조건에서의 컬럼뽑기 제외니깐 not like 를 사용하였다.having 예제6
select deptno , count(deptno)
from emp
group by deptno
having count(deptno) >= 6;
--count를 이용한 having 조건 필터적용. 사원번호가 6개 레코드 이상인..having 예제7
select 회사원이름컬럼 , count(회사원이름컬럼)
from 회사원정보테이블
group by 회사원이름컬럼
having count(회사원이름컬럼) > 1;
--처음시도한 코드
--어떤 회사의 동명이인의 이름을 찾는문제
--그루핑을 회사원 이름마다 해 주었기 때문에 동명이인이 있다면 2이상이 카운트 될 것이다 . 따라서 having
--조건을 1보다 크게하여 동명이인을 찾을 수 있게된다.
select 회사원이름컬럼 , count(회사원이름컬럼)
from 회사원정보테이블
group by 회사원이름컬럼
having count(*) > 1;
--정답 코드
그룹함수 + 조건함수 예제 1
select sum(case job when 'CLERK' then 1 else 0 end) "CLERK",
sum(case job when 'SALESMAN' then 1 else 0 end) "SALESMAN",
sum(case job when 'MANAGER' then 1 else 0 end) "MANAGER",
sum(case job when 'ANALYST' then 1 else 0 end) "ANALYST",
sum(case job when 'PRESIDENT' then 1 else 0 end) "PRESIDENT"
from emp
group by job;
--job별 인원수와 총인원수를 구한것. 약간어렵다.
--첫번째 줄을 일단 해석하면. job컬럼에서 CLERK 이면 1을 반환하고 아니면 0을 반환한후 합계를구한다.
--두번째줄은 마찬가지로 SALESMAN 이면 ~~ 이렇게 모두 합계를구한후 job별로 그루핑하면 해당 job에 인원이
--몇명인지 합계를 알 수 있다.그룹함수 + 조건함수 예제2
select count(*) 총인원수 ,
sum(decode(to_char(hiredate,'YYYY'),1980,1,0)) "1980",
sum(decode(to_char(hiredate,'YYYY'),1981,1,0)) "1981",
sum(decode(to_char(hiredate,'YYYY'),1982,1,0)) "1982"
from emp;
-- to_char 이용하여 데이터타입 변경. 입사년도를 추출하고 decode함수로 입사년도를 비교한다 ,
--일치하면 1 아니면 0 반환하여 sum 함수로 총합을구한다. 그러면 년도별 입사자수의 합계를 볼 수 있다.고난도 예제1
select 회사원이름컬럼 , to_char(입사일컬럼,'YYMMDD') , 회사원주소컬럼 as "거주지주소"
from 회사원정보테이블
where (회사원주소컬럼 like '%강원%' or 회사원주소컬럼 like '%경기%')
and to_char(입사일컬럼,'YYYY') like '19%'
order by 1;
--지역이 강원 또는 경기 이면서 입사일이 19xx년도인 회사원의 이름과 사원번호 거주지주소를 셀렉하는 문제
-- 가장중요한 포인트는 and 와 or 의 순위때문에 괄호로 묶어주어서 먼저 처리할것을 정해주는것이다.
생각 (질문)
출처 (문헌)
학원에서 준 바인딩 책임