# 车辆表
CREATE TABLE VEHICLE (
VEHICLE_ID VARCHAR(32) NOT NULL,
PLATE_NUMBER VARCHAR(20) NOT NULL,
VEHICLE_TYPE VARCHAR(20) NOT NULL,
BRAND VARCHAR(50),
MODEL VARCHAR(50),
OWNER_NAME VARCHAR(100),
OWNER_PHONE VARCHAR(20),
COLOR VARCHAR(20),
REGISTER_DATE DATE,
INSPECTION_DATE DATE,
VEHICLE_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_VEHICLE PRIMARY KEY (VEHICLE_ID)
) ORGANIZE BY ROW;

# 道路表
CREATE TABLE ROAD (
ROAD_ID VARCHAR(32) NOT NULL,
ROAD_NAME VARCHAR(100) NOT NULL,
ROAD_TYPE VARCHAR(20),
START_POINT VARCHAR(200),
END_POINT VARCHAR(200),
LENGTH DECIMAL(10, 2),
SPEED_LIMIT INTEGER,
LANE_COUNT INTEGER,
ROAD_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_ROAD PRIMARY KEY (ROAD_ID)
) ORGANIZE BY ROW;

# 路口表
CREATE TABLE INTERSECTION (
INTERSECTION_ID VARCHAR(32) NOT NULL,
INTERSECTION_NAME VARCHAR(100) NOT NULL,
LATITUDE DECIMAL(10, 6),
LONGITUDE DECIMAL(10, 6),
CONTROL_TYPE VARCHAR(20),
INTERSECTION_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_INTERSECTION PRIMARY KEY (INTERSECTION_ID)
) ORGANIZE BY ROW;

# 创建索引
CREATE UNIQUE INDEX IDX_VEHICLE_PLATE ON VEHICLE(PLATE_NUMBER);
CREATE INDEX IDX_VEHICLE_TYPE ON VEHICLE(VEHICLE_TYPE, VEHICLE_STATUS);
CREATE INDEX IDX_ROAD_TYPE ON ROAD(ROAD_TYPE, ROAD_STATUS);
CREATE INDEX IDX_INTERSECTION_LOCATION ON INTERSECTION(LATITUDE, LONGITUDE);

# 车辆轨迹表
CREATE TABLE VEHICLE_TRACK (
TRACK_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
VEHICLE_ID VARCHAR(32) NOT NULL,
LATITUDE DECIMAL(10, 6) NOT NULL,
LONGITUDE DECIMAL(10, 6) NOT NULL,
SPEED DECIMAL(5, 2),
DIRECTION INTEGER,
TRACK_TIME TIMESTAMP NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_VEHICLE_TRACK PRIMARY KEY (TRACK_ID)
) PARTITION BY RANGE (TRACK_TIME)
(
STARTING ‘2026-01-01-00.00.00.000000’
ENDING ‘2026-12-31-23.59.59.999999’
EVERY 1 HOURS
);

# 流量表
CREATE TABLE TRAFFIC_FLOW (
FLOW_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
ROAD_ID VARCHAR(32) NOT NULL,
INTERSECTION_ID VARCHAR(32),
DIRECTION VARCHAR(20),
VEHICLE_COUNT INTEGER NOT NULL,
AVG_SPEED DECIMAL(5, 2),
CONGESTION_LEVEL VARCHAR(20),
FLOW_TIME TIMESTAMP NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_TRAFFIC_FLOW PRIMARY KEY (FLOW_ID)
) PARTITION BY RANGE (FLOW_TIME)
(
STARTING ‘2026-01-01-00.00.00.000000’
ENDING ‘2026-12-31-23.59.59.999999’
EVERY 5 MINUTES
);

# 创建索引
CREATE INDEX IDX_VEHICLE_TRACK_VEHICLE ON VEHICLE_TRACK(VEHICLE_ID, TRACK_TIME DESC) PARTITIONED;
CREATE INDEX IDX_TRAFFIC_FLOW_ROAD ON TRAFFIC_FLOW(ROAD_ID, FLOW_TIME DESC) PARTITIONED;
CREATE INDEX IDX_TRAFFIC_FLOW_TIME ON TRAFFIC_FLOW(FLOW_TIME DESC) PARTITIONED;

