基於PolarDB的時序資料庫：股票金融交易資料分析 - PolarDB

本文類比股票交易資料，基於PolarDB的GanosBase TSDB，在股票交易情境執行時序資料分析，確保交易系統精準追蹤價格波動，即時計算技術指標（如移動平均趨勢線、RSI），並支撐高頻交易演算法進行歷史模式比對與即時決策。

背景

在股票金融交易情境中，每秒產生海量高頻時序資料（如逐筆報價、分時成交量、委託訂單流等）。這些資料不僅要求毫秒級即時寫入與持久化儲存，還需支援高並發查詢、快速時間範圍檢索和複雜時序分析。

時序資料庫針對時間戳記進行儲存結構最佳化及高效資料壓縮演算法，能夠即時捕獲並儲存每秒數十萬筆市場行情資料，從而確保交易系統能夠精準追蹤價格波動並即時計算技術指標（如移動平均趨勢線、RSI），同時也支撐高頻交易演算法進行歷史模式比對與即時決策。此外，時序資料庫的時間視窗彙總功能可快速產生分鐘/小時K線圖（Candlestick Chart），以滿足不同交易策略對時間顆粒度的需求。

最佳實務

建立超表

建立資料表，也稱為源表。

CREATE TABLE market_trades (
    market_id BIGINT NOT NULL,            -- Market ID
    trade_id BIGINT,                      -- Trade ID
    inst_name VARCHAR,                    -- Instrument name
    price DECIMAL NOT NULL,               -- Trade price
    amount DECIMAL NOT NULL,              -- Trade amount
    trade_ts TIMESTAMP NOT NULL,          -- Trade timestamp
    insert_ts BIGINT NOT NULL             -- insert timestamp
);

將資料錶轉為超表。

SELECT create_hypertable(
    'market_trades',                     -- 資料表名
    'trade_ts',                           -- 時間分區欄位
    chunk_time_interval => INTERVAL '1 day', -- 時間分區間隔
    partitioning_column => 'market_id',   -- 空間分區欄位
    number_partitions => 20            -- 空間分區數量
);

（可選）時區校正

PolarDB叢集預設時區為UTC。您可按需調整，本案例將時區設定為中國標準時間，並設定起始時間為交易資料的最小時間。

CREATE OR REPLACE FUNCTION get_start_time() RETURNS timestamptz LANGUAGE SQL AS
$$
  -- 測試資料的最小時間為'2024-01-01 01:01:38.710554+08'，按實際資料的開始時間進行修改
  SELECT timezone('Asia/Shanghai', '2024-01-01 01:01:38.710554+08'::timestamp);
$$ VOLATILE;

建立固定K線圖

在源表基礎上建立K線連續彙總，並設定重新整理策略。

秒K線圖

建立秒K線圖。

CREATE MATERIALIZED VIEW kline_sec
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 second', trade_ts) AS candle_ts,
    market_id,
    MIN(price) AS low,
    MAX(price) AS high,
    first(price, trade_ts) AS open,
    last(price, trade_ts) AS close,
    last(insert_ts, trade_ts) AS insert_ts,
    SUM(amount) AS vol
FROM market_trades
GROUP BY candle_ts, market_id
WITH NO DATA;

啟用即時查詢。

ALTER MATERIALIZED VIEW kline_sec SET (timescaledb.materialized_only = false);

建立連續彙總自動重新整理策略。
1. 建立重新整理策略。
```
CREATE OR REPLACE PROCEDURE kline_sec_refresh(job_id int, config jsonb) LANGUAGE PLPGSQL AS
$$
DECLARE
  start_time timestamp without time zone = date_trunc('sec', get_start_time());
BEGIN
  CALL refresh_continuous_aggregate('kline_sec', start_time, NULL);
END
$$;
```
2. 執行重新整理策略。
  說明
  當每秒寫入量較大（例如寫入量超過3000條/秒）時，當前採用整秒重新整理機制，即時查詢可能會出現輕微抖動問題。為瞭解決該抖動問題，您可以將重新整理頻率設定得更加頻繁，例如調整為0.05秒。
