SQL Test 2 - 주유소 데이터 분석 Code Review

솔비·2024년 2월 7일
mini projectsqlzerobase
0

📚 SQL. 관계형 데이터베이스

목록 보기
15/19
post-thumbnail





문제1

주유소 데이터를 저장할 테이블을 다음의 구조로 생성하세요

import mysql.connector

remote = mysql.connector.connect(
    host = {} ,
    port = 3306,
    user = "admin",
    password = {},
    database = "oneday"
)

sql = "CREATE TABLE GAS_BRAND(id INT, name VARCHAR(16), PRIMARY KEY (id))"

remote.cursor().execute(sql)

sql = '''
    CREATE TABLE GAS_STATION (
    id INT auto_increment,
    brand INT,
    name VARCHAR(64),
    city char(2),
    gu VARCHAR(10),
    address VARCHAR(128),
    gasoline INT,
    diesel INT,
    self BOOLEAN,
    car_wash BOOLEAN,
    charging_station BOOLEAN,
    car_maintenance BOOLEAN,
    convenience_store BOOLEAN,
    24_hours BOOLEAN,
    lat DECIMAL(16,14),
    lng DECIMAL(17,14),
    PRIMARY KEY (id),
    CONSTRAINT PK_GAS FOREIGN KEY (brand) REFERENCES GAS_BRAND (id))
'''

remote.cursor().execute(sql)

📌 BOOLEAN타입이란

  • Ture or False / 1,0 으로 반환
  • 내부적으로는 Boolean 타입 자체가 없기때문에 tinyint(1) 으로 생성됨
  • 1,0이외의 값도 유효성 체크없이 데이터가 삽입이 되므로 주의해야함.
  • 참고링크 : https://yscho03.tistory.com/305





문제2

문제 2. Python 코드로 GAS_BRAND 데이터를 다음과 같이 입력하고 확인하세요.

data = [(1, "SK에너지"),(2, "현대오일뱅크"),(3, "GS칼텍스"),(4, "S-OIL"),(5, "알뜰주유소"),(6, "자가상표")]

sql = "INSERT INTO GAS_BRAND VALUES (%s,%s)"

for i in data :
    remote.cursor(buffered=True).execute(sql, i)
    print(i)
    remote.commit()





문제3

문제 3. 다음의 함수와 그 함수를 테스트하는 코드를 작성하세요.

  • 화폐단위 문자형을 입력받아 숫자형으로 반환하는 함수 (테스트 입력 : ‘1,000’)
def money(str) :
    result = int(str.replace(",",""))

    return result

replace 메서드를 사용하여 ,를 제거하고
int로 변환하는 원초적인 함수를 만들었다 🥲

  • 주유소 브랜드를 입력하면 GAS_BRAND 데이터를 참고하여 ID 를 반환하는 함수 (테스트 입력 : ‘SK에너지’)
def brand_to_id(str) :
    sql = f"SELECT id FROM GAS_BRAND WHERE name LIKE '%{str}%'"

    cursor = remote.cursor()
    cursor.execute(sql)
    result = cursor.fetchone()
    
    if result :
        return result [0]
    else :
        return None

📌 fetchall() / fetchone()
둘 다 select 쿼리의 결과를 가져오는 메서드이다.
다만 차이점이 있다면,

  • fetchall() : 모든행을 가져와서 리스트로 반환
    일치하는 데이터가 없다면 빈 리스트 반환
  • fetchone() : 첫번째 행만 가져와서 튜플로 반환
    일치하는 데이터가 없다면 None 반환

if 구문에서 result 값을 넣었는데,
fetchone()으로 인해 None값이 반환되면 False,
데이터가 있다면, True가 되므로
튜플값이 return 되는 것을 막았다.

  • 주소를 입력받아 구 이름을 반환하는 함수 (테스트 입력 : ‘서울시 강남구 헌릉로 730’)
def ads(str):
    raw = str.split()
    result = raw[1]

    return result

🤨 다른 형태의 주소가 들어오면 안될것같은데..
라는 고민은 들었지만, 일단은 split으로 자른 후 구에 해당하는 값만 return 시킴

  • 주소를 입력받아 위도, 경도를 반환하는 함수 (테스트 입력 : ‘서울시 강남구 헌릉로 730’)
def lan_lng(str):
    import googlemaps
    gmaps_key = "{개인키값}"
    gmaps = googlemaps.Client(key = gmaps_key)

    lat = gmaps.geocode(str, language = "ko")[0].get("geometry")["location"]["lat"]
    lng = gmaps.geocode(str, language = "ko")[0].get("geometry")["location"]["lng"]

    return lat, lng





문제4

문제 4. Python 코드에서 주유소 페이지에서 데이터를 가져올때, GAS_STATION 테이블에 바로 입력하도록 수정하세요. (앞서 생
성한 함수활용)

  • 주의. city 는 ‘서울’ 로 고정, 부가정보 데이터 타입
  • 입력된 데이터의 총 갯수를 쿼리하여 결과를 확인합니다.
  • 입력된 데이터 상위 10개를 쿼리하여 결과를 확인합니다.