# 添加空间列
ALTER TABLE ROAD ADD COLUMN SHAPE DB2GSE.ST_LINESTRING;
ALTER TABLE INTERSECTION ADD COLUMN LOCATION DB2GSE.ST_POINT;

# 更新空间数据
UPDATE INTERSECTION SET
LOCATION = DB2GSE.ST_POINT(LONGITUDE, LATITUDE, 1)
WHERE LONGITUDE IS NOT NULL AND LATITUDE IS NOT NULL;

# 创建空间索引
CREATE INDEX IDX_INTERSECTION_LOCATION_SPATIAL ON INTERSECTION(LOCATION)
EXTEND USING DB2GSE.SPATIAL_INDEX;

# 查询附近路口
SELECT
INTERSECTION_ID,
INTERSECTION_NAME,
DB2GSE.ST_DISTANCE(LOCATION, DB2GSE.ST_POINT(116.404, 39.915, 1), ‘METERS’) AS DISTANCE
FROM INTERSECTION
WHERE DB2GSE.ST_WITHIN_DISTANCE(
LOCATION,
DB2GSE.ST_POINT(116.404, 39.915, 1),
1000,
‘METERS’
) = 1
ORDER BY DISTANCE;

# 查询道路流量
SELECT
r.ROAD_ID,
r.ROAD_NAME,
AVG(f.AVG_SPEED) AS AVG_SPEED,
SUM(f.VEHICLE_COUNT) AS TOTAL_VEHICLES,
MAX(f.CONGESTION_LEVEL) AS MAX_CONGESTION
FROM ROAD r
INNER JOIN TRAFFIC_FLOW f ON r.ROAD_ID = f.ROAD_ID
WHERE f.FLOW_TIME BETWEEN CURRENT TIMESTAMP – 1 HOUR AND CURRENT TIMESTAMP
GROUP BY r.ROAD_ID, r.ROAD_NAME
ORDER BY TOTAL_VEHICLES DESC;

# 流量统计MQT
CREATE TABLE MQT_TRAFFIC_DAILY AS (
SELECT
DATE(FLOW_TIME) AS FLOW_DATE,
ROAD_ID,
DIRECTION,
AVG(AVG_SPEED) AS AVG_SPEED,
SUM(VEHICLE_COUNT) AS TOTAL_VEHICLES,
COUNT(*) AS RECORD_COUNT
FROM TRAFFIC_FLOW
GROUP BY DATE(FLOW_TIME), ROAD_ID, DIRECTION
) DATA INITIALLY DEFERRED REFRESH DEFERRED
ORGANIZE BY COLUMN;

REFRESH TABLE MQT_TRAFFIC_DAILY;

# 日流量统计
SELECT
FLOW_DATE,
r.ROAD_NAME,
DIRECTION,
AVG_SPEED,
TOTAL_VEHICLES,
RECORD_COUNT
FROM MQT_TRAFFIC_DAILY t
INNER JOIN ROAD r ON t.ROAD_ID = r.ROAD_ID
WHERE FLOW_DATE BETWEEN ‘2026-04-01’ AND ‘2026-04-07’
ORDER BY FLOW_DATE, TOTAL_VEHICLES DESC;

# 拥堵分析
SELECT
r.ROAD_NAME,
f.CONGESTION_LEVEL,
COUNT(*) AS OCCURRENCE_COUNT,
ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM TRAFFIC_FLOW
WHERE ROAD_ID = r.ROAD_ID
AND FLOW_TIME BETWEEN CURRENT TIMESTAMP – 7 DAYS AND CURRENT TIMESTAMP), 2) AS PERCENTAGE
FROM TRAFFIC_FLOW f
INNER JOIN ROAD r ON f.ROAD_ID = r.ROAD_ID
WHERE f.FLOW_TIME BETWEEN CURRENT TIMESTAMP – 7 DAYS AND CURRENT TIMESTAMP
GROUP BY r.ROAD_NAME, f.CONGESTION_LEVEL
ORDER BY r.ROAD_NAME, OCCURRENCE_COUNT DESC;