Sql 코딩테스트 예제 - sql kodingteseuteu yeje

📚 시리즈/- 초보자도 준비하는 SQL 코딩 테스트

2021. 2. 25.

카테고리 초보자도 준비하는 SQL 코딩 테스트에 가시면 해당 시리즈를 확인하실 수 있습니다.

• 해당 시리즈를 시작하며
• 기초 이론 다지기
  • 이론 및 실습 환경 세팅 샘플 데이터 및 샘플 쿼리문
  • 데이터베이스와 SQL이란?
    1. SELECT의 WHERE과 ORDER BY
    2. 집계 함수와 GROUP BY
    3. LIMIT와 서브 쿼리에 대하여
    4. 중복과 NULL 다루기
    5. SQL 코테의 꽃 조인
• 초보자도 준비하는 SQL 코딩 테스트 시리즈를 마치며 부제-이제 뭘 해야 하나요?

목차

• 집계 함수
  • AVG, MIN, MAX, COUNT
  • 실습 해보기
  • 혼자 생각해보기
• GROUP BY
  • 기본적인 GROUP BY 형식
  • GROUP BY를 더 깊게 사용하기
  • 혼자 생각해보기
• HAVING
  • 왜 HAVING이 필요할까
  • HAVING절 사용하기

지난 시간에 우리는 SELECT의 기본적인 사용 방법과 SELECT와 함꼐 쓰일 수 있는 WHERE, ORDER BY에 대해서 배웠다.

이번 시간에는 우리가 원하는 데로 데이터를 묶어주는 GROUP BY에 대해서 알아보자.

GROUP BY 절은 SQL 코딩 테스트에서 자주 출제되는 유형중 하나이다.

집계 함수

집계 함수는 특정 값들을 말 그대로 집계 할 때 사용된다.

집계에는 여러 가지 종류가 존재한다.

그 중에 대표적인 4가지를 봐보자.

• AVG : 평균을 구하는 집계
• MIN : 최소를 구하는 집계
• MAX : 최대를 구하는 집계
• COUNT : 총 개수를 구하는 집계

그럼 우리는 다음과 같은 쿼리를 처리할 수 있다.

buytb1 에서 총 얼만큼 주문되었는지 총 주문량을 출력하라

SELECT sum(amount)
FROM buytb1;

usertb1 에서 사용자들의 평균 키를 출력하라

SELECT avg(height)
FROM usertb1;

이런 집계 함수는 사용될 수 있는 쿼리문 영역이 매우 다양하며, 자주 사용될 수 있다.

그 말은 코딩 테스트에서도 기본적으로 이런 집계 함수를 이용하는 문제가 자주 출제될 수 있다는 소리이다.

혼자 생각해보자!

만약 usertb1 에서 키가 가장 큰 사용자와 키가 가장 작은 사용자의 이름을 출력하라! 라고 한다면 어떤 쿼리문이 나올까?

분명 여러분은 할 수 있을 것이라고 생각한다.

못 해도 좋습니다. 이건 꽤나 어려운 문제에요! 아주 어렵지만 여러번 보고 여러 문제를 보면 할 수 있으니 걱정하지 마세요 :)
지금은 몰라도 아마 한 80% 예상하는데, 아마 이론 시리즈만 끝내면 이 문제 스스로 풀 수 있을걸요?
만약 답을 모르겠다면 비밀 댓글도 좋으니 달아주세요 ㅎㅎ

GROUP BY

SELECT 필드명
FROM 테이블명
GROUP BY 필드명 혹은 표현식

우선 GROUP BY에 대해서 알기 전에 어디서 사용되는지 예제를 먼저 접하고 이해해보자.

• 1 buytb1 에서 물품 별로 총 몇 개가 주문되었는지 출력하라
• 2 usertb1 에서 지역 별로 사용자들의 키 평균이 몇인지 출력하라
• 3 usertb1 에서 번호 앞 자리(010인지 011인지) 별로 몇 명이 있는지 출력하라.
• 4 buytb1 에서 물품 별로 가장 많이 주문된 것을 출력하라

GROUP BY를 사용하지 않고 위의 4개 쿼리를 혼자서 한 번 수행해보자.
쉽지 않을 것이다. 이 과정을 거친다면 왜 우리는 GROUP BY 함수를 써야하는지 아주 직관적이게 이해할 수 있을 것이다.

위에 나온 문제들은 모두 GROUP BY 절이 사용되어야 출력할 수 있다.

그럼 공통점을 한 번 찾아보자.

• 총 몇개, 평균이 몇 명, 가장 큰, 몇 명이 있는지와 같은 집계 함수가 사용되었다.
• xx 별로라는 말이 쓰였다.

