-- Holiday statistics module. Run against the traffic_audit database.
|
CREATE TABLE IF NOT EXISTS holiday_calendar (
|
id BIGINT NOT NULL AUTO_INCREMENT,
|
year INT NOT NULL,
|
holiday_type VARCHAR(32) NOT NULL,
|
holiday_date DATE NOT NULL,
|
day_index INT NOT NULL,
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
PRIMARY KEY (id),
|
UNIQUE KEY uk_holiday_calendar_seq (year, holiday_type, day_index),
|
UNIQUE KEY uk_holiday_calendar_date (year, holiday_type, holiday_date),
|
KEY idx_holiday_calendar_date (holiday_date)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
CREATE TABLE IF NOT EXISTS holiday_passenger_flow (
|
id BIGINT NOT NULL AUTO_INCREMENT,
|
year INT NOT NULL,
|
flow_date DATE NOT NULL,
|
transport_mode VARCHAR(32) NOT NULL,
|
passenger_count DECIMAL(30,15) NOT NULL,
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
PRIMARY KEY (id),
|
UNIQUE KEY uk_holiday_passenger (year, flow_date, transport_mode),
|
KEY idx_holiday_passenger_date (flow_date)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
CREATE TABLE IF NOT EXISTS holiday_vehicle_flow (
|
id BIGINT NOT NULL AUTO_INCREMENT,
|
year INT NOT NULL,
|
flow_date DATE NOT NULL,
|
road_category VARCHAR(32) NOT NULL,
|
vehicle_type VARCHAR(32) NOT NULL,
|
vehicle_count DECIMAL(30,15) NOT NULL,
|
comparison_base DECIMAL(30,15) NULL COMMENT '普通国省道环比基期（万辆次，仅当年使用）',
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
PRIMARY KEY (id),
|
UNIQUE KEY uk_holiday_vehicle (year, flow_date, road_category, vehicle_type),
|
KEY idx_holiday_vehicle_date (flow_date)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
CREATE TABLE IF NOT EXISTS holiday_passenger_coefficient (
|
id BIGINT NOT NULL AUTO_INCREMENT,
|
year INT NOT NULL,
|
date_category VARCHAR(32) NOT NULL,
|
coefficient DECIMAL(30,15) NOT NULL,
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
PRIMARY KEY (id),
|
UNIQUE KEY uk_holiday_coefficient (year, date_category)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
