서브 쿼리와 WITH, SUBSTRING, 조건문(CASE)

들어가며

4주차에서는 Sub Query와 실전에서 유용한 SQL 문법에 대해서 공부하였는데, 약 2일 정도 손에 익을 때까지 계속 연습한 후에 개발일지로 정리하였다.

Sub Query

서브 쿼리는 말 그대로 보조역할을 하는 쿼리로, 정확히는 쿼리 안에 있는 하위 쿼리라는 의미이다. 이는 하위 쿼리의 결과를 상위 쿼리에서 활용하기 위해 사용되며, WHERE, SELECT, FROM에서 사용된다.

WHERE에서의 서브 쿼리

서브 쿼리는WHERE ~ IN ()을 통해 WHERE절에서 사용할 수 있다. WHERE ~ IN ()이 무엇인지 알기 위해 간단한 예를 들어보도록 하자. 먼저, WHRER절로 users 테이블에서 특정 아이디에 해당하는 정보만 불러오면 다음과 같다.

SELECT * FROM users
WHERE user_id = '특정 아이디';

그렇다면, 특정 아이디가 여러 개인 경우에는 어떻게 해야할까? 이때 사용하는 것이 바로 WHERE ~ IN ()인데, 이를 통해 아래 쿼리문과 같이 WHERE { field } in ('target')으로 유효성 검사를 수행할 수 있다.

SELECT * FROM users
WHERE user_id in ("3b3eac9f", "afb35ce0");

이제 WHERE절에서 서브 쿼리를 사용해보자. 예를 들어, 'kakaopay'로 결재한 유저의 아이디, 이름, 이메일을 INNER JOIN으로 조회하면 다음과 같다.

SELECT u.user_id, u.name, u.email FROM users u
INNER JOIN orders o 
    ON u.user_id = o.user_id 
WHERE o.payment_method = "kakaopay";

서브 쿼리를 통해 위의 쿼리문을 통해 얻은 결과와 같은 결과를 출력할 수 있다. 먼저, 아래와 같이 유저의 아이디, 이름, 이메일을 조회하는 쿼리문과 'kakaopay'로 결제한 유저의 아이디를 조회하는 쿼리문을 각각 따로 작성해보자. 여기서 첫 번째 쿼리문을 상위 쿼리, 두 번째 쿼리를 하위 쿼리라고 한다.

-- 유저의 아이디, 이름, 이메일 조회
SELECT user_id, name, email FROM users;

-- 'kakaopay'로 결재한 유저의 아이디 조회
SELECT users_id FROM orders
WHERE payment_method = 'kakaopay';

이어서 서로 다른 두 개의 쿼리문으로 얻은 결과를 합쳐주어야 하며, 아래 쿼리문과 같이 상위 쿼리에 WHERE ~ IN (하위 쿼리)를 입력한다.

SELECT user_id, name, email FROM users
WHERE user_id in 
    (
        SELECT user_id FROM orders
        WHERE payment_method = 'kakaopay' 
    );

즉, 상위 쿼리에서 찾고자 하는 user_id가 하위 쿼리에서 조회한 'kakaopay'를 사용한 user_id에 포함되어 있는지 확인하고, 조건에 맞는 결과만 출력한다.

SELECT에서의 서브 쿼리

GROUP BY를 사용하여 checkins 테이블에서 user_id별 좋아요 수의 평균값을 구하는 쿼리문을 아래와 같이 작성할 수 있다.

SELECT user_id, ROUND(AVG(likes), 2) FROM checkins
GROUP BY user_id;

이번에는 user_id가 '4b8a10e6'인 유저의 좋아요 수의 평균값을 구해보면 다음과 같다.

SELECT ROUND(AVG(likes), 2) FROM checkins
WHERE user_id = '4b8a10e6';

이제 checkins 테이블에서 체크인 아이디, 유저 아이디, 좋아요 수, 좋아요 수의 평균값을 얻어야 하는데, 어떻게 할 수 있을까? WHERE에서의 서브 쿼리를 어느정도 이해한 경우 어떻게 쿼리문을 작성해야 하는지 감이 잡혔을 것이라고 생각한다. 아래 쿼리문을 살펴보자.

