CS/기초데이터베이스

[데이터베이스] 14. SQL 집계 함수와 Group By

에버듀 2024. 10. 21. 10:39
반응형

관계 대수에서는 없었지만, SQL에는 테이블에 있는 데이터들에 대해 다음의 5가지 집계 함수를 제공한다.

 

1. count()

2. sum()

3. avg()

4. max()

5. min()

 

이때 각 함수에는 하나의 컬럼이 들어가며, 1, 2, 3 은 그 컬럼에 대해 Distinct 를 취할 수도 있다.

(4, 5번에도 사용은 가능하나 DISTINCT 를 취해도 같은 결과가 나온다.)

 

 

1. 모든 선원의 평균 나이

SELECT avg(S.age)
FROM sailors S;

 

 

2. 등급이 10인 선원의 평균 나이

SELECT avg(S.age)
FROM sailors S
WHERE S.rating = 10;

 

 

3. 가장 나이가 많은 선원의 이름과 나이

SELECT S.sname, S.age
FROM sailors S
WHERE S.age >= ALL (SELECT S2.age
                    FROM sailors S2);
SELECT S.sname, S.age
FROM sailors S
WHERE S.age = (SELECT max(S2.age)
                    FROM sailors S2);

 

 

 

 

참고로 이런 식의 문법은 허용하지 않는다.

max 값의 결과는 하나인데, S.sname 의 결과는 여러개 일 수 있으며, 의도가 모호하다.

(나이가 최대인 사람의 이름을 보여주는 것인지, 이름은 따로 보여주고 최댓값을 따로 보여주라는 것인지)

(그냥 max 하나만 조회하는 것만 허용된다.)

 

 

4. 선원들의 숫자

SELECT COUNT(*)
FROM sailors;

 

쿼리의 실행 결과는 10이다.

 

 

5. 서로 다른 뱃사람 이름 가짓수

SELECT COUNT(DISTINCT S.sname)
FROM sailors S;

 

Horatio 만 한번 겹치므로 9 가지의 이름이 존재한다.

 

이번엔 GROUP BY 문법을 살펴보자.

그룹 바이는 특정한 기준을 가지고, 그 기준과 같은 값을 같은 튜플들을 모아 그룹을 만든다.