📌 페이지 접근 코드는 생략하고 입력코드만 기재함

from bs4 import BeautifulSoup
import time


# 구 이름 넣기
for gu in tqdm_notebook(gu_list) :
    gu_raw = driver.find_element(By.CSS_SELECTOR,"#SIGUNGU_NM0")
    gu_raw.send_keys(gu)
    
    html = driver.page_source
    soup = BeautifulSoup(html,"html.parser")
    cnt = int(soup.find(id = "totCnt").text)


    for n in range(1,cnt+1) :
        driver.find_element(By.CSS_SELECTOR,f'#body1 > tr:nth-child({n}) > td.rlist > a').click()
        html = driver.page_source
        soup = BeautifulSoup(html,"html.parser")

        data = []

        # brand
        bd = soup.find(id = "poll_div_nm").text
        brand = brand_to_id(bd)
        data.append(brand)

        # name
        name = soup.find(id = "os_nm").text
        data.append(name)

        # city 
        city = "서울"
        data.append(city)

        # address / gu
        address = soup.find(id = "rd_addr").text
        gu = ads(address)
        data.append(gu)
        data.append(address)

        #gasoline
        gs = soup.find(id = "b027_p").text
        gasoline = money(gs)
        data.append(gasoline)

        #diesel
        ds = soup.find(id = "d047_p").text
        diesel = money(ds)
        data.append(diesel)

        #self
        if soup.find('img', {'alt': '셀프주유소', 'class': 'bul'}):
            self = "1"
        else:
            self = "0"

        data.append(self)

        # car_wash
        if "off" in soup.find(id = "cwsh_yn").get("src")  :
            wash = "0"
        else:
            wash = "1"

        data.append(wash)

        #charging_station
        if "off" in soup.find(id = "lpg_yn").get("src")  :
            charging = "0"
        else:
            charging = "1"

        data.append(charging)

        #car_maintenance
        if "off" in soup.find(id = "maint_yn").get("src"):
            center = "0"
        else:
            center = "1"

        data.append(center)

        # convenience_store
        if "off" in soup.find(id = "cvs_yn").get("src")  :
            store = "0"
        else:
            store = "1"

        data.append(store)

        # 24_hours
        if "off" in soup.find(id = "sel24_yn").get("src")  :
            night = "0"
        else:
            night = "1"

        data.append(night)

        # lat / lat

        try :
            lat = lan_lng(address)[0]
            lng = lan_lng(address)[1]

        except :
            lat = ''
            lng = ''
            
        data.append(lat)
        data.append(lng)
        
        sql = "INSERT INTO GAS_STATION (brand,name,city,gu,address,gasoline,diesel,self,car_wash,charging_station,car_maintenance,convenience_store,24_hours,lat,lng) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) "

        cursor = remote.cursor(buffered=True)
        cursor.execute(sql,tuple(data))
        print(tuple(data))
        remote.commit()
  • 위도경도 에러는 try~except 구문으로 처리
    GAS_STATION의 primary key인 id는 auto라 코드가 잘못되어 다시 for문을 돌릴 때마다 drop하고 다시 create 하였다는 슬픈 스토리..ㅎ





문제5

문제 5. 시각화 프로젝트를 위하여 다음의 규칙으로 쿼리하여 CSV 파일로 저장합니다. (Python 코드로 작성)

  • 전체 데이터를 가져오는데, 주유소 브랜드 아이디 대신 브랜드명이 표시되어야 합니다. (정렬 : 주유소 매장 아이디 순)
  • 다음의 형식으로 저장되어야 함 (브랜드 이름, 칼럼 명 주의, id : GAS_STORE.id)
sql = '''
SELECT gs.id, gb.name, gs.name, city, gu, address, gasoline, diesel, self, car_wash, charging_station, car_maintenance, convenience_store, 24_hours, lat, lng
FROM GAS_STATION gs LEFT OUTER JOIN GAS_BRAND gb
ON gs.brand = gb.id
ORDER BY gs.id
'''

cursor = remote.cursor(buffered=True)
cursor.execute(sql)
result = cursor.fetchall()

import pandas as pd

df = pd.DataFrame(result)
df.columns = ["id", "brand", "name", "city", "gu", "address", "gasoline", "diesel", "self", "car_wash", "charging_station", "car_maintenance", "convenience_store", "24_hours", "lat", "lng"]
df.to_csv('./Oil Price Analysis.csv',index = False, encoding = "euc-kr")

GAS_STATION 기존 컬럼에서 brand 번호만 GAS_STORE의 name으로 변경하면 되서 left outer join을 사용했다.





문제6 💢

