삼팔광땡 MYSQL 입문기 03 - 복수열 갱신, 집계함수(count/sum/avg/min/max), 그룹화 group by

삼팔광땡~ MYSQL 입문기 03

자, 이제 첫 걸음이다.  칼을 뽑았으니 이제 무 밭을 발견했으니 무를 뽑아보자. 

아, 미리 언지를 드리면 실습에 사용되는 테이블은 동일하지만 책에서 설명하고있는 예제와는 똑같지는 않습니다.  >< 

0. 이런 실수? 할 수 있다. 

비전공자 또는 낙엽줄로 개발 공부하면서 섭섭하게 느끼는 점이 하나 있다면 너무 당연?한 것은 알려주지 않는다는 것이다. 매번 포스팅을 작성하기 앞서 그런 내용들을 기억나는 데로 적어두려한다. 

1) terminal을 실행하긴 했는데.. 어떻게 닫아야하지? 모를 수 있다. 당연한건 없다. 결론은 exit 하고 엔터. 

    MySQL은 친절하게 안녕 이라고 응답한다. ;D

    -> 결과 

1. 복수열 갱신   

제목이 뭔가 어려워 보이는데... 별 건 아니고. 테이블에 열의 값들을 갱신(수정 ;D) 할 수 있는데 그 행위를 한번에 여러번 할 수 있다는 말이다. 바로 실습을 통해 이해해보자

1) 먼저 실습하기 위한 테이블을 열람해보자

 select * from sample41; 

+ α )   추아.. 테이블이 텅~ 비어 있네.. 후다닥 값을 넣어보자

(1) 값을 넣기 전에 테이블의 구성을 확인해보자 

(2) 그럼 구성 조건에 맞춰 임의의 값들을 넣어보자 

insert into sample41(no,a,b) values(1,'ABC','2014-01-05'); 

insert into sample41(no,a,b) values(2,'XYZ',null);  

(3) OK 라고 한다. 그럼 테이블을 다시 확인해보자

 select * from sample41; 

2) 위의 테이블에 일단 1개의 열만 갱신하는 실습을 해보자

 update sample41 set no=no+1; 

 select * from sample41; 

3) 그럼 이번엔 두개의 열을 한번에 갱신하는 실습을 해보자 

 update sample41 set a='xxx', b='2014-01-01' where no=2; 

 select * from sample41; 

2. AVG 집계함수로 평균값 계산    

AVG는 평균을 구하는 함수고 집계함수는 테이블의 특정 값 들에 대한 집계를 위한 함수이다. 집계의 종류에는 대략 행 수를 카운트COUNT하거나 합계SUM를 구하거나 평균AVG, 최대값MAX, 최소값MIN을 구하는 함수가 있다.

1) 음.. 실습에 앞서 실습을 위한 예제 테이블을 살펴보자요 ;D , 참고로 요 테이블은 3번 실습 그룹화(group by)에서도 같이 쓰입니다~  

 select * from sample51;

2) 먼저 집계합수를 한번씩 실습을 해보기로하자. 

(1) 카운트 COUNT - SQL에서  * 기호는 '모든' 을 의미한다 

 select count(*) from sample51; 

+ α )   그런데말입니다, 행의 갯수를 헤아릴때말입니다. 값이 같은 경우를 제외하고 싶은때는 말입니다. distinct 함수를 사용하묜 중복되는 행을 제외한 갯수를 구할 수 있습니다.

select distinct name from sample51; 

(2) 합계 SUM - 예제 테이블 컬럼 중 quantity에 대한 합계를 알아보자

 select sum(quantity) from sample51; 

+ α )   합계를 구할때 특정 컬럼의 조건에 충족하는 범위에 대한 합계만 구할 수도 있다. 이번 예제에서는 name 컬럼의 값이 'A'인 경우의 값만 더해서 합계를 구해보자.

 select count(*) from sample51 where name='A'; 

(3) 평균 AVG - 평균을 구하는 방법은 두가지가 있습니다.

a) 일단, 앞에서 실습한 방법을 응용해서 구하는 방법  - sum(quantity)/count(quantity)

b) AVG 함수를 이용하는 방법  - avg 

select 
  avg(quantity) '함수사용', 
  sum(quantity)/count(quantity) '이전방법응용' 
from 
  sample51; 

