Learning SQL 8장 요약 - 그룹화와 집계

8장 그룹화와 집계

 

데이터를 그룹화하고 집계(aggregate)하는 방법을 알아본다.

 

그룹화의 개념

결과셋을 생성하기 전에 데이터베이스 서버에서 데이터를 먼저 손을 보는 것

 

다음 쿼리를 실행해보면,

SELECT customer_id FROM rental;

599명의 고객의 16,000개 넘는 대여기록을 일일이 조회한 결과임을 알 수 있다.

 

대신 group by 절을 사용해서 데이터베이스 서버에서 데이터를 그룹화 할 수 있다.

SELECT customer_id
FROM rental
GROUP BY customer_id;

결과셋에는 customer_id 열의 각 고윳값을 가지는 행이 하나씩 포함된다.

 

각 고객이 대여한 영화 수를 확인하려면 집계함수를 사용해서 행 수를 계산할 수 있다.

SELECT customer_id, count(*)
FROM rental
GROUP BY customer_id;

 

가장 많은 영화를 대여한 고객을 확인하려면 order by 절을 추가하면 된다.

SELECT customer_id, count(*)
FROM rental
GROUP BY customer_id
ORDER BY 2 desc;

 

데이터를 그룹화할 때 데이터 그룹을 기반으로 필터링을 하는 절은 having 절이다.

반면 where 절은 원시 데이터를 필터링하는 절이다.

또한 where 절이 적용된 뒤에 group by 절이 실행된다.

 

40개 미만의 영화를 대여한 고객을 필터링하는 예제는 다음과 같다.

SELECT customer_id, count(*)
FROM rental
GROUP BY customer_id
HAVING count(*) >= 40;

 

집계함수

함수	반환값
max()	집합 내 최댓값
min()	집합 내 최솟값
avg()	집합의 평균값
sum()	집합의 총합
count()	집합의 전체 레코드 수

SELECT
    MAX(amount) max_amt,
    MIN(amount) min_amt,
    AVG(amount) avg_amt,
    SUM(amount) tot_amt,
    COUNT(amount) num_payments
FROM payment;

 

명시적 그룹과 암시적 그룹

바로 위 예제를 보면

group by 절이 없으므로 단일 암시적 그룹 결과이다.

 

모든 고객이 아닌 각 고객에 대해(열 customer_id 추가)

동일한 5개의 집계 함수를 실행하려면 명시적으로 지정해줘야 한다.

SELECT
    customer_id,
    MAX(amount) max_amt,
    MIN(amount) min_amt,
    AVG(amount) avg_amt,
    SUM(amount) tot_amt,
    COUNT(amount) num_payments
FROM payment
GROUP BY customer_id;

 

고유한 값 계산

SELECT
    COUNT(customer_id) num_rows,
    COUNT(DISTINCT customer_id) num_customers
FROM payment;

첫번째 열엔 16044가 반환, 두번째 열엔 599가 반환된다.

 

표현식 사용

집계시엔 열 이외에도 표현식을 사용할 수 있다.

SELECT MAX(datediff(return_date, rental_date))
FROM rental;

11장에서 집계함수와 case 표현식을 함께 사용해서 특정 행을 집계에 포함할지 여부를 알아본다.

 

Null 처리 방법

먼저 임의의 테이블을 만들고

1, 3, 5를 집어 넣자.

CREATE TABLE number_tbl
(val SMALLINT);

INSERT INTO number_tbl VALUES (1);
INSERT INTO number_tbl VALUES (3);
INSERT INTO number_tbl VALUES (5);

 

그리고 숫자들에 대해 집계 함수를 실행해보자.

SELECT
    COUNT(*) num_rows,
    COUNT(val) num_vals,
    SUM(val) total,
    MAX(val) max_val,
    AVG(val) avg_val
FROM number_tbl;

 

이번엔 null 값을 추가하고 다시 실행해보자.

INSERT INTO number_tbl VALUES (NULL);

SELECT
    COUNT(*) num_rows,
    COUNT(val) num_vals,
    SUM(val) total,
    MAX(val) max_val,
    AVG(val) avg_val
FROM number_tbl;

 

결과는 다음과 같다.

// 쿼리결과
+----------+----------+-------+---------+---------+
| num_rows | num_vals | total | max_val | avg_val |
+----------+----------+-------+---------+---------+
|        3 |        3 |     9 |       5 |  3.0000 |
+----------+----------+-------+---------+---------+

// null값을 추가하고 난 쿼리결과
+----------+----------+-------+---------+---------+
| num_rows | num_vals | total | max_val | avg_val |
+----------+----------+-------+---------+---------+
|        4 |        3 |     9 |       5 |  3.0000 |
+----------+----------+-------+---------+---------+

count(*)는 전체 행 수를 세는 반면

count(val)은 val 열에 저장된 값의 수를 계산한다. (null 무시)

 

그룹 생성

단일 열 그룹화

가장 단순하고 자주 사용된다.

예) 각 배우와 관련된 영화의 수

SELECT actor_id, count(*)
FROM film_actor
GROUP BY actor_id;

 

다중 열 그룹화

하나 이상의 열을 이용해서 그룹을 생성해야 할 때도 있다.

예) 각 배우의 영화 등급에 대한 총 영화수

SELECT fa.actor_id, f.rating, count(*)
FROM film_actor fa
    INNER JOIN film f
    ON fa.film_id = f.film_id
GROUP BY fa.actor_id, f.rating
ORDER BY 1, 2;

 

그룹화와 표현식

표현식으로 생성한 값 기반으로 그룹을 만들 수도 있다.

예) 연도별 대여 수

SELECT
    extract(YEAR FROM rental_date) year,
    COUNT(*) how_many
FROM rental
GROUP BY extract(YEAR FROM rental_date);

 

롤업 생성

예) 각 배우의 영화 등급에 대한 총 영화수와 더불어 각 개별 배우의 총합

group by 절에 with rollup 옵션을 사용해보자.

SELECT fa.actor_id, f.rating, count(*)
FROM film_actor fa
    INNER JOIN film f
    ON fa.film_id = f.film_id
GROUP BY fa.actor_id, f.rating WITH ROLLUP
ORDER BY 1, 2;

 

그룹 필터조건

having 절로 그룹을 필터링 할 수 있다.

SELECT fa.actor_id, f.rating, count(*)
FROM film_actor fa
    INNER JOIN film f
    ON fa.film_id = f.film_id
WHERE f.rating IN ('G', 'PG')
GROUP BY fa.actor_id, f.rating
HAVING count(*) > 9;

 

이 쿼리에는 두 가지 필터조건이 있다.

where와 having이다.

순서는 where가 having보다 먼저 실행된다.

즉, where는 그룹화 되기 전의 필터조건이다.

 

다음과 같이 where 절에 모두 필터를 넣으면 오류가 발생한다.

SELECT fa.actor_id, f.rating, count(*)
FROM film_actor fa
    INNER JOIN film f
    ON fa.film_id = f.film_id
WHERE f.rating IN ('G', 'PG') AND count(*) > 9
GROUP BY fa.actor_id, f.rating;