241218 TIL 데이터 분석과정 - SQL 코드카타(저자 별 카테고리 별 매출액 집계하기, 그룹별 조건에 맞는 식당 목록 출력하기, 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기, 오프라인/온라인 판매 데이터 통합하기)

241218 TIL 

 

1. SQL 코드카타 정리

❤️문제1. 저자 별 카테고리 별 매출액 집계하기 

총 세 개의 테이블이 주어졌고, 특정 기간의 카테고리 별 매출액, 저자 ID, 저자명, 카테고리, 매출액을 구해주어야 한다. 

 

BOOK 테이블 

BOOK 테이블 

 

AUTHOR 테이블 

 

 

BOOK_SALES 테이블

 

 

✅문제풀이 

 

#AUTHOR_ID, AUTHOR_NAME, CATEGORY, SALES출력 - 2022년 1월만 추출해야 함. 
# 매출액 구하는 식만들기
#GROUP BY로 2022년 1월을 묶어준다. 

 

2022년 1월 매출액만 불러오기 위해서 GROUP BY로 먼저 그룹화해 준 후 HAVING절로 2022년 1월 매출값을 가져왔더니 동작하지 않았다. 

 

그 이유는 HAVING절은 집계 함수의 결과나, GROUP BY로 그룹화된 값에 조건을 적용할 때만 사용하는데

SALES_DATE는 BOOK_SALES 테이블의 개별 행 데이터로, 집계 함수(SUM, COUNT 등)로 계산되거나 GROUP BY로 묶이지도 않았기 때문에 작동하지 않는다. 

따라서 WHERE 절로 먼저 2022-01 항목을 추출해주어야 한다.

 

SELECT B.AUTHOR_ID, 
       A.AUTHOR_NAME,
       B.CATEGORY, 
       SUM(B.PRICE * S.SALES) AS TOTAL_SALES 
FROM BOOK AS B 
     JOIN AUTHOR AS A ON B.AUTHOR_ID = A.AUTHOR_ID
     JOIN BOOK_SALES AS S ON B.BOOK_ID = S.BOOK_ID 
WHERE SALES_DATE LIKE '2022-01%'  # HAVING 절은 집계 함수의 결과나, GROUP BY로 그룹화된 값에 조건을 적용할 때 사용됩니다.
GROUP BY AUTHOR_ID, CATEGORY 
ORDER BY AUTHOR_ID ASC, CATEGORY DESC;

 

 

💟 복습할 개념

 

1. SQL의 실행 순서

FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

 

이번 문제에서 이 실행 순서를 떠올렸다면, 헷갈리지 않았을 것 같다. 

 

 

2. 조건절의 정리

• case when(경우의 수가 3가지 이상)
• on(두 테이블의 공통된 키)
• where(from 이나 join다음에 실행) 
• having(집계함수)
• if(조건, 조건이 맞을 경우, 조건이 틀릴 경우)

그동안 배운 순서대로 머릿속에 산재되어 있던 조건절에 대한 정보를 정리했다. 조건절 별로 카테고리화를 하니 어떤 경우에 사용해야 하는지 눈에 더 잘 들어온다. 

 

 

3. WHERE와 HAVING의 차이 

WHERE

- 개별 행을 기준으로 그룹화 이전의 데이터만을 필터링한다. 

HAVING

- GROUP BY된 값이나, 집계 함수의 결과에 조건을 걸어줄 때 필요함. 

 

GROUP BY

- 특정 열을 기준으로 그룹화를 시켜줄 때 사용하고, 마지막 열은 집계함수를 적용. 

- 그룹화 하고자 하는 열은 반드시 SELECT절에 들어있는 값이어야 한다. 

- SELECT절에는 GROUP BY에 포함된 기준 열과 집계 함수만 사용이 가능하며, 집계 함수를 제외한 다른 컬럼들은 모두 GROUP BY에 포함되어야 한다. 

 

---

 

❤️문제2. 그룹별 조건에 맞는 식당 목록 출력하기

 

✅문제풀이

 

# 두 개의 서로 다른 테이블이 제시되었으므로, JOIN으로 두 테이블을 고유값인 MEMBER_ID키로 연결해준다. 

 

#조건절: 가장 많은 리뷰를 작성한 회원의 리뷰들을 조회 

회원을 조회하는 고유값인 MEMBER_ID에서 가장 많은 리뷰를 작성한 회원을 찾는 별도의 서브쿼리를 걸어준다. 

 

