[SQL] SQL 집계함수, GROUP BY(HAVING), 조인(JOIN)

 

⭐️ 집계 함수 

레코드의 개수나 합계, 평균, 최댓값, 최솟값 등 구할 때 사용

 

그 외에도

• 문자형 함수
• 날짜/시간형 함수 
• 형 변환 함수
• 제어 흐름 함수

---

▶︎  GROUP BY

특정 컬럼을 기준으로 그룹화

그룹화를 통해 조회된 데이터로 집계함수(count, avg, sum, max, min)를 사용하기에 용이해짐

 

- 담당자직위별로 묶고, 같은 담당자직위에 대해서는 도시별로 묶어서 집계한 결과( 1. 담장자직위 순, 2. 도시 순으로 정렬)

select 담당자직위, 도시, count(*) as 고객수, avg(마일리지) as 평균마일리지
from 고객
group by 담당자직위, 도시
order by 1,2;

 

* HAVING절

- 고객테이블에서 도시별로 그룹을 묶어서 고객수와 평균마일리지를 구하고  그 중에서 고객수가 10명 이상인 레코드만 걸러낸 결과

select 도시, count(*) as 고객수, avg(마일리지) as 평균마일리지 
from 고객 
group by 도시 
having count(*) >= 10;

 

* WITH ROLLUP : 그룹별 소계와 전체 총계를 한 번에 확인하고 싶을 때 (group by 다음에)사용

- 담당자직위에 '마케팅'이 들어가 있는 고객에 대한 고객(담당자직위, 도시)별 고객수를 보이고, 담당자직위별 고객수와 전체 고객수도 함께 보이게 하기

select ifnull(담당자직위,'총계'), 도시, count(*) as 고객수 
from  고객
where 담당자직위 like '%마케팅%'
group by 담당자직위, 도시
with rollup;

 

🚨 null 값이 테이블에 허용되지 않는다면

1. update 입력

update 고객
set 지역 = null
where 지역 ='';

그래도 안된다면

2. setting -> editor 맨 아래 체크 박스 허용 x 후 재실행

---

▶︎ JOIN(조인)

: 두개 이상의 테이블을 연결해서 데이터 검색하는 방법

 

* CROSS JOIN : 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인

- 일반적으로 테이블 간의 관계가 없을 때 사용

- 두 테이블 간의 모든 가능한 조합을 만들어내는 조인 방식

select 고객.고객번호, 담당자명, 마일리지, 등급.*
from 고객
cross join 마일리지등급 as 등급
where 담당자명 = '이은광';

 

✰✰✰✰✰

* INNER JOIN(내부 조인) : 각 테이블에서 조인 조건이 일치하는 데이터만 가져오는 조인

 

1. 이소미 사원의 사원번호, 직위, 부서번호, 부서명을 가져오기

🌱 부서번호가 사원 테이블에도, 부서 테이블에도 있기 때문에 확실하게 명시해주는 것이 좋음

select 사원번호, 직위, 사원.부서번호, 부서명
from 사원
inner join 부서 on 사원.부서번호 = 부서.부서번호
where 이름 = '이소미';

 

2.  고객별(고객번호, 담당자명, 고객회사명)로 주문금액 합을 보이되, 주문금액 합이 많은 순서대로 정렬

🌱 고객 테이블에 있는 고객번호와 주문 테이블에 있는 고객번호 조인 & 주문 테이블에 있는 주문번호와 주문세부 테이블에 있는 주문번호 조인

select 고객.고객번호, 담당자명, 고객회사명, sum(주문수량 * 단가) as 주문금액합
from 고객
inner join 주문 on 고객.고객번호 = 주문.고객번호
inner join 주문세부 on 주문.주문번호 = 주문세부.주문번호
group by 고객.고객번호, 담당자명, 고객회사명
order by 4 desc;

 

 

* OUTER JOIN  : 두 테이블에서 공통된 값을 가지지 않는 행도 반환 - null값을 포함

• LEFT OUTER JOIN
  - 왼쪽 테이블의 모든 행과 '오른쪽 테이블에서 왼쪽 테이블과 공통된 값’을 가지고 있는 행들을 반환
  - 오른쪽 테이블에서 공통된 값을 가지고 있는 행이 없다면 NULL 값을 반환
• RIGHT OUTER JOIN 
  - 오른쪽 테이블의 모든 행과 '왼쪽 테이블에서 오른쪽 테이블과 공통된 값’을 가지고 있는 행들을 반환
  - 왼쪽 테이블에서 공통된 값을 가지고 있는 행이 없다면 NULL 값을 반환
• FUll OUTER JOIN 
  - 두 테이블에서 ‘모든 값’을 반환
  - 공통된 값을 가지고 있지 않는 행이 있다면 NULL 값을 반환

select 사원번호, 이름, 부서명
from 사원
left outer join 부서 on 사원.부서번호 = 부서.부서번호
where 성별 = '여';

select 부서명, 사원.*
from 사원
right outer join 부서 on 사원.부서번호 = 부서.부서번호;

 

 

* SELF JOIN : 하나의 테이블 내에서 다른 열을 참조하기 위해 사용하는 '자기 자신과의 조인' 방법

select 사원.이름 as 이름, 직위, 상사.이름 as 상사이름
from 사원 as 상사
right outer join 사원 on 사원.상사번호 = 상사.사원번호
order by 상사이름;

 

 

사진 출처 : https://hongong.hanbit.co.kr/sql-%EA%B8%B0%EB%B3%B8-%EB%AC%B8%EB%B2%95-joininner-outer-cross-self-join/