[SQL/Programmers] GROUP BY

프로그래머스 문제집을 풀며 GROUP BY 절을 마스터해보자.

 

https://school.programmers.co.kr/learn/courses/30/parts/17044

프로그래머스

 

---

GROUP BY 절

FROM 절과 WHERE 절 뒤에 오며, 데이터들을 작은 그룹으로 분류하여 소그룹에 대한 항목별 통계 정보를 얻을 때 추가로 사용한다.

 

SELECT DISTINCT 칼럼명 ALIAS명
FROM 테이블명
WHERE 조건식
GROUP BY 칼럼/표현식
HAVING 그룹의 조건식;

 

🔺 GROUP BY에 사용된 컬럼 이외의 컬럼은 단독으로 select 절에 사용할 수 없다.

🔺 WHERE절과 GROUP BY절에는 Alias명을 쓰지 않는다!

 

 

1. GROUP BY 절과 HAVING 절의 특성

• GROUP BY 절을 통해 소그룹별 기준을 정한 뒤, SELECT 절에 집계 함수 사용
• 집계 함수의 통계 정보는 NULL 값을 가진 행을 제외하고 수행
• 집계 함수는 WHERE 절에는 올 수 없음 (GROUP BY 절보다 WHERE 절이 먼저 수행)
• WHERE 절은 전체 데이터를 GROUP으로 나누기 전에 행들을 미리 제거하는 역할
• HAVING 절은 GROUP BY 절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건을 표시할 수 있음
• GROUP BY 절에 의한 소그룹별로 만들어진 집계 데이터 중, HAVING 절에서 제한 조건을 두어 조건을 만족하는 내용만을 출력
• HAVING 절은 일반적으로 GROUP BY 절 뒤에 위치
• GROUP BY 절에서는 ALIAS명을 사용할 수 없다
• 원칙적으로 ORDER BY 절 명시해야 정렬 (일부 DB 과거 버전에서 자동 오름차순 정렬)

 

2. HAVING 절

• WHERE 절에는 집계 함수를 사용할 수 없다
• GROUP BY 절보다 HAVING 절을 앞에 사용해도 같은 결과가 나오지만, 논리적으로 순서를 지키는 것을 권고한다. 
• 가능하면 WHERE 절에서 조건절을 적용하여 GROUP BY 계산 대상을 줄이는 것이 효과적
• HAVING 절은 SELECT 절에 사용되지 않은 칼럼이나 집계 함수가 아니더라도, GROUP BY 절의 기준 항목이나 소그룹의 집계 함수를 이용한 조건을 표시 가능
• WHERE 절 조건 변경은 대상 데이터 개수가 변경되므로 결과 데이터 값이 변경 가능성O, HAVING 절 조건 변경은 결과 데이터 변경은 없고 출력되는 레코드 개수만 변경 가능성O

 

 

3. 문제 풀이

 

🟩 고양이와 개는 몇 마리 있을까

SELECT ANIMAL_TYPE, COUNT(*) as count
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE;

 

🟩 동명 동물 수 찾기

 

SELECT NAME, COUNT(*) as COUNT
FROM ANIMAL_INS
WHERE NAME is not null
GROUP BY NAME
HAVING COUNT(*) >=2
ORDER BY NAME;

 

NAME 컬럼에 null 값을 제외한다는 문장을 놓치면 안된다!

 

 

🟩 입양 시각 구하기(1)

• 처음 풀이

SELECT HOUR(DATETIME) as HOUR, COUNT(*) as COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR
HAVING 9 <= HOUR and HOUR <= 19
ORDER BY HOUR;

 

이때 내가 위에 정리한 개념을 놓치고 sql문을 작성한 것을 알게 되었다.

• 새로 작성한 풀이

SELECT HOUR(DATETIME) AS HOUR, COUNT(*) AS COUNT
FROM ANIMAL_OUTS
WHERE 9 <= HOUR(DATETIME) and HOUR(DATETIME) <= 19
GROUP BY HOUR(DATETIME)
ORDER BY HOUR;

 

1. 가능하면 WHERE 절에서 조건절을 적용하여 GROUP BY 계산 대상을 줄이는 것이 효과적
2. GROUP BY 절에서는 ALIAS명을 사용할 수 없다

이 두가지를 고려해서 다시 풀어봤다.

 

 

🟩 입양 시각 구하기(2)

 

처음에 이 문제를 봤을 때 테이블에 없는 시간 데이터까지(0~23) 조회해야한다.

그래서 알아본 결과 SET 구문을 이용해 새로운 변수를 할당해주고 서브쿼리를 이용해야하는 문제였다.

 

• SET : The SET command is used with UPDATE to specify which columns and values that should be updated in a table.

 

 

• 정답 

SET @HOUR = -1;
SELECT @HOUR := @HOUR + 1 AS HOUR,
      (SELECT COUNT(*)
       FROM ANIMAL_OUTS
       WHERE @HOUR = HOUR(DATETIME)) AS COUNT 
FROM ANIMAL_OUTS
WHERE @HOUR < 23

 

1) SET @HOUR = -1;

 

SET을 이용해 @HOUR 새로운 변수를 할당했다.

이때는 '=' 연산자는 대입 연산자이다. 하지만 나머지 SELECT절에서는 ':='가 대입 연산자가 된다.

나머지 경우 '='는 비교 연산자.

 

 

2) SELECT @HOUR := @HOUR + 1 AS HOUR

 

@HOUR 를 @HOUR+1로 재할당했다.

이로써 출력은 0부터 1씩 증가할 것이다.

 

 

3) SELECT절 서브 쿼리

 

SELECT절 서브 쿼리는 하나의 열처럼 생각하면 된다.

 

이때 새롭게 할당한 @HOUR과 원래 데이터의 DATETIME의 HOUR가 같을 때 값을 세어주면 된다.

 

 

4) WHERE @HOUR < 23

 

현재 @HOUR:=@HOUR+1로 즉, @HOUR+1로 재할당되어 있기 때문에 23까지 출력하기 위해서는 '<23'을 해야된다.

 

 

4. 활용

BRANCH_ID가 같은 사람들 중 SALARY가 가장 큰 사람 전부 출력

SELECT e.BRANCH_ID, e.NAME
FROM EMPLOYEES e
INNER JOIN (
    SELECT BRANCH_ID, MAX(SALARY) SALARY
    FROM EMPLOYEES
    GROUP BY BRANCH_ID
) b ON e.BRANCH_ID = b.BRANCH_ID AND e.SALARY = b.SALARY
ORDER BY BRANCH_ID, NAME;

 

---

📌 Reference

• 데이터 전문가 포럼, [SQL 개발자(SQLD) 가이드]
• [SQL] MySQL - SET 과 서브쿼리 사용하기