문제 6. 위도, 경도 정보를 이용하여 미왕빌딩에서 1킬로 이내에 위치한 주유소 정보를 검색하세요.

  • 주유소 아이디, 주유소 브랜드명, 주유소 매장명, 주소, 미왕빌딩으로 부터의 거리 (km) 
sql = '''
SELECT id, brand, name, address, ROUND((6371*acos(cos(radians(37.495535))*cos(radians(lat))*cos(radians(lng) -radians(127.029357))+sin(radians(37.495535))*sin(radians(lat)))), 2) AS distance
FROM GAS_STATION HAVING distance < 1
ORDER BY distance;
'''

cursor.execute(sql)
result = cursor.fetchall()

for i in result:
    print(i)

📌 답 기록

  • https://yusang.tistory.com/48
    풀이식은 모르겠고;
    링크보고 그대로 따라 적었다.
    • 미왕빌딩 좌표값
      target_lat = 37.495535
      target_lng = 127.029357

📌 추가시도기록

  • https://mizykk.tistory.com/21
    해당 내용 참고하여
    haversine 라이브러리 install 한뒤 아래처럼 풀었으나,
    거리까지 출력되게하려면 임시로 테이블을 하나 만들어서 INSERT한뒤 SELECT 해야할것아서
    다른방법을 찾게되었다.... ㅜ.ㅜ
    from haversine import haversine
target = (37.495535, 127.029357)
target = (37.495535, 127.029357)
sql = 'SELECT id, lat, lng FROM GAS_STATION'
cursor = remote.cursor(buffered=True)
cursor.execute(sql)
result = cursor.fetchall()
for i in result :
    temp = tuple(float(x) for x in i)
    temp_id = int(temp[0])
    temp_lat_lng = temp[1:]
    if haversine(target, temp_lat_lng, unit='km') <= 1 :       
        sql = f'SELECT GAS_STATION.id, GAS_BRAND.name, GAS_STATION.name, GAS_STATION.address FROM GAS_STATION LEFT OUTER JOIN GAS_BRAND ON GAS_STATION.brand = GAS_BRAND.id WHERE GAS_STATION.id = {temp_id} '
        cursor = remote.cursor(buffered=True)
        cursor.execute(sql)
        result = cursor.fetchall()
        for i in result :
            print(i)
추가로 위에서 temp = tuple(float(x) for x in i)는
위도 경도가 Decimal('37.46715650000000'), Decimal('127.11870810000000') 처럼
타입까지 출력되서 GPT에 물어봤다.





문제7

문제 7. 위도, 경도 정보를 이용하여 미왕빌딩에서 셀프주유가 가능하고 24시간이면서 편의점이 있는 가장 가까운 주유소 10개를 휘발유 가격이 가장 저렴한 순으로 정렬하여 조회하세요.

  • 주유소 아이디, 주유소 브랜드명, 주유소 매장명, 주소, 휘발유 가격, 부가정보 (셀프, 24시간, 편의점 여부), 미왕빌딩으로 부터의
    거리 (km)
sql = '''
SELECT gs.id, gb.name, gs.name, address, gasoline, self, 24_hours, convenience_store, ROUND((6371*acos(cos(radians(37.495535))*cos(radians(lat))*cos(radians(lng) -radians(127.029357))+sin(radians(37.495535))*sin(radians(lat)))), 2) AS distance
FROM GAS_STATION gs LEFT OUTER JOIN GAS_BRAND gb
ON gs.brand = gb.id
ORDER BY distance, gasoline
LIMIT 10
'''

cursor.execute(sql)
result = cursor.fetchall()

for i in result:
    print(i)

6번풀면서 뇌정지가 와서
가까운 주유소 10개를 뽑고 휘발유 저렴한 순이면
LIMIT에 조건을 걸어야하나..? 🥲
라는 바보같은 생각으로 10여분을 보내고 ㅋㅋㅋ
order by에 거리를 먼저 걸어주면 된다 ㅎㅅㅎ!





문제8

문제 8. 구별로 주유소 브랜드 별 휘발유 평균가격을 조회하여 저렴한 순으로 출력하세요.

  • 구 이름, 주유소 브랜드 이름, 휘발유 평균 가격
sql = '''
SELECT gs.gu, gb.name, avg(gasoline)
FROM GAS_STATION gs LEFT OUTER JOIN GAS_BRAND gb
ON gs.brand = gb.id
GROUP BY gs.gu, gb.name
ORDER BY avg(gasoline)
'''

cursor.execute(sql)
result = cursor.fetchall()

for i in result:
    gu, name, avg_gasoline = i
    avg_gasoline = round(avg_gasoline,2)
    print(gu, name, avg_gasoline)





Zero Base 데이터분석 스쿨
Daily Study Note
profile
솔비
Study Log
이전 포스트

SQL Test - 스타벅스 이디야 데이터 분석 Code Review

다음 포스트

Daily SQL Query by programmers (Level 3)

0개의 댓글