SQL 풀이

Attention

테이블 생성 및 데이터 로드는 파이썬 기반으로 동작합니다.
적절한 쿼리를 입력하여 데이터 전처리를 진행합니다

코드 가이드

Attention

1. connectDB 함수를 통해 url csv를 로컬에 sqlite3 형식의 데이터 베이스로 바꿉니다.

2. 쿼리를 str형식으로 작성합니다(DB table 이름은 주어집니다)

3. pd.read_sql({쿼리},con={테이블}) 형식으로 쿼리를 날려 결과를 출력합니다.

ex) table = connectDB(‘https://raw.githubusercontent.com/Datamanim/pandas/main/lol.csv’,’lol’,’\t’)

url, table_name, sep

sql =’select * from lol’ # query Ans = pd.read_sql(sql,con=table) # result

import pandas as pd
def connectDB(url,table_name,sep=',',encoding='utf-8'):
    import sqlite3
    cnx = sqlite3.connect(':memory:')
    df = pd.read_csv(url,sep=sep,encoding=encoding)
    df.to_sql(name=table_name, con=cnx)    
    return cnx

Question 1

전체 데이터를 로드하라.

기본코드

table = connectDB(‘https://raw.githubusercontent.com/Datamanim/pandas/main/lol.csv’,’lol’,’\t’) sql =’’ # table_name = lol Ans = pd.read_sql(sql,con=table)

table = connectDB('https://raw.githubusercontent.com/Datamanim/pandas/main/lol.csv','lol','\t')
sql ='select * from lol'
Ans = pd.read_sql(sql,con=table)
display(Ans)

	index	gameId	creationTime	gameDuration	seasonId	winner	firstBlood	firstTower	firstInhibitor	firstBaron	...	t2_towerKills	t2_inhibitorKills	t2_baronKills	t2_dragonKills	t2_riftHeraldKills	t2_ban1	t2_ban2	t2_ban3	t2_ban4	t2_ban5
0	0	3326086514	1504279457970	1949	9	1	2	1	1	1	...	5	0	0	1	1	114	67	43	16	51
1	1	3229566029	1497848803862	1851	9	1	1	1	1	0	...	2	0	0	0	0	11	67	238	51	420
2	2	3327363504	1504360103310	1493	9	1	2	1	1	1	...	2	0	0	1	0	157	238	121	57	28
3	3	3326856598	1504348503996	1758	9	1	1	1	1	1	...	0	0	0	0	0	164	18	141	40	51
4	4	3330080762	1504554410899	2094	9	1	2	1	1	1	...	3	0	0	1	0	86	11	201	122	18
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
51485	51485	3308904636	1503076540231	1944	9	2	1	2	2	0	...	10	2	0	4	0	55	-1	90	238	157
51486	51486	3215685759	1496957179355	3304	9	2	1	1	2	2	...	11	7	4	4	1	157	55	119	154	105
51487	51487	3322765040	1504029863961	2156	9	2	2	2	2	0	...	10	2	0	2	0	113	122	53	11	157
51488	51488	3256675373	1499562036246	1475	9	2	2	2	2	0	...	11	3	0	1	0	154	39	51	90	114
51489	51489	3317333020	1503612754059	1445	9	1	1	1	1	1	...	1	0	0	1	0	11	157	141	31	18

51490 rows × 62 columns

Question 2

데이터의 상위 5개 행을 출력하라

기본코드

table = connectDB(‘https://raw.githubusercontent.com/Datamanim/pandas/main/lol.csv’,’lol’,’\t’)

1번에서 실행했다면 추가 실행 할 필요없음

sql =’’ # table_name = lol Ans = pd.read_sql(sql,con=table)

sql ='select * from lol LIMIT 5' 
Ans = pd.read_sql(sql,con=table)
Ans

	index	gameId	creationTime	gameDuration	seasonId	winner	firstBlood	firstTower	firstInhibitor	firstBaron	...	t2_towerKills	t2_inhibitorKills	t2_baronKills	t2_dragonKills	t2_riftHeraldKills	t2_ban1	t2_ban2	t2_ban3	t2_ban4	t2_ban5
0	0	3326086514	1504279457970	1949	9	1	2	1	1	1	...	5	0	0	1	1	114	67	43	16	51
1	1	3229566029	1497848803862	1851	9	1	1	1	1	0	...	2	0	0	0	0	11	67	238	51	420
2	2	3327363504	1504360103310	1493	9	1	2	1	1	1	...	2	0	0	1	0	157	238	121	57	28
3	3	3326856598	1504348503996	1758	9	1	1	1	1	1	...	0	0	0	0	0	164	18	141	40	51
4	4	3330080762	1504554410899	2094	9	1	2	1	1	1	...	3	0	0	1	0	86	11	201	122	18

5 rows × 62 columns

Question 3

데이터의 행의 갯수를 파악하라

sql ="select count(*) as rowcount from lol;" 
Ans = pd.read_sql(sql,con=table)
Ans

	rowcount
0	51490

Question 4

전체 컬럼을 출력하라(sqlite만의 문법)

sql ="select name from pragma_table_info('lol');" 
Ans = pd.read_sql(sql,con=table)
Ans

	name
0	index
1	gameId
2	creationTime
3	gameDuration
4	seasonId
...	...
57	t2_ban1
58	t2_ban2
59	t2_ban3
60	t2_ban4
61	t2_ban5

62 rows × 1 columns

Question 5

6번째 컬럼명을 출력하라

sql ="select name from pragma_table_info('lol') LIMIT 1 OFFSET 5;" 
Ans = pd.read_sql(sql,con=table)
Ans

	name
0	winner

Question 6

6번째 컬럼의 데이터 타입을 확인하라

sql ="select type from pragma_table_info('lol') LIMIT 1 OFFSET 5;" 
Ans = pd.read_sql(sql,con=table)
Ans

	type
0	INTEGER

Question 7

gameId 컬럼의 3번째 값은 무엇인가?

sql ="SELECT gameId FROM lol LIMIT 1 OFFSET 2;" 
Ans = pd.read_sql(sql,con=table)
Ans

	gameId
0	3327363504

Question 8

3번째 행의 creationTime, gameDuration 컬럼에 해당하는 값은 무엇인가?

sql ="SELECT creationTime,gameDuration FROM lol LIMIT 1 OFFSET 2;" 
Ans = pd.read_sql(sql,con=table)
Ans

	creationTime	gameDuration
0	1504360103310	1493

Attention

제주 날씨,인구에 따른 교통량데이터 : 출처 제주 데이터 허브 DataUrl = ‘https://raw.githubusercontent.com/Datamanim/pandas/main/Jeju.csv’

기본코드

table = connectDB(‘https://raw.githubusercontent.com/Datamanim/pandas/main/Jeju.csv’,’jeju’)

최초 1회만 실행

sql =’’ # table_name = jeju Ans = pd.read_sql(sql,con=table)

Question 9

데이터를 로드하라. 컬럼이 한글이기에 적절한 처리해줘야함

table = connectDB('https://raw.githubusercontent.com/Datamanim/pandas/main/Jeju.csv','jeju',encoding='euc-kr') 
# 최초 1회만 실행
sql ='SELECT * FROM jeju LIMIT 5' # table_name = jeju
Ans = pd.read_sql(sql,con=table)
Ans

	index	id	일자	시도명	읍면동명	거주인구	근무인구	방문인구	총 유동인구	평균 속도	평균 소요 시간	평균 기온	일강수량	평균 풍속
0	0	22448	2018-01-01	제주시	도두동	32249.987	3418.266	102709.092	138377.345	39.556	29.167	5.0	0.0	2.5
1	1	22449	2018-01-01	제주시	외도동	213500.997	10341.172	112692.789	336534.958	32.900	30.900	5.0	0.0	2.5
2	2	22450	2018-01-01	제주시	이도2동	1212382.218	96920.834	541194.481	1850497.533	29.538	35.692	2.9	0.0	2.4
3	3	22451	2018-01-01	제주시	일도1동	33991.653	6034.253	72155.919	112181.825	30.000	23.500	2.9	0.0	2.4
4	4	22452	2018-01-01	서귀포시	대천동	155036.925	9403.969	150882.409	315323.303	41.583	14.375	5.1	0.0	2.3

Question 10

데이터 마지막 3개행을 출력하라

sql ='SELECT * FROM jeju LIMIT 10 OFFSET (SELECT count(*) FROM jeju)-3' # table_name = jeju
Ans = pd.read_sql(sql,con=table)
Ans

	index	id	일자	시도명	읍면동명	거주인구	근무인구	방문인구	총 유동인구	평균 속도	평균 소요 시간	평균 기온	일강수량	평균 풍속
0	9618	32066	2020-04-30	제주시	도두동	28397.481	3144.895	84052.697	115595.073	41.053	29.421	20.3	0.0	3.0
1	9619	32067	2020-04-30	서귀포시	안덕면	348037.846	29106.286	251129.660	628273.792	46.595	49.189	17.6	0.0	3.5
2	9620	32068	2020-04-30	제주시	연동	1010643.372	65673.477	447622.068	1523938.917	40.863	27.765	14.1	0.0	4.8

Question 11

수치형 변수를 가진 컬럼만 필터하여 데이터프레임을 만들고 상위 5행을 출력하라

sql ="SELECT name FROM pragma_table_info('jeju') WHERE type ='INTEGER' or type='REAL';" 
Ans = pd.read_sql(sql,con=table)
Ans

	name
0	index
1	id
2	거주인구
3	근무인구
4	방문인구
5	총 유동인구
6	평균 속도
7	평균 소요 시간
8	평균 기온
9	일강수량
10	평균 풍속