다양한 Group By 사옹법

group by 는 집계합수들인 min, max, count.. 등을 사용하여 데이타를 집계할때 주로 사용한다.

집계함수

SUM

합계를 구하는 함수(NULL 값들은 무시)

SELECT SUM(salary) FROM employee; // salary 컬럼들의 총 합을 구함

AVG

평균을 구하는 함수(NULL 값들은 무시).

SELECT AVG(salary) FROM employee;  

COUNT

개수를 새는 함수(NULL 값들은 무시).

SELECT COUNT(salary), COUNT(commission) FROM employees;

MIN, MAX

최댓값과 최솟값을 구하는 함수

SELECT MIN(salary), MAX(salary) FROM employees;

GROUP BY

아래처럼 select에 올 수 있는 것은 group by에 정의된 컬럼과 집계함수로 구성된다.

SELECT job, AVG(salary) as avg FROM employee GROUP BY job;

집계함수 외에 다른 컬럼을 가져오고 싶을때

어떤 테이블에 주식회사의 재무재표가 있다고 가정하고 최근 제무재표를 가져오고 싶을때 어떻게 할까?
group by를 이용하여 회사별로 묶는 것 까지는 쉬운것 같은데 각각의 eps나 per같은 최근 정보를 가져오고 싶을때...
이때는 join 문이나 in 절과 함께 사용해야 한다.

In을 사용한 예시

SELECT 
  code, ym, per, eps....
FROM 
  TableName
WHERE (code, created_at)
IN(
  SELECT 
    code, max(created_at) AS created_at
  FROM 
    TableName
  GROUP BY 
    code
)

HAVING

그룹에 조건을 거는 것이다.

SELECT job, COUNT(job) as jobs FROM employee GROUP BY job HAVING COUNT(job) > 2;

group by max 속도 올리기

아주 많은 데이타에 대해 groub by를 실행하면 속도가 상당히 떨어진다.

select 
  max(win)
from 
  table
group by user_id

이경우 일반적으로 index를 잡게 되는데 index를 잡는 방식에 따라 안되는 경우가 있다

INDEX `mb_id_group` (`user_id`, `win`) USING BTREE

반드시 지켜야 할 것은 groupby 에 걸리는 필드가 먼저오고 그다음에 max에 오는 필드가 와야 한다.
만약 where가 존재하면 where에 걸리는 키를 각각 하나씩 입력한후 전체 필드에서도 where가 먼저 오도록 걸어 주어야 한다.

select 
  max(win)
from 
  table
where field1 = 'aa'
group by user_id

INDEX `mb_id_group` ('field1 ',`user_id`, `win`) USING BTREE

그렇지 않으면 index가 제대로 걸리지 않는다. 기타 선택필드가 있다면 이후로 계속 나열하면 된다.

기타 예제

sum 및 group by 및 case문을 이용한 응용예제

아래는 특정필드의 값의 갯수를 구하는 예입니다. 한방 쿼리를 고민하시는 분들께 좋은 예제가 될 듯 합니다.

select
  uid,
  count(uid) as total, 
  sum(case field1 when 1 then 1 else 0 end) as out1,
  sum(case field1 when 2 then 1 else 0 end) as out2,
  sum(case field1 when 3 then 1 else 0 end) as out3,
from tablename where [조건절]
group by uid

select 
  sum(case  when process_status >= 30 then 1 else 0 end) as buyer_cnt,
  sum(case  when post_url <> '' then 1 else 0 end) as post_cnt,
  sum(case  when post_status >= 10 then 1 else 0 end) as post_cnt 
from 테이블명 where 조건절;

• 상기와 결과는 같지만 다른 케이스

select 
  count(case  when process_status >= 30 then true else null end) as buyer_cnt,
  count(case  when post_url <> '' then true else null end) as post_cnt,
  count(case  when post_status >= 10 then true else null end) as post_cnt 
from 테이블명 where 조건절;

날짜별로 조건검색하기

select FROM_UNIXTIME( R_CONTACT_TIME, '%Y%m' ), 
  sum(case R_TYPE when 'T' then 1 else 0 end) as type_t,
  sum(case R_TYPE when 'O' then 1 else 0 end) as type_o,
  sum(case when R_TYPE not in ('T', 'O') and R_AIRPORT = 'ICN' then 1 else 0 end) as type_i,
  sum(case when R_TYPE not in ('T', 'O') and R_AIRPORT = 'GMP' then 1 else 0 end) as type_g
from ".$this->tables["m_reservation"]."
group by FROM_UNIXTIME( R_CONTACT_TIME, '%Y%m' )  

self join 을 사용

select distinct m1.userid, m1.reccnt, count(m1.uid) as ranking
from MemberInfo m1, MemberInfo m2
where m1.reccnt < m2.reccnt or m1.uid = m2.uid
group by m1.uid
order by ranking

mysql group by 시 max row에 해당하는 값 가져오기

SELECT tt.*
FROM topten tt
INNER JOIN
    (SELECT home, MAX(datetime) AS MaxDateTime
    FROM topten
    GROUP BY home) groupedtt 
ON tt.home = groupedtt.home 
AND tt.datetime = groupedtt.MaxDateTime