인공지능 부트캠프 24기 _ 4일차 (그룹함수, Oracle JOIN)

※ 그룹 함수 ※

 

단일 함수와는 달리 그룹 함수는 여러 행 또는 테이블 전체에 대해 함수가 적용되어 하나의 결과를 반환하는 함수를 의미한다.

그룹 함수 종류

그룹 함수는 grouping을 하는 방식의 차이에 따라 크게 2가지로 분류할 수 있다.

 

- 자동 그루핑 

따로 grouping 단위를 지정하지 않아도 sql 내부적으로 칼럼 단위로 grouping을 실시한다.

위의 그룹 함수를 grouping하지 않고 실행할 시 column단위로 그룹함수가 실행되어짐

 

- 명시적 그루핑 (group by 절) 

명시적으로 grouping의 단위를 지정해줌으로써 특정한 단위의 요구상황을 해결하기 우해 사용된다.

 

 

** select 문 기본 형식 및 작동 순서 **

이장의 group by와 추후 group의 조건을 지정하는 having절을 배우게 되면 select절에 나올 수 있는 모든 절을 배우게 된다.

select문은 작성 순서와 sql내부적으로 작동하는 순서가 정해져있고 이는 상이하기 때문에 코드 작동 원리 이해를 위해 숙지하여야 한다.

5-Select-1

1-From-2

2-Where 조건-3

3-Group by-4

4-Having 조건-5

6-Order by-6

빨간 색: optimizer 작동 순서, 파란 색: 문법작성 순서

 

1) 자동 groupnig

자동 grouping 은 크게 sum, avg, max, min, count가 있으며 대부분의 경우 뒤 괄호 안의 해당 컬럼을 명시하여 컬럼단위의 그룹함수 작동을 수행한다.

문법을 살펴본 결과 모든 group 함수 뒤에는 특정 컬럼명을 명시하며

COUNT의 경우 *이라는 식별자를 추가적으로 사용할 수 있는데 이는 'NULL'값을 포함한 전체 행의 갯수를 의미하게 된다.

*이 아닌 컬럼명을 명시할 경우 'null'값을 제외한 record의 갯수만 반환한다.

 

2) 명시적 grouping (Group by)

자동 grouping을 사용하게 되면 select문에서 grouping되지 않은 column과 group 함수의 결과를 출력할 수 없다.

(record의 갯수가 상이 하기 때문에 표현이 안되서 error가 발생한다.)

 

그래서 이를 위해 group by로 해당 칼럼을 묶어주어서 select문에 표현이 가능하게 한다.

Group by 함수 문법

Group by 함수 요약도

Group by 수행 예시

위에서 볼 수 있듯이 Group by를 활용했을때 Select문에서도 동일한 표현식을 사용해주어야한다. (=명시적 grouping을 쓰는 이유)

추가적으로 !! group by 절내에서는 별칭 지정이 안되므로 이를 유의하자.

 

3) Having 절

Grouping된 단위의 조건을 입력하기 위해 사용되는 절이다. 

 

SELECT문 조건을 줄 때 사용되는 Where절에 들어가는 조건문과 유사하다.

 

Where 절내에서는 그룹함수가 사용이 불가능 하나 Having절에서는 group함수를 같이 사용하여 줄 수 있다.

 

Having 절 활용 예시 (각 department_id당 salary의 합계가 90000이상인 부서만 출력하시오.)

※ JOIN 문법 ※

Join 문법은 sql 에서 매우 중요한 문법중에 하나이며 물리적으로 떨어져 있는 테이블을 논리적으로 하나의 테이블처럼 합쳐서 취급할 수 있게 해준다.

 

Join종류에 따라 다양한 table을 생성할 수 있기에 유용하지만 그에 따라 논리적인 table이 어떻게 생성되는지 이해가 크게 필요하므로 잘 익혀두는 것이 좋다.

 

** JOIN 종류

join문법의 종류를 나누는 방법은 다양하지만 가장 기본적으로 inner join과 outer join으로 나눌 수 있다.

 

- Inner join

Primary key와 Foriegn key 의 교집합. 즉, 떨어져 있는 table의 일치하는 값만을 매칭하여 논리적인 table을 생성한다.

가장 일반적인 join방법으로 사용된다.

 

 

- Outer join

Inner join 실행시 값이 매칭되지 않으면 누락되는 data가 발생한다. 이런 누락된 데이터를 보존하기 위해 outer join을 사용한다.

Outer join을 사용할떄는 어느쪽 table의 누락된 데이터를 보존할지 혹은 양쪽의 모든 데이터를 보존할지 선택할 수 있으며 이는 추후에 자세히 다룰 예정.

 

 

2번째로 join은 사용 범용성에 따라 나눌 수 있는데 oracle에서 사용되는 "oralce join" 모든 DBMS에서 사용가능한 "ANSI JOIN"으로 구분된다.

Oracle join VS ANSI join

 

1) 오라클 join

오라클 join의 기본적인 특징은 Where 절안에 작성되는 것이다.

즉, where 절의 특정 record를 selection 하는 것을 넘어 테이블을 join하는 기능을 추가하여 작성하게 된다.

 

1_1 . Inner join

Inner join의 경우 join방식에 따라 조건에 일치하는 column에 대해서만 join을 실시한다.

 

1_1_1. Equi join

'='연산자의 join이며 두개의 테이블.컬럼을 = 로 묶어 명시하여 2개의 컬럼에서 일치하는 값만을 join한다

equi join 문법

Equi join 활용 예시

** 칼럼의 모호성 : 2개의 table의 같은 이름의 column이 존재할시 select문에도 table명을 무조건 붙여주어야 한다. (안쓰면 error)

** join문법에서 별칭을 사용하기 위해서는 from절에 별칭을 지정후 그 별칭을 사용하여야 한다. (from 절 별칭은 as 사용 불가)

** Where 절에 Selection 조건을 쓰고 싶다면 and 혹은 or을 써서 뒤에 나열하여 주면 된다.

 

1_1_2. non_equi join

두 테이블간의 부등연산자로 비교하여 해당조건에 일치하는 record를 서로 join한다.

 

non-equi join 사용 예시

1_1_3. self join

이름그대로 자기자신의 talbe을 join하는 경우에 사용한다.

이는 동일한 table을 사용하므로 table의 별칭을 각각 주어 다른 table인것처럼 사용

self join 사용 예시

1_2 . Outer join

Oracle join의 Outer join은 역시 where 절에 사용되며 (+) 연산자를 활용하여 어떤 테이블의 누락된 데이터를 살릴지 결정한다.

Oracle_Outer join 문법

 

Oracle join의 경우 양 쪽 테이블의 누락된 데이터를 동시에 볼수 없으며(ANSI는 가능) 보존한 데이터의 나머지 칼럼들은 모두 null 값으로 저장 된다.

 

Oracle Inner join vs outer join 비교

위의 예시는 e table (첫번쨰), m table(두번쨰)의 누락된 record를 보존하는 내용이며 위와 같이 보존하고자 하는 table의 반대편에 (+)를 붙여주면 된다. (Null값을 그 (+) table에 추가한다는 의미)

 

1_3. Cartesian product

기본적으로 n개의 table을 join하기 위해서는 (n-1)개의 join이 요구된다.

만약 이를 충족하지 못할 경우 테이블의 record갯수를 모두 곱한 수의 record를 가진 table이 나오게된다.

이는 그냥 쓰레기 데이터이므로 error가 뜨지 않았다고 된게 아니라 join의 갯수를 확인하여야 한다.