아니 이거 풀옵션인데 진짜

내가 사랑하는 나의 노란 아이맥은 나와 일주일에 영상을 5개씩 만들 때도 나에게 렉을 선물하지 않았다. 풀옵션을 뽑았으니 가격 생각하면 그럴만도 한데, 생각해보면 정말 안 해본 작업이 업을 정도로 다양한 일을 나와 한 이 친구가, 처음으로 업무 처리가 느려진 것을 보았을 때, 내가 하고 있는 작업이 얼마나 Big Deal인지 이해해버렸다.

오늘은 27일은 SQL 문제를 총 9개를 풀었으며, 발표 녹화 영상을 제작했으며, 세션 복습을 진행했다. 이 내용들을 한 번 알아보자.

파이썬은 언제하냐...

모든 과제는 프로그래머스로 진행했으므로, 해당 코드들을 보자.

SELECT DR_NAME, DR_ID, MCDP_CD, 
       date_format(date(HIRE_YMD), '%Y-%m-%d') HIRE_YMD
FROM DOCTOR
WHERE MCDP_CD IN ('GS', 'CS')
ORDER BY HIRE_YMD DESC, DR_NAME;

흉부외과 의사와 일반외과 의사를 뽑아내는 문제였다. 쿼리 작성은 전혀 어려운 것이 없었다. date_format 함수를 처음 써보면서 조금 헤매긴 했다. 문제는 ORDER BY에 기준을 2개 줄 수 있다는 걸 처음 알아서 계속 오답이 나왔다. 적어도 이제는 틀릴 일이 없을 것 같다.

SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE
FROM
(
SELECT PRODUCT_ID, PRODUCT_NAME, PRODUCT_CD, CATEGORY, PRICE,
       RANK() over(order by PRICE DESC) pr
FROM FOOD_PRODUCT
)a
WHERE pr = 1

가장 비싼 상품을 뽑아내는 쿼리이다. 이 쿼리의 핵심은 인라인 뷰 서브쿼리를 이쁘게 적는 것도 있지만, 거기에 1등을 뽑기 위한 RANK 함수 사용이 아닐까 싶다. 순위를 정해준 다음에 외부 쿼리에서 where로 순위 조건을 걸어줘서 답을 맞췄는데, 이럴때는 어거지로 답을 뽑아낸 것 같아 마음이 불편하다. 뭔가 더 좋은 방법이 있지 않았을까? 싶어서 좀 조사해보련다.

SELECT COUNT(user_id) as USERS
FROM
(
SELECT user_id, gender, age, date_format(date(JOINED),'%Y-%m-%d') JOINED_R
FROM USER_INFO
WHERE date_format(date(JOINED),'%Y-%m-%d') BETWEEN '2021-01-01' AND '2021-12-31'
)a
WHERE age BETWEEN 20 and 29

조건에 맞는 회원의 수를 구하는 쿼리이다. 개인적으로 date, date_format 함수의 사용법을 빠르게 익혀가려고 노력 중이다. 보통 SQL로 다루는 자료가 인적 데이터이고, 거기에 날짜는 기본적으로 사용되기 때문이다. 이걸 배우면서 억지로 substring 함수를 쓰지 않아도 되니 마음이 편하다. 또한 인라인 뷰 서브쿼리를 작성하는데 점점 익숙해지고 있다.

SELECT ANIMAL_ID, NAME,
       CASE WHEN SEX_UPON_INTAKE LIKE ('%intact%') THEN 'X'
            ELSE 'O' END CUT
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

중성화 여부를 체크하는 쿼리이다. 이것도 코드를 우겨서 답을 뽑아낸 느낌이었다. 하지만 내 생각엔 이 방식이 가장 쉬운 방법이라고 생각한다.

SELECT SUBSTR(PRODUCT_CODE,1,2) CATEGORY, COUNT(PRODUCT_ID) PRODUCT
FROM PRODUCT
GROUP BY CATEGORY

오히려 substr을 사용하는게 이제는 쉽게 느껴진다. 아무리 복잡한 내용도 단순화시켜서 필요한 내용만 남기면 내가 맘대로 쓸 수 있으니까. 복잡한 내용이 컬럼을 가득 채워도 겁낼 필요가 없다.

SELECT ANIMAL_TYPE, COUNT(ANIMAL_ID) count
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE ASC

할 말이 필요없이 너무 쉬운 문제였다.

다음 문제들은 최근 배운 join을 활용한 문제들이었다. 소현 선생님께서 테이블 결합에 대해 정말 좋은 설명을 해주셨다고 생각한다. 정말 어려운 개념이었는데 문제가 풀리면서 성취감을 느낄 수 있었다.

SELECT NAME, DATETIME
FROM 
    (
        SELECT I.NAME,
               I.DATETIME,
               DENSE_RANK() OVER(ORDER BY I.DATETIME) AS RNK
        FROM ANIMAL_INS I 
        left join ANIMAL_OUTS O 
        on I.ANIMAL_ID=O.ANIMAL_ID
        WHERE O.DATETIME is NULL
        ORDER BY I.DATETIME
    )a
WHERE RNK <= 3

오랜 기간 보호한 동물을 찾는 쿼리이다. 주어진 테이블은 보호소로 인입된 동물 테이블 INS, 그리고 보호소에서 입양간 동물 테이블 OUTS 2개였다. 따라서 보호소에 들어온 날짜를 확인해야하며, OUTS에서 입양을 가지 않은 동물들을 따로 뽑아야 했는데, 이때 입양 보낸 날짜가 NULL인 친구들을 찾아서 구할 수 있었다.