SELECT 
    c.checkin_id,
    c.user_id,
    c.likes,
    (
        SELECT ROUND(AVG(likes), 2) FROM checkins
        WHERE user_id = c.user_id
    ) as likes_avg
    FROM checkins c;

다소 복잡해보일 수 있지만, 그 원리는 WHERE에서의 서브 쿼리와 동일하다. 즉, 상위 쿼리에서 얻어온 user_id별 좋아요 수의 평균값을 하위 쿼리에서 구하고, 이를 상위 쿼리에서 나타낸 것이다.

FROM에서의 서브 쿼리

서브 쿼리는 FROM에서 가장 많이 사용된다. 먼저, GROUP BY를 사용하여 유저별 포인트 점수와 좋아요 수의 평균값을 구하는 쿼리문을 아래와 같이 작성할 수 있다.

SELECT pu.user_id, pu.point, ROUND(AVG(c.likes), 2) 
    FROM point_users pu 
INNER JOIN checkins c
    ON pu.user_id = c.user_id
GROUP BY user_id;

위에서 얻은 결과를 서브 쿼리로 나타내면 아래 쿼리문과 같다.

SELECT pu.user_id, pu.point, c.likes_avg FROM point_users pu
INNER JOIN 
    (
        SELECT user_id, ROUND(AVG(likes), 2) as likes_avg 
        FROM checkins
        GROUP BY user_id
    ) as c 
    ON pu.user_id = c.user_id;

이번에도 다소 복잡해보이지만, point_users 테이블과 서브 쿼리의 결과를 user_id를 기준으로 합쳐주는 것으로, 그 원리는 매우 간단하다.

실습

❓ 포인트가 전체 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기

SELECT * FROM point_users pu
WHERE pu.point > 
    (
        SELECT AVG(point) FROM point_users
    );

WHERE에서 서브 쿼리를 작성하여 전체 유저의 포인트의 평균보다 포인트가 큰 유저의 데이터를 가져오도록 조건을 걸면 쉽게 해결할 수 있다.

❓ 이씨 성을 가진 유저의 포인트의 평균보다 큰 유저들의 데이터 추출하기

SELECT * FROM point_users pu
WHERE pu.point > 
    (
        SELECT AVG(point) FROM point_users
        WHERE user_id IN 
        (
            SELECT user_id FROM users
            WHERE name LIKE '이%'
        )
    );

'서브 쿼리 안에 또 다른 서브 쿼리가 들어갈 수 있지 않을까?'라는 생각으로 이 문제에 접근하여 문제를 해결하였다. 물론, 강의에서 알려준 쿼리문과는 차이가 있지만 이 또한 정답이 될 수 있기에 남겨두었다. 강의에서는 아래 쿼리문과 같이 서브 쿼리에서 INNER JOIN을 통해 point_users 테이블과 users 테이블을 결합하여 결과를 출력하였다.

SELECT * FROM point_users pu1
WHERE pu1.point > 
    (
        SELECT AVG(pu2.point) FROM point_users pu2
        INNER JOIN users u 
            ON pu2.user_id = u.user_id
        WHERE u.name LIKE '이%'
    );

❓ checkins 테이블에 course_id 별 평균 좋아요 수를 필드 우측에 결합하기

SELECT c1.checkin_id, 
       c1.course_id, 
       user_id, 
       likes, 
       (
           SELECT ROUND(AVG(likes), 1) FROM checkins c2
           WHERE c2.course_id = c1.course_id
       ) as likes_avg 
    FROM checkins c1;

❓ checkins 테이블에 과목명별 평균 좋아요 수를 필드 우측에 결합하기

SELECT c1.checkin_id,
       c3.title,
       c1.user_id,
       c1.likes,
       (
           SELECT ROUND(AVG(likes), 1) FROM checkins c2
           WHERE c1.course_id = c2.course_id
       ) as course_avg
 FROM checkins c1
 INNER JOIN courses c3
     ON c1.course_id = c3.course_id

❓ course_id별 체크인 수와 course_id별 수강중인 유저의 수 구하기

SELECT c.course_id, 
       c.cnt_checkins, 
       o.cnt_total 
    FROM 
    (
        SELECT course_id,
               COUNT(DISTINCT(user_id)) as cnt_checkins 
            FROM checkins
        GROUP BY course_id
    ) as c
