( )( ) 아침…
내일배움캠프. 내일배움캠프면 내일 배워야 하는데 나는 왜 지금 이렇게 공부를 하고 있나, 그런 생각을 했다.
…
……아무래도 얼른 주말이 와야 할 것 같다.
오늘 한 일은,
- SQL 공부
- [코드카타] SQL 5문제 풀기 (69~73번)
- 파이썬 공부
- [코드카타] 어제 푼 문제들 복습 (51~53번)
- [코드타카] 알고리즘 1문제 풀기 (54번)
- [파이썬 종합반] 2주차 수강하기
- [개인 과제] 1~4번 풀기 *5번 시도 중
SQL 공부: [코드카타] SQL 문제 풀기(69~73번)
69. 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수(컬럼명: RECORDS) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 월을 기준으로 오름차순 정렬하고, 월이 같다면 자동차 ID를 기준으로 내림차순 정렬해주세요. 특정 월의 총 대여 횟수가 0인 경우에는 결과에서 제외해주세요.
SELECT MONTH(start_date) month,
car_id,
COUNT(start_date) records
FROM car_rental_company_rental_history
WHERE car_id IN (
SELECT car_id
FROM car_rental_company_rental_history
WHERE start_date BETWEEN '2022-08-01' AND '2022-10-31' --start_date 기준으로 8월부터 10월까지의 대여 기록 중에
GROUP BY car_id --car_id별로
HAVING COUNT(start_date) >= 5 --start_date를 셌을 때 5 이상인 것들만 (=대여 횟수가 5회 이상)
)
GROUP BY month, car_id
ORDER BY month, car_id DESC우선 문제에서 주어진 조건 중 '대여 시작일 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서'의 부분을 해결해주기로 한다. 해당 조건을 걸기 위해 WHERE절에 서브쿼리로 걸어서 시도했다.
사실 서브쿼리 내 SELECT절에 car_id뿐만 아니라 HAVING절에서 보이는 COUNT(start_date)도 추가했었는데, 그 상태로 WHERE절에 작성한 것처럼 조건을 걸으니 "Operand should contains 1 column(s)" 에러가 떠서 car_id만 남기고 날렸다.
그리고 월별, car_id별 조회하기 위해 SELECT절에 컬럼들을 설정해주고 대여 횟수를 알려주는 records 컬럼은 COUNT() 함수를 사용해 start_date를 세주었다. 그리고 그 연산을 수행하기 위해 GROUP BY절을 작성하고, 마지막으로 결과 테이블의 정렬 조건까지 ORDER BY절에 반영했다. 쿼리를 실행했더니 결과 테이블이 나오길래 제출했는데 틀렸다고...
혹시나 해서 슬랙 질문방에 해당 문제에 대한 질문은 없는지 찾아보니까 69번에 대한 질문만 5건 조회됐다. (역시 다들 어려웠던 거였어..)
첫 번째 게시글부터 확인하는데 바로 내 쿼리의 문제를 찾을 수 있었다. 즉, WHERE절의 서브쿼리 안에 WHERE절로 작성한 start_date 컬럼에 대한 조건이 그 바깥(메인 쿼리? 본 쿼리?)상위 쿼리에서도 적용되게끔 주어야 한다는 점이었다. 그러니까 WHERE절의 서브쿼리로 주어진 조건은 해당 서브쿼리 내에서만 한 번 사용하고 그치는 것이다.
SELECT MONTH(start_date) month,
car_id,
COUNT(start_date) records
FROM car_rental_company_rental_history
WHERE (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(start_date) >= 5
)) AND
(start_date BETWEEN '2022-08-01' AND '2022-10-31') --추가한 start_date의 범위에 관한 조건
GROUP BY month, car_id
ORDER BY month, car_id DESC위와 같이 수정해 제출하니 정답이란 메시지가 떴다.
그런데 예전에 다른 문제를 풀다가 의문을 해결하기 위해 질문방에서 본 튜터님 답변 중에 WHERE절의 서브쿼리 내 테이블과 상위 쿼리의 테이블을 어떻게 연결시켜서 SQL을 작성했던 걸 본 적 있는 것 같은데...
티스토리를 뒤져보니 2025년 2월 27일에 작성한 본캠프 9일차 TIL에 해당 답변을 찾아볼 수 있었다. 그래서 해당 쿼리를 참고하여, start_date의 범위에 관한 조건을 2번 쓰지 않고 한 번만 쓰기 위해서 쿼리를 수정해 시도해봤다. 근데 시도를 해도 잘 안 되길래 질문방에 관련 질문을 남겼다.
--쿼리②(상관 질의 방식을 시도함)
SELECT MONTH(start_date) month,
car_id,
COUNT(start_date) records
FROM car_rental_company_rental_history rh1
WHERE car_id IN (
SELECT car_id
FROM car_rental_company_rental_history rh2
WHERE (start_date BETWEEN '2022-08-01' AND '2022-10-31') AND
(rh1.car_id = rh2.car_id)
GROUP BY car_id
HAVING COUNT(start_date) >= 5
)
GROUP BY month, car_id
ORDER BY month, car_id DESC
즉 쿼리②처럼 상관 질의 방식을 시도한다고 해서 외부 쿼리의 start_date 제한 범위가 서브쿼리에 설정한 것이 연동되어 적용되는 것이 아니며, 외부 쿼리의 WHERE절에 ⑴car_id를 제한하는 조건, ⑵start_date를 제한하는 조건 2가지 모두가 적어야 한다는 점이었다.
(상관 질의는 그러면 언제 쓸 수 있는 것인가... 어렵다)
70. 그룹별 조건에 맞는 식당 목록
MEMBER_PROFILE와 REST_REVIEW 테이블에서 리뷰를 가장 많이 작성한 회원의 리뷰들을 조회하는 SQL문을 작성해주세요. 회원 이름, 리뷰 텍스트, 리뷰 작성일이 출력되도록 작성해주시고, 결과는 리뷰 작성일을 기준으로 오름차순, 리뷰 작성일이 같다면 리뷰 텍스트를 기준으로 오름차순 정렬해주세요.
이번 문제는 문제 푸는 데 시간이 많이 걸려서(…정말 많이……) 풀이 과정을 남겨놓으려고 한다.
--과정① 테이블 JOIN하기(필요한 컬럼들만 택하여)
SELECT
FROM (
SELECT member_id,
review_text,
review_date,
COUNT(review_id) OVER (PARTITION BY member_id) review_cnt --회원별 리뷰 작성 건수를 세야 했기 때문에 review_cnt 값을 구함
FROM rest_review
) r
INNER JOIN
(
SELECT member_id,
member_name
FROM member_profile
) m
ON r.member_id = m.member_id일단 문제에서 결과 테이블에 회원 이름과 리뷰 텍스트, 리뷰 작성일을 조회하라고 요구하는데 이름은 member_profile 테이블에, 리뷰 내용과 작성 일자는 rest_review 테이블에 있기 때문에 두 테이블을 JOIN했다.
이때 rest_review 테이블에서 회원별 리뷰 작성 건수를 집계하기 위해 윈도우 함수를 사용해서 review_cnt 값을 구해줬다. 'COUNT() 함수 사용 + GROUP BY절'을 사용하지 못한 것은 SELECT절에 GROUP BY절에 사용되지 않는 컬럼들(review_text, review_date)도 함께 조회되고 있기 때문에 윈도우 함수를 사용해서 리뷰 작성 건수를 구하게 됐다.
--과정② SELECT절 추가, WHERE절 조건 주기, ORDER BY절 작성
SELECT m.member_name,
r.review_text,
SUBSTR(r.review_date, 1, 10) review_date
FROM (
SELECT member_id,
review_text,
review_date,
COUNT(review_id) OVER (PARTITION BY member_id) review_cnt
FROM rest_review
) r
INNER JOIN
(
SELECT member_id,
member_name
FROM member_profile
) m
ON r.member_id = m.member_id
WHERE r.review_cnt = (
SELECT COUNT(review_id) OVER (PARTITION BY member_id) review_cnt
FROM rest_review
ORDER BY review_cnt DESC LIMIT 1
) -- 최다 리뷰 작성자에 한하는 데이터만 조회하기 위한 조건 (review_cnt 집계 > review_cnt 내림차순 정렬 > 그 상태에서 첫 행만 조회)
ORDER BY review_date, r.review_text최다 리뷰 작성자를 선택하기 위해 WHERE절 조건을 주는 것이 가장 까다로웠다. 왜냐하면 review_cnt를 구했기 때문에 최댓값을 구하면 되겠거니 싶었는데 WHERE절에는 MIN() / MAX() 함수도, 윈도우 함수도 사용할 수가 없었다.
그래서 여러 에러 메시지들을 만나고 택한 방식이 위의 쿼리에 적어놓은 방식이다. 풀어서 설명하면 WHERE절에 준 조건은 다음과 같다.
- 외부 쿼리의 review_cnt와 서브쿼리의 review_cnt가 일치하는 데이터만 조회한다.
- 서브쿼리 내 연산은 다음과 같다.
- rest_review 테이블에서 윈도우 함수(COUNT() OVER)를 사용해 review_cnt를 구한다.
- review_cnt를 내림차순으로 정렬한다.
- 그 상태에서 첫 행만 조회한다.
그밖에 SELECT절, ORDER BY절은 문제에서 요구하는 대로 작성했다.
71. 오프라인/온라인 판매 데이터 통합하기
ONLINE_SALE 테이블과 OFFLINE_SALE 테이블에서 2022년 3월의 오프라인/온라인 상품 판매 데이터의 판매 날짜, 상품ID, 유저ID, 판매량을 출력하는 SQL문을 작성해주세요. OFFLINE_SALE 테이블의 판매 데이터의 USER_ID 값은 NULL 로 표시해주세요. 결과는 판매일을 기준으로 오름차순 정렬해주시고 판매일이 같다면 상품 ID를 기준으로 오름차순, 상품ID까지 같다면 유저 ID를 기준으로 오름차순 정렬해주세요.
이번 문제는 주어진 두 테이블 간에 공통 컬럼이 없어 UNION/UNION ALL을 사용해 두 테이블을 합쳐주어야 한다.
단, UNION을 사용할 경우 주의할 점이 있다.
- 합치고자 하는 테이블들의 컬럼 개수와 컬럼 순서가 동일해야 함
- 또한 그 컬럼들의 이름 역시 일치해야 함
두 가지 조건들에 유의하며 UNION으로 다음과 같이 두 테이블을 합쳤다.
SELECT SUBSTR(sales_date, 1, 10) sales_date,
product_id,
user_id,
sales_amount
FROM online_sale
WHERE sales_date LIKE '2022-03%'
UNION
SELECT SUBSTR(sales_date, 1, 10) sales_date,
product_id,
NULL user_id, --오프라인 판매의 경우, user_id가 없으므로 NULL값이 들어간 컬럼을 하나 만듦
sales_amount
FROM offline_sale
WHERE sales_date LIKE '2022-03%'
ORDER BY sales_date, product_id, user_idonline_sale 테이블과 offline_sale 테이블을 UNION으로 합칠 때 offline_sale의 경우엔 user_id 컬럼이 없으므로 NULL값을 사용해 user_id 컬럼을 만들어서 두 테이블의 컬럼 개수와 순서를 일치시켰다.
73. 입양 시각 구하기⑵
보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.
SELECT HOUR(datetime) hour,
COUNT(DISTINCT animal_id) count
FROM animal_outs
GROUP BY hour
ORDER BY hour
처음에 작성한 쿼리는 datetime에 값이 존재하는 시간대만큼만(7~19시) 결과 테이블에서 조회됐다. 결과 테이블을 보고서 누군가 질문방에 이 문제에 대한 질문을 올렸을 거란 확신으로 질문방에 73번 문제를 검색했다.
…?
뭘까 이게…
난 WITH 구문도 그 존재만 알지 써 본 적도 없는데 RECURSIVE라는 이상한 것도 보이고.
…일단 WITH RECURSIVE 어쩌구가 무엇인지부터 알아봐야 문제를 풀 수 있을 것 같다.
[참고] [SQL] Recursive 표현으로 쿼리 만들기
- 파이썬의 for문, while문과 비슷한 쿼리
- 기준 요소(Anchor member)에 재귀적 요소(Recursive member)를 UNION ALL로 합침
- WHERE절에 중단 조건(Termination condition)을 작성함
- CTE(Common Table Expression) : 하나의 쿼리 범위 내에서만 존재하고 여러 번 참조할 수 있는 이름이 지정된 일회성 테이블
WITH RECURSIVE cte_hour AS ( --WITH RECURSIVE 구문으로 0~23시까지의 시간대를 담은 임시 테이블 cte_hour 만듦
SELECT 0 hour
UNION ALL
SELECT hour + 1
FROM cte_hour
WHERE hour < 23
)
SELECT c.hour,
IFNULL(ao.out_cnt, 0) out_cnt --out_cnt가 없으면 0으로 채워서 조회되도록 함
FROM cte_hour c
LEFT JOIN
(
SELECT HOUR(datetime) hour,
COUNT(DISTINCT animal_id) out_cnt
FROM animal_outs
GROUP BY hour
) ao
ON c.hour = ao.hour --cte_hour 테이블과 animal_outs 테이블을 시간대를 구한 컬럼 기준으로 합침위에서 대충 익힌 WITH RECURSIVE 구문으로 0시부터 23시까지 있는 cte_hour 테이블을 만들었다.
그리고 animal_outs 테이블도 시간대 컬럼(hour)과 시간대별 입양 건수(out_cnt) 컬럼을 조회하여서 cte_hour와 animal_outs 테이블을 시간대 컬럼 기준으로 LEFT JOIN으로 합쳤다.
그리고 최종적으로 테이블이 조회될 때 해당 시간대에 out_cnt가 없어도 NULL값이 아니라 숫자 0으로 대체해 나타내도록 IFNULL() 함수를 사용했다.
…
오늘 코드카타 SQL 문제 쉽지 않았다
이렇게 시간 많이 걸릴 줄은……
파이썬 공부①: [코드카타] 알고리즘 문제 풀기(54번)
54. 2016년
2016년 1월 1일은 금요일입니다. 두 수 a, b를 입력받아 2016년 a월 b일이 무슨 요일인지 반환하는 함수, solution을 완성하세요. 요일의 이름은 일요일부터 토요일까지 각각 SUN,MON,TUE,WED,THU,FRI,SAT입니다. 예를 들어 a=5, b=24라면 5월 24일은 화요일이므로 문자열 "TUE"를 반환하세요.
def solution(a, b):
days = ['THU', 'FRI', 'SAT', 'SUN', 'MON', 'TUE', 'WED']
months = [31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]
answer = days[(sum(months[:a-1]) + b) % 7]
return answer우선 요일의 이름을 담은 리스트 days를 만든다. days를 만들 때 요일의 이름을 담은 순서는 인덱스를 고려하여 담았다. '2016월 1월 1일은 금요일'이라는 의미는 1을 7로 나눴을 때 나머지는 1이고 그렇다면 days[1] = 'FRI'가 나와야 한다.
그리고 각 달의 일수를 담은 리스트 months도 만들었다.
이 리스트들을 이용하여,
- a월이면 month에서 이전 달까지의 일수를 모두 더하고 sum(months[:a-1])
- 거기에 b일까지 더하여서 sum(months[:a-1]) + b
- 그 모든 일수들을 7로 나눈 나머지를 구해서 (sum(months[:a-1]) + b) % 7
- days 리스트의 인덱스로 사용한다 days[(sum(months[:a-1]) + b) % 7]
파이썬 공부②: [데이터 분석 파이썬 종합반] 2주차 수강하기
리스트(List)
리스트와 자주 사용하는 메소드
- list.append(n) : list의 마지막에 n을 추가함
- list.extend(list_2) : list의 마지막에 list_2의 모든 항목들을 추가함
- list.insert(i, n) : list의 인덱스 i 위치에 n을 추가함
- list.remove(n) : list 안에 있는 n이라는 값을 list에서 제거함
- list 안에 n이라는 값이 2개 이상 있을 경우 가장 첫 번째로 조회된 값 하나만 삭제함
- list.pop(i) : list[i]의 값을 list에서 제거함
- i 생략 시엔 해당 리스트의 가장 끝 값을 삭제함
- list.index(n) : list에서 n이라는 값의 인덱스를 찾아줌
- list.index(n, x)나 list.index(n, x, y) : n이라는 값을 찾을 범위를 지정할 수도 있음 (list[x]부터 list[y]까지)
- list.index(n)은 n이 가장 처음으로 나타난 인덱스를 알려줌
- list.count(n) : list에서 n이라는 값이 몇 개 있는지 알려줌
- list.sort() : list의 값들을 오름차순으로 정렬함
- list.sort(reverse=True)로 작성하면 list의 값들을 내림차순으로 정렬함
- list에 담긴 값들이 숫자 아니라 문자열이어도 정렬 가능 (알파벳 순)
- list.reverse() : list에 값들이 담겨있는 순서를 역순으로 뒤집음
튜플(Tuple)
- 변경할 수 없는(immutable) 시퀀스(sequence) 자료형
- 리스트와 유사하지만, 한 번 생성된 이후에 요소를 추가, 삭제, 수정할 수 없음
- 파이썬에서 데이터를 보호하고자 할 때 사용함
- 소괄호 ( )를 사용하여 생성하며, 각 요소는 쉼표로 구분함
튜플 → 리스트로, 리스트 → 튜플로 변경
- 튜플 → 리스트 : list() 함수를 사용함 list = list(tuple)
- 리스트 → 튜플 : tuple() 함수를 사용함 tuple = tuple(list)
딕셔너리(Dictionary)
- 키-값 쌍의 데이터를 저장하는 자료 구조
- 중괄호 { }를 사용해 생성하며, 각 요소는 쉼표로 구분함
- 각 키는 유일해야 하지만 값은 중복될 수 있음
딕셔너리와 자주 사용하는 메소드
- dict.keys() : dict의 모든 키를 dict_keys 객체로 반환함
- dict.values() : 모든 값을 dict_values 객체로 반환함
- dict.items() : 모든 키-값 쌍을 (키, 값) 튜플로 구성된 dict_items 객체로 반환함
2주차 Quiz
파이썬 공부③: 파이썬 개인 과제 풀기
난이도 : BASIC
#문제1. 숫자 리스트의 평균 구하기
def calculate_avg(numbers):
total_avg = sum(numbers) / len(numbers)
return total_avg#문제2. 일일 최고 기온과 일일 최저 기온을 활용해 일교차 구하기
def calculate_diff_temperature(numbers):
diff_temp = max(numbers) - min(numbers)
return diff_temp
문제 1, 2번은 어려울 것 없이 금방 풀었다.
난이도 : INTERMEDIATE
문제3
해당 문제는 판매 데이터가 '제품명 : 판매 수량' 한 쌍의 형태로 딕셔너리에 들어 있을 때, 판매 수량이 가장 높은 제품의 이름과 판매 수량을 출력하는 함수를 만들어야 했다.
#문제3. 가장 많이 판매된 제품의 이름과 수량을 조회하기
def find_top_seller(sales_data):
max_sales = max(sales_data.values()) #max_sales는 sales_date의 value값 중에서 최댓값을 구함
top_product = []
for i, j in sales_data.items(): #top_product의 경우에 max_sales에 해당하는 제품이 둘 이상일 수 있다고 생각함
if max_sales == j:
top_product.append(i) #그래서 top_product를 빈 리스트로 두고 value값이 max_sales과 일치하는 key를 추가함
return top_product, max_sales
원래 테스트 데이터로 주어진 sales_data엔 lemon에 대한 값이 없다. 그렇지만 문제를 보면서 '판매 수량이 max_sales와 같은 제품이 둘 이상이면 어떻게 해야 하지?' 싶은 의문이 들었다.
주어진 문제에 대한 설명에 sales_data에 대한 제한 조건이 있는지 살폈으나 그런 내용은 없었다. max_sales에 해당하는 제품은 1개밖에 없다든지, 아니면 출력되는 top_product 값은 어떠해야 하는지 등. 그래서 위와 같이 코드를 작성해 문제를 풀었다.
문제4
#문제4. 사칙연산 프로그램 구현
def simple_calculator(num1, num2, operator):
if operator == '+':
return num1 + num2
elif operator == '-':
return num1 - num2
elif operator == '*':
return num1 * num2
elif operator == '/':
if num2 == 0:
return 'Cannot divide by zero'
else:
return num1 / num2
문제에서 요구하는 방식으로 잘 푼 것인진 모르겠지만 if문으로 각 operator가 입력되는 것에 따라 해당 연산을 수행해 반환하도록 작성했다. 단, 나눗셈의 경우 나누는 숫자가 0일 경우 문제에 제시된 대로 'Cannot divide by zero'라는 메시지가 뜨드록 하는 내용을 추가해줬다.
문제5
#문제5. 이메일 주소 유효성 프로그램
def validate_emails(email_list):
for i in range(len(email_list)):
if '@' in email_list[i]:
id, domain = email_list[i].split('@')
if len(id) > 0:
if '.' in domain:
return f'{email_list[i]}는 유효한 이메일 주소입니다.'
else:
f'{email_list[i]}는 유효하지 않은 이메일 주소입니다.'
else:
return f'{email_list[i]}는 유효하지 않은 이메일 주소입니다.'
else:
return f'{email_list[i]}는 유효하지 않은 이메일 주소입니다.'
처음 작성한 코드는 위와 같은데 어떤 이유에선지 email_list 내에 있는 모든 이메일 주소들에 대한 내용이 출력되지 않고 리스트 속 첫 번째 이메일 주소에 대한 판단만 반환됐다.
(일단 시간이 늦어서 자러 갔다. 내일 풀어야지)
'[내배캠] 데이터분석 6기 > 본캠프 기록' 카테고리의 다른 글
| [본캠프 17일차] SQL 코드카타, 파이썬 코드카타, 파이썬 공부 (0) | 2025.03.12 |
|---|---|
| [본캠프 15일차] 아티클 스터디③, SQL 코드카타, 파이썬 코드카타, 파이썬 공부 (0) | 2025.03.10 |
| [본캠프 14일차] SQL 코드카타, 파이썬 코드카타, QCC ①, 데이터 리터러시 공부 (0) | 2025.03.07 |
| [본캠프 13일차] 파이썬 코드카타, SQL 코드카타, 데이터 리터러시 공부, 아티클 스터디② (0) | 2025.03.06 |
| [본캠프 12일차] ADsP 공부, 데이터 리터러시 공부, 파이썬 코드타카, SQL 코드카타 (0) | 2025.03.05 |