-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathinit-script.sql
More file actions
116 lines (102 loc) · 4.07 KB
/
Copy pathinit-script.sql
File metadata and controls
116 lines (102 loc) · 4.07 KB
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
DROP TABLE IF EXISTS `dw_weather`;
DROP TABLE IF EXISTS `dw_station_means`;
DROP TABLE IF EXISTS `dw_station_sampled`;
DROP TABLE IF EXISTS `dw_station_state`;
DROP TABLE IF EXISTS `dw_station`;
DROP TABLE IF EXISTS `dw_city`;
SET sql_mode = '';
# Cleaned city list
CREATE TABLE `DW_city` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
# Cleaned static stations infos
CREATE TABLE `DW_station` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`station_number` int(11) DEFAULT NULL,
`city_id` int(11) DEFAULT NULL,
`station_name` varchar(255) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`banking` tinyint(4) DEFAULT NULL,
`bonus` tinyint(4) DEFAULT NULL,
`latitude` float DEFAULT NULL,
`longitude` float DEFAULT NULL,
`elevation` float DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `city_id_idx` (`city_id`),
CONSTRAINT `city_id` FOREIGN KEY (`city_id`) REFERENCES `DW_city` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
# Cleaned weather
CREATE TABLE `DW_weather` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`city_id` int(11) DEFAULT NULL,
`weather_group` varchar(45) DEFAULT NULL,
`pressure` int(11) DEFAULT NULL,
`humidity_percentage` float DEFAULT NULL,
`temperature` float DEFAULT NULL,
`min_temperature` float DEFAULT NULL,
`max_temperature` float DEFAULT NULL,
`wind_speed` float DEFAULT NULL,
`wind_direction` float DEFAULT NULL,
`cloudiness_percentage` int(11) DEFAULT NULL,
`rain_quantity` int(11) DEFAULT NULL,
`snow_quantity` int(11) DEFAULT NULL,
`sun_set` int(11) DEFAULT NULL,
`sun_rise` int(11) DEFAULT NULL,
`calculation_time` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `city_id_idx` (`city_id`),
CONSTRAINT `city_id2` FOREIGN KEY (`city_id`) REFERENCES `DW_city` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
# Cleaned states per station
CREATE TABLE `DW_station_state` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_station` int(11) DEFAULT NULL,
`status` tinyint(4) DEFAULT NULL,
`operational_bike_stands` int(11) DEFAULT NULL,
`available_bike_stands` int(11) DEFAULT NULL,
`available_bikes` int(11) DEFAULT NULL,
`last_update` bigint(20) DEFAULT NULL,
`movements` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX station_time (id_station,last_update),
KEY `id_station_idx` (`id_station`),
CONSTRAINT `id_st` FOREIGN KEY (`id_station`) REFERENCES `DW_station` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
# Global means for each station, divided by day-time ranges
CREATE TABLE `DW_station_means` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_station` int(11) NOT NULL,
`week_day` int(3) DEFAULT NULL,
`range_start` int(11) DEFAULT NULL,
`range_end` int(11) DEFAULT NULL,
`movement_mean` float DEFAULT NULL,
`availability_mean` float DEFAULT NULL,
`velib_nb_mean` float DEFAULT NULL,
`movement_mean_rain` float DEFAULT NULL,
`movement_mean_sun` float DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id_station_idx` (`id_station`),
CONSTRAINT `id_st2` FOREIGN KEY (`id_station`) REFERENCES `DW_station` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
# Sampled data table, generated from DW_station_state
CREATE TABLE `DW_station_sampled` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`id_station` int(11) NOT NULL,
`timestamp_start` int(11) DEFAULT NULL,
`timestamp_end` int(11) DEFAULT NULL,
`movement_mean` float DEFAULT NULL,
`availability_mean` float DEFAULT NULL,
`velib_nb_mean` float DEFAULT NULL,
`weather` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX station_time (id_station,timestamp_start),
KEY `id_station_idx` (`id_station`),
CONSTRAINT `id_st3` FOREIGN KEY (`id_station`) REFERENCES `DW_station` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `MS_DataCleaning_conf` (
`name` varchar(45) NOT NULL,
`value` varchar(45) DEFAULT NULL,
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;