[mySQL] 3장 내부 함수, 그룹 묶기 (SUM, COUNT, SUBSTRING, GROUP BY)

저번 시간을 통해 데이터를 선택하고, 변경하는 것에 대해 알아봤다. 이번 시간에 알아볼 것은 mySQL 에서 효율적으로 데이터를 처리하는

방법에 대해 공부하려 한

데이터 베이스에서 쓰지않는 데이터를 모두 가져오는 일은 좋은 것이 아니다. 테이블 조차도 필요한 칼럼에 중복을 최소화 하는 것이 좋다.

1. 내부 함수 사용하기 (SUM, COUNT, AVG)
2. 문자열을 다루는 내부함수 사용하기 (RIGHT, SUBSTRING_INDEX, UPPER)
3. 데이터의 그룹별 선택 (GROUP BY)

아래 링크를 통해 1장 과정을 통해 생기는 테이블과 데이터를 추가하실 수 있습니다.

https://gist.github.com/a12b884cfa4480714f6e4b998f2991d7.git

현재 student 테이블의 내용입니다. (2장에서 hero로 바꾸셨던 분은 같은 방법으로 student로 바꾸시길 바랍니다.)

1. 내부 함수를 사용하기 (SUM, COUNT, AVG)

내부 함수를 사용하면 데이터를 효율적으로 처리할 수 있습니다. 다음의 경우를 생각해봅시다.

1.  belong 이 'IDE'인 사람이 몇명인지 알고 싶다면??
2. belong 인 'IDE'인 사람들의 status 값의 합은?

첫 번째 쿼리 : 저번 시간에 배웠던 belong 이 'IDE' 값들을 택하는 쿼리다.

두 번째 쿼리 : 결과 4, 선택된 아이템의 개수

세 번째 쿼리 : 선택된 결과들의 status 총합

COUNT, SUM 외에도 AVG(평균), MIN(최소값), MAX(최대값) 도 사용할 수 있다.

2. 문자열을 다루는 내부함수 사용하기 (RIGHT, SUBSTRING_INDEX, UPPER)

흔히, 학번을 물어보면, 11학번, 16학번 이런식으로 대답을 하게 된다. 우리는 _id 값을 통해서 학번을 내부함수를 통해 구해볼 것이다.

예시를 보면 '20100303' 에서 학번을 나타내는 값은 **10**** 이다. 우리는 저 두 문자를 통해 학번을 알 수 있다.

첫 쿼리와 두 번째 쿼리의 차이점이 보이는가?

실제 사용시 우리는 쿼리 결과의 칼럼명을 통해 필드값에 접근한다. 이럴 때 의미에 맞는 변수명을 지어줄 때 AS를 사용할 수 있다. AS는 쿼리를 더욱 간결

하게 만들 때도 사용한다. 그것은 나중에 다루도록 하겠다.

SUBSTRING('20100103', 3, 3) : 3 번째 위치부터 3개의 문자열을 반환  -> '100'

SUBSTRING_INDEX('20100103', '1', 2) : '1'문자가 두 번째로 나오기 전까지 모든 문자열 -> '20100'

이것 외에도 문자열을 다루는 내부 함수는 UPPER, LOWER, REVERSE, LTRIM, LENGTH 등이 있다.

3. 데이터의 그룹별 선택 (GROUP BY)

이런 경우는 어떨까? 만약 특정 belong 별로 인원수가 몇명인지 알고 싶을 때.

쿼리 결과는 다음과 같다. belong을 기준으로 값은 값 끼리 묶어서 가져온다.

그러나 GROUP BY는 주의해야 할 점이 있다. 아래의 결과는 오류를 만든다.

그룹지었을 때 name을 특정 지을 수 없기 때문이다.

그러면 아래의 쿼리 결과를 예측해보자.

쿼리에 GROUP BY, ORDER BY 등이 모두 등장하여 조금 어려울 수 있지만, 차례대로 생각해보자. 우선 GROUP BY 를 이용하여 belong값으로 묶었다.

우리가 얻은 결과값은 belong, 각 그룹에 속한 아이템의 개수, 그룹 내 status의 최대값을 볼 수 있다. 우리는 이 결과를 얻은 3가지 필드값으로 정렬할 수 있다.

여기서 ORDER BY에 두 개의 기준이 제시된다. 첫 째, MAX(status)값을 통해서 내림차순으로 정렬된다. 여기서 중복값이 있는 것들 중에 내부 정렬을 두 번째

기준인 COUNT(*) 오름차순으로 하게 되는 것이다. 

결과적으로 최대값이 가장 큰 값을 기준으로 정렬되고, 가장 큰 값이 중복이 된다면, 사람 수가 적은 그룹이 우선순위가 되는 것이다.