INNER JOIN 
    (
        SELECT course_id,
               COUNT(DISTINCT(user_id)) as cnt_total
               FROM orders
           GROUP BY course_id
    ) as o
ON c.course_id = o.course_id;

강의자료의 풀이 설명에는 course_id별 like 개수와 course_id별 인원수로 되어 있는데, course_id별 checkins 개수와 course_id별 인원수를 잘못 기록한 것으로 보인다.

WITH

서브 쿼리를 더욱 간단하게 사용할 수 있도록 해주는 WITH에 대해서 알아보자. WITH은 서브 쿼리로 얻은 테이블에 별칭을 붙여주는 기능으로, 일종의 Alias라고 생각하면 이해하기 쉽다. 예를 들어, 실습한 문제들 중에서 가장 마지막 문제를 살펴보자. 해당 쿼리문에는 2개의 서브 쿼리를 사용하였는데, WITH을 사용하여 나타내면 아래 쿼리문과 같이 더욱 간단하게 서브 쿼리의 결과를 사용할 수 있다.

WITH 
    tbl_checkins as
        (
            SELECT course_id,
                   COUNT(DISTINCT(user_id)) as cnt_checkins 
                FROM checkins
            GROUP BY course_id
        ),
    tbl_orders as
        (
            SELECT course_id,
                   COUNT(DISTINCT(user_id)) as cnt_total
                   FROM orders
               GROUP BY course_id
        )
SELECT c.course_id, 
       c.cnt_checkins, 
       o.cnt_total
    FROM tbl_checkins as c
INNER JOIN tbl_orders as o
    ON c.course_id = o.course_id;

실전에서 유용한 SQL 문법

이제부터는 실전에서 유용한 SQL 문법에 대해서 정리해보았다.

SUBSTRING_INDEX

SUBSTRING_INDEX는 문자열을 특정 문자를 기준으로 나누고, 나누어진 문자열에서 원하는 개수만큼 출력할 수 있는 함수이다. 이는 python의 split과 비슷하나, 마지막 인자에 인덱스 번호가 아닌 가져올 문자열의 수를 입력한다는 점에 차이가 있다. SUBSTRING_INDEX을 사용하는 방법은 다음과 같다.

SUBSTRING_INDEX("문자열", "문자열을 나누는 기준이 되는 문자", 가져올 문자열의 수);

예를 들어, 유저의 이메일 정보를 이메일 계정과 도메인으로 나누려면 아래 쿼리문과 같이 나타낼 수 있다.

SELECT SUBSTRING_INDEX(email, '@', 1) as account,
       SUBSTRING_INDEX(email, '@', -1) as domain
    FROM users;

SUBSTRING

SUBSTRING은 문자열을 인덱스로 나누어 출력할 수 있는 함수이며, python의 문자열 슬라이싱(slicing)과 같은 개념이다. 단, 여기서 인덱스는 python이나 Javascript와 같이 zero-based-index(0부터 시작)가 아니므로, 헷갈리지 않도록 유의하여야 한다. SUBSTRING을 사용하는 방법은 다음과 같으며, 문자열의 길이를 생략하면 시작위치로부터 문자열의 끝까지 출력된다.

SUBSTRING("문자열", 시작위치);

-- 또는 

SUBSTRING("문자열", 시작위치, 문자열의 길이);

예를 들어, 유저의 가입일시 데이터를 날짜와 시간으로 나누어 출력하려면 아래 쿼리문과 같이 나타낼 수 있다.

SELECT SUBSTRING(created_at, 1, 10) as created_date,
       SUBSTRING(created_at, 12) as created_time
   FROM users;

인덱스	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19
문자	2	0	2	1	-	0	2	-	1	3		1	5	:	0	0	:	0	0

이를 활용하여, 아래 쿼리문과 같이 일별로 몇 개씩 주문이 들어왔는지 확인할 수 있다.

SELECT SUBSTRING(created_at, 1, 10) as date, 
       COUNT(*) as cnt 
   FROM orders
GROUP BY date;

CASE

CASE는 SQL에서 조건문을 사용할 수 있도록 해주는 문법이다. 예를 들어, 포인트가 10,000보다 크면 '상', 5,000보다 크면 '중', 그 외에는 '하'라는 등급을 매기려면 아래 쿼리문과 같이 작성할 수 있다.

