12/22 WIL

1. SQL 카타

65. 우유와 요거트가 담긴 장바구니

with, union all 활용

정답

with temp as                  <----- 4) 3)의 임시테이블 생성하고 temp라 이름 붙이기
(select distinct(cart_id)     <----- 1)'yogurt'만 있는 'cart_id' 출력
from cart_products
where name = 'yogurt'
union all                     <----- 3)milk만 있는 cart_id와 yogurt만 있는 cart_id 합치기(distinct 중요!)
select distinct(cart_id)      <----- 2)'milk'만 있는 'cart_id' 출력
from cart_products 
where name = 'milk')

select cart_id                
from temp
group by cart_id              <----- 5)cart_id로 그룹화
having count(*)>=2            <----- 6)cart_id 두 번 이상 나온것들만 출력(distinct가 중요한 이유)

코드블럭에 작성했듯 distinct가 중요한 이유는 milk든 yogurt든 테이블에서 이를 가진 cart_id가 중복 출력되고, 'having count(*)>2'부분에서 이들 중복된 값이 그대로 나올 수 있기 때문이다. 즉, milk만 두 번 담아서 두 번 조회된 cart_id가 출력될 수 있으니 distinct로 한 번만 출력되게 해야한다는 것이다.

 

with와 union all은 아직 익숙하지 않다보니 문제 구조가 생각나지 않았을 순 있지만, distinct를 간과한 것은 반성해야할 부분인듯ㅠ

 

덧붙여 전에 작성했지만 간단하게 짚고 넘어가면,

with : 가상의 테이블을 임시로 생성하는 함수. 

union all : 두 테이블을 합치는 함수

 

참고 : 다양한 방식의 풀이들

https://school.programmers.co.kr/questions/52835

프로그래머스

 

73. 입양 시각 구하기(2) -2

1)변수 선언 및 서브쿼리

2)재귀함수

 

ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다.

보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.

with recursive st as             
    (select 0 hour            <-----hour의 초기값 0으로 설정(처음에 비반복문 최소 1개 이상 사용)
    union all                     <--------재귀함숫 사용시 반드시 union 사용!
    select hour +1 from st       <-------hour가 23이 될 때까지 반복
     where hour <23
    )
select st.hour,
       count(animal_id) count
from st left outer join animal_outs on hour(datetime) = st.hour
group by st.hour
order by st.hour

 

WITH RECUSRSIVE 재귀함수

(1) 구조

WITH RECURSIVE 가상테이블 이름 AS
(
	초기 쿼리
    
    	UNION ALL

	반복할 쿼리 *반복 중단할 WHERE 포함
)

핵심 정리

메모리에 가상의 테이블을 저장

반드시 UNION 사용

반드시 비반복문(Non-Recursive)도 시작할 때 한 개 필수

가상 테이블을 참조하는 문장(반복문)이 바깥에 반드시 필요

 

---

 

2. 팀플(파이썬)

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
plt.rc('font', family='NanumBarunGothic')

#한글 깨짐 방지! 실행하고서 런타인 세션 재시작
!sudo apt-get install -y fonts-nanum
!sudo fc-cache -fv
!rm ~/.cache/matplotlib -rf


#1. 불러오기 및 기본 가공 : 2~5 피봇테이블 및 히트맵 만드는 데 필수!
#1)불러오기
ycn = pd.read_csv('/content/illegal_parking.csv')

#2)단속일시 시간 칼럼 및 요일, 시간 칼럼 만들기
format = '%Y-%m-%dT%H-%M-%S.%f'
ycn['단속일시자료'] = pd.to_datetime(ycn['단속일시'], format=format, infer_datetime_format=True)
ycn['단속요일'] = ycn['단속일시자료'].dt.day_name()
ycn['단속시간'] = ycn['단속일시자료'].dt.hour

#3)요일 자료 정렬해줄 weeks 미리 형성
weeks = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']



#3.교육기관
#1)교육기관 여부 칼럼 만들기
ycn['교육기관'] = np.where(ycn['단속장소'].str.contains(r'(여고|학교|어린이집|유치원|초교|학원)'), '1', "0")
#<------------'단속장소'에서 '여고, 학교, 어린이집, 유치원, 초교, 학원' 중 하나라도 언급되는 케이스는 1로 표시, 그렇지 않으면 0으로 표시되는 "교육기관"칼럼 생성

#2)교육기관에 해당하는 행들만 뽑아서 새 테이블 만들고 인덱스 리셋
ycn_schools = ycn.query('교육기관 == "1"')   #<-------'교육기관' 칼럼이 1인 케이스(행)들만으로 테이블 생성
ycn_schools.reset_index()

#3)교육기관 피봇테이블 만들기
schools_pivot = pd.pivot_table(ycn_schools, values = '연번',
                               index = ['단속요일'],
                               columns= ['단속시간'],
                               aggfunc="count").agg(weeks)

#4)교육기관 히트맵 만들기 matplotlib.pyplot.pcolor 사용
plt.figure(figsize = (14, 5))
plt.pcolor(schools_pivot)
plt.xticks(np.arange(0, len(schools_pivot.columns), 1), schools_pivot.columns)
plt.yticks(np.arange(0, len(schools_pivot.index), 1), schools_pivot.index)
plt.title('교육기관 요일시간 교차 히트맵')
plt.xlabel('시간')
plt.ylabel('요일')
plt.colorbar()
plt.show()


#5)교육기관 히트맵 만들기 : seaborn.heatmap 사용
plt.figure(figsize=(12, 5))

sns.heatmap(data = schools_pivot,
            annot=True,
            vmin=0,
            vmax=250,
            fmt='g',  #<------수치가 %가 아닐 경우 'g'를 입력 안 하면 출력 오류 발생
            cmap='BuGn')
plt.title('교육기관 요일시간별 건수')
plt.xlabel('단속시간')
plt.ylabel('단속요일')
plt.yticks(rotation = 60)
plt.show()

 

 

 

 

---

3. 팀 프로젝트

 

1)첫 팀 프로젝트라 잘 할 수 있을 지도, 그리고 다른 팀들의 수준이 어느 정도일지도 가늠이 안 가서 여러모로 걱정했는데, 모든 면에서 최고의 팀플이었다! 팀원들 간 티키타카가 맞아서 자료 조사부터 정리, 시각화, ppt만들기, 그리고 발표까지 완벽했고, 튜터님도 칭찬만 하셨으니 무슨 말이 더 필요하려나 낄낄

2)팀플 끝난 김에 스몰토크. 이래저래 백그라운드든, 취향이든 공통분모가 겹치는 부분이 많아서 신기했고, 그래서 팀플이 더 순조롭게 잘 진행되지 않았나 싶다. 거진 매니저님이 작정하시고 팀을 짜주신게 아닌가 싶을 정도였다.

3)오프라인으로든 온라인으로든 팀 회식 했으면 좋겠다!

4)그래도 나름 사회과학으로 대학원까지 다니긴 했다고...비교적 데이터 분석을 어떻게 해야하는지 감이 빨리 잡히는 듯??학부 때부터 주구장창 하던 레포트, 논문 쓰기 방식을 그대로 적용하니 확실히 쉽고 재밌었다! 이대로도 쭉 재밌게 할 수 있길. 그리고 추천해준 친구에게 무한 감사를..