changhozz

이번엔 SQL기법중 GROUP BY기법과 내부함수에 대하여 알아보자.

1.GROUP BY
GROUP BY란 특정 칼럼을 기준으로 같은 값을 가지는 행들을 그룹별로 모아 자료를 가져오는 것이다.
쉽게말해서, 중복된 여러개의 값들 중 대표선수 한명만 뽑겠다는 뜻으로 보면 될 것이다.

예시를 보면서 설명하도록 해보겠다.

위와 같이 사람들이 좋아하는 축구선수 목록 테이블인 tb_soccer_player가 있다고 할때,
방금 설명한 내용대로 축구선수의 name을 GROUP BY 해보도록 하겠다.

Query :  SELECT * FROM tb_soccer_player GROUP BY name;

result :

결과를 보게되면, 원래 테이블은 8개의 행이였는데 5개의 행으로 줄어든 것을 볼 수 있다.
이유는 C.Ronaldo, Kaka, Messi는 값이 2개씩 존재했기 때문에, 그룹화 하여 단 한개의 데이터로 취급한 것 이다.

그렇다면 축구선수의 num으로 GROUP BY를 해봤을 경우를 예상해보자.

Query :  SELECT * FROM tb_soccer_player GROUP BY num;

result : 

예상대로 num중 7,9,10만 출력되는 것을 볼 수 있다.
8개의 행들 중 num 칼럼은 모두 7,9,10들 뿐이었기 때문이다.
그런데 여기서 7번에는 ji-sung도 있고, 10번에는 Zidane도있는데 왜 C.Ronaldo와 Messi가 선택되었을까? 
우리가 따로 정렬에 대한 정보를 입력하지 않았다면 GROUP BY는 내부적으로 시컨스를 기준으로 정렬(sorting)이 이루어진다.  따라서 id값이 작은 순서대로(먼저 들어온 데이터 순) 정렬이 되었기 때문에,
뒤늦게 들어온 ji-sing과 Zidane은 밀려버린 것 이다.

2.HAVING

지금까지는 조건이 없는 GROUP BY문들이었다.
평소 어떤 쿼리를 작성할때 조건을 달고싶었다면 WHERE절을 이용하였는데, GROUP BY에서의 조건은
WHERE로 처리하지 않고 HAVING절을 사용한다. 

그럼 HAVING절을 이용하여 GROUP BY 해보도록 하겠다.
먼저 조건을 주기위해 tb_soccer_player테이블에 선수 몸값 칼럼을 추가해보겠다.

여기서,
축구선수들 중 몸 값이 400이상인 선수들의 목록을 그룹화 하여 보고싶다면,

Query : SELECT * FROM tb_soccer_player GROUP BY name HAVING price > 400; 

result : 

이러한 결과가 나오게 되는 것 이다.

이번엔 조금더 실질적으로 사용되는 예들을 들어보겠다.  
집계함수를 사용하는 것인데, 우선 집계함수에는 어떠한 것들이 있는지 알아보도록 하겠다.

3. 집계함수

집계란 이미 계산 된 것들을 모아서 계산하는 것을 말한다. 즉, 이미 계산된 결과를 어떤 기준으로
그룹화 하는 함수를 집계함수라고한다.

그렇다면 어떤 집계함수들이 있는지 확인해보자.

합수를 사용한 예를들기위해 새로운 테이블 tb_employee를 만들었다.
현재 tb_employee 칼럼으로는 dept(부서), position(직책), headcnt(인원수), salary(급여)가 존재한다.

ex.1) 각 부서당 몇개의 직책이 있는지 알고싶다.

Query : SELECT dept,count(position) position_cnt FROM tb_employee GROUP BY dept;

result : 

ex.2) 각 부서당 전체사원의 수를 알고싶다.

Query : SELECT dept, sum(headcnt) from tb_employee GROUP BY dept;

result :  

ex.3) 직책별로 평균 급여를 알고싶다.

Query : SELECT position, CEILING(avg(salary)) salary_avg from tb_employee GROUP BY position;

result : 

(여기서 CEILING()은 내장함수로 소수점을 버려준다. )

앞으로 공부를 하면서 더 나은 예제가 있다면 또 다시 정리해보자.

SQL의 기법중 JOIN기법에 대하여 알아보도록하자.

참고, SQL이란?

Structed Query Language의 약자로써 해석하자면 '구조화된 질의어'란 뜻이다.

