{ "cells": [ { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "u'Connected: @flights.db'" ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%load_ext sql\n", "%sql sqlite:///flights.db" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "숙제 1\n", "=======\n", "\n", "### 일러두기 :\n", "\n", "**_꼼꼼하게 읽어보기 바랍니다_**\n", "\n", "\n", "* `prettytable` 모듈을 설치해야 스크립트를 실행할 수 있음. (설치 방법: `pip install --user prettytable`)\n", " * `flights.db` 파일이 숙제용 Jupyter notebook과 같은 디렉터리에 있어야 함 (없다면 [여기서](http://open.gnu.ac.kr/lecslides/2018-2-DB/Assignments1/flights.db.zip) 다운 받기) 압축을 해제해야 함. `flights.db.zip`이 있는 곳에서 `unzip flights.db.zip`으로 압축을 해제하면 됨\n", "* 데이터베이스 `flights.db`를 다운 받은 후 가장 위의 셀의 명령 실행하기\n", "* 테스트, 디버그, 탐색하기 등을 위해서 새로운 셀을 생성하는 것을 적극 권장함\n", "* 셀을 실행시키고 셀 왼 편에 `In [*]:` 이 보인다면 _실행 중_ 을 의미함\n", " * **만약 셀이 오랫 동안 결과를 내 놓지 않고 멈춘 것 같다면: SQL 에 다시 연결하도록 python kernel을 다시 시작해야 함**\n", " * 커널을 다시 시작하는 방법: \"Kernel >> Restart & Clear Output\", 그리고 위의 셀부터 아래로 하나씩 실행 함 \n", " * 다른 버전의 데이터베이스를 로드하기 위해서도 마찬가지를 새로운 연결을 만들어야 함\n", "* 기억하기:\n", " * `%sql [SQL 질의문;]` 은 _한 줄짜리_ SQL 질의문에 사용\n", " * `%%sql \n", " [SQL 질의문;]` 은 _여러 줄짜리_ SQL 질의문에 사용\n", "* `submit.py` 을 실행하면 질의문을 처리하고 출력함 \n", "* 실행의 결과는 `correct_output.txt` 파일에 나와 있음.\n", " * 실행 결과의 비교를 원한다면 `python sanity_check.py` 을 실행하거나, 다음의 명령을 실행하여 결과를 얻을 수 있음 `python submit.py > my_output; diff my_output correct_output.txt` 터미널에서 입력해야 함\n", " * **숙제로 작성한 `submit.py` 파일은 아래의 형식을 절대적으로 따라야 함.** 형식이라 함은:\n", " * 컬럼의 이름은 `correct_output.txt` 에 나와 있는 이름과 **똑같은 이름**이어야 함\n", " * 컬럼의 순서도 `correct_output.txt` 에 나와 있는 순서와 **똑같은 순서**이어야 함 \n", "\n", "### 제출 방법:\n", " * iPython notebook 자체를 제출하지 말 것\n", " * 대신에, `submit.py` 에 작성한 번호에 맞게 질의문을 복사 붙여 넣기 할 것\n", " * `%sql` 또는 `%%sql` 명령은 SQL 문에 포함시키지 말 것\n", " * 제출한 질의문을 똑같은 스키마에서 임의로 선택된 값에 대상으로 실행시켜 평가를 할 것임. 그렇기 때문에 해답과 똑같은 결과가 나오도록 상수등을 써서 조작하지 말것\n", " * **`submission_instructions.txt` 에 설명된 방법으로 해답을 제출할 것**\n", "\n", "_즐겁게 시작해봅시다!_" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "개요: 여행 일정 지연\n", "------------------------\n", "\n", "여행 일정이 지연 되는 것만큼 짜증 나는 일은 없습니다. 그렇지 않나요?\n", "\n", "여행 일정이 지연되지 않도록 여러가지 새로운 방법을 찾아봅니다. 최근에 찾은 데이터가 왜 일정이 지연되는지 이유와 무엇을 포기할지를 잘 설명해주고 있습니다. \n", "\n", "SQL을 사용해서 한 번 그 이유들을 알아봅시다." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "----" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "이 과제에서는 2017년 7월의 여객기의 지연 정보의 정보를 담고 있습니다. 데이터베이스의 기본 릴레이션에 대한 정보를 알아 봅시다." ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite:///flights.db\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
yearquartermonthday_of_monthday_of_weekfl_dateunique_carrierairline_idcarriertail_numfl_numorigin_airport_idorigin_airport_seq_idorigin_city_market_idoriginorigin_city_nameorigin_state_abrorigin_state_fipsorigin_state_nmorigin_wacdest_airport_iddest_airport_seq_iddest_city_market_iddestdest_city_namedest_state_abrdest_state_fipsdest_state_nmdest_waccrs_dep_timedep_timedep_delaydep_delay_newdep_del15dep_delay_groupdep_time_blktaxi_outwheels_offwheels_ontaxi_incrs_arr_timearr_timearr_delayarr_delay_newarr_del15arr_delay_grouparr_time_blkcancelledcancellation_codedivertedcarrier_delayweather_delaynas_delaysecurity_delaylate_aircraft_delayunnamed: 55
201737162017-07-01AS19930ASN559AS111278112780330852DCAWashington, DCVA51Virginia3814747147470330559SEASeattle, WAWA53Washington93800750.0-10.00.00.0-1.00800-085917.0807.01010.011.010531021.0-32.00.00.0-2.01000-10590.0None0.0NoneNoneNoneNoneNoneNone
" ], "text/plain": [ "[(2017, 3, 7, 1, 6, u'2017-07-01', u'AS', 19930, u'AS', u'N559AS', 1, 11278, 1127803, 30852, u'DCA', u'Washington, DC', u'VA', 51, u'Virginia', 38, 14747, 1474703, 30559, u'SEA', u'Seattle, WA', u'WA', 53, u'Washington', 93, 800, 750.0, -10.0, 0.0, 0.0, -1.0, u'0800-0859', 17.0, 807.0, 1010.0, 11.0, 1053, 1021.0, -32.0, 0.0, 0.0, -2.0, u'1000-1059', 0.0, None, 0.0, None, None, None, None, None, None)]" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT * \n", "FROM flight_delays \n", "LIMIT 1;" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "굉장히 많은 컬럼들이 있는 것을 알 수 있는데, 그러면 몇 줄이나 될까요?" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * sqlite:///flights.db\n", "Done.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
num_rows
509070
" ], "text/plain": [ "[(509070,)]" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "SELECT COUNT(*) AS num_rows\n", "FROM flight_delays" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "데이터의 양이 상당합니다! 손과 머리로만 해답을 찾지 못할 것 같군요. \n", "\n", "데이터베이스에 더 많은 데이터를 넣을 필요는 없겠군요. 컬럼들이 어떤 의미를 갖는지 알아 보려면 [이 링크](https://www.transtats.bts.gov/DL_SelectFields.asp?Table_ID=236)를 따라가기 바랍니다. \n", "\n", "몇 개의 추가적인 테이블들을 같이 포함해 놓았습니다. 이 테이블들을 사용하면 `airline_id`, `airport_id`, 그리고 `day_of_week` 을 사람이 읽기 편한 정보로 변환할 수 있습니다. \n", "\n", "아래의 셀을 이용하여 `airlines`과 `weekdays` 의 정보를 확인해보기 바랍니다:" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%sql\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "좋습니다. 이제 시작해봅시다." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# SQL 질의문" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "질의문 1: 항공편의 평균 지연 시간은? \n", "------------------------\n", "데이터에 대한 이해를 돕기 위해, 간단한 질의문을 작성해봅시다.\n", "\n", "아래의 셀에 2017년 7월동안 모든 항공편의 평균 지연시간을 구하는 질의문을 작성해봅시다." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%sql\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "질의문 2: 가장 긴 지연 시간은?\n", "------------------------\n", "평균은 그리 크지 않군요. 하지만 _최장_ 지연 시간은 어떻게 되나요?\n", "\n", "아래의 셀에 2017년 7월동안 가장 늦게 도착한 시간을 찾는 질의문을 작성해봅시다." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%sql\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "질의문 3: 어떤 항공편을 피하는 것이 정신 건강에 좋을까요?\n", "------------------------\n", "\n", "어떤 항공편이 가장 늦었나요?\n", "\n", "\n", "아래의 셀에 2017년 7월에 가장 늦게 도착한 항공사(`carrier`)와 항공편 명, 출발 도시 명, 도착 도시 명, 항공 일정을 출력하는 질의문을 작성 바랍니다. 앞에서 얻은 정보를 질의문에 삽입해서 계산하지 말고 중첩 질의문을 쓰기 바랍니다." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%sql\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "질의문 4: 어떤 요일이 여행하기 가장 안좋은 날인가요?\n", "------------------------\n", "\n", "학기가 시작되었으니 먼 곳으로 여행을 할 수는 없지만, 출장은 가야하겠지요. 비행기를 타기 가장 안좋은 날은 무슨 요일인가요?\n", "\n", "아래의 셀에 요일마다 평균 지연 시간이 어떻게 되는지 내림차순으로 정렬하여 결과를 출력하도록 질의문을 작성하기 바랍니다. 출력 결과의 스키마는 (`weekday_name`, `average_delay`)의 형태를 갖고 있어야 합니다.\n", "\n", "**Note: 요일의 ID를 그대로 출력하지 말기 바랍니다.** (Hint: `weekdays` 테이블을 사용하여 join하여 요일의 이름을 출력하도록 합시다.)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%sql\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "질의문 5: SFO에서 출발하는 항공사 중 지연 시간이 가장 긴 항공사는 어디입니까?\n", "------------------------\n", "\n", "어떤 요일을 피해야 할지 알았으니 SFO에서 출발하는 항공사 중 한 곳을 정해야 합니다. 어디로 갈지는 말하지 않았으니, SFO에서 출발하는 모든 항공사의 항공편들의 평균 지연시간을 구해 봅시다.\n", "\n", "아래의 셀에 2017년 7월에 SFO에서 출발한 각 항공사 별로 모든 항공편에 대해 평균 지연시간을 내림차순으로 구하는 질의문을 작성해봅시다.\n", "\n", "**Note: 항공사 ID를 그대로 출력하지 맙시다.** (Hint: 중첩 질의문으로 `airlines` 테이블을 join 하여 항공사 이름을 출력합시다.)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%sql\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "질의문 6: 항공사들의 지연 비율을 알아 봅시다\n", "------------------------\n", "지연되는 항공편이 많습니다. 어떤 항공사가 지연시간이 많은 알아봅시다.\n", "\n", "아래의 셀에 평균 10분 이상 지연되는 항공편이 있었던 항공사들의 비율을 구해봅시다. 전체 항공사의 수를 세서 질의문에 포함시키지 말기 바랍니다. 그리고 질의문에는 최소한 하나 이상의 `HAVING` 절을 사용합시다.\n", "\n", "Note: sqlite 의 `COUNT(*)`는 정수형을 리턴하기 때문에 실수형으로 결과를 출력하려면 최소한 한 번 이상 `SELECT CAST (COUNT(*) AS float)` 또는 `COUNT(*)*1.0` 절을 써야 합니다." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%sql\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "질의문 7: 출발 지연이 도착 지연에 어떤 영향을 미치나요?\n", "------------------------\n", "\n", "비행기가 지연 출발하면 도착 시간에 얼마나 영향을 주는지 알고 싶습니다.\n", "\n", "\n", "[샘플 공분산](https://en.wikipedia.org/wiki/Covariance) 은 두 변수 간의 분산량을 측정하여 상관관계가 있는지 알려주는 통계치입니다. 공분산이 클수록 상관관계가 높고 음수인 경우 역상관관계가 있습니다. 샘플 공분산의 계산 식은 다음과 같습니다:\n", "$$\n", "Cov(X,Y) = \\frac{1}{n-1} \\sum_{i=1}^n (x_i-\\hat{x})(y_i-\\hat{y})\n", "$$\n", "이 때, $x_i$ 는 $X$의 $i$번째 값이고, $y_i$는 $Y$의 $i$번째 값입니다. $X$ 와 $Y$의 평균은 $\\bar{x}$ 과 $\\bar{y}$으로 표현 하였습니다.\n", "\n", "아래의 셀에 도착 지연과 출발 지연 시간의 공분산을 구하는 하나의 질의문을 작성 해보기 바랍니다.\n", "\n", "*Note: [Pearson correlation coefficient](https://en.wikipedia.org/wiki/Pearson_correlation_coefficient) 으로 구할 수도 있습니다. 그 결과는 정규화 되어 1 부터 -1의 값으로 상관관계를 알려 줍니다. 하지만, SQLite는 루트 계산 함수가 들어 있지 않기 때문에 이 계산식을 쓸 수 가 없습니다. 다른 보편적인 데이터베이스(PostgreSQL와 MySQL)에는 루트 계산 함수가 구현되어 있습니다.*" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%sql\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "질의문 8: 한 주가 엉망이었습니다...\n", "------------------------\n", "\n", "7월 어떤 항공사의 마지막 한 주(24일 이후)의 평균 지연 시간이 그 이전 주(24일 이전)들의 평균 지연 시간보다 절대적으로 길었나요?\n", "\n", "아래의 셀에 1일부터 23일까지의 평균 지연 시간 대비 24일 부터 31일 사이의 평균 지연 시간이 절대적으로 길었던 항공사의 이름을 출력하는 질의문을 작성하기 바랍니다.\n", "\n", "Note: [sqlite에서 날짜 다루기](http://www.sqlite.org/lang_datefunc.html)에 따라 `day_of_month`을 사용하여 질의문을 작성하는 것이 편할 것입니다.\n", "\n", "Note 2: 아마 과제 중 가장 어려운 질의문이 될 수도 있는데, 작은 단위로 질의문을 작성하여 한 부분씩 해결하고, 그 질의문을 합쳐서 최종 질의문을 작성하는 것이 좋습니다.\n", "\n", "Hint: 두 개의 하위 질의문으로 계산할 수 있습니다. 하나의 질의문이 24일 이후의 평균 도착 시간을 계산하고, 다른 질의문이 24일 이전의 도착 시간을 계산하고, 두 질의문을 join하여 지연 시간의 차를 계산하면 됩니다." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%sql\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "질의문 9: 진보적인 그리고 혁명적인\n", "------------------------\n", "포트랜드 (PDX)와 유진 (EUG)로 가기를 원하지만, 한 번에 가기가 쉽지 않군요. 우수 고객 마일리지를 채우기 위해 같은 항공편으로 각 도시로 이동하기를 원합니다. SFO -> PDF와 SFO -> EUG 로 가는 같은 항공사가 있는지 알고 싶습니다.\n", "\n", "아래의 셀에 2017년 7월에 SFO -> PDX 과 SFO -> EUG 을 출항한 항공사의 유일한 이름(중복 없음 ID 가 아님)을 출력하는 하나의 SQL 질의문을 작성하기 바랍니다." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%sql\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "질의문 10: 피로도와 등거리 간의 결정\n", "------------------------\n", "\n", "시카고에서 캘리포니아로 이동하려고 합니다. Midway (MDW) 또는 O'Hare (ORD) 에서 샌프란시스코 (SFO), 산호세 (SJC), 오크랜드 (OAK)로 도착하면 좋겟습니다. 만약 이 번 달이 7월이라고 하면 시카고에서 현지 시간 14시에 출발하는 경로 중 지연 시간이 가장 짧은 경로가 어떤 것입니까?\n", "\n", "아래의 셀에 MDW 또는 ORD 에서 현지 시간 14시(`crs_dep_time`)에 출발하고 SFO, SJC, 또는 OAK에 도착하는 항공편들의 평균 지연 시간을 구하는 하나의 질의문을 작성하기 바랍니다. 출발과 도착 공항을 Group by로 묶고 지연 시간의 내림차순으로 출력하기 바랍니다.\n", "\n", "Note: `crs_dep_time` 필드는 정수 형을 갖고 있으며 hhmm (e.g. 4:15pm 은 1615 임) 형을 따름." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "%%sql\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 다 끝났습니다. 이제 제출합시다.\n", " * 제출하는 방법은 가장 위의 설명문을 참고하기 바랍니다." ] } ], "metadata": { "kernelspec": { "display_name": "Python 2", "language": "python", "name": "python2" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 2 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython2", "version": "2.7.15" } }, "nbformat": 4, "nbformat_minor": 2 }