(선원 테이블에서 나이를 기준으로 그룹을 만든다면, (나이가 1인 사람의 그룹, 2인 사람의 그룹, ..., 64인 사람의 그룹) 과 같은 식이다.

그룹 바이는 이렇게 그룹을 지었을 때, 각각의 그룹에 대해 집계 함수를 사용할 수 있도록 해준다.

 

예를 들어 다음과 같은 문제를 생각해보자.

 

 

6. 각 rating 별로, 가장 어린 선원의 나이 조회

 

단순하게 집계 함수를 사용해본다면

 

이렇게 작성한 SQL을 각 rating 값에 대해 모두 다 해봐야 할 것이다.

이 문제는 group by 를 사용하여 해결할 수 있다.

 

 

그룹 바이의 문법은 위와 같다.

실행 순서는 FROM - WHERE - GROUP BY - HAVING - SELECT 순서이다.

테이블에서 모든 데이터를 가져온 뒤, WEHRE 절로 조건에 맞는 튜플을 거른 뒤, GROUP BY로 튜플들을 그루핑하고, 각각의 그룹 중 조건에 맞는 그룹을 HAVING 절을 통해 거른 최종 결과에 대해 SELECT 한다.

(HAVING 은 그룹 중에서 조건에 맞는 데이터를 가진(having) 그룹을 선별하겠다는 의미로 보면 된다.)

 

grouping-list 에는 그룹핑할 기준 컬럼들을 나열할 수 있다.

이때 SELECT 절에는 grouping-list 에 명시한 기준 컬럼들을 제외하고는 집계 함수만 사용할 수 있다.

그룹 바이를 통해 조회하는 값은 각각의 그룹들에 대한 '대표값' 을 조회하는 것이므로

각 그룹별로 1개의 값을 특정할 수 있는 값을 보여줘야 하기 때문이다.

(그루핑 기준은 그 그룹에 1개 밖에 없는 속성 또는 속성 조합이므로 괜찮지만, 다른 컬럼은 그룹 내에 여러가지 값이 존재할 수 있으므로 대표값을 선정할 수 없다.)

 

다시 문제로 돌아오면 문제의 답은 다음과 같다.

SELECT S.rating, MIN(S.age)
FROM sailors S
GROUP BY S.rating;

 

 

rating 순서가 뒤죽박죽이긴 하지만, 이렇게 rating 별로 그룹을 묶고, 각각의 그룹에 존재하는 튜플들에 대해 최소 나이를 구하였다.

 

 

2. 두 명 이상의 선원이 가진 등급에 대해, 각 등급별로 나이가 18세 이상인 사람 중 가장 어린 선원의 나이 구하기

 

책에서는 다음과 같은 해답을 제시하였다.

SELECT S.rating, MIN(S.age)
FROM sailors S
WHERE S.age >= 18
GROUP BY S.rating
HAVING 1 < count(*);

 

그런데 데이터베이스 스터디를 하다가 스터디원 중 한 명이 의문을 제기했다.

이건 '모든 두 명 이상의 선원이 가진 등급에 대해서 구한 것이 아니므로 답이 아니지 않냐. 만약 나이가 18세 미만인 선원들로 구성된 등급 그룹이 있다면, 그 그룹에 대해서도 NULL 과 같은 결과를 보여줘야 하지 않는가?'

 

그래서 한국어 질문에 맞게 풀어보기로 했다.

 

 

먼저 데이터베이스에 18세 미만의 같은 등급을 갖는 선원들을 추가해주었다.

 

SELECT S.rating, MIN(S.age)
FROM sailors S
WHERE S.age >= 18
GROUP BY S.rating
HAVING 1 < count(*)
UNION
SELECT S.rating, NULL
FROM sailors S
WHERE S.age < 18
GROUP BY S.rating
HAVING 1 < count(*)

 

그리고 다음과 같이 UNION 을 사용하여 쿼리를 작성했다.

 

하지만 지금 생각해보니, 이 풀이는 올바른 풀이가 아니다.

나이가 17, 20 이렇게 섞여서 들어있는 경우는 제외되기 때문이다.

먼저 선원이 2명 이상 존재하는 모든 등급 리스트를 뽑고, 각 레이팅에 대해 돌면서 최소 나이를 구해야 할 것 같다.

 

이와 관련하여 GPT는 SELECT 절에서 서브쿼리를 작성하는 풀이를 제시하기도 했다.

SELECT 
    rating,
    (SELECT AVG(age) 
     FROM Sailors s2 
     WHERE s2.rating = s1.rating AND s2.age >= 18) as avg_age
FROM 
    (SELECT DISTINCT rating
     FROM Sailors
     GROUP BY rating
     HAVING COUNT(*) >= 2) s1

 

하지만 SELECT 절에서 서브쿼리를 쓰는 것은 배운 내용이 아니라서 시험에서까지 활용하기는 힘들 것 같다.

(아무리 고민해도 이 방법 밖에 안 떠오르긴 한다.)

 

 

3. 각각의 빨간색 배에 대해, 그 배를 예약한 건수 조회

SELECT R.bid, count(*)
FROM Reserves R
WHERE R.bid IN (SELECT B.bid
                FROM Boats B
                WHERE B.color = 'red')
GROUP BY R.bid;

 

 

책에서는 서브 쿼리 대신 조인을 통해 해결하였다.

 

 

만약 책에서 제시한 쿼리에서 B.color = 'red' 조건을 HAVING 절로 옮기면 어떻게 될까?

정답은 문법 오류가 난다.

 

지금 이 쿼리 상황에서는 B.bid 를 기준으로 그룹핑을 했기 때문에, 그룹 안에 있는 보트는 모두 같은 색을 갖는 것이 당연하다.

하지만 만약 GROUP BY 를 B.name 으로 한다고 해보자.

그러면 같은 이름을 갖는 서로 다른 색의 배가 있을 수 있으므로, HAVING 절에서 색상 조건을 걸면 그룹 안에 그 색이 존재만 하면 되는 것인지, 모든 보트가 그 색이어야 하는지 조건이 애매해진다.

 

따라서, HAVING 에는 반드시 그룹에서 항상 유일하게 존재할 수 있다고 여겨지는 대표값으로 식별해야 하며,

그 값은 당연히 그룹 기준이 된 컬럼과 각 그룹에 대한 집계 함수 결과 밖에 될 수 없다.

 

 

4. 두 명 이상의 선원이 있는 각 등급에 대해, 등급별 선원의 평균 나이 구하기

SELECT S.rating, avg(S.age)
FROM sailors S
GROUP BY S.rating
HAVING 1 < count(*);

 

 

 

5. 두 명 이상의 선원이 있는 각 등급에 대해, 18세 이상인 선원들의 평균 나이 구하기

 

위에서 정리했던 그 논란과 동일한 논란의 문제이다.

두 명 이상의 선원이 있는 모든 등급 (11, 3, 10, 7, 8) 에 대해서 18세 이상인 선원들의 평균 나이를 구하는 것인지,

아니면 두 명 이상의 선원이 있는 모든 등급 중, 18세 이상인 선원들이 존재하여 평균을 구할 수 있는 등급에 대해 평균을 구하는 것인지 모호하다.

 

책에서는 후자의 방식으로 구하였다.

SELECT S.rating, avg(S.age)
FROM sailors S
WHERE S.age >= 18
GROUP BY S.rating
HAVING 1 < (SELECT count(*)
            FROM sailor S2
            WHERE S.rating = S2.rating)

 

(교수님 말씀으로는 NULL 이 나오는 것이 맞는데, 여기서는 row를 아예 보여주지 않고 있다는 느낌으로 표현하셨다.)

 

 

6. 18세 이상의 선원이 두 명 이상 있는 등급에 대해, 각 등급 별 선원들의 평균 나이 구하기

 

이번에는 그룹을 선별할 때 아무나 2명 이상이 아니라 18세 이상인 선원이 2명 이상인 경우를 뽑아야 한다.

SELECT S.rating, avg(S.age)
FROM sailors S
WHERE S.age >= 18
GROUP BY S.rating
HAVING 1 < count(*);

 

이 경우엔 간단하게 이렇게 구하면 된다.

 

 

7. 모든 등급 별 선원들의 평균 나이에 대해, 평균 나이가 최소인 등급 구하기

등급별로 평균 나이를 구해두고, 그 결과에 대해 최소인 등급을 구하면 된다.

SELECT T.rating, T.avg_age
FROM (SELECT S.rating, avg(S.age) as avg_age
      FROM sailors S
      GROUP BY S.rating) T
WHERE T.avg_age = (SELECT MIN(T2.avg_age)
                   FROM (SELECT avg(S.age) as avg_age
                         FROM sailors S
                         GROUP BY S.rating) T2

 

똑같은 서브쿼리를 두번 쓰기 싫었는데, T 를 WHERE 절 서브쿼리 안에서 FROM 절에 그대로 쓰니 에러가 났다ㅠㅠ

(책에서는 똑같은 T 라는 테이블을 두번째 서브쿼리 안에서 그대로 활용하였다.)

 

책에서는 다음과 같은 쿼리가 같은 답을 낼 지 추측해보라는 연습 문제도 제시하고 있었다.

SELECT Temp.rating, MIN(Temp.avg_age)
FROM (SELECT S.rating, avg(S.age) as avg_age
      FROM sailors S
      GROUP BY S.rating) AS Temp
GROUP BY Temp.rating;

 

당연히 같은 답을 내지 않는다.

이미 rating 값 하나에 평균값 하나가 할당된 상태에서, 다시 rating 으로 그룹 바이를 해봤자 의미가 없다.

 

실행결과는 위와 같다.

 

 

반응형
저작자표시 비영리 변경금지