# 数据来源

`https://flight.qunar.com/touch/api/domestic/wbdflightlist?departureCity=%E5%8C%97%E4%BA%AC&arrivalCity=%E6%B7%B1%E5%9C%B3&departureDate=2018-06-12&ex_track=&__m__=09de50bb5812f2686e2db9b0f49185d0&sort=`

# 数据处理

### 1. 数据入库

```CREATE TABLE domestic_flight
(
departure_city character varying(30),
departure_cy double precision,
departure_cx double precision,
landing_city character varying(30),
landing_cy double precision,
landing_cx double precision,
mileage character varying(255),
flight_schedules character varying(255),
airlines character varying(255),
aircraft_models character varying(255),
departure_time character varying(255),
landing_time character varying(255),
departure_airport character varying(255),
departure_y double precision,
departure_x double precision,
landing_airport character varying(255),
landing_y double precision,
landing_x double precision,
punctuality_rate character varying(255),
average_delayed character varying(255),
is_mon smallint,
is_tue smallint,
is_wed smallint,
is_thr smallint,
is_fri smallint,
is_sat smallint,
is_sun smallint
)```

### 2. 提取城市和机场数据

##### 2.1 新建机场表
```CREATE TABLE flight_airport
(
id serial,
name character varying(30),
province character varying(30),
airport_x numeric,
airport_y numeric,
CONSTRAINT pkey_air_airporty PRIMARY KEY (id)
) ```
##### 2.2 插入数据

```INSERT INTO flight_airport (NAME, airport_x, airport_y)(
SELECT DISTINCT
departure_airport AS airport,
departure_x AS airport_x,
departure_y AS airport_y
FROM
domestic_flight
)
UNION
(
SELECT DISTINCT
landing_airport AS airport,
to_number(landing_x, '999.999999999') AS airport_x,
to_number(landing_y, '999.999999999') AS airport_y
FROM
domestic_flight
)```

##### 2.3 更新省属性

```UPDATE flight_airport
SET province = (
SELECT
NAME
FROM
province
WHERE
st_within (
st_point (
flight_airport.airport_x,
flight_airport.airport_y
),
province.geom
)
)```

ok， 大工告成，最后的数据如下：

# 数据展示与分析

### 2.数据统计

##### 2.1 省机场数排名
```SELECT
province,
COUNT (1) AS airport_num
FROM
flight_airport
GROUP BY
province
ORDER BY
airport_num DESC```

16

15

13

11

11

11

10

9

9

8

8

8

7

6

6

6

6

6

5

5

5

5

5

4

4

4

3

3

2

2

1

##### 2.1 航空公司航班数排名
```SELECT
airlines as name,
COUNT (1) AS num
FROM
domestic_flight
GROUP BY
airlines
ORDER BY
num DESC```

2552

1862

1440

1183

1000

982

779

565

468

463

332

298

292

278

268

239

219

214

212

195

167

133

111

92

90

68

67

60

52

52

50

46

42

40

30

26

18

17

17

13

13

8

4

4

3

3

2

2

##### 2.3 飞机机型排名
```SELECT
aircraft_models as name,
COUNT (1) AS num
FROM
domestic_flight
GROUP BY
aircraft_models
ORDER BY
num DESC```

6243

3697

1278

JET

987

787

ERJ-190(中)

678

547

457

145

108

37

30

CRJ(小)

24

ERJ(小)

20

15

10

5

2

1

259 篇文章46 人订阅

0 条评论

## 相关文章

3157

3685

1296

4565

3609

902

### 黑程序员最狠的十句话

1. 今天去买了几箱水果，结账一共260元。我说：“都是老顾客了，零头就抹了吧。”老板也很爽快：“行，凑个整，你给256块吧。”我顿时肃然起敬：“您以前当过程序...

3757

2022

2986

2846