利用SQL技能处理数据集

问答题 （100分）
【第一题】
1、创建db名字叫 flight 创建新表名字叫on_time_performance

mysql> create database flight character set 'utf8';
Query OK, 1 row affected (0.00 sec)

2、航班数据检查csv ，导入到sql数据库中

3、执行count语句查看总行数

mysql> select count(*) from on_time_performance;
+----------+
| count(*) |
+----------+
|   469008 |
+----------+
1 row in set (0.16 sec)

【第二题】
1、在航班及时率表中有如下delay延迟字段，请找出导致延迟次数最多的是哪一个原因 ，并给出各个原因累计的延迟时间
WeatherDelay CarrierDelay NASDelay SecurityDelay LateAircraftDelay

mysql> create view delay_view as
    -> select 'WeatherDelay' as delay_name, count(WeatherDelay) as delay_times, sum(WeatherDelay) as dalay_hours from on_time_performance where WeatherDelay > 0
    -> union
    -> select 'CarrierDelay', count(CarrierDelay), sum(CarrierDelay) from on_time_performance where CarrierDelay > 0
    -> union
    -> select 'NASDelay', count(NASDelay), sum(NASDelay) from on_time_performance where NASDelay > 0
    -> union
    -> select 'SecurityDelay', count(SecurityDelay), sum(SecurityDelay) from on_time_performance where SecurityDelay > 0
    -> union
    -> select 'LateAircraftDelay', count(LateAircraftDelay), sum(LateAircraftDelay) from on_time_performance where LateAircraftDelay > 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from delay_view order by delay_times desc limit 1;
+--------------+-------------+-------------+
| delay_name   | delay_times | dalay_hours |
+--------------+-------------+-------------+
| CarrierDelay |       46191 |     1639773 |
+--------------+-------------+-------------+
1 row in set (1.46 sec)

mysql> select * from delay_view order by delay_times desc;
+-------------------+-------------+-------------+
| delay_name        | delay_times | dalay_hours |
+-------------------+-------------+-------------+
| CarrierDelay      |       46191 |     1639773 |
| NASDelay          |       45739 |     1155982 |
| LateAircraftDelay |       45259 |     2031277 |
| WeatherDelay      |        5140 |      249979 |
| SecurityDelay     |         273 |        6398 |
+-------------------+-------------+-------------+
5 rows in set (1.48 sec)

2、延迟倒数第二的是哪个原因 ，给出累计的延迟时间

mysql> select * from delay_view order by delay_times limit 1, 1;
+--------------+-------------+-------------+
| delay_name   | delay_times | dalay_hours |
+--------------+-------------+-------------+
| WeatherDelay |        5140 |      249979 |
+--------------+-------------+-------------+
1 row in set (1.47 sec)

【第三题】
1、请显示2015-01-03日的航班数据，要求显示AirlineID,FlightNum,Origin,Dest,DepTime,TailNum,AirTime,Distance如上字段

mysql> select AirlineID, FlightNum, Origin, Dest, DepTime, TailNum, AirTime, Distance from on_time_performance where FlightDate = '2015/1/13';

2、由于时间部分查询非常多，请在时间字段上增加索引，加快查询速度，此处请截取两张图片，比对执行速度的区别

3、由于数据很多，，所以请给出第50-55行数据的AirlineID,FlightNum,Origin,Dest,DepTime,TailNum,AirTime,Distance 截图；同时写出快速获取到第50-55行所用的sql语句。

mysql> select AirlineID, FlightNum, Origin, Dest, DepTime, TailNum, AirTime, Distance from on_time_performance limit 49, 6;

【第四题】
1、探究一年内飞行航班的季节性，并统计出12个月的航班总数

mysql> select Quarter, Month, count(*) as totle from on_time_performance group by Quarter, Month;
+---------+-------+-------+
| Quarter | Month | totle |
+---------+-------+-------+
| 1       | 1     | 38024 |
| 1       | 2     | 34487 |
| 1       | 3     | 40424 |
| 2       | 4     | 39187 |
| 2       | 5     | 40029 |
| 2       | 6     | 40747 |
| 3       | 7     | 41852 |
| 3       | 8     | 41095 |
| 3       | 9     | 37625 |
| 4       | 10    | 39011 |
| 4       | 11    | 37853 |
| 4       | 12    | 38674 |
+---------+-------+-------+
12 rows in set (0.53 sec)

2、找出航班最多的一个月是几月份

mysql> select Quarter, Month, count(*) as totle from on_time_performance group by Quarter, Month order by totle desc limit 1;
+---------+-------+-------+
| Quarter | Month | totle |
+---------+-------+-------+
| 3       | 7     | 41852 |
+---------+-------+-------+
1 row in set (0.54 sec)

【第五题】
现有一个json文件的airplane数据，写一段python程序把数据转换成csv，导入数据库中。然后统计飞机最多的两个生产商

mysql> select Manufacturer, count(*) as c_num from airplanes group by Manufacturer order by c_num desc limit 2;
+--------------+-------+
| Manufacturer | c_num |
+--------------+-------+
| BOEING       |  1953 |
| AIRBUS       |   519 |
+--------------+-------+
2 rows in set (0.00 sec)