이 둘을 종합해서 본다면, GROUP BY는 집계 함수를 통해서 xx 별로 그들 만의 그룹을 만들어주는 함수라고 정의할 수 있다.

그럼 각각의 문제에 대해서 쿼리를 진행할 수 있다.

/* 1번 buytb1 에서 **물품 별로** 총 몇 개가 주문되었는지 출력하라 */
SELECT prodname, count(amount)
FROM buytb1
GROUP BY prodname;

/* 2번 usertb1 에서 **지역 별로** 사용자들의 키 평균이 몇인지 출력하라 */
SELECT addr, avg(height)
FROM usertb1
GROUP BY addr;

/* 3번 usertb1 에서 **번호 앞 자리(010인지 011인지) 별로** 몇 명이 있는지 출력하라.
 */
SELECT mobile1, count(*)
FROM usertb1
GROUP BY mobile1;

/* 4번 buytb1 에서 **물품 별로** 가장 많이 주문된 것을 출력하라 */
SELECT prodname, max(amount)
FROM buytb1
GROUP BY prodname;

GROUP BY 함수를 좀 더 깊게 사용해보자.

buytb1 에서 사용자들의 총 구매 금액을 출력하라.

우선 우리는 사용자 별로 구매 금액을 따로 나눠야 하니 사용자를 그룹으로 묶어줘야 한다.

그리고 각각의 총 구매 금액을 계산해보자.

SELECT userid, amount * price
FROM buytb1;

을 수행하면 각각 사용자의 물품별 구매 금액이 나온다.

우리는 이 물품별 구매 금액을 모두 합산해야 하므로 sum을 이용해 집계할 수 있다.

SELECT userid, sum(amount * price)
FROM buytb1;

오 근데 에러가 난다.

왜냐? 집계 함수로 집계를 한 결과가 어떤 그룹에 속해야 할지 모르기 때문에.

userid는 여러 개가 존재하는데, 그 중 어디에 속해야 할지 므로므로 userid로 GROUP 지어주면 된다.

SELECT userid, sum(amount * price)
FROM buytb1
GROUP BY userid;

그럼 우리가 원하는 결과가 아주 아름답게 잘 나오게 된다.

혼자 생각해보자!

만약 buytb1에서 사용자들이 평균적으로 몇 개의 물건을 샀는지 출력하라고 하면 어떻게 해야할까?

이 문제 또한 못 해도 좋습니다. 앞서 말 했듯 여러번 보고 여러 문제를 보면 할 수 있으니 걱정하지 마세요 :)
만약 답을 모르겠다면 비밀 댓글도 좋으니 달아주세요 ㅎㅎ

HAVING

그럼 내친김에 이것도 풀어보자.

buytb1 에서 사용자들의 총 구매 금액이 1500 이상인 사용자를 출력하라.

위에서 우리는 총 구매 금액을 구한적이 있다.

SELECT userid, sum(amount * price)
FROM buytb1
GROUP BY userid;

그럼 이제 1500 이상인 사용자를 구하는 것은 아주 쉽다.

WHERE 을 사용하자.

SELECT userid, sum(amount * price)
FROM buytb1
GROUP BY userid
WHERE sum(amount * price) > 1500;

엥? 안된다.

우리의 논리라면 돼야 하는데 안된다..

이유는 우리의 논리라서 그렇다.

우리의 논리를 좀 확장 시키면 이건 당연히 안되는 것이 된다.

논리를 확장시켜보자.

집계 함수에서 조건을 줄 때

집계 함수에서 조건을 줄 때는 WHERE 대신 HAVING을 사용한다.

즉, HAVING 절은 집계 함수에서 조건을 줄 때 사용한다.

결국 우리는 위에서 조건을 집계함수를 사용한 값으로 줬기 때문에 동작하지 않는 것이다.

이제 HAVING이 왜 필요한지 알게 되었다.

HAVING절 사용 방법

SELECT 필드명
FROM 테이블명
GROUP BY 필드명 혹은 표현식
HAVING where조건

여기서 HAVING은 where 조건식과 동일한 형태의 조건식이 들어갈 수 있다.

그리고 중요한 것이 HAVING은 무조건 GROUP BY 뒤에 위치해야 한다.

이제 우리는 앞서 봤던 문제를 해결할 수 있게 되었다.

SELECT userid, sum(amount * price)
FROM buytb1
GROUP BY userid
HAVING sum(amount * price) > 1500;

카테고리 초보자도 준비하는 SQL 코딩 테스트에 가시면 해당 시리즈를 확인하실 수 있습니다.