---
# System prepended metadata

title: 系統總架構

---

# 系統架構

## 架構總覽
- [程式碼](https://github.com/project-team-2025/highway-predictor/tree/local/work-with-codex)。目前branch：`local/work-with-codex`
- **前端(純 HTML / CSS / JavaScript)**：
顯示
    - [前端figma連結](https://www.figma.com/design/FgZq5YAy7cj7r8uyYHmiM6/%E9%AB%98%E9%80%9F%E5%85%AC%E8%B7%AF%E4%BA%A4%E9%80%9A%E5%A3%85%E5%A1%9E%E9%A0%90%E6%B8%AC%E6%A8%A1%E5%9E%8B-%E5%89%8D%E7%AB%AF?node-id=33-104&t=WkB4fRjKVZ6ZH8NN-0)
    - [文件說明](https://hackmd.io/nAaJy5VKSXimSF1uvaV6Tw#%E4%BB%8B%E9%9D%A2)
    - 模型：文字(長度1~10個英文字)
    - 路段：文字(長度2~20個中文字)
    - 預測結果(可能有好幾個，先預設3個)
        - 範例：ttc、speed1、speed2
- **資料庫**：
  - 共 3 張資料表，分別儲存原始數據、清洗/處理後的資料、模型預測結果。[與上述資料相關的操作](#與上述資料相關的操作)
  - `database.py`：負責與資料庫相關的操作。所有與資料庫的互動必須透過本程式，其他程式不得直接存取資料庫。
- **後端（Python）**：包成Docker.
  1. 爬蟲(`crawler.py`)：抓取最新資料並存到資料庫。
  2. 資料清洗、處理(`data_processor.py`)：從資料庫處理原始資料並存回資料庫。
  3. 模型預測(`predict.py`)：預測數值並存到資料庫。
  4. API(FastAPI)(`app.py`)：`GET /api/data`：從資料庫撈資料給前端。
  5. 排程(`scheduler.py`)：負責在適當時機呼叫`crawler.py`、`data_processor.py`、`predict.py`程式。比如`crawler.py`成功抓取最新資料，則需要呼叫`data_processor.py`接著處理。

    - ![後端總架構圖](https://hackmd.io/_uploads/HyEfNzCyZg.gif)
    - ![爬蟲呼叫邏輯](https://hackmd.io/_uploads/H1d27nYbWg.svg)


---

## API：GET /api/data

### 功能
回傳單一(最新)時間點的資料。

### Query Parameters

- 無

### Response

#### 200 Success
```json
{
  "timestamp": "2025-07-20T09:00:00Z",
  "predictResults": {
    "RNN": {
      "ttcttf": "",
      "speed5": "",
      "speed10": "",
      "speed15": "",
      "speed30": "",
      "level5": "",
      "level10": "",
      "level15": "",
      "level30": ""
    },
    "LSTM": {
      "ttcttf": "",
      "speed5": "",
      "speed10": "",
      "speed15": "",
      "speed30": "",
      "level5": "",
      "level10": "",
      "level15": "",
      "level30": ""
    },
    "GRU": {
      "ttcttf": "",
      "speed5": "",
      "speed10": "",
      "speed15": "",
      "speed30": "",
      "level5": "",
      "level10": "",
      "level15": "",
      "level30": ""
    },
    "CNN": {
      "ttcttf": "",
      "speed5": "",
      "speed10": "",
      "speed15": "",
      "speed30": "",
      "level5": "",
      "level10": "",
      "level15": "",
      "level30": ""
    },
    "GBRT": {
      "ttcttf": "",
      "speed5": "",
      "speed10": "",
      "speed15": "",
      "speed30": "",
      "level5": "",
      "level10": "",
      "level15": "",
      "level30": ""
    }
  },
  "traficState": ""
}

```

- 解釋：
    - `timestamp`為時間，只這筆預測值是屬於哪個時間點的，換句話說，其中「預測5分鐘後車速」的資料是預測`timestamp`+5分鐘這個時刻的，前端讚時不用管他，反正就把各數值填到對應的地方就對了。
    - "predictResults"，內為各模型各預測目標的數值
        - "ttcttf": ttc或ttf,
          "speed5": 5分鐘後車速,
          "speed10": 10分鐘後車速,
          "speed15": 15分鐘後車速,
          "speed30": 30分鐘後車速,
          "level5": 5分鐘後車速等級,
          "level10": 10分鐘後車速等級,
          "level15": 15分鐘後車速等級,
          "level30": 30分鐘後車速等級
    - "traficState": 0為目前通暢，1為目前雍塞，用於網頁顯示當前狀態。
    - 各數值(例如"speed5")型別都是小數、整數，或者回傳空字串(`""`)，此時前端需進行處理，避免報錯。
    - `get_predict()`回傳之`ttc`、`ttf`，必有其一為None(NULL)，以此判斷`traficState`
        - 看LSTM的
        - 若ttc為None： `traficState`為1
        - 反之ttf為None: `traficState`為0
    - `speed`需確保>=0，並取整數。
    - `level`需確保介於0~5之間(含)並四捨五入為整數。
    - `ttcttf`則為`ttc`、`ttf`兩者非None的一方之數值，須確保>0、<=37。
    - 若有數值為None(`ttcttf`則是`ttc`、`ttf`皆為None)，則該值回傳空字串(`""`)


- 補充：門架距離是`1.6`km，預計行車時間就直接拿1.6/speed，然後換算成分鐘。

- 以下為舊版本
```json
{
  "segment_id": "A123",
  "timestamp": "2025-07-20T09:00:00Z",
  "model_id": "xgboost_v1",
  "type": "actual",
  "features": {
    "speed": 75.2,
    "travel_time": 4.8,
    … //找的到的feature都傳
  }
}
```

#### 400 Bad Request
<!-- 少傳或多傳參數
```json
{
  "error": "Bad Request",
  "message": "Missing or invalid parameters",
  "invalid_params": ["timestamp", …] //缺少跟多餘的傳入參數
}
``` -->

#### 404 Not Found
<!-- 查無指定資料
```json
{
  "error": "Not Found",
  "message": "No data for {timestamp}, {segment_id}, {type} and {model_id}."
}
``` -->

## 資料流定義

### 資料表

1. 原始資料表
僅把xml或csv中特定的欄位取出轉換為二維表格，除此之外**不做**任何處理。每個資料集一個獨立表格。所有欄位的格式在本資料表中都以`string、int64, float64`儲存。
2. 特徵資料表
根據各模型的需要把`原始資料`經過所有必需的計算與清洗，包含補缺失值、取平均等。總共只有一個表格，primary key為間隔5分鐘且符合[ISO 8601 UTC](https://www.timestamp-converter.com/)的時間，該時間表示資料的蒐集時間(非檔案時間)。
> 例如有一筆資料a來自檔名為`2024/1/1 13:05`的原始檔案，但它記錄的是`2024/1/1 13:00`的資料，則應該在特徵資料中紀錄為`2024/1/1 13:00`。
3. 預測結果資料表
記錄各模型的預測結果。

### 資料表命名規則

- 原始資料表
orgin + 資料集名稱，以下滑線分隔。

> 例如資料集`VD 動態資訊(v2.0)`的原始資料表應該命名為`orgin_vd`。
> 例如資料集`站間各車種中位數行駛車速 (M05A)`的原始資料表應該命名為`orgin_m05`。

- 特徵資料表
只有一個表，所有資料集、模型共用，名稱為`feature`。

- 預測結果資料表
只有一個表，所有資料集、模型共用，名稱為`predict`。

### 欄位命名規則

- 原始資料表
原始欄位完整名稱。
> 例如資料集`vd`的原始xml中該欄位名稱應為`speed`，則在原始資料表中應該命名為`speed`。

- 特徵資料表
直接定義，請見範例。名稱中包含vd代表由vd計算而得，未包含則是由m05計算而得。
另新增兩個欄位vd_finished、m05_finished，為bool，分別記錄vd與m05資料在該row是否已經完成資料處理
<!-- 資料集名稱 + 特徵名稱
> 例如由vd的原始資料表中計算而得的`speed_avg`，應該命名為`vd_speed_avg`。 -->

- 預測結果資料表
模型編號 + 特徵資料表中的feature名稱 + predict + 時間備註(可選)，以下滑線分隔。

> 例如由模型編號A預測speed_avg，應該命名為`A_speed_avg_predict`。
> 例如由模型編號A預測15分鐘後的speed_avg，應該命名為`A_speed_avg_predict_15min`。

### 與上述資料相關的操作
1. 爬蟲
&rarr; 原始資料表
1. 資料清洗
原始資料表 &rarr; 特徵資料表
1. 預測
特徵資料表 &rarr; 預測結果資料表
3. api回應
預測結果資料表 &rarr;

### 資料流範例
a. 原始資料表
- orgin_vd：
[完整範例檔案](https://1drv.ms/x/c/002d06ef873e3a00/EZhbL6ICF0ZFpwhnmsLHbrcBWe-zjWGc4CzHRJTwDX5h4w?e=NsO7WK)

|VDID|DataCollectTime|LinkID|LaneID|LaneType|Speed|Occupancy|VehicleType|Volume|VehicleSpeed|
|---|---|---|---|---|---|---|---|---|---|
|VD-N1-S-25.850-M-LOOP|2024-01-01 00:00:00|0000100002560A|0|2|91|1|S|1|90|
|VD-N1-S-25.850-M-LOOP|2024-01-01 00:00:00|0000100002560A|0|2|91|1|L|0|0|
|VD-N1-S-25.850-M-LOOP|2024-01-01 00:00:00|0000100002560A|0|2|91|1|T|0|0|
|...|...|...|...|...|...|...|...|...|...|
|VD-N1-N-28.670-M-LOOP|2024-01-01 00:04:00|0000100102800F|3|2|0|0|L|0|0|
|VD-N1-N-28.670-M-LOOP|2024-01-01 00:04:00|0000100102800F|3|2|0|0|T|0|0|

- orgin_m05
[完整範例檔案](https://1drv.ms/x/c/002d06ef873e3a00/ESpm2sWDfvlLmE4NKbUPbT4BrH87o2APpjeNtHKt2tGK_g?e=Ycrj7Z)

|DataCollectTime|GantryFrom|GantryTo|VehicleType|Speed|Volume|
|---|---|---|---|---|---|
|2024-01-01 00:00:00|01F0213N|01F0155N|31.0|86.0|31.0|
|2024-01-01 00:00:00|01F0213N|01F0155N|32.0|82.0|4.0|
|2024-01-01 00:00:00|01F0213N|01F0155N|41.0|0.0|0.0|
|...|...|...|...|...|...|
|2024-01-01 00:05:00|01F0293S|01F0339S|42.0|79.0|2.0|
|2024-01-01 00:05:00|01F0293S|01F0339S|5.0|0.0|0.0|

b. 特徵資料表
- feature
[~~完整~~部分範例檔案](https://1drv.ms/x/c/002d06ef873e3a00/EeLNBIZAUh9Psk3CsysIaNoBxFGyEVvNVkW7QzuxQDkpzQ?e=2at3Bf) (完整的新增兩個欄位`vd_finished`、`m05_finished`，為`bool`，分別記錄vd與m05資料在該row是否已經完成資料處理，並可供`predict.py`預測。

欄位名稱：
|timestamp|spdavg_VD-N1-N-18.010-M-LOOP|spdavg_VD-N1-N-18.770-M-RS|spdavg_VD-N1-N-21.230-M-LOOP|spdavg_VD-N1-N-22.990-N-LOOP|spdavg_VD-N1-N-23.780-M-LOOP|spdavg_VD-N1-N-24.200-M-RS|spdavg_VD-N1-N-25.200-N-LOOP|spdavg_VD-N1-N-25.800-M-LOOP|spdavg_VD-N1-N-26.780-M-RS|spdavg_VD-N1-N-27.100-N-LOOP|spdavg_VD-N1-N-27.970-M-LOOP|spdavg_VD-N1-N-28.670-M-LOOP|spdavg_VD-N1-N-30.550-M-RS|spdavg_VD-N1-N-31.830-N-LOOP|spdavg_VD-N1-N-32.200-M-LOOP|spdavg_VD-N1-S-18.710-M-LOOP|spdavg_VD-N1-S-20.000-M-RS|spdavg_VD-N1-S-21.390-M-LOOP|spdavg_VD-N1-S-22.700-M-LOOP|spdavg_VD-N1-S-23.280-N-LOOP|spdavg_VD-N1-S-23.900-M-LOOP|spdavg_VD-N1-S-24.400-M-RS|spdavg_VD-N1-S-25.440-N-LOOP|spdavg_VD-N1-S-25.850-M-LOOP|spdavg_VD-N1-S-26.350-M-RS|spdavg_VD-N1-S-26.750-N-LOOP|spdavg_VD-N1-S-28.840-M-RS|spdavg_VD-N1-S-30.540-M-LOOP|spdavg_VD-N1-S-31.400-M-LOOP|spdavg_VD-N1-S-32.080-N-LOOP|spdtrnd_VD-N1-N-18.010-M-LOOP|spdtrnd_VD-N1-N-18.770-M-RS|spdtrnd_VD-N1-N-21.230-M-LOOP|spdtrnd_VD-N1-N-22.990-N-LOOP|spdtrnd_VD-N1-N-23.780-M-LOOP|spdtrnd_VD-N1-N-24.200-M-RS|spdtrnd_VD-N1-N-25.200-N-LOOP|spdtrnd_VD-N1-N-25.800-M-LOOP|spdtrnd_VD-N1-N-26.780-M-RS|spdtrnd_VD-N1-N-27.100-N-LOOP|spdtrnd_VD-N1-N-27.970-M-LOOP|spdtrnd_VD-N1-N-28.670-M-LOOP|spdtrnd_VD-N1-N-30.550-M-RS|spdtrnd_VD-N1-N-31.830-N-LOOP|spdtrnd_VD-N1-N-32.200-M-LOOP|spdtrnd_VD-N1-S-18.710-M-LOOP|spdtrnd_VD-N1-S-20.000-M-RS|spdtrnd_VD-N1-S-21.390-M-LOOP|spdtrnd_VD-N1-S-22.700-M-LOOP|spdtrnd_VD-N1-S-23.280-N-LOOP|spdtrnd_VD-N1-S-23.900-M-LOOP|spdtrnd_VD-N1-S-24.400-M-RS|spdtrnd_VD-N1-S-25.440-N-LOOP|spdtrnd_VD-N1-S-25.850-M-LOOP|spdtrnd_VD-N1-S-26.350-M-RS|spdtrnd_VD-N1-S-26.750-N-LOOP|spdtrnd_VD-N1-S-28.840-M-RS|spdtrnd_VD-N1-S-30.540-M-LOOP|spdtrnd_VD-N1-S-31.400-M-LOOP|spdtrnd_VD-N1-S-32.080-N-LOOP|occ_VD-N1-N-18.010-M-LOOP|occ_VD-N1-N-18.770-M-RS|occ_VD-N1-N-21.230-M-LOOP|occ_VD-N1-N-22.990-N-LOOP|occ_VD-N1-N-23.780-M-LOOP|occ_VD-N1-N-24.200-M-RS|occ_VD-N1-N-25.200-N-LOOP|occ_VD-N1-N-25.800-M-LOOP|occ_VD-N1-N-26.780-M-RS|occ_VD-N1-N-27.100-N-LOOP|occ_VD-N1-N-27.970-M-LOOP|occ_VD-N1-N-28.670-M-LOOP|occ_VD-N1-N-30.550-M-RS|occ_VD-N1-N-31.830-N-LOOP|occ_VD-N1-N-32.200-M-LOOP|occ_VD-N1-S-18.710-M-LOOP|occ_VD-N1-S-20.000-M-RS|occ_VD-N1-S-21.390-M-LOOP|occ_VD-N1-S-22.700-M-LOOP|occ_VD-N1-S-23.280-N-LOOP|occ_VD-N1-S-23.900-M-LOOP|occ_VD-N1-S-24.400-M-RS|occ_VD-N1-S-25.440-N-LOOP|occ_VD-N1-S-25.850-M-LOOP|occ_VD-N1-S-26.350-M-RS|occ_VD-N1-S-26.750-N-LOOP|occ_VD-N1-S-28.840-M-RS|occ_VD-N1-S-30.540-M-LOOP|occ_VD-N1-S-31.400-M-LOOP|occ_VD-N1-S-32.080-N-LOOP|01F0248S_01F0264S_spd|traffic_state|vd_finished|m05_finished|
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

- 欄位(python list格式，方便複製)
```python
["timestamp", "spdavg_VD-N1-N-18.010-M-LOOP", "spdavg_VD-N1-N-18.770-M-RS", "spdavg_VD-N1-N-21.230-M-LOOP", "spdavg_VD-N1-N-22.990-N-LOOP", "spdavg_VD-N1-N-23.780-M-LOOP", "spdavg_VD-N1-N-24.200-M-RS", "spdavg_VD-N1-N-25.200-N-LOOP", "spdavg_VD-N1-N-25.800-M-LOOP", "spdavg_VD-N1-N-26.780-M-RS", "spdavg_VD-N1-N-27.100-N-LOOP", "spdavg_VD-N1-N-27.970-M-LOOP", "spdavg_VD-N1-N-28.670-M-LOOP", "spdavg_VD-N1-N-30.550-M-RS", "spdavg_VD-N1-N-31.830-N-LOOP", "spdavg_VD-N1-N-32.200-M-LOOP", "spdavg_VD-N1-S-18.710-M-LOOP", "spdavg_VD-N1-S-20.000-M-RS", "spdavg_VD-N1-S-21.390-M-LOOP", "spdavg_VD-N1-S-22.700-M-LOOP", "spdavg_VD-N1-S-23.280-N-LOOP", "spdavg_VD-N1-S-23.900-M-LOOP", "spdavg_VD-N1-S-24.400-M-RS", "spdavg_VD-N1-S-25.440-N-LOOP", "spdavg_VD-N1-S-25.850-M-LOOP", "spdavg_VD-N1-S-26.350-M-RS", "spdavg_VD-N1-S-26.750-N-LOOP", "spdavg_VD-N1-S-28.840-M-RS", "spdavg_VD-N1-S-30.540-M-LOOP", "spdavg_VD-N1-S-31.400-M-LOOP", "spdavg_VD-N1-S-32.080-N-LOOP", "spdtrnd_VD-N1-N-18.010-M-LOOP", "spdtrnd_VD-N1-N-18.770-M-RS", "spdtrnd_VD-N1-N-21.230-M-LOOP", "spdtrnd_VD-N1-N-22.990-N-LOOP", "spdtrnd_VD-N1-N-23.780-M-LOOP", "spdtrnd_VD-N1-N-24.200-M-RS", "spdtrnd_VD-N1-N-25.200-N-LOOP", "spdtrnd_VD-N1-N-25.800-M-LOOP", "spdtrnd_VD-N1-N-26.780-M-RS", "spdtrnd_VD-N1-N-27.100-N-LOOP", "spdtrnd_VD-N1-N-27.970-M-LOOP", "spdtrnd_VD-N1-N-28.670-M-LOOP", "spdtrnd_VD-N1-N-30.550-M-RS", "spdtrnd_VD-N1-N-31.830-N-LOOP", "spdtrnd_VD-N1-N-32.200-M-LOOP", "spdtrnd_VD-N1-S-18.710-M-LOOP", "spdtrnd_VD-N1-S-20.000-M-RS", "spdtrnd_VD-N1-S-21.390-M-LOOP", "spdtrnd_VD-N1-S-22.700-M-LOOP", "spdtrnd_VD-N1-S-23.280-N-LOOP", "spdtrnd_VD-N1-S-23.900-M-LOOP", "spdtrnd_VD-N1-S-24.400-M-RS", "spdtrnd_VD-N1-S-25.440-N-LOOP", "spdtrnd_VD-N1-S-25.850-M-LOOP", "spdtrnd_VD-N1-S-26.350-M-RS", "spdtrnd_VD-N1-S-26.750-N-LOOP", "spdtrnd_VD-N1-S-28.840-M-RS", "spdtrnd_VD-N1-S-30.540-M-LOOP", "spdtrnd_VD-N1-S-31.400-M-LOOP", "spdtrnd_VD-N1-S-32.080-N-LOOP", "occ_VD-N1-N-18.010-M-LOOP", "occ_VD-N1-N-18.770-M-RS", "occ_VD-N1-N-21.230-M-LOOP", "occ_VD-N1-N-22.990-N-LOOP", "occ_VD-N1-N-23.780-M-LOOP", "occ_VD-N1-N-24.200-M-RS", "occ_VD-N1-N-25.200-N-LOOP", "occ_VD-N1-N-25.800-M-LOOP", "occ_VD-N1-N-26.780-M-RS", "occ_VD-N1-N-27.100-N-LOOP", "occ_VD-N1-N-27.970-M-LOOP", "occ_VD-N1-N-28.670-M-LOOP", "occ_VD-N1-N-30.550-M-RS", "occ_VD-N1-N-31.830-N-LOOP", "occ_VD-N1-N-32.200-M-LOOP", "occ_VD-N1-S-18.710-M-LOOP", "occ_VD-N1-S-20.000-M-RS", "occ_VD-N1-S-21.390-M-LOOP", "occ_VD-N1-S-22.700-M-LOOP", "occ_VD-N1-S-23.280-N-LOOP", "occ_VD-N1-S-23.900-M-LOOP", "occ_VD-N1-S-24.400-M-RS", "occ_VD-N1-S-25.440-N-LOOP", "occ_VD-N1-S-25.850-M-LOOP", "occ_VD-N1-S-26.350-M-RS", "occ_VD-N1-S-26.750-N-LOOP", "occ_VD-N1-S-28.840-M-RS", "occ_VD-N1-S-30.540-M-LOOP", "occ_VD-N1-S-31.400-M-LOOP", "occ_VD-N1-S-32.080-N-LOOP", "01F0248S_01F0264S_spd", "traffic_state", "vd_finished", "m05_finished"]
```

<!-- 舊欄位名稱(已棄用)：
|timestamp|vd_spdavg_VD-N1-S-23.900-M-LOOP|vd_spdtrnd_VD-N1-S-23.900-M-LOOP|vd_occ_VD-N1-S-23.900-M-LOOP|vd_spdavg_VD-N1-S-24.400-M-RS|vd_spdtrnd_VD-N1-S-24.400-M-RS|vd_occ_VD-N1-S-24.400-M-RS|vd_spdavg_VD-N1-S-25.440-N-LOOP|vd_spdtrnd_VD-N1-S-25.440-N-LOOP|vd_occ_VD-N1-S-25.440-N-LOOP|vd_spdavg_VD-N1-S-25.850-M-LOOP|vd_spdtrnd_VD-N1-S-25.850-M-LOOP|vd_occ_VD-N1-S-25.850-M-LOOP|vd_spdavg_VD-N1-S-26.350-M-RS|vd_spdtrnd_VD-N1-S-26.350-M-RS|vd_occ_VD-N1-S-26.350-M-RS|vd_spdavg_VD-N1-S-26.750-N-LOOP|vd_spdtrnd_VD-N1-S-26.750-N-LOOP|vd_occ_VD-N1-S-26.750-N-LOOP|vd_spdavg_VD-N1-S-28.840-M-RS|vd_spdtrnd_VD-N1-S-28.840-M-RS|vd_occ_VD-N1-S-28.840-M-RS|m05_speed|m05_state|m05_speed_level|vd_finished|m05_finished|
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|2024-01-01T00:00:00+08:00|78.61111111||4.5|90.15384615||1.25|99.78947368||2.333333333|87.4||1.75|91.38461538||1.5|91.29166667||2.375|102.1||1.25|92.484375|0|4|1|1|
|2024-01-01T00:05:00+08:00|81.70526316|9|3.9|88.83486239|6|2.2|99.78947368|23|2.333333333|85.03225806|16|4.15|84.56842105|28|1.7|91.29166667|7|2.375|101.1547619|33|1.4|96.5505618|0|4|1|1| -->

<!-- 欄位(python list格式，方便複製)
    ```
    [
        "timestamp",
        "vd_spdavg_VD-N1-S-23.900-M-LOOP",
        "vd_spdtrnd_VD-N1-S-23.900-M-LOOP",
        "vd_occ_VD-N1-S-23.900-M-LOOP",
        "vd_spdavg_VD-N1-S-24.400-M-RS",
        "vd_spdtrnd_VD-N1-S-24.400-M-RS",
        "vd_occ_VD-N1-S-24.400-M-RS",
        "vd_spdavg_VD-N1-S-25.440-N-LOOP",
        "vd_spdtrnd_VD-N1-S-25.440-N-LOOP",
        "vd_occ_VD-N1-S-25.440-N-LOOP",
        "vd_spdavg_VD-N1-S-25.850-M-LOOP",
        "vd_spdtrnd_VD-N1-S-25.850-M-LOOP",
        "vd_occ_VD-N1-S-25.850-M-LOOP",
        "vd_spdavg_VD-N1-S-26.350-M-RS",
        "vd_spdtrnd_VD-N1-S-26.350-M-RS",
        "vd_occ_VD-N1-S-26.350-M-RS",
        "vd_spdavg_VD-N1-S-26.750-N-LOOP",
        "vd_spdtrnd_VD-N1-S-26.750-N-LOOP",
        "vd_occ_VD-N1-S-26.750-N-LOOP",
        "vd_spdavg_VD-N1-S-28.840-M-RS",
        "vd_spdtrnd_VD-N1-S-28.840-M-RS",
        "vd_occ_VD-N1-S-28.840-M-RS",
        "m05_speed",
        "m05_state",
        "m05_speed_level"
    ]
    ``` 
-->

c. 預測結果資料表
- predict
> `timestamp`: 使用`feature`資料表中哪個時間點與之前的資料進行預測，例如用`...~2024-01-01T00:05:00+08:00`的資料進行預測，則`timestamp`為`2024-01-01T00:05:00+08:00`。

[完整範例檔案](https://1drv.ms/x/c/002d06ef873e3a00/EcDED6R2FJpIvxOhsmywfH0BYB0027_8jYe1xbZu3dxCGg?e=fKSugY)

|timestamp|LSTM_m05_speed_predict_5min|LSTM_m05_speed_predict_10min|LSTM_m05_speed_predict_15min|LSTM_m05_speed_predict_30min|LSTM_m05_speedlevel_predict_5min|LSTM_m05_speedlevel_predict_10min|LSTM_m05_speedlevel_predict_15min|LSTM_m05_speedlevel_predict_30min|LSTM_m05_ttf|LSTM_m05_ttc|GRU_m05_speed_predict_5min|GRU_m05_speed_predict_10min|GRU_m05_speed_predict_15min|GRU_m05_speed_predict_30min|GRU_m05_speedlevel_predict_5min|GRU_m05_speedlevel_predict_10min|GRU_m05_speedlevel_predict_15min|GRU_m05_speedlevel_predict_30min|GRU_m05_ttf|GRU_m05_ttc|RNN_m05_speed_predict_5min|RNN_m05_speed_predict_10min|RNN_m05_speed_predict_15min|RNN_m05_speed_predict_30min|RNN_m05_speedlevel_predict_5min|RNN_m05_speedlevel_predict_10min|RNN_m05_speedlevel_predict_15min|RNN_m05_speedlevel_predict_30min|RNN_m05_ttf|RNN_m05_ttc|GBRT_m05_speed_predict_5min|GBRT_m05_speed_predict_10min|GBRT_m05_speed_predict_15min|GBRT_m05_speed_predict_30min|GBRT_m05_speedlevel_predict_5min|GBRT_m05_speedlevel_predict_10min|GBRT_m05_speedlevel_predict_15min|GBRT_m05_speedlevel_predict_30min|GBRT_m05_ttf|GBRT_m05_ttc|CNN_m05_speed_predict_5min|CNN_m05_speed_predict_10min|CNN_m05_speed_predict_15min|CNN_m05_speed_predict_30min|CNN_m05_speedlevel_predict_5min|CNN_m05_speedlevel_predict_10min|CNN_m05_speedlevel_predict_15min|CNN_m05_speedlevel_predict_30min|CNN_m05_ttf|CNN_m05_ttc|
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
2024-01-01T00:00:00+08:00||||||||||||||||||||||||||||||||||||||||||||||||||
2024-01-01T00:05:00+08:00||||||||||||||||||||||||||||||||||||||||||||||||||

### 資料庫須提供function
1. 寫入origin_vd資料表
    - ```python
        def save_to_origin_vd(cols: List[str], data: np.ndarray) -> bool:
            pass
        ```
    - 將`data`寫入`origin_vd資料表`，data中可能有一筆或數筆資料
    - `cols`為`data`的欄位名稱，需以此將`data`的資料存入`origin_vd資料表`中的對應欄位。
2. 寫入origin_m05資料表
    - ```python
        def save_to_origin_m05(cols: List[str], data: np.ndarray) -> bool:
            pass
        ```
    - 將`data`寫入`origin_m05資料表`，data中可能有一筆或數筆資料
    - `cols`為`data`的欄位名稱，需以此將`data`的資料存入`origin_m05資料表`中的對應欄位。
3. 回傳origin_vd資料表中，`DataCollectTime`介於指定參數之間(含)的所有資料。
    - ```python
        def get_origin_vd(cols: List[str], time_start: str, time_end: str) -> np.ndarray:
            pass
        ```
    - 回傳值不包含欄位名稱
    - 回傳的data以`cols`排序
4. 回傳origin_m05資料表中，`TimeStamp`介於指定參數之間(含)的所有資料。
    - ```python
        def get_origin_m05(cols: List[str], time_start: str, time_end: str) -> np.ndarray:
            pass
        ```
    - 回傳值不包含欄位名稱
    - 回傳的data以`cols`排序
5. 回傳origin_vd資料表中`DataCollectTime`欄位最新(最大)的值
    - ```python
        def get_origin_vd_latest_time() -> str:
            pass
        ```
6. 回傳origin_m05資料表中`TimeStamp`欄位最新(最大)的值
    - ```python
        def get_origin_m05_latest_time() -> str:
            pass
        ```
7. 寫入feature資料表
    - ```python
        def save_to_feature(cols: List[str], data: np.ndarray) -> bool:
            pass
        ```
8. 回傳feature資料表中`vd_finished`與`m05_finished`皆為true的row中，timestamp最新的四筆資料。
    - ```python
        def get_features(cols: List[str]) -> np.ndarray:
            pass
        ```
    - 回傳值不包含欄位名稱
    - 回傳的data以`cols`排序

9. 回傳predict資料表中，timestamp最新的一筆row。
    - ```python
        def get_predict(cols: List[str]) -> np.ndarray:
            pass
        ```
    - 回傳值不包含欄位名稱
    - 回傳的data以`cols`排序

10. 寫入predict資料表
    - ```python
        def save_to_predict(cols: List[str], data: np.ndarray) -> bool:
            pass
        ```
### 各資料表初始化

在資料一啟動時要做的事

1. `origin_vd資料表`初始化如下
    - 單純建表，無預設資料

|VDID|DataCollectTime|LinkID|LaneID|LaneType|Speed|Occupancy|VehicleType|Volume|VehicleSpeed|
|---|---|---|---|---|---|---|---|---|---|

2. `origin_m05資料表`初始化如下
    - 單純建表，無預設資料

|DataCollectTime|GantryFrom|GantryTo|VehicleType|Speed|Volume|
|---|---|---|---|---|---|

3. `feature資料表`初始化如下
    - `vd_finished`, `m05_finished`設為1(true)，`timstamp`為2025-01-01 00:00:00~2025-01-01 00:15:00，共四個row，其他欄位都設0。

|timestamp|spdavg_VD-N1-N-18.010-M-LOOP|spdavg_VD-N1-N-18.770-M-RS|spdavg_VD-N1-N-21.230-M-LOOP|spdavg_VD-N1-N-22.990-N-LOOP|spdavg_VD-N1-N-23.780-M-LOOP|spdavg_VD-N1-N-24.200-M-RS|spdavg_VD-N1-N-25.200-N-LOOP|spdavg_VD-N1-N-25.800-M-LOOP|spdavg_VD-N1-N-26.780-M-RS|spdavg_VD-N1-N-27.100-N-LOOP|spdavg_VD-N1-N-27.970-M-LOOP|spdavg_VD-N1-N-28.670-M-LOOP|spdavg_VD-N1-N-30.550-M-RS|spdavg_VD-N1-N-31.830-N-LOOP|spdavg_VD-N1-N-32.200-M-LOOP|spdavg_VD-N1-S-18.710-M-LOOP|spdavg_VD-N1-S-20.000-M-RS|spdavg_VD-N1-S-21.390-M-LOOP|spdavg_VD-N1-S-22.700-M-LOOP|spdavg_VD-N1-S-23.280-N-LOOP|spdavg_VD-N1-S-23.900-M-LOOP|spdavg_VD-N1-S-24.400-M-RS|spdavg_VD-N1-S-25.440-N-LOOP|spdavg_VD-N1-S-25.850-M-LOOP|spdavg_VD-N1-S-26.350-M-RS|spdavg_VD-N1-S-26.750-N-LOOP|spdavg_VD-N1-S-28.840-M-RS|spdavg_VD-N1-S-30.540-M-LOOP|spdavg_VD-N1-S-31.400-M-LOOP|spdavg_VD-N1-S-32.080-N-LOOP|spdtrnd_VD-N1-N-18.010-M-LOOP|spdtrnd_VD-N1-N-18.770-M-RS|spdtrnd_VD-N1-N-21.230-M-LOOP|spdtrnd_VD-N1-N-22.990-N-LOOP|spdtrnd_VD-N1-N-23.780-M-LOOP|spdtrnd_VD-N1-N-24.200-M-RS|spdtrnd_VD-N1-N-25.200-N-LOOP|spdtrnd_VD-N1-N-25.800-M-LOOP|spdtrnd_VD-N1-N-26.780-M-RS|spdtrnd_VD-N1-N-27.100-N-LOOP|spdtrnd_VD-N1-N-27.970-M-LOOP|spdtrnd_VD-N1-N-28.670-M-LOOP|spdtrnd_VD-N1-N-30.550-M-RS|spdtrnd_VD-N1-N-31.830-N-LOOP|spdtrnd_VD-N1-N-32.200-M-LOOP|spdtrnd_VD-N1-S-18.710-M-LOOP|spdtrnd_VD-N1-S-20.000-M-RS|spdtrnd_VD-N1-S-21.390-M-LOOP|spdtrnd_VD-N1-S-22.700-M-LOOP|spdtrnd_VD-N1-S-23.280-N-LOOP|spdtrnd_VD-N1-S-23.900-M-LOOP|spdtrnd_VD-N1-S-24.400-M-RS|spdtrnd_VD-N1-S-25.440-N-LOOP|spdtrnd_VD-N1-S-25.850-M-LOOP|spdtrnd_VD-N1-S-26.350-M-RS|spdtrnd_VD-N1-S-26.750-N-LOOP|spdtrnd_VD-N1-S-28.840-M-RS|spdtrnd_VD-N1-S-30.540-M-LOOP|spdtrnd_VD-N1-S-31.400-M-LOOP|spdtrnd_VD-N1-S-32.080-N-LOOP|occ_VD-N1-N-18.010-M-LOOP|occ_VD-N1-N-18.770-M-RS|occ_VD-N1-N-21.230-M-LOOP|occ_VD-N1-N-22.990-N-LOOP|occ_VD-N1-N-23.780-M-LOOP|occ_VD-N1-N-24.200-M-RS|occ_VD-N1-N-25.200-N-LOOP|occ_VD-N1-N-25.800-M-LOOP|occ_VD-N1-N-26.780-M-RS|occ_VD-N1-N-27.100-N-LOOP|occ_VD-N1-N-27.970-M-LOOP|occ_VD-N1-N-28.670-M-LOOP|occ_VD-N1-N-30.550-M-RS|occ_VD-N1-N-31.830-N-LOOP|occ_VD-N1-N-32.200-M-LOOP|occ_VD-N1-S-18.710-M-LOOP|occ_VD-N1-S-20.000-M-RS|occ_VD-N1-S-21.390-M-LOOP|occ_VD-N1-S-22.700-M-LOOP|occ_VD-N1-S-23.280-N-LOOP|occ_VD-N1-S-23.900-M-LOOP|occ_VD-N1-S-24.400-M-RS|occ_VD-N1-S-25.440-N-LOOP|occ_VD-N1-S-25.850-M-LOOP|occ_VD-N1-S-26.350-M-RS|occ_VD-N1-S-26.750-N-LOOP|occ_VD-N1-S-28.840-M-RS|occ_VD-N1-S-30.540-M-LOOP|occ_VD-N1-S-31.400-M-LOOP|occ_VD-N1-S-32.080-N-LOOP|01F0248S_01F0264S_spd|traffic_state|vd_finished|m05_finished|
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|2025-01-01 00:00:00|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|1|1|
|2025-01-01 00:05:00|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|1|1|
|2025-01-01 00:10:00|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|1|1|
|2025-01-01 00:15:00|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|1|1|

4. `predict資料表`初始化如下
    - 單純建表，無預設資料

|timestamp|LSTM_m05_speed_predict_5min|LSTM_m05_speed_predict_10min|LSTM_m05_speed_predict_15min|LSTM_m05_speed_predict_30min|LSTM_m05_speedlevel_predict_5min|LSTM_m05_speedlevel_predict_10min|LSTM_m05_speedlevel_predict_15min|LSTM_m05_speedlevel_predict_30min|LSTM_m05_ttf|LSTM_m05_ttc|GRU_m05_speed_predict_5min|GRU_m05_speed_predict_10min|GRU_m05_speed_predict_15min|GRU_m05_speed_predict_30min|GRU_m05_speedlevel_predict_5min|GRU_m05_speedlevel_predict_10min|GRU_m05_speedlevel_predict_15min|GRU_m05_speedlevel_predict_30min|GRU_m05_ttf|GRU_m05_ttc|RNN_m05_speed_predict_5min|RNN_m05_speed_predict_10min|RNN_m05_speed_predict_15min|RNN_m05_speed_predict_30min|RNN_m05_speedlevel_predict_5min|RNN_m05_speedlevel_predict_10min|RNN_m05_speedlevel_predict_15min|RNN_m05_speedlevel_predict_30min|RNN_m05_ttf|RNN_m05_ttc|GBRT_m05_speed_predict_5min|GBRT_m05_speed_predict_10min|GBRT_m05_speed_predict_15min|GBRT_m05_speed_predict_30min|GBRT_m05_speedlevel_predict_5min|GBRT_m05_speedlevel_predict_10min|GBRT_m05_speedlevel_predict_15min|GBRT_m05_speedlevel_predict_30min|GBRT_m05_ttf|GBRT_m05_ttc|CNN_m05_speed_predict_5min|CNN_m05_speed_predict_10min|CNN_m05_speed_predict_15min|CNN_m05_speed_predict_30min|CNN_m05_speedlevel_predict_5min|CNN_m05_speedlevel_predict_10min|CNN_m05_speedlevel_predict_15min|CNN_m05_speedlevel_predict_30min|CNN_m05_ttf|CNN_m05_ttc|
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|

## 1