그리고 오래된 순서로 3등을 뽑으라고 해서, 또 다시 RANK 함수를 사용했다. 서브쿼리 안에서 순위를 매긴 다음, 외부 쿼리에서 해당 순위를 3이하로 조건을 걸어 가장 오래된 동물 3마리를 뽑는 쿼리를 완성했다.

RANK를 이렇게 쓰는게 맞을까 싶으면서도, 그거 아니면 어떻게 풀겠나 싶기도 하다.

SELECT WRITER_ID, NICKNAME, TOTAL_SALES 
FROM
(
        SELECT B.WRITER_ID,
               U.NICKNAME,
               SUM(PRICE) TOTAL_SALES
        FROM USED_GOODS_BOARD B 
            INNER JOIN USED_GOODS_USER U
            ON B.WRITER_ID = U.USER_ID
        GROUP BY WRITER_ID
)a
WHERE TOTAL_SALES >= 700000
ORDER BY TOTAL_SALES ASC

조건에 맞는 구매자와 총 거래금액을 찾는 쿼리다. 인라인 뷰 서브쿼리에서 기준이 되는 두 컬럼의 이름은 다르지만 값이 동일한 경우이다. 따라서 이 점을 파악하고 들어가면 결합이 쉬워진다. 결합을 통해 자체 테이블을 만들어주고, 여기에 총 70만 원 이상 금액이라는 조건을 걸어주면 깔끔하게 정답이 나온다.

SELECT ANIMAL_ID, ANIMAL_TYPE, NAME
FROM 
(
    SELECT O.ANIMAL_ID,
           O.ANIMAL_TYPE,
           O.NAME,
           O.SEX_UPON_OUTCOME,
           I.SEX_UPON_INTAKE
    FROM ANIMAL_OUTS O 
    INNER JOIN ANIMAL_INS I
    ON O.ANIMAL_ID = I.ANIMAL_ID
    WHERE I.SEX_UPON_INTAKE LIKE ('%intact%')
) a
WHERE SEX_UPON_OUTCOME NOT LIKE ('%intact%')

가장 성취감을 줬던 문제였다. 보호소에서 중성화를 한 동물을 찾는 쿼리를 짜야 했기 때문에, 보호소에 들어왔을 때 중성화 상태, 그리고 나갈 때 중성화 상태를 확인할 수 있어야 했다.

여기에서 중요한 것 하나, 기준 테이블을 OUTS로 잡아야 한다는 것이다. 우린 보호소에 들어왔다가 입양을 가지 못한 친구들은 집계하면 안되기 때문이다. 따라서 outs를 기준으로 inner join을 걸면, 보호소에 들어와서 입양을 간 친구들의 자료만을 볼 수 있게 된다.

여기에서 인라인 뷰 서브 쿼리에선 중성화가 되지 않은 상태로 보호소에 들어온 친구들을 찾아주고, 외부 쿼리에선 그 친구들 중에 중성화가 된 상태로 입양을 간 친구들을 찾아주면 된다. 중성화 문제는 아까도 풀었지만, LIKE 함수를 사용하는게 편하다.

처음부터 끝까지 어떻게 풀어야할지 스스로 근거를 대면서 문제를 풀 수 있는 건 즐거운 일이다. 그 즐거움을 요즘 다시금 찾고 있다.

JOIN과 UNION

조인과 유니온은 각각 수평결합과 수직결합을 의미한다.

조인은 수평결합을 하기 위해 기준이 되는 테이블과 붙이는 테이블 사이에 공통 기준이 되는 컬럼이 필요하다. 내 쿼리 풀이에서 언급했듯, 쿼리에 담긴 데이터만 동일하면 되므로, 이름이 굳이 같을 필요는 없다. 막상 붙이면 이게 뭔가 싶겠지만, SELECT에서 필요한 컬럼을 각 테이블에서 가져오면 멋진 쿼리가 작성된다.

INNER과 LEFT의 차이도 신경써야 한다. INNER JOIN은 기준이 되는 테이블과 붙이는 테이블의 겹치는 데이터만 붙인다. 동일한 데이터를 서로 갖고있는 것이 조건이므로, 그외에 붙이는 테이블이 가진 데이터는 가져오지 않는다. 반면 LEFT 조인은 통째로 다 갖다 붙인다. 만약 대응하는 데이터가 없을 경우는 NULL을 써서라도 붙인다.

UNION은 수직 결합이다. 수평결합이 동일한 개체에 대한, 서로 다른 테이블에 저장된 정보를 한번에 보기 위한 결합이라면, 수직 결합은 시기에 따라 정리된 동일한 카테고리의 데이터들을 합치는데 사용한다고 생각하면 편하다.

따라서 컬럼 명과 컬럼의 수가 완전히 동일할 때 결합이 이루어진다. 그 조건에만 부합하면 1개가 아니라 4개도 붙일 수 있다. 다만 내가 직접 5개의 테이블을 결합했을 때, 쿼리를 불러오는 시간이 너무 걸리더라.

이렇게만 정리해도 결합에 대해서는 충분히 힘을 쓸 수 있을 것 같고, 실제로 문제에서도 문제없이 사용할 수 있었다.

밤샘 발표 준비

오랜만에 밤을 새워가며 준비했다. 오후에 팀원들이 만든 발표 자료를 바탕으로 멘토들께 제출할 발표 녹화본을 만들었다. 열심히 제작하여 제출이 잘 되었다고 한다. 다행이다. 지금 이걸 쓰면서도 좀 정신이 없다.

다음은?

아티클과 rank, date 함수를 정리해볼 예정이다. 화이팅!