#출력할 값: MEMBER_NAME, REVIEW_TEXT, REVIEW_DATE

REVIEW_DATE는 현재 시간까지 같이 출력되기 때문에, 날짜만 나오도록 DATE_FORMAT 함수로 형태를 변경한다. 

 

#마지막으로 REVIEW_DATE, REVIEW_TEXT 기준으로 정렬해주어야 한다. 

 

조건절에서 서브쿼리를 걸어서, 가장 많은 리뷰를 작성한 회원을 찾는 것이 핵심인 문제.

SELECT MP.MEMBER_NAME, 
       R.REVIEW_TEXT, 
       DATE_FORMAT(R.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM MEMBER_PROFILE AS MP 
           JOIN REST_REVIEW AS R ON MP.MEMBER_ID = R.MEMBER_ID
WHERE MP.MEMBER_ID = (SELECT MEMBER_ID
                   FROM REST_REVIEW
                   GROUP BY MEMBER_ID
                   ORDER BY COUNT(MEMBER_ID) DESC LIMIT 1 
                   )
ORDER BY REVIEW_DATE, REVIEW_TEXT

 

 

💟내가 놓쳤거나 복습할 개념

 

1. 서브쿼리

 (1) 서브쿼리를 사용할 수 있는 위치:

SELECT, FROM, WHERE, HAVING, JOIN, ORDER BY, 기타 DML(INSERT, DELETE, UPDATE)절

단, GROUP BY절에는 사용이 불가하다.

 

• 서브쿼리는 외부 쿼리와 상관없이 독립적으로 실행될 수도 있고, 외부 데이터를 참조하여 실행될 수도 있음
• 서브쿼리는 외부 쿼리보다 먼저 실행되며, 반환된 결과는 외부 쿼리에서 사용됨.

 

(2) 서브쿼리의 종류

 

1-1 SELECT 절에서 사용

SELECT절의 서브쿼리는 단일값만을 반환하고, 외부 쿼리의 결과에 새로운 파생변수 생성

 

1-2 FROM 절에서 사용

복잡한 데이터 집합을 가상 테이블로 구조화하여 외부 쿼리에서 활용할 때

 

1-3 WHERE 절에서 사용

비교 연산자(>, <, =, !=, >=, <=) 나 논리 연산자(ANY, ALL, IN, EXISTS) 와 함께 특정 조건을 필터링 

 

 

2 -1 상관 서브쿼리: 내부 쿼리가 외부 쿼리의 데이터를 참조하며, 외부 쿼리의 각 행에 대해 반복적으로 실행된다. 

2- 2 비상관 서브쿼리: 서브쿼리가 외부 쿼리와는 독립적으로 1번만 실행되고, 그 결과는 외부 쿼리에서 사용된다. 

---

❤️문제3. 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

1)2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상이고, 2)해당 기간 동안에 월별 자동차 ID 별 총 대여 횟수를 출력해야 한다. 

 

 

✅문제풀이

#조건절

서브쿼리절에서 8월 ~ 10월 사이에 대여한 사람들 중 5회 이상 빌린사람들을 구하는 조건을 만들어준다. 그리고 외부 쿼리에서 그 사람들이 몇 번을 대여했는지를 조회한다.

 

서브쿼리를 다는 부분이 까다로웠던 문제이다. 

SELECT MONTH(START_DATE) AS MONTH,
       CAR_ID,
       COUNT(*) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE MONTH(START_DATE) BETWEEN 8 AND 10     #8월에서 10월 사이의 대여, 그 중에서 5회 이상 대여한 사람들 
            AND (CAR_ID IN (SELECT CAR_ID
                 FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                 WHERE START_DATE BETWEEN '2022-08-01' AND '2022-10-31'
                 GROUP BY CAR_ID
                 HAVING COUNT(CAR_ID) >= 5))
GROUP BY MONTH, CAR_ID
ORDER BY MONTH ASC, CAR_ID DESC;

---

 

❤️문제4. 오프라인/온라인 판매 데이터 통합하기

 

이번 문제는 ONLINE_SALE과 OFFLINE_SALE 두 개의 테이블이 주어졌다. 

2022년 3월의 오프라인/온라인 상품 판매 데이터의 판매 날짜, 상품ID, 유저ID, 판매량을 출력해야 한다. 

 

✅풀이방법

#서로 다른 테이블이 나와서 당연히 JOIN을 하는 문제라고 생각했지만, UNION ALL을 이용해서 두 테이블을 위 아래로 붙여주어야 한다. 두 테이블이 가지고 있는 컬럼이 모두 동일하기 때문에 UNION이 가능하다. 