```
SELECT add_job('kline_sec_refresh','1 sec');
```

分鐘K線

分鐘K線是在秒K線上建立的嵌套彙總。

建立分鐘K線圖。

CREATE MATERIALIZED VIEW kline_min
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 min', candle_ts) AS candle_ts,
    market_id,
    MIN(low) AS low,
    MAX(high) AS high,
    first(open, candle_ts) AS open,
    last(close, candle_ts) AS close,
    last(insert_ts, candle_ts) AS insert_ts,
    SUM(vol) AS vol
FROM kline_sec
GROUP BY time_bucket('1 min', candle_ts), market_id
WITH NO DATA;

啟用即時查詢。

ALTER MATERIALIZED VIEW kline_min SET (timescaledb.materialized_only = false);

建立連續彙總自動重新整理策略。

建立重新整理策略。

CREATE OR REPLACE PROCEDURE kline_min_refresh(job_id int, config jsonb) LANGUAGE PLPGSQL AS
$$
DECLARE
  start_time timestamp without time zone = date_trunc('min', get_start_time());
BEGIN
  CALL refresh_continuous_aggregate('kline_min', start_time, NULL);
END
$$;

執行重新整理策略。

SELECT add_job('kline_min_refresh','1 min');

小時K線

小時K線是在分鐘K線上建立的嵌套彙總。

建立小時K線圖。

CREATE MATERIALIZED VIEW kline_hour
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', candle_ts) AS candle_ts,
    market_id,
    MIN(low) AS low,
    MAX(high) AS high,
    first(open, candle_ts) AS open,
    last(close, candle_ts) AS close,
    last(insert_ts, candle_ts) AS insert_ts,
    SUM(vol) AS vol
FROM kline_min
GROUP BY time_bucket('1 hour', candle_ts), market_id
WITH NO DATA;

啟用即時查詢。

ALTER MATERIALIZED VIEW kline_hour SET (timescaledb.materialized_only = false);

建立連續彙總自動重新整理策略。

建立重新整理策略。

CREATE OR REPLACE PROCEDURE kline_hour_refresh(job_id int, config jsonb) LANGUAGE PLPGSQL AS
$$
DECLARE
  start_time timestamp without time zone = date_trunc('hour', get_start_time());
BEGIN
  CALL refresh_continuous_aggregate('kline_hour', start_time, NULL);
END
$$;

執行重新整理策略。

SELECT add_job('kline_hour_refresh','1 hour', initial_start => date_trunc('hour', now()));

天K線圖

天K線是在小時K線上建立的嵌套彙總。

建立天K線圖。

CREATE MATERIALIZED VIEW kline_day
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 day', candle_ts) AS candle_ts,
    market_id,
    MIN(low) AS low,
    MAX(high) AS high,
    first(open, candle_ts) AS open,
    last(close, candle_ts) AS close,
    last(insert_ts, candle_ts) AS insert_ts,
    SUM(vol) AS vol
FROM kline_hour
GROUP BY time_bucket('1 day', candle_ts), market_id
WITH NO DATA;

啟用即時查詢。

ALTER MATERIALIZED VIEW kline_day SET (timescaledb.materialized_only = false);

建立連續彙總自動重新整理策略。

建立重新整理策略。

CREATE OR REPLACE PROCEDURE kline_day_refresh(job_id int, config jsonb) LANGUAGE PLPGSQL AS
$$
DECLARE
  start_time timestamp without time zone = date_trunc('day', get_start_time());
BEGIN
  CALL refresh_continuous_aggregate('kline_day', start_time, NULL);
END
$$;

執行重新整理策略。

SELECT add_job('kline_day_refresh','1 day',initial_start => date_trunc('hour', now()));

（可選）建立動態K線圖