SELECT user_id, point,
       (
               CASE 
                   WHEN point > 10000 THEN '상'
                   WHEN point > 5000 THEN '중'
                   ELSE '하'
               END 
       ) as level
   FROM point_users;

실습

❓ 포인트가 평균 이상이면 '잘 하고 있어요', 평균보다 낮으면 '열심히 합시다!'를 출력하기

SELECT pu.user_id, pu.point,
       (
               CASE 
                   WHEN pu.point > 
                       (
                        SELECT AVG(point) as point_avg 
                            FROM point_users
                       )
                   THEN "잘 하고 있어요"
                   ELSE "열심히 합시다!"
               END
       ) as msg
    FROM point_users pu;

❓ 이메일 도메인별 유저의 수 파악하기

SELECT SUBSTRING_INDEX(email, '@', -1) as domain, 
       COUNT(*) 
    FROM users
GROUP BY domain;

단순히 GROUP BY만을 사용하면 위와 같이 작성할 수도 있지만, 서브 쿼리를 사용하면 아래와 같이 작성할 수도 있다.

WITH
    domains as
        (
            SELECT SUBSTRING_INDEX(email, '@', -1) as domain
                FROM users
        )
SELECT domain, 
       COUNT(*) as cnt
    FROM domains
GROUP BY domain;

❓ '화이팅'이 포함된 오늘의 다짐만 출력하기

SELECT comment FROM checkins
WHERE comment LIKE '%화이팅%';

강의에서 내준 문제인데, 위에서 배운 문법을 적용하려고 할 수록 지식의 늪에 빠질 수 있는 문제이다. 단순히 문제에서 요구하는 결과만을 생각한다면 1주차에 배운 LIKE 만으로 해결할 수 있다. 실무에서도 마찬가지로 어떠한 문제가 발생했을 때, 복잡하게 생각할수록 정답과는 멀어질 수 있음을 명심하자.

❓ 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의 수 출력하기

WITH 
    totals as 
        (
            SELECT enrolled_id, COUNT(*) as total_cnt 
                FROM enrolleds_detail
            GROUP BY enrolled_id
        ),
    dones as 
        (
            SELECT enrolled_id, COUNT(*) as done_cnt 
                FROM enrolleds_detail
            WHERE done = 1
            GROUP BY enrolled_id
        )
SELECT t.enrolled_id, 
       d.done_cnt,
       t.total_cnt
    FROM totals t
INNER JOIN dones d 
    ON t.enrolled_id = d.enrolled_id;

❓ 수강등록정보(enrolled_id)별 전체 강의 수와 들은 강의 수, 진도율 출력하기

WITH 
    totals as 
        (
            SELECT enrolled_id, COUNT(*) as total_cnt 
                FROM enrolleds_detail
            GROUP BY enrolled_id
        ),
    dones as 
        (
            SELECT enrolled_id, COUNT(*) as done_cnt 
                FROM enrolleds_detail
            WHERE done = 1
            GROUP BY enrolled_id
        )
SELECT t.enrolled_id, 
       d.done_cnt,
       t.total_cnt,
       ROUND(d.done_cnt / t.total_cnt, 2) as ratio
    FROM totals t
INNER JOIN dones d 
    ON t.enrolled_id = d.enrolled_id;

수강 완료 상태(=done)는 0과 1로 구분되는데, 이는 enrolled_Id 별로 done을 합산한 결과와 같기 때문에 아래와 같이 더욱 간단한 쿼리문으로 작성할 수도 있다.

SELECT enrolled_id, 
       SUM(done) as done_cnt, 
       COUNT(*) as total_cnt,
       ROUND(d.done_cnt / t.total_cnt, 2) as ratio
   FROM enrolleds_detail
GROUP BY enrolled_id;

마치며

약 1주일 동안 SQL 문법을 꽤 효율적으로 공부한 것 같다. 물론, 자유자재로 SQL을 다루기 위해서는 더 많은 내용을 공부해야겠지만, 스파르타코딩클럽에서 제공해주는 내용만으로도 어느정도 활용이 가능할 것이라고 생각한다. 나중에 따로 시간을 내어 1주차부터 4주차 동안에 배운 SQL 문법 복습하면서, 내용을 보기 좋게 따로 정리하겠다.