대략 2주 만에 SQL에 대한 모든 내용을 끝마쳤다. 학습하고 있을 때는 미처 몰랐는데, 튜터님의 말씀을 듣고 생각해보니 과연. 어마무시한 학습량이 아닌가 싶다.
맨처음 SQL 쿼리를 실행한 게 진짜 엊그제(?)인데 말이다. 짧은 시간에 SQL의 다양한 함수들과 문법들을 익히는 것이 쉽지는 않았지만, 그래도 벌써 전부를 해치웠다는 게 실감이 나지 않는다. 복습을 꾸준히 해줘야 하지만, 처음에 비하면 벌써 열 걸음은 나아간 듯하다. 이제 본격적으로 파이써넹 대해서 배울텐데, 파이썬도 마찬가지로 열심히 익혀서 내 것으로 만들 것이다.
-
오늘 학습 키워드
파이썬의 자료구조 이해하기, SQL join문 실습 및 window function 확인하기 -
오늘 학습한 내용을 나만의 언어로 정리하기
파이썬에서 주로 사용하는 자료구조의 개념 이해하기, SQL join문 확실히 이해하고 응용하기
-
학습 내용
파이썬의 자료구조는 몇 가지 종류가 있다. 그 중에서 자주 쓰이는 세 가지가 리스트, 튜플, 딕셔너리이다.리스트는 코드를 작성할 때 여러 데이터값에 순서를 부여해서 저장할 수 있도록 만든다. 저장된 값은 순서 변경이나 내용 삭제, 또는 추가도 가능하다. 대괄호 [] 를 사용해서 값을 저장한다.
예시는 다음과 같다.numbers = [1, 2, 3, 4, 5]
튜플은 리스트와 비슷하게 여러 데이터값에 순서를 부여해서 저장한다. 하지만 튜플로 저장된 값은 순서 변경이나 내용 삭제 등의 변경이 불가능하다. 주로 변해선 안 되는 데이터를 보호하기 위해 사용된다. 소괄호 () 를 사용해서 값을 저장한다.
예시는 다음과 같다.numbers = (1, 2, 3, 4, 5)
딕셔너리는 키(key)와 값(value)라는 두 요소를 한 쌍으로 묶어서 가지는 자료형이다. 데이터의 정체성을 보다 확실하게 부여하기 위해 사용된다. 중괄호 {} 로 선언하며, 키를 통해 데이터값에 접근한다.
예시는 다음과 같다.number = {
"First": 1,
"Second": 2,
"Third": 3
}각각의 요소들은 서로 섞어서 사용할 때 진가를 발휘한다고 본다. 각 자료형을 개별적으로 사용하는 것도 충분히 활용 가능하다. 하지만 이들을 적절히 합성해서 사용할 경우, 더욱 정확한 코드를 작성하는 것이 가능해진다. 아직은 개념을 이해하기에도 벅차지만, 파이썬에 익숙해지면 자연스럽게 활용하게 될 것이라고 생각한다.
SQL은 지난 번에 이어서 join문을 계속해서 풀어 봤다. 오늘은 3번 문제까지만 풀어보았다.
1번 문제는 다음과 같다.
아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일 순으로 조회해야 합니다.
해당 문제에서는 두 가지 테이블을 활용하게 된다. 하나는 동물 보호소에 들어온 동물들의 정보를 담은 테이블이고, 다른 하나는 동물 보호소에서 입양 보낸 정보를 담은 테이블이다. 이 두 테이블을 left join해서 조건을 만족하는 데이터를 추출하는 문제이다.
이 문제의 쿼리를 작성하면 다음과 같다.
SELECT
ins.name,
ins.datetime
FROM
animal_ins AS ins
LEFT JOIN animal_outs AS outs
ON
ins.animal_id = outs.animal_id
WHERE
outs.datetime IS NULL
ORDER BY
ins.datetime
LIMIT 3
;코드의 실행값은 다음과 같다.
위의 사진처럼 세 마리의 동물이 출력되는 것을 확인할 수 있다.
2번 문제는 다음과 같다.
USED_GOODS_BOARD와 USED_GOODS_USER 테이블에서 완료된 중고 거래의 총금액이 70만 원 이상인 사람의 회원 ID, 닉네임, 총거래금액을 조회하는 SQL문을 작성해주세요. 결과는 총거래금액을 기준으로 오름차순 정렬해주세요.
해당 문제에서는 "중고 상품의 정보를 담은 테이블"과 "중고 상품을 구매한 사람의 정보를 담은 테이블"을 활용해서 join문을 작성하는 문제이다. 해당 문제에 대한 쿼리는 다음과 같다.
SELECT
uu.user_id,
uu.nickname,
sum(ub.price) AS total_sales
FROM
used_goods_board AS ub
LEFT JOIN used_goods_user AS uu
ON
ub.writer_id = uu.user_id
WHERE
ub.status = 'done'
GROUP BY
uu.user_id,
uu.nickname
HAVING
sum(ub.price)>= 700000
ORDER BY
total_sales
;주의할 점은 "완료된 중고 거래의 총금액이 70만 원 이상"이라는 조건이다. 해당 조건이 where절에 들어갈 경우, sum 함수로 먼저 계산되지 않고 where절의 필터링이 먼저 작동한다. 이렇게 되면 요류가 발생해서 쿼리가 동작하지 않게 된다. 따라서 해당 조건을 having절에 넣어야 한다. 쿼리를 실행하면 다음과 같이 출력된다.
총 구매 금액 70만 원 이상의 사람들이 출력된 것을 확인할 수 있다.
다음으로 3번 문제이다.
보호소에서 중성화 수술을 거친 동물 정보를 알아보려 합니다. 보호소에 들어올 당시에는 중성화되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물의 아이디와 생물 종, 이름을 조회하는 아이디 순으로 조회하는 SQL 문을 작성해주세요.
*중성화를 거치지 않은 동물은 성별 및 중성화 여부에 Intact, 중성화를 거친 동물은 Spayed 또는 Neutered라고 표시되어있습니다.
이 문제는 앞서 풀었던 1번 문제와 동일한 테이블들을 이용하고 있다. 그러나 1번 문제와는 다르게 inner join을 이용해서 풀어야 한다. 보호소에 들어 온 기록과 보호소에서 나가는 기록이 모두 존재해야 하기 때문이다. 이를 적용해서 쿼리를 작성하면 다음과 같이 작성할 수 있다.
SELECT
ai.animal_id,
ai.animal_type,
ai.name
FROM
animal_ins AS ai
INNER JOIN animal_outs AS ao
ON
ai.animal_id = ao.animal_id
WHERE
(ai.sex_upon_intake LIKE 'intact%')
AND ((ao.sex_upon_outcome LIKE 'Spayed%')
OR (ao.sex_upon_outcome LIKE 'Neutered%'))
ORDER BY
1
;해당 쿼리문에서 where절에
AND ((ao.sex_upon_outcome LIKE 'Spayed%') OR (ao.sex_upon_outcome LIKE 'Neutered%')) 대신
AND O.SEX_UPON_OUTCOME NOT LIKE 'INTACT%'로 작성해도 동일한 결과를 얻을 수 있다. 서로 문장의 길이가 다를 뿐 같은 의미를 가지고 있기 때문이다. 위 쿼리를 실행하면 다음과 같은 결과를 얻게 된다.
위와 같이 총 두 마리의 정보를 얻을 수 있다.
마지막으로 4번 문제는 다음과 같다.
아래 조건을 만족하여, 알맞은 조인방식으로 users 테이블과 payment 테이블을 조인해주세요.
- case when 구문을 사용하여 결제를 한 게임계정과 결제를 하지 않은 게임계정을 구분해주시고, 컬럼이름을 gb로 지정해주세요.
- gb를 기준으로 게임계정수를 중복값없이 추출해주세요. 컬럼 이름은 usercnt로 지정해주세요.
*조건1) payment 테이블에 게임계정이 있으면 결제한 고객으로 생각해주세요.
힌트: 기준이 되는 테이블의 데이터는 그대로 두어야겠죠?
이 문제에서 주의해야 할 점은 count를 적용할 컬럼을 잘 선정해야 한다는 점이다. 각각의 두 테이블에는 동일한 이름을 가진 컬럼이 있다. 만족해야 하는 조건 중 "결제를 한 게임계정과 결제를 하지 않은 게임계정을 구분"해야 한다는 구문에 적용시켜야 한다.
해당 문제의 쿼리는 다음과 같이 작성된다.
SELECT
CASE
WHEN b.game_account_id IS NULL THEN '결제 안 함'
ELSE '결제함'
END AS 'gb',
count(DISTINCT a.game_account_id) AS usercnt
FROM
basic.users AS a
LEFT JOIN basic.payment AS b
ON
a.game_account_id = b.game_account_id
GROUP BY
1
;해당 쿼리의 실행값은 다음의 사진과 같다.
쿼리문 중 "count(DISTINCT a.game_account_id)" 부분에 a.game_account_id 가 아니라 b.game_account_id 를 작성해서 넣을 경우, 조건에 부합하는 결과를 얻을 수 없게 된다. 우리가 추출해야 하는 데이터는 두 테이블을 합친 모든 데이터에서 가져와야 하기 때문이다.
b.game_account_id 에는 a.game_account_id 와 일치하지 않는 빈 공간이 존재한다. 따라서
count(DISTINCT b.game_account_id) 를 할 경우 "전체 데이터"가 아니라 "전체 데이터의 일부분"만을 가져오게 된다. 즉 "결제를 한 게임계정"만을 가져오게 되는 것이다.
일단 오늘은 4번 문제까지 풀고 넘어가게 되었다. 남은 두 문제가 많이 어렵기 때문에 시간이 많이 들 것이라고 예상하고 있다. 오늘 모든 문제를 풀다가는 밤을 새게 될 것이 불 보듯 뻔하다... 오늘 푹 쉬고, 에너지를 충전해서 내일 다시 문제 풀이에 들어가면 될 듯 하다.
-
학습하며 느낀 점
SQL은 같은 조건을 가지고 쿼리를 작성해도 서로 다른 접근법으로 작성하게 될 가능성이 높은 것 같다. 위의 문제들을 푼 사람들과 쿼리문을 비교해보면, 모두가 비슷하지만 다른 형태로 작성했다. 결국 SQL 쿼리 작성에 정답은 없다는 뜻이다. 하지만 답은 같더라고 처리 과정에서의 비용 차이가 발생하게 된다. 정확하지만 더 효율적인 방법을 찾는 것이 중요한 것 같다.파이썬은 이제 두 번째 대면이라 아직도 많이 서먹하다. SQL처럼 파이썬도 꾸준하게 자주, 많이 접하면서 빨리 익숙해져야겠다.
마치며 : SQL 학습 내용은 종료되었지만, 앞으로도 꾸준하게 SQL 쿼리를 작성하면서 감을 잃지 않도록 해야 한다. 앞으로 약 2주 동안 진행될 파이썬도 마찬가지로 꾸준하게 연습해야 한다. 근성으로 꾸준하게 이어가자.
