2024-04-12

그룹화와 집계

그룹화의 개념

HAVING은 GROUP BY의 where이라고 보면 된다.

집계 함수

COUNT(DISTINCT )를 통해서 고유한 값에 대해서만 count 할 수 있음

NULL 처리

COUNT(*)를 하면 row 숫자, 즉 NULL을 포함한다.
COUNT(field) 를 하면 NULL은 제외된다.

롤업

GROUP BY ~~ WITH ROLLUP 을 하면 NULL 값으로 총 개수를 반환해준다.

서브 쿼리

서브 쿼리 유형

비상관 서브쿼리

단일 행과 열이 포함된 결과셋 반환
- 스칼라 서브쿼리로도 불리며 일반적인 연산자를 사용해서 조건의 양쪽에 기술
- 하지만 둘 이상의 행을 반환하면 오류 발생
단일 열을 가진 다중 행을 반환하는 서브 쿼리
- IN을 사용한다.
- ALL을 사용하면 앞에 =, <>, >, < 등의 연산자 사용이 가능하다.
- ANY는 ALL과 같이 연산자를 사용하는데, 비굣값 중 하나라도 만족하면 true를 반환

상관 서브쿼리

포함 구문을 실행하기 전에 실행되지 않고, 후보 행에 대해 한번씩 실행

동등 조건 가능

SELECT c.first_name, c.last_name
FROM customer c
WHERE 20 = 
  (SELECT count(*) FROM rental r WHERE r.customer_id = c.customer_id)

서브쿼리가 20을 반환하면 필터 조건이 충족되고 해당 행은 결과셋에 추가됨
EXIST랑도 많이 쓰인다.
- 서브 쿼리 안에서는 select 1 또는 select *을 정의하는 것이 규칙이다.
UPDATE, DELETE 문에서 많이 쓰인다. WHERE이 없기 때문이다.

서브 쿼리를 사용하는 경우

FROM 절에서도 많이 쓰인다. -> 비상관 관계여야 한다.

이렇게 테이블을 생성하고, 등급을 나눌 수도 있다.

SELECT fa.actor_id, grp.level, fa.cnt 
FROM (
  SELECT actor_id, COUNT(*) cnt 
  FROM film_actor 
  GROUP BY actor_id
) fa 
INNER JOIN (
  SELECT 'Hollywood Star' level, 30 min_roles, 999999 max_roles 
  UNION ALL 
  SELECT 'Prolific Actor' level, 20 min_roles, 29 max_roles 
  UNION ALL 
  SELECT 'Newcomer' level, 1 min_roles, 19 max_roles
) grp ON fa.cnt BETWEEN grp.min_roles AND grp.max_roles;
+----------+----------------+-----+
| actor_id | level          | cnt |
+----------+----------------+-----+
|        1 | Newcomer       |  19 |
|        2 | Prolific Actor |  25 |
|        3 | Prolific Actor |  22 |
|        4 | Prolific Actor |  22 |
              ...
|      190 | Prolific Actor |  27 |
|      191 | Hollywood Star |  30 |
|      192 | Prolific Actor |  29 |
|      193 | Prolific Actor |  23 |
|      194 | Prolific Actor |  22 |
|      195 | Prolific Actor |  27 |
|      196 | Hollywood Star |  30 |
|      197 | Hollywood Star |  33 |
|      198 | Hollywood Star |  40 |
|      199 | Newcomer       |  15 |
|      200 | Prolific Actor |  20 |
+----------+----------------+-----+
200 rows in set (0.00 sec)

SELECT grp.level, COUNT(*) cnt
FROM (
  SELECT actor_id, COUNT(*) cnt 
  FROM film_actor 
  GROUP BY actor_id
) fa 
INNER JOIN (
  SELECT 'Hollywood Star' level, 30 min_roles, 999999 max_roles 
  UNION ALL 
  SELECT 'Prolific Actor' level, 20 min_roles, 29 max_roles 
  UNION ALL 
  SELECT 'Newcomer' level, 1 min_roles, 19 max_roles
) grp ON fa.cnt BETWEEN grp.min_roles AND grp.max_roles
GROUP BY grp.level;
+----------------+-----+
| level          | cnt |
+----------------+-----+
| Newcomer       |  10 |
| Prolific Actor | 118 |
| Hollywood Star |  72 |
+----------------+-----+

테스크 지향 서브쿼리: 무지성 조인을 하지 말고, 먼저 서브 쿼리로 id들을 뽑아오고 나머지를 조인하면 훨씬 효율적이다.
공통 테이블 표현식(CTE): 임시 테이블을 만들어준다.
```
WITH ~~ AS (
  SELECT ~ 
)
```