즉, 데이터베이스에서 데이터를 조회, 입력, 수정, 삭제, 저장하는데 사용되는 질의 언어이다.

구조화된 언어라는 말은 일정한 틀이나 패턴이 있다는 뜻이다.

일정한 틀과 패턴에 맞게 조건들을 나열하면 SQL은 원하는 결과를 가져다 준다.

패턴에 맞게 조건들을 선언한다는 의미에서 SQL을 선언적(declarative)언어라고도 한다.

*JOIN*

-JOIN이란 기법은 여러 테이블에 흩어져 있는 정보 중에서 사용자가 필요한 정보만 가져온뒤

가상의 테이블을 만들어 결과를 보여주는 기법이다.

-JOIN에는 오라클에서 사용하는 JOIN문과 모든 제품들에서서 공통적으로 사용 가능한 

표준(ANSI) JOIN 방법이 있다.

우리는 MySQL을 사용하므로 표준JOIN방법을 이용하면되겠다!.

그럼 우선 어떤  종류의 JOIN연산자가 있는지 알아보도록하자.

그럼 이제 실제로 테이블을 생성하여 직접 사용해보자.
오늘의 테스트를 위한 테이블 4 개를 생성해보겠다. 

지금부터 생성한 4개의 테이블을 이용하여 알아보도록 하자.

1. 내부 조인(INNER JOIN)

 - 조건을 사용하여 두 테이블의 레코드를 결합한다.

- 동등 조인, 비동등 조인, 자연 조인 등이 있다.

ex) Select somecolumns From table1 Inner Join table2 ON somecondition; 

1) 동등 조인(EQUI JOIN)

- 두 테이블 사이의 같은 행들을 반환한다.

ex) 각 boy가 어떤 toy를 가지고 있는지 조사한다. (매핑 확인)

SELECT tb_boys.boy, tb_toys.toy FROM tb_boys INNER JOIN tb_toys 
ON tb_boys.toy_id =  tb_toys.toy_id;

 2) 비동등 조인(NON-EQUI JOIN)

-두 테이블 사이의 같지 않은 모든 행들을 반환한다.

ex) SELECT tb_boys.boy, tb_toys.toy FROM tb_boys INNER JOIN tb_toys ON tb_boys.toy_id <> tb_toys.toy_id ORDER BY tb_boys.boy;

참고:여기서 <> ㅡ> 이것은 같지 않음을 나타냄.

3) 자연 조인(NATURAL JOIN)

- 두 테이블에 같은 이름의 열이 있을때만 동작한다.

- ON이 필요없다.

ex) SELECT tb_boys.boy, tb_toys.toy FROM tb_boys NATURAL JOIN tb_toys;

2. 외부 조인(OUTER JOIN)

- 내부 조인과 유사하며 일치하는 것이 없을 경우 NULL로 표시한다.

- 왼쪽 테이블은 FROM 바로 다음에 나오는 테이블이고, JOIN 뒤에 나오는 테이블이 오른쪽 테이블이다.

- 왼쪽 외부 조인(LEFT OUTER JOIN)과 오른쪽 외부 조인(RIGHT OUTER JOIN)이 있다.

- 왼쪽 외부 조인을 사용할 경우 왼쪽 테이블을 오른쪽 테이블에 비교한다. 오른쪽 외부 조인도 그 반대로 동작한다. 

유용한 예) girls가 어느 toys를 가지고 있는지 알아보도록 쿼리를 작성한다.

i) 왼쪽 테이블을 오른쪽 테이블에 비교하는 방법 

SELECT g.girl, t.toy FROM tb_girls g LEFT OUTER JOIN tb_toys t ON g.toy_id = t.toy_id;

 ii) 반대로 했을때

SELECT g.girl, t.toy FROM tb_toys t LEFT OUTER JOIN tb_girls g ON g.toy_id = t.toy_id;

3. 크로스 조인(CROSS JOIN)

- 한 테이블의 모든 행과 다른 테이블의 모든 행을 짝지워 반환한다.

ex) SELECT t.toy, b.boy FROM tb_toys AS t CROSS JOIN tb_boys AS b;

4. 셀프 조인(SELF JOIN)

- 자기 자신을 조인한다.

- 자기 자신을 하나씩 비교하기 위해 사용한다.

- 하나의 테이블로 같은 정보를 가진 테이블이 두 개 있는 것처럼 쿼리를 보낼 수 있다.

예) 각 사람의 이름(name)과 그 사람을 담당하는 보스(boss)의 이름을 가져온다.