# 중복된 값을 삭제하라는 내용은 없기 때문에, UNION ALL을 사용하면 된다. 

 

#오프라인 판매는 USER_ID는 NULL로 처리하라고 써있기 때문에, SELECT절에서 NULL처리 해주었다. 

SELECT DATE_FORMAT(SALES_DATE,'%Y-%m-%d') as SALES_DATE,
       PRODUCT_ID,
       USER_ID,
       SALES_AMOUNT
FROM ONLINE_SALE
WHERE SALES_DATE >= '2022-03-01' AND SALES_DATE < '2022-04-01'

UNION ALL 

SELECT DATE_FORMAT(SALES_DATE,"%Y-%m-%d") AS SALES_DATE,
       PRODUCT_ID,
       NULL AS USER_ID,
       SALES_AMOUNT
FROM OFFLINE_SALE 
WHERE SALES_DATE >= '2022-03-01' and SALES_DATE < '2022-04-01'
    
ORDER BY SALES_DATE , PRODUCT_ID , USER_ID

 

💟 내가 놓친 개념

 

1. UNION 

여러 SELECT 문의 결과를 수직으로 결합하면서 중복된 행은 제거함. 열 수와 데이터 형식이 동일해야 함.  

 

2. UNION ALL 

여러 SELECT문을 수직으로 결합하지만 중복된 행은 제거하지 않음. 

 

 

---

2. 판다스 정리 - 1회차🎯

 

이 외에 오늘 판다스의 개념에 대해서 다시 한 번 정리해보는 시간을 가졌다. 아직 판다스 설치 및 데이터 입출력까지 밖에 정리하지 못했는데, 차근차근 정리해보려고 한다.  

 

데이터 전처리를 위한 - 판다스(Pandas) 기초

 

---

3. 회고🎯

일간 목표

• 데이터 전처리 3주차 강의 
• 데이터 전처리 3주차 실습 진행
• SQL 코드카타 71번
• 판다스 기초 이론 정리하기 

주간 목표

• 데이터 시각화, 전처리 완강
• 파이썬 코드카타 1레벨
• SQL 코드카타 5, 6레벨 → SQL 능숙하게 쓸 수 있도록 연습
• 파이썬 반복학습하기

 

오늘 하루는 판다스 실습을 집중적으로 한 날이었다. 4주차 강의 수가 많지 않아서 이어서 들을 수 있었지만, 실습을 하면서 듣지 않으면 아무 의미가 없어서 오늘은 3주차 강의만 천천히 이해하며 수강했다. 

 

Visual Studio에 판다스를 설치하는 과정만 무사히 넘어가니, 파이썬에 비해서는 크게 어려운 문법이 많이 없어서 재미있게 하고 있다. 

특히 판다스 이해에 오늘 진행되었던 라이브 세션을 들은 것이 가장 많은 도움이 되었다. 

튜터님께서 어렵지 않게 설명해주셔서, 온라인 강의로만 들었을 때 머릿 속에서 산재되어 있던 개념들이 하나씩 연결고리를 만들어 가고 있는 듯하다. 

그리고 SQL은 매일 코드카타를 적은 양이라도 진행하면서 감도 유지하고 내가 헷갈려 했던 개념들을 다시 한 번 정리해나가며 다듬고 있다. 서브쿼리와 윈도우 함수를 빨리 넘어가서 적용이 잘 안되기 때문이다. 

아직 파이썬은 이제 조금씩 친해지려고 하고 있는데, 시간이 꽤 걸릴 것 같다는 생각이 든다. 기본 개념은 다 듣기는 했지만, 적용을 하는 부분에 어려움이 있는데 어느 부분을 질문해야 할 지 아직도 감이 오지 않는다. 

 

21일간의 다이어트 프로그램을 마치고 5kg를 감량했다. 아무래도 집에 12시간동안 있다보니 움직임이 많지 않아서 밀가루, 흰 밥, 간식을 끊고 쉐이크만 먹는 게 가능했던 것 같다. 

어제부터 크로스핏도 다시 시작해서 오늘은 몸도 가볍고 컨디션도 좋은 편이었다. 그동안 몸에 쌓여있던 지방이 많이 빠져나간 기분이다. 이제 사회로 다시 나가기 전 몸을 본격적으로 만들어보아야 겠다. 

몸도 정신도 건강히, 지식도 건강히 쌓는 기간이 될 수 있도록!