-
Notifications
You must be signed in to change notification settings - Fork 1
/
fare_data_merge_clean.sql
148 lines (123 loc) · 11.5 KB
/
fare_data_merge_clean.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
-- show global variables like 'local_infile';
-- set global local_infile=true;
-- show global variables like 'datadir';
-- CREATE SCHEMA BruinAir;
USE BruinAir;
-- DROP TABLE Q2_2022;
-- DROP TABLE Q1_2022;
-- DROP TABLE Q4_2021;
-- DROP TABLE Q2_2018;
CREATE TABLE Q2_2022 (
year INT, quarter INT, origin_airport_id INT, origin_airport_seq_id INT, origin_city_market_id INT, origin VARCHAR(3), origin_country VARCHAR(2), origin_state_abr VARCHAR(2), origin_state_nm VARCHAR(20), dest_airport_id INT, dest_airport__seq_id INT, dest_city_market_id INT, dest VARCHAR(3), dest_country VARCHAR(2), dest_state_abr VARCHAR(2), dest_state_nm VARCHAR(20), bulk_fare INT, passengers FLOAT(3,2), market_fare FLOAT(6,2), market_miles_flown FLOAT(6,2)
);
LOAD DATA LOCAL INFILE '/Users/junwonchoi/Desktop/DataRes/BruinAir/2022Q2.csv' INTO TABLE Q2_2022 COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;
CREATE TABLE Q1_2022 (
year INT, quarter INT, origin_airport_id INT, origin_airport_seq_id INT, origin_city_market_id INT, origin VARCHAR(3), origin_country VARCHAR(2), origin_state_abr VARCHAR(2), origin_state_nm VARCHAR(20), dest_airport_id INT, dest_airport__seq_id INT, dest_city_market_id INT, dest VARCHAR(3), dest_country VARCHAR(2), dest_state_abr VARCHAR(2), dest_state_nm VARCHAR(20), bulk_fare INT, passengers FLOAT(3,2), market_fare FLOAT(6,2), market_miles_flown FLOAT(6,2)
);
LOAD DATA LOCAL INFILE '/Users/junwonchoi/Desktop/DataRes/BruinAir/2022Q1.csv' INTO TABLE Q1_2022 COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;
CREATE TABLE Q4_2021 (
year INT, quarter INT, origin_airport_id INT, origin_airport_seq_id INT, origin_city_market_id INT, origin VARCHAR(3), origin_country VARCHAR(2), origin_state_abr VARCHAR(2), origin_state_nm VARCHAR(20), dest_airport_id INT, dest_airport__seq_id INT, dest_city_market_id INT, dest VARCHAR(3), dest_country VARCHAR(2), dest_state_abr VARCHAR(2), dest_state_nm VARCHAR(20), bulk_fare INT, passengers FLOAT(3,2), market_fare FLOAT(6,2), market_miles_flown FLOAT(6,2)
);
LOAD DATA LOCAL INFILE '/Users/junwonchoi/Desktop/DataRes/BruinAir/2021Q4.csv' INTO TABLE Q4_2021 COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;
CREATE TABLE Q3_2021 (
year INT, quarter INT, origin_airport_id INT, origin_airport_seq_id INT, origin_city_market_id INT, origin VARCHAR(3), origin_country VARCHAR(2), origin_state_abr VARCHAR(2), origin_state_nm VARCHAR(20), dest_airport_id INT, dest_airport__seq_id INT, dest_city_market_id INT, dest VARCHAR(3), dest_country VARCHAR(2), dest_state_abr VARCHAR(2), dest_state_nm VARCHAR(20), bulk_fare INT, passengers FLOAT(3,2), market_fare FLOAT(6,2), market_miles_flown FLOAT(6,2)
);
LOAD DATA LOCAL INFILE '/Users/junwonchoi/Desktop/DataRes/BruinAir/2021Q3.csv' INTO TABLE Q3_2021 COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;
CREATE TABLE Q2_2021 (
year INT, quarter INT, origin_airport_id INT, origin_airport_seq_id INT, origin_city_market_id INT, origin VARCHAR(3), origin_country VARCHAR(2), origin_state_abr VARCHAR(2), origin_state_nm VARCHAR(20), dest_airport_id INT, dest_airport__seq_id INT, dest_city_market_id INT, dest VARCHAR(3), dest_country VARCHAR(2), dest_state_abr VARCHAR(2), dest_state_nm VARCHAR(20), bulk_fare INT, passengers FLOAT(3,2), market_fare FLOAT(6,2), market_miles_flown FLOAT(6,2)
);
LOAD DATA LOCAL INFILE '/Users/junwonchoi/Desktop/DataRes/BruinAir/2021Q2.csv' INTO TABLE Q2_2021 COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;
CREATE TABLE Q1_2021 (
year INT, quarter INT, origin_airport_id INT, origin_airport_seq_id INT, origin_city_market_id INT, origin VARCHAR(3), origin_country VARCHAR(2), origin_state_abr VARCHAR(2), origin_state_nm VARCHAR(20), dest_airport_id INT, dest_airport__seq_id INT, dest_city_market_id INT, dest VARCHAR(3), dest_country VARCHAR(2), dest_state_abr VARCHAR(2), dest_state_nm VARCHAR(20), bulk_fare INT, passengers FLOAT(3,2), market_fare FLOAT(6,2), market_miles_flown FLOAT(6,2)
);
LOAD DATA LOCAL INFILE '/Users/junwonchoi/Desktop/DataRes/BruinAir/2021Q1.csv' INTO TABLE Q1_2021 COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;
CREATE TABLE Q4_2020 (
year INT, quarter INT, origin_airport_id INT, origin_airport_seq_id INT, origin_city_market_id INT, origin VARCHAR(3), origin_country VARCHAR(2), origin_state_abr VARCHAR(2), origin_state_nm VARCHAR(20), dest_airport_id INT, dest_airport__seq_id INT, dest_city_market_id INT, dest VARCHAR(3), dest_country VARCHAR(2), dest_state_abr VARCHAR(2), dest_state_nm VARCHAR(20), bulk_fare INT, passengers FLOAT(3,2), market_fare FLOAT(6,2), market_miles_flown FLOAT(6,2)
);
LOAD DATA LOCAL INFILE '/Users/junwonchoi/Desktop/DataRes/BruinAir/2020Q4.csv' INTO TABLE Q4_2020 COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;
CREATE TABLE Q3_2020 (
year INT, quarter INT, origin_airport_id INT, origin_airport_seq_id INT, origin_city_market_id INT, origin VARCHAR(3), origin_country VARCHAR(2), origin_state_abr VARCHAR(2), origin_state_nm VARCHAR(20), dest_airport_id INT, dest_airport__seq_id INT, dest_city_market_id INT, dest VARCHAR(3), dest_country VARCHAR(2), dest_state_abr VARCHAR(2), dest_state_nm VARCHAR(20), bulk_fare INT, passengers FLOAT(3,2), market_fare FLOAT(6,2), market_miles_flown FLOAT(6,2)
);
LOAD DATA LOCAL INFILE '/Users/junwonchoi/Desktop/DataRes/BruinAir/2020Q3.csv' INTO TABLE Q3_2020 COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;
CREATE TABLE Q2_2020 (
year INT, quarter INT, origin_airport_id INT, origin_airport_seq_id INT, origin_city_market_id INT, origin VARCHAR(3), origin_country VARCHAR(2), origin_state_abr VARCHAR(2), origin_state_nm VARCHAR(20), dest_airport_id INT, dest_airport__seq_id INT, dest_city_market_id INT, dest VARCHAR(3), dest_country VARCHAR(2), dest_state_abr VARCHAR(2), dest_state_nm VARCHAR(20), bulk_fare INT, passengers FLOAT(3,2), market_fare FLOAT(6,2), market_miles_flown FLOAT(6,2)
);
LOAD DATA LOCAL INFILE '/Users/junwonchoi/Desktop/DataRes/BruinAir/2020Q2.csv' INTO TABLE Q2_2020 COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;
CREATE TABLE Q1_2020 (
year INT, quarter INT, origin_airport_id INT, origin_airport_seq_id INT, origin_city_market_id INT, origin VARCHAR(3), origin_country VARCHAR(2), origin_state_abr VARCHAR(2), origin_state_nm VARCHAR(20), dest_airport_id INT, dest_airport__seq_id INT, dest_city_market_id INT, dest VARCHAR(3), dest_country VARCHAR(2), dest_state_abr VARCHAR(2), dest_state_nm VARCHAR(20), bulk_fare INT, passengers FLOAT(3,2), market_fare FLOAT(6,2), market_miles_flown FLOAT(6,2)
);
LOAD DATA LOCAL INFILE '/Users/junwonchoi/Desktop/DataRes/BruinAir/2020Q1.csv' INTO TABLE Q1_2020 COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;
CREATE TABLE Q4_2019 (
year INT, quarter INT, origin_airport_id INT, origin_airport_seq_id INT, origin_city_market_id INT, origin VARCHAR(3), origin_country VARCHAR(2), origin_state_abr VARCHAR(2), origin_state_nm VARCHAR(20), dest_airport_id INT, dest_airport__seq_id INT, dest_city_market_id INT, dest VARCHAR(3), dest_country VARCHAR(2), dest_state_abr VARCHAR(2), dest_state_nm VARCHAR(20), bulk_fare INT, passengers FLOAT(3,2), market_fare FLOAT(6,2), market_miles_flown FLOAT(6,2)
);
LOAD DATA LOCAL INFILE '/Users/junwonchoi/Desktop/DataRes/BruinAir/2019Q4.csv' INTO TABLE Q4_2019 COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;
CREATE TABLE Q3_2019 (
year INT, quarter INT, origin_airport_id INT, origin_airport_seq_id INT, origin_city_market_id INT, origin VARCHAR(3), origin_country VARCHAR(2), origin_state_abr VARCHAR(2), origin_state_nm VARCHAR(20), dest_airport_id INT, dest_airport__seq_id INT, dest_city_market_id INT, dest VARCHAR(3), dest_country VARCHAR(2), dest_state_abr VARCHAR(2), dest_state_nm VARCHAR(20), bulk_fare INT, passengers FLOAT(3,2), market_fare FLOAT(6,2), market_miles_flown FLOAT(6,2)
);
LOAD DATA LOCAL INFILE '/Users/junwonchoi/Desktop/DataRes/BruinAir/2019Q3.csv' INTO TABLE Q3_2019 COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;
CREATE TABLE Q2_2019 LIKE Q3_2019;
LOAD DATA LOCAL INFILE '/Users/junwonchoi/Desktop/DataRes/BruinAir/2019Q2.csv' INTO TABLE Q2_2019 COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;
CREATE TABLE Q1_2019 LIKE Q3_2019;
LOAD DATA LOCAL INFILE '/Users/junwonchoi/Desktop/DataRes/BruinAir/2019Q1.csv' INTO TABLE Q1_2019 COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;
CREATE TABLE Q4_2018 (
year INT, quarter INT, origin_airport_id INT, origin_airport_seq_id INT, origin_city_market_id INT, origin VARCHAR(3), origin_country VARCHAR(2), origin_state_abr VARCHAR(2), origin_state_nm VARCHAR(20), dest_airport_id INT, dest_airport__seq_id INT, dest_city_market_id INT, dest VARCHAR(3), dest_country VARCHAR(2), dest_state_abr VARCHAR(2), dest_state_nm VARCHAR(20), bulk_fare INT, passengers FLOAT(3,2), market_fare FLOAT(6,2), market_miles_flown FLOAT(6,2)
);
LOAD DATA LOCAL INFILE '/Users/junwonchoi/Desktop/DataRes/BruinAir/2018Q4.csv' INTO TABLE Q4_2018 COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;
CREATE TABLE Q3_2018 (
year INT, quarter INT, origin_airport_id INT, origin_airport_seq_id INT, origin_city_market_id INT, origin VARCHAR(3), origin_country VARCHAR(2), origin_state_abr VARCHAR(2), origin_state_nm VARCHAR(20), dest_airport_id INT, dest_airport__seq_id INT, dest_city_market_id INT, dest VARCHAR(3), dest_country VARCHAR(2), dest_state_abr VARCHAR(2), dest_state_nm VARCHAR(20), bulk_fare INT, passengers FLOAT(3,2), market_fare FLOAT(6,2), market_miles_flown FLOAT(6,2)
);
LOAD DATA LOCAL INFILE '/Users/junwonchoi/Desktop/DataRes/BruinAir/2018Q3.csv' INTO TABLE Q3_2018 COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;
CREATE TABLE Q2_2018 (
year INT, quarter INT, origin_airport_id INT, origin_airport_seq_id INT, origin_city_market_id INT, origin VARCHAR(3), origin_country VARCHAR(2), origin_state_abr VARCHAR(2), origin_state_nm VARCHAR(20), dest_airport_id INT, dest_airport__seq_id INT, dest_city_market_id INT, dest VARCHAR(3), dest_country VARCHAR(2), dest_state_abr VARCHAR(2), dest_state_nm VARCHAR(20), bulk_fare INT, passengers FLOAT(3,2), market_fare FLOAT(6,2), market_miles_flown FLOAT(6,2)
);
LOAD DATA LOCAL INFILE '/Users/junwonchoi/Desktop/DataRes/BruinAir/2018Q2.csv' INTO TABLE Q2_2018 COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;
DELETE FROM Q2_2022 WHERE origin <> "LAX";
DELETE FROM Q1_2022 WHERE origin <> "LAX";
DELETE FROM Q4_2021 WHERE origin <> "LAX";
DELETE FROM Q3_2021 WHERE origin <> "LAX";
DELETE FROM Q2_2021 WHERE origin <> "LAX";
DELETE FROM Q1_2021 WHERE origin <> "LAX";
DELETE FROM Q4_2020 WHERE origin <> "LAX";
DELETE FROM Q3_2020 WHERE origin <> "LAX";
DELETE FROM Q2_2020 WHERE origin <> "LAX";
DELETE FROM Q1_2020 WHERE origin <> "LAX";
DELETE FROM Q4_2019 WHERE origin <> "LAX";
DELETE FROM Q3_2019 WHERE origin <> "LAX";
DELETE FROM Q2_2019 WHERE origin <> "LAX";
DELETE FROM Q1_2019 WHERE origin <> "LAX";
DELETE FROM Q4_2018 WHERE origin <> "LAX";
DELETE FROM Q3_2018 WHERE origin <> "LAX";
DELETE FROM Q2_2018 WHERE origin <> "LAX";
SELECT * FROM Q2_2022
UNION ALL
SELECT * FROM Q1_2022
UNION ALL
SELECT * FROM Q4_2021
UNION ALL
SELECT * FROM Q3_2021
UNION ALL
SELECT * FROM Q2_2021
UNION ALL
SELECT * FROM Q1_2021
UNION ALL
SELECT * FROM Q4_2020
UNION ALL
SELECT * FROM Q3_2020
UNION ALL
SELECT * FROM Q2_2020
UNION ALL
SELECT * FROM Q1_2020
UNION ALL
SELECT * FROM Q4_2019
UNION ALL
SELECT * FROM Q3_2019
UNION ALL
SELECT * FROM Q2_2019
UNION ALL
SELECT * FROM Q1_2019
UNION ALL
SELECT * FROM Q4_2018
UNION ALL
SELECT * FROM Q3_2018
UNION ALL
SELECT * FROM Q2_2018;