+ α )   그런데말입니다. 평균값에 이상한 점이 있습니다. 왜 40000일까요? 32000이어야하지 않나요? 원인은 NULL 표시가 되어있는 행까지 행의 갯수에 포함하여 연산했기 때문입니다. 그이럴경우에는 NULL을 0으로 변환하여 계산을 해야합니다. 지난번에 실습한 내용으로 해볼까요? 방법은 두가지가 있었죠?

i) coalesce함수를 사용하는 방법과 ii)case문을 사용하는 방법 ;D 그 두가지를 방법은 각각의 컬럼으로 출력해볼까요? 두가지 방법 모두 32000이 나오는 것을 확인할 수 있습니다.

select 
  sum(quantity)/count(coalesce(quantity,0)) i, 
  sum(quantity)/count(case when quantity is null then 0 else quantity end) ii 
from 
  sample51; 

(4) 최대값 MAX, 최소값 MIN - 문자의 경우 알파벳 순으로 크기가 정의됩니다.

 select min(name), max(name) , min(quantity), max(quantity) from sample51; 

3. 그룹화 - group by 절을 이용하여 집계함수의 활용범위를 넓힐 수 있습니다.

1) 먼저 간략한 실습을 진행해볼까요? 아래 실습은 그냥 name 컬럼을 기준으로 그룹을 묶은 예 입니다. 요컨데 name 컬럼의 값이 같은 거 끼리 묶는 거죠. 실행 전에 생각해보면 이 코드의 결과는 select distinct name from sample51; 앞에서 했던 쿼리와 같은 결과일 것으로 예측할 수 있습니다.

select name from sample51 group by name; 

2) 이번엔 그룹으로 묶은 녀석?들 끼리 집계연산을 해볼까요? 결과는 이름 그룹핑된 이름 컬럼, 그 이름의 합계컬럼으로 해보죠 

select 
  name '그룹핑된_값들', 
  count(name) '그룹핑별_갯수' ,
  sum(quantity) '그룹핑별_합' 
from 
  sample51 
group by name; 

3) 자, 여기서 조건을 넣어볼까요? 이를테면 앞에서 구한 그룹핑된 녀석? 들 중 내가 원하는 조건에 맞는 것만 뽑아내는 거죠. 이번엔 그룹핑별 합이 3인 녀석들만 가져오게 해볼까요?

select 
  name '그룹핑된_값들', 
  sum(quantity) '그룹핑별_합' 
from 
  sample51 
group by 
  name 
having 
  sum(quantity)=3 ; 

+ α )   혹시 select절에 Alias(알리아스)를 사용한 경우,

having을 사용할 때, 뿐만아니라 다른 연산을 할 경우에

위와 같이 sum(quantity)을 써도 되고 아래와 같이 Alias( 그룹핑별_합 )만 써도 됩니다~

다만, ORACLE의 경우엔 아래의 예제가 실행되지 않습니다. ><  

select 
  name '그룹핑된_값들', 
  sum(quantity) '그룹핑별_합' 
from 
  sample51 
group by 
  name 
having 
  그룹핑별_합=3 ; 

4) 지금까지 select안에는 name열을 그룹핑한 거 밖에 없었는데요, 다수열을 select안에 넣으려면 어떻게 해야할까요? 그냥 넣으면 터지고요, group by 절에 먼저 선언하고나서 select 절에 넣어야해요. 요컨데 group by 절에서 지정한 열이 아닌 이외의 열은 select 구문 안에 넣을 수가 없답니다. 그럼 예제를 통헤 확인해볼까요?

(1) 이렇게 하면 에러가 나고요.. 소위 '터진다-' 고 하죠 ;D 

select 
  no '선언_안한_no열', 
  name '선언_한_name열' ,
  quantity '선언_안한_quantity열'
from 
  sample51 
group by name; 

(2) 이렇게 하면 무난하게 실행이 됩니다.

select 
  no '선언_한_no열', 
  name '선언_한_name열' ,
  quantity '선언_한_quantity열'
from 
  sample51 
group by 
  no, name, quantity;

+ α )   혹시 여기에서 정렬 조건을 추가하고 싶으신 분들도 있으시겠죠? 이를테면 '선언_한_quantity열'의 값이 큰 순서대로 보고 싶다든지. 말이죠. 그럴땐, 아래와 같이 맨 뒤에 DESC(내림차순) 함수를 사용하면 됩니다. 

select 
  no '선언_한_no열', 
  name '선언_한_name열' ,
  quantity '선언_한_quantity열'
from 
  sample51 
group by 
  no, name, quantity
order by 
  quantity 
desc;