如果固定K線圖不滿足查詢需求時，您可以在固定K線圖基礎上建立動態K線圖。例如，在分鐘K線圖基礎上建立30分鐘K線圖。

SELECT
    market_id,
    (EXTRACT(EPOCH FROM time_bucket('30 min', candle_ts)) * 1000)::BIGINT AS candle_ts,
    MIN(low) AS low,
    MAX(high) AS high,
    first(open, candle_ts) AS open,
    last(close, candle_ts) AS close,
    SUM(vol) AS vol
FROM kline_min
WHERE market_id = 100001
GROUP BY time_bucket('30 min', candle_ts), market_id
ORDER BY candle_ts DESC
limit 10;

類比股票交易資料

CREATE OR REPLACE FUNCTION test_trade_data(cnt int)
    RETURNS void
AS $$
DECLARE
   sql text;
   i int := 0;
   tm timestamp without time zone := '2024-01-01 01:01:38.710554+08'::TIMESTAMPTZ;
BEGIN
    FOR i IN 1..cnt
    LOOP
        -- 資料產生頻率為0.1 sec
        tm = tm + '0.1 sec';
        sql = format('INSERT INTO market_trades(market_id, price, amount, trade_ts, insert_ts) VALUES(100001, random(), random(), ''%s'', (EXTRACT(EPOCH FROM ''%s''::timestamp) * 1000)::bigint);', tm ,tm);
        execute sql;
    END LOOP;
END;
$$
LANGUAGE 'plpgsql';

SELECT test_trade_data(1000000);

查看K線圖

秒K線圖

SELECT market_id,candle_ts,low,high,open,close,vol FROM kline_sec WHERE market_id = 100001 ORDER BY candle_ts limit 10;

返回結果如下：

 market_id |      candle_ts      |         low         |       high        |        open        |        close        |         vol
-----------+---------------------+---------------------+-------------------+--------------------+---------------------+---------------------
    100001 | 2024-01-01 01:01:38 |   0.279485633997758 | 0.681338873589468 |  0.279485633997758 |   0.681338873589468 |   0.910744891936063
    100001 | 2024-01-01 01:01:39 |  0.0525641673891641 | 0.962955545742446 |  0.385015528170719 |  0.0525641673891641 |   4.620692226670329
    100001 | 2024-01-01 01:01:40 |  0.0633381498328447 | 0.954725391899331 |  0.801216359386196 |   0.242894295894374 |  3.6880741489060102
    100001 | 2024-01-01 01:01:41 |   0.077063096976957 | 0.619752482643143 |  0.619752482643143 |   0.362329000463575 |  5.4477741841190114
    100001 | 2024-01-01 01:01:42 |   0.130160131849806 | 0.904649178467757 |  0.751462633424246 |     0.8657081421063 | 4.60274994611772491
    100001 | 2024-01-01 01:01:43 |  0.0245408224841981 | 0.915085200903377 |  0.659205003206203 |  0.0245408224841981 |   6.882016111031889
    100001 | 2024-01-01 01:01:44 | 0.00436203999301199 | 0.999261582297358 |  0.999261582297358 | 0.00436203999301199 |   6.343432050925331
    100001 | 2024-01-01 01:01:45 |  0.0235861333017553 | 0.998218944784099 |  0.837373493299758 |   0.690308731587692 | 5.02263333895452072
    100001 | 2024-01-01 01:01:46 |   0.167417759341774 |   0.9150153383248 |  0.213189330042447 |   0.796555201647649 |  4.9323075845995145
    100001 | 2024-01-01 01:01:47 |  0.0337299584831712 | 0.765553193903251 | 0.0337299584831712 |   0.160180953095164 |   5.136451153685312
(10 rows)

分鐘K線圖

SELECT market_id,candle_ts,low,high,open,close,vol FROM kline_min WHERE market_id = 100001 ORDER BY candle_ts limit 10;

