group by의 모든 것

요약

• 소리가 갑자기 작아졌다.
• 내가 having을 몰라서 쓸데없이 서브쿼리를 쓰고 그랬구나…
• 근데 권철민님도 group by 하고 이름 뽑을 때 max 쓰시는데…? 이게 정공법이었나…?
• rollup과 cube를 사용하더라도 보기좋게 만들기 위해서는 엑셀파일을 가공해야 할 것 같은데… 어쨌든 소계의 null값은 select에서 case when으로 다른 값으로 바꿔주면 될 것 같다.

group by 이해

• group by 절에 기술된 컬럼 값(또는 가공컬럼)으로 그룹화한 뒤 집계(aggregation)함수와 함께 사용되어 그룹화된 집계 정보를 제공한다.
• group by 절에 기술된 컬럼 값으로 반드시 1의 집합을 가지게 된다.
• 집계함수와 같이 쓰이지 않으면 group by를 사용하지 않은 column은 select절에서 사용할 수 없다.

참고 : having을 이용해서 group by로 집계된 내역을 다시 한 번 where 걸 수 있다.

집계함수

• 집계함수는 null을 계산하지 않습니다.
• min / max는 숫자값 말고 문자열/날짜/시간도 가능함. (그래서 이름을 max로 뽑을 수 있다.)
• count(*) : 무조건 row레벨로 센다는 뜻

distinct

: 정해진 집합 레벨에서 해당 컬럼으로 중복을 배제하고 고유한 값을 가져와서 계산함

floor() : 소수점 제거하는 함수

: 아래와 같은 테이블에서 1000단위로 나누어서 group by를 실행하고 싶다면 이렇게 하면 된다.

• hr.emp테이블
  

empno	sal
1	1500
2	1600
3	2300

select floor(sal/1000)*1000, count(*)
from hr.emp
group by floor(sal/1000)*1000

Pivoting : aggregation과 case when을 활용

: 행과 열을 바꿔서 볼 수 있음.

인프런의 예 : 비즈니스 월별 매출 등 여러가지 통계자료도 pivoting 활용가능

select year, sum(revenue) as year_rev, 
  sum(case when Month = 1 then revenue end) as Jan,
  sum(case when Month = 2 then revenue end) as Feb,
  ...
from sales
group by year

group by Rollup과 Cube

• rollup과 cube는 group by와 함께 사용되어 group by 절에 사용되는 컬럼들에 대해서 추가적인 group by를 수행
• rollup은 계층적인 방식으로 group by 추가 수행
• cube는 group by 절에 기재된 컬럼들의 가능한 combination으로 group by 수행한다

1. rollup 인프런 예시 : 각 그룹의 강의별 매출 + 각 그룹의 매출합을 소계로 구할 수 있다.

신기한데?

{그룹별 합} + {그룹 안에서 구매한 강의별 합}

select group_id, course_id, sum(ct.pay_price)
from carts ct 
inner join orders od on od.id = ct.order_id 
where od.deleted_at is null 
and od.group_id is not null 
and od.created_at > '2022-03-31' 
and ct.pay_price > 0
group by rollup(od.group_id, ct.course_id)
order by group_id desc, course_id desc

2. cube 예시 : rollup에서 했던 것 + 가능한 모든 결합을 실행함.

(rollup은 계층이지만 cube는 아니다.)

{그룹별 합} + {그룹 안에서 구매한 강의별 합} + {그룹과 관계없이 강의별 합}

select group_id, course_id, sum(ct.pay_price)
from carts ct 
inner join orders od on od.id = ct.order_id 
where od.deleted_at is null 
and od.group_id is not null 
and od.created_at > '2022-03-31' 
and ct.pay_price > 0
group by cube(od.group_id, ct.course_id)
order by group_id desc, course_id desc