# 网点表
CREATE TABLE SITE (
SITE_ID VARCHAR(20) NOT NULL,
SITE_NAME VARCHAR(100) NOT NULL,
SITE_TYPE VARCHAR(20) NOT NULL,
ADDRESS VARCHAR(200),
CITY VARCHAR(50),
PROVINCE VARCHAR(50),
POSTAL_CODE VARCHAR(10),
CONTACT_PERSON VARCHAR(50),
CONTACT_PHONE VARCHAR(20),
LATITUDE DECIMAL(10, 6),
LONGITUDE DECIMAL(10, 6),
SITE_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_SITE PRIMARY KEY (SITE_ID)
) ORGANIZE BY ROW;

# 车辆表
CREATE TABLE VEHICLE (
VEHICLE_ID VARCHAR(20) NOT NULL,
PLATE_NUMBER VARCHAR(20) NOT NULL,
VEHICLE_TYPE VARCHAR(20) NOT NULL,
BRAND VARCHAR(50),
MODEL VARCHAR(50),
CAPACITY DECIMAL(10, 2),
LOAD_WEIGHT DECIMAL(10, 2),
CURRENT_SITE_ID VARCHAR(20),
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 DRIVER (
DRIVER_ID VARCHAR(20) NOT NULL,
DRIVER_NAME VARCHAR(50) NOT NULL,
ID_CARD VARCHAR(20) NOT NULL,
PHONE VARCHAR(20),
LICENSE_TYPE VARCHAR(20),
LICENSE_NUMBER VARCHAR(50),
CURRENT_SITE_ID VARCHAR(20),
DRIVER_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_DRIVER PRIMARY KEY (DRIVER_ID)
) ORGANIZE BY ROW;

# 创建索引
CREATE INDEX IDX_SITE_CITY ON SITE(CITY, SITE_STATUS);
CREATE INDEX IDX_VEHICLE_SITE ON VEHICLE(CURRENT_SITE_ID, VEHICLE_STATUS);
CREATE INDEX IDX_DRIVER_SITE ON DRIVER(CURRENT_SITE_ID, DRIVER_STATUS);

# 运单主表
CREATE TABLE WAYBILL (
WAYBILL_NO VARCHAR(32) NOT NULL,
ORDER_NO VARCHAR(32),
SENDER_NAME VARCHAR(100),
SENDER_PHONE VARCHAR(20),
SENDER_ADDRESS VARCHAR(200),
SENDER_CITY VARCHAR(50),
RECEIVER_NAME VARCHAR(100),
RECEIVER_PHONE VARCHAR(20),
RECEIVER_ADDRESS VARCHAR(200),
RECEIVER_CITY VARCHAR(50),
GOODS_NAME VARCHAR(200),
GOODS_WEIGHT DECIMAL(10, 2),
GOODS_VOLUME DECIMAL(10, 2),
GOODS_QUANTITY INTEGER,
SHIPPING_TYPE VARCHAR(20),
TOTAL_FEE DECIMAL(10, 2),
PAYMENT_TYPE VARCHAR(20),
WAYBILL_STATUS VARCHAR(20) NOT NULL,
CREATE_SITE_ID VARCHAR(20),
CURRENT_SITE_ID VARCHAR(20),
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_WAYBILL PRIMARY KEY (WAYBILL_NO)
) ORGANIZE BY ROW;

# 运单状态轨迹表
CREATE TABLE WAYBILL_TRACK (
TRACK_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
WAYBILL_NO VARCHAR(32) NOT NULL,
STATUS VARCHAR(20) NOT NULL,
STATUS_DESC VARCHAR(200),
SITE_ID VARCHAR(20),
OPERATOR_ID VARCHAR(20),
OPERATOR_NAME VARCHAR(50),
LATITUDE DECIMAL(10, 6),
LONGITUDE DECIMAL(10, 6),
TRACK_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_WAYBILL_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 MONTH
);

# 创建索引
CREATE UNIQUE INDEX IDX_WAYBILL_NO ON WAYBILL(WAYBILL_NO);
CREATE INDEX IDX_WAYBILL_STATUS ON WAYBILL(WAYBILL_STATUS, UPDATE_TIME DESC);
CREATE INDEX IDX_WAYBILL_CURRENT_SITE ON WAYBILL(CURRENT_SITE_ID, WAYBILL_STATUS);
CREATE INDEX IDX_WAYBILL_TRACK_WAYBILL ON WAYBILL_TRACK(WAYBILL_NO, TRACK_TIME DESC) PARTITIONED;
CREATE INDEX IDX_WAYBILL_TRACK_SITE ON WAYBILL_TRACK(SITE_ID, TRACK_TIME) PARTITIONED;

# 调度单表
CREATE TABLE DISPATCH (
DISPATCH_ID VARCHAR(32) NOT NULL,
VEHICLE_ID VARCHAR(20) NOT NULL,
DRIVER_ID VARCHAR(20),
FROM_SITE_ID VARCHAR(20) NOT NULL,
TO_SITE_ID VARCHAR(20) NOT NULL,
PLAN_DEPART_TIME TIMESTAMP,
PLAN_ARRIVE_TIME TIMESTAMP,
ACTUAL_DEPART_TIME TIMESTAMP,
ACTUAL_ARRIVE_TIME TIMESTAMP,
DISPATCH_STATUS VARCHAR(20) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_DISPATCH PRIMARY KEY (DISPATCH_ID)
) ORGANIZE BY ROW;

# 调度明细表
CREATE TABLE DISPATCH_DETAIL (
DETAIL_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
DISPATCH_ID VARCHAR(32) NOT NULL,
WAYBILL_NO VARCHAR(32) NOT NULL,
LOAD_ORDER INTEGER,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_DISPATCH_DETAIL PRIMARY KEY (DETAIL_ID)
) ORGANIZE BY ROW;

# 车辆位置轨迹表
CREATE TABLE VEHICLE_TRACK (
TRACK_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
VEHICLE_ID VARCHAR(20) 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 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 DAY
);

# 创建索引
CREATE INDEX IDX_DISPATCH_VEHICLE ON DISPATCH(VEHICLE_ID, DISPATCH_STATUS);
CREATE INDEX IDX_DISPATCH_SITES ON DISPATCH(FROM_SITE_ID, TO_SITE_ID, DISPATCH_STATUS);
CREATE INDEX IDX_DISPATCH_DETAIL_DISPATCH ON DISPATCH_DETAIL(DISPATCH_ID);
CREATE INDEX IDX_VEHICLE_TRACK_VEHICLE ON VEHICLE_TRACK(VEHICLE_ID, TRACK_TIME DESC) PARTITIONED;

# 创建空间列
ALTER TABLE SITE ADD COLUMN LOCATION DB2GSE.ST_POINT;

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

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

# 车辆位置空间表
ALTER TABLE VEHICLE_TRACK ADD COLUMN LOCATION DB2GSE.ST_POINT;

UPDATE VEHICLE_TRACK SET
LOCATION = DB2GSE.ST_POINT(LONGITUDE, LATITUDE, 1);

CREATE INDEX IDX_VEHICLE_TRACK_LOCATION ON VEHICLE_TRACK(LOCATION)
EXTEND USING DB2GSE.SPATIAL_INDEX PARTITIONED;

# 查找附近网点
SELECT
SITE_ID,
SITE_NAME,
ADDRESS,
DB2GSE.ST_DISTANCE(LOCATION, DB2GSE.ST_POINT(116.404, 39.915, 1)) AS DISTANCE_METERS
FROM SITE
WHERE DB2GSE.ST_WITHIN_DISTANCE(
LOCATION,
DB2GSE.ST_POINT(116.404, 39.915, 1),
5000,
‘METERS’
) = 1
ORDER BY DISTANCE_METERS;

# 查找区域内车辆
SELECT
v.VEHICLE_ID,
v.PLATE_NUMBER,
vt.TRACK_TIME,
vt.LATITUDE,
vt.LONGITUDE
FROM VEHICLE v
INNER JOIN (
SELECT
VEHICLE_ID,
LATITUDE,
LONGITUDE,
TRACK_TIME,
ROW_NUMBER() OVER (PARTITION BY VEHICLE_ID ORDER BY TRACK_TIME DESC) AS RN
FROM VEHICLE_TRACK
WHERE TRACK_TIME > CURRENT TIMESTAMP – 1 HOUR
) vt ON v.VEHICLE_ID = vt.VEHICLE_ID AND vt.RN = 1
WHERE DB2GSE.ST_WITHIN(
vt.LOCATION,
DB2GSE.ST_POLYGON(
‘POLYGON((116.0 39.5, 116.0 40.0, 116.8 40.0, 116.8 39.5, 116.0 39.5))’,
1
)
) = 1;

# 计算两点间距离
SELECT
DB2GSE.ST_DISTANCE(
DB2GSE.ST_POINT(116.404, 39.915, 1),
DB2GSE.ST_POINT(121.473, 31.230, 1),
‘KILOMETERS’
) AS DISTANCE_KM
FROM SYSIBM.SYSDUMMY1;

# 创建运单跟踪视图
CREATE OR REPLACE VIEW V_WAYBILL_TRACK AS
SELECT
w.WAYBILL_NO,
w.ORDER_NO,
w.SENDER_NAME,
w.RECEIVER_NAME,
w.WAYBILL_STATUS,
t.STATUS,
t.STATUS_DESC,
t.SITE_ID,
s.SITE_NAME,
t.OPERATOR_NAME,
t.LATITUDE,
t.LONGITUDE,
t.TRACK_TIME
FROM WAYBILL w
LEFT JOIN WAYBILL_TRACK t ON w.WAYBILL_NO = t.WAYBILL_NO
LEFT JOIN SITE s ON t.SITE_ID = s.SITE_ID;

# 运单跟踪查询
SELECT * FROM V_WAYBILL_TRACK
WHERE WAYBILL_NO = ‘WB2026040800001’
ORDER BY TRACK_TIME DESC;

# 创建存储过程
CREATE OR REPLACE PROCEDURE SP_GET_WAYBILL_TRACK(
IN p_waybill_no VARCHAR(32),
OUT p_result_code VARCHAR(10),
OUT p_result_msg VARCHAR(200)
)
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE v_count INTEGER;

SET p_result_code = ‘SUCCESS’;
SET p_result_msg = ‘查询成功’;

SELECT COUNT(*) INTO v_count FROM WAYBILL WHERE WAYBILL_NO = p_waybill_no;

IF v_count = 0 THEN
SET p_result_code = ‘FAIL’;
SET p_result_msg = ‘运单不存在’;
RETURN;
END IF;

BEGIN
DECLARE c1 CURSOR WITH RETURN FOR
SELECT
t.TRACK_ID,
t.STATUS,
t.STATUS_DESC,
t.SITE_ID,
s.SITE_NAME,
t.OPERATOR_NAME,
t.LATITUDE,
t.LONGITUDE,
t.TRACK_TIME
FROM WAYBILL_TRACK t
LEFT JOIN SITE s ON t.SITE_ID = s.SITE_ID
WHERE t.WAYBILL_NO = p_waybill_no
ORDER BY t.TRACK_TIME DESC;

OPEN c1;
END;
END;

# 调用存储过程
CALL SP_GET_WAYBILL_TRACK(‘WB2026040800001’, ?, ?);

# 创建运单统计MQT
CREATE TABLE MQT_WAYBILL_STAT AS (
SELECT
CURRENT_SITE_ID,
WAYBILL_STATUS,
COUNT(*) AS COUNT,
SUM(TOTAL_FEE) AS TOTAL_FEE_SUM
FROM WAYBILL
GROUP BY CURRENT_SITE_ID, WAYBILL_STATUS
) DATA INITIALLY DEFERRED REFRESH DEFERRED;

REFRESH TABLE MQT_WAYBILL_STAT;

# 创建索引
CREATE INDEX IDX_MQT_WAYBILL_SITE ON MQT_WAYBILL_STAT(CURRENT_SITE_ID, WAYBILL_STATUS);

# 按时间统计MQT
CREATE TABLE MQT_WAYBILL_DAILY AS (
SELECT
DATE(CREATE_TIME) AS CREATE_DATE,
CREATE_SITE_ID,
COUNT(*) AS COUNT,
SUM(TOTAL_FEE) AS TOTAL_FEE_SUM
FROM WAYBILL
GROUP BY DATE(CREATE_TIME), CREATE_SITE_ID
) DATA INITIALLY DEFERRED REFRESH DEFERRED;

REFRESH TABLE MQT_WAYBILL_DAILY;

# 自动刷新MQT
CREATE OR REPLACE PROCEDURE SP_REFRESH_MQTS()
LANGUAGE SQL
BEGIN
REFRESH TABLE MQT_WAYBILL_STAT;
REFRESH TABLE MQT_WAYBILL_DAILY;
END;