返回結果如下：

 market_id |      candle_ts      |         low          |       high        |        open        |        close        |          vol
-----------+---------------------+----------------------+-------------------+--------------------+---------------------+------------------------
    100001 | 2024-01-01 01:01:00 |  0.00436203999301199 | 0.999261582297358 |  0.279485633997758 |   0.660205011565097 |  107.67711307252750133
    100001 | 2024-01-01 01:02:00 | 0.000382727352629786 | 0.997355089315917 |  0.638467919873655 |   0.741995626890457 | 300.032527480741814196
    100001 | 2024-01-01 01:03:00 |  0.00102311960848311 | 0.998987023712075 |  0.494493945315959 | 0.00933623188228694 | 296.670624428911073230
    100001 | 2024-01-01 01:04:00 | 0.000317312327567265 | 0.999400263658501 |  0.914812683364666 |   0.565009458532018 |  308.44719638361391718
    100001 | 2024-01-01 01:05:00 |  0.00165624319932078 | 0.990618859346739 |  0.456251672433702 |   0.825974036343325 |  297.87230623497759724
    100001 | 2024-01-01 01:06:00 |  0.00446447743636469 | 0.994494939263078 | 0.0486883210703937 |   0.690970192417581 | 304.612071727522449497
    100001 | 2024-01-01 01:07:00 | 0.000609624951934506 | 0.999061576948947 |  0.663441135677637 |   0.843439992945548 |  317.33816039424379517
    100001 | 2024-01-01 01:08:00 | 0.000305593115932368 | 0.999032452035479 |  0.595979795892312 |   0.545231376105335 |   293.4771868344440740
    100001 | 2024-01-01 01:09:00 |  0.00180631157653011 | 0.999969521686225 |  0.687172827619953 |    0.52655462636659 |  308.64126233531476704
    100001 | 2024-01-01 01:10:00 |   0.0042720602925499 | 0.999550291053481 | 0.0572916237256678 |   0.827660040200954 |  302.52094683726853244
(10 rows)

小時K線圖

SELECT market_id,candle_ts,low,high,open,close,vol FROM kline_hour WHERE market_id = 100001 ORDER BY candle_ts limit 10;

返回結果如下：

 market_id |      candle_ts      |          low           |       high        |       open        |       close       |            vol
-----------+---------------------+------------------------+-------------------+-------------------+-------------------+----------------------------
    100001 | 2024-01-01 01:00:00 |  0.0000311086421334039 | 0.999988514373982 | 0.279485633997758 | 0.122312797390521 |  17496.2413504152012247561
    100001 | 2024-01-01 02:00:00 |  0.0000770308891162585 | 0.999986986486086 | 0.545347700734887 | 0.125192844770879 |  18083.5114260775312946946
    100001 | 2024-01-01 03:00:00 |  0.0000845044836879083 | 0.999952117680969 | 0.426587036254354 | 0.532456849129801 | 18030.73594659831131232683
    100001 | 2024-01-01 04:00:00 |  0.0000918109849195048 | 0.999995562667976 |   0.6330394930823 | 0.373607753625436 |  18015.5229018762197568761
    100001 | 2024-01-01 05:00:00 |  0.0000109588696517449 | 0.999994772802758 | 0.258798587687728 | 0.507375948925699 |  18051.3594940787675161618
    100001 | 2024-01-01 06:00:00 |  0.0000487699150326648 | 0.999988924300197 |  0.28910545006503 | 0.634414585708271 |  18025.7784576000281124843
    100001 | 2024-01-01 07:00:00 | 0.00000107306622965098 | 0.999981224025916 | 0.433356636233988 | 0.559994887160599 |  18010.8893090606375400777
    100001 | 2024-01-01 08:00:00 |  0.0000171556727366351 | 0.999982847376515 | 0.208111940739776 | 0.706701461479895 |  18042.9095973077942234180
    100001 | 2024-01-01 09:00:00 |  0.0000391086173721078 | 0.999991541819419 | 0.270102209152959 | 0.877127614373137 |  18006.6961534152591878249
    100001 | 2024-01-01 10:00:00 |  0.0000039186593809859 | 0.999974658507568 | 0.999237150569492 | 0.266570661557068 |  17965.2763406833559493498
(10 rows)

天K線圖

SELECT market_id,candle_ts,low,high,open,close,vol FROM kline_day WHERE market_id = 100001 ORDER BY candle_ts limit 10;

返回結果如下：

 market_id |      candle_ts      |          low           |       high        |       open        |       close       |             vol
-----------+---------------------+------------------------+-------------------+-------------------+-------------------+-----------------------------
    100001 | 2024-01-01 00:00:00 | 0.00000107306622965098 | 0.999995695245573 | 0.279485633997758 | 0.400038274292626 | 413100.59225003849417556234
    100001 | 2024-01-02 00:00:00 |  0.0000168010291758947 | 0.999989906749448 | 0.383386779917068 | 0.842509523496489 |  86494.66799924685771865093
(2 rows)

30分鐘K線圖

SELECT
    market_id,
    time_bucket('30 min', candle_ts) AS candle_ts,
    MIN(low) AS low,
    MAX(high) AS high,
    first(open, candle_ts) AS open,
    last(close, candle_ts) AS close,
    SUM(vol) AS vol
FROM kline_min
WHERE market_id = 100001
GROUP BY time_bucket('30 min', candle_ts), market_id
ORDER BY candle_ts DESC
limit 10;

返回結果如下：

 market_id |      candle_ts      |          low          |       high        |       open        |       close       |            vol
-----------+---------------------+-----------------------+-------------------+-------------------+-------------------+---------------------------
    100001 | 2024-01-02 04:30:00 | 0.0000868399533757724 | 0.999973308725529 | 0.245099633513782 | 0.842509523496489 |   5504.421751602083391644
    100001 | 2024-01-02 04:00:00 | 0.0000322917197408401 | 0.999930785807123 | 0.205706123491304 | 0.461819536540872 |   9105.530763894508472418
    100001 | 2024-01-02 03:30:00 | 0.0000195752894498469 |  0.99995978492796 | 0.864512243732669 | 0.877628779498252 |  8976.6974200916718262393
    100001 | 2024-01-02 03:00:00 | 0.0000965453148040751 | 0.999730903399971 | 0.277744581160881 |  0.40121776736629 |  8950.0175974347619817090
    100001 | 2024-01-02 02:30:00 | 0.0000168010291758947 | 0.999989906749448 | 0.628462923153656 |  0.59543446274796 |  8994.8387945492956960329
    100001 | 2024-01-02 02:00:00 | 0.0000243959481878164 | 0.999908087762357 | 0.397794388402794 | 0.253756033949969 |  8999.4657434897451608921
    100001 | 2024-01-02 01:30:00 |  0.000107806352637851 | 0.999920775786947 |  0.72757380777356 | 0.487782751467137 |   8978.433358958287225616
    100001 | 2024-01-02 01:00:00 |  0.000149064256355302 | 0.999904610171029 | 0.474390355164054 | 0.349888134466767 |  9026.5921875235542230817
    100001 | 2024-01-02 00:30:00 |  0.000112103957143006 | 0.999988398043051 |  0.45504042836459 | 0.491025347273023 |  8951.0063568393831949862
    100001 | 2024-01-02 00:00:00 | 0.0000272969867580741 | 0.999900252112297 | 0.383386779917068 | 0.353689845851306 | 9007.66402486356654603173
(10 rows)

效能對比

查詢類型	未經處理資料量	無連續彙總查詢耗時	使用連續彙總查詢耗時	效能提升倍數
過去7天的每小時最大值	100,000,000行	12秒	0.2秒	60
過去1年的每日平均值	1,000,000,000行	逾時（>5分鐘）	0.5秒	>600