PolarDB ベースの時系列データベース: 株式取引データ分析 - PolarDB

このトピックでは、PolarDB ベースの GanosBase 時系列データベース（TSDB）を使用して、株式取引データをシミュレートし、時系列クエリを実行する方法について説明します。このソリューションにより、株式取引システムは価格変動を正確に追跡し、テクニカル指標をリアルタイムで計算し、高頻度取引（HFT）アルゴリズムを使用して履歴パターンの認識とリアルタイムの意思決定を行うことができます。

背景情報

株式取引のシナリオでは、ティックごとの気配値、分単位の取引量、注文フローなど、高頻度の時系列データが毎秒膨大な規模で生成されます。データベースは、このデータのミリ秒レベルのリアルタイム書き込みと永続ストレージを確保すると同時に、高並列クエリをサポートし、特定の時間範囲内のデータの高速取得を可能にし、複雑な時系列分析の強力なサポートを提供する必要があります。

GanosBase TSDB は、タイムスタンプのストレージ構造を最適化し、非常に効率的なデータ圧縮アルゴリズムを採用しており、1 秒あたり数十万件の市場データエントリをリアルタイムでキャプチャおよび保存できます。これにより、取引システムは価格変動を正確に追跡し、移動平均や RSI などのテクニカル指標をリアルタイムで計算できます。GanosBase TSDB は、高頻度取引アルゴリズムを使用して、過去のパターンマッチングとリアルタイムの意思決定を行うこともできます。さらに、GanosBase TSDB のタイムウィンドウ集約機能により、分単位または時間単位のローソク足チャートを迅速に生成し、さまざまな取引戦略のさまざまな時間粒度の要件を満たすことができます。

ベストプラクティス

ハイパーテーブルを作成する

データテーブル (ソーステーブルとも呼ばれます) を作成します。

CREATE TABLE market_trades (
    market_id BIGINT NOT NULL,            -- 市場 ID
    trade_id BIGINT,                      -- 取引 ID
    inst_name VARCHAR,                    -- 銘柄名
    price DECIMAL NOT NULL,               -- 取引価格
    amount DECIMAL NOT NULL,              -- 取引量
    trade_ts TIMESTAMP NOT NULL,          -- 取引タイムスタンプ
    insert_ts BIGINT NOT NULL             -- 挿入タイムスタンプ
);

データテーブルをハイパーテーブルに変換します。

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;

固定ローソク足チャートを作成する

ソーステーブルに基づいて連続集計を使用してローソク足チャートを作成し、更新ポリシーを設定します。

秒単位のローソク足チャート

秒単位のローソク足チャートを作成します。

CREATE MATERIALIZED VIEW kline_sec
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 second', trade_ts) AS candle_ts,  -- 1秒間隔のバケット
    market_id,                           -- 市場 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. 更新ジョブをスケジュールします。
  説明
  書き込み量が毎秒 3,000 レコードを超えるなど、多い場合、現在の秒単位の更新メカニズムでは、リアルタイムクエリにわずかなジッターが発生する可能性があります。この問題に対処するには、更新頻度を増やします。たとえば、更新間隔を 0.05 秒に調整できます。
```
SELECT add_job('kline_sec_refresh','1 sec'); -- 1秒ごとに更新ジョブを実行
```

分単位のローソク足チャート

分単位のローソク足チャートを作成するには、秒単位のローソク足データを 1 分間隔に集計する連続集計を作成します。

分単位のローソク足チャートを作成します。

CREATE MATERIALIZED VIEW kline_min
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 min', candle_ts) AS candle_ts, -- 1分間隔のバケット
    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'); -- 1分ごとに更新ジョブを実行

時間単位のローソク足チャート

時間単位のローソク足チャートを作成するには、分単位のローソク足データを 1 時間間隔に集計する連続集計を作成します。

時間単位のローソク足チャートを作成します。

CREATE MATERIALIZED VIEW kline_hour
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', candle_ts) AS candle_ts, -- 1時間間隔のバケット
    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())); -- 1時間ごとに更新ジョブを実行、初回開始時刻は現在の時刻の時間単位で切り捨て

日単位のローソク足チャート

日単位のローソク足チャートを作成するには、時間単位のローソク足データを 1 日間隔に集計する連続集計を作成します。

日単位のローソク足チャートを作成します。

CREATE MATERIALIZED VIEW kline_day
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 day', candle_ts) AS candle_ts, -- 1日間隔のバケット
    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())); -- 1日ごとに更新ジョブを実行、初回開始時刻は現在の時刻の時間単位で切り捨て

(オプション) 動的ローソク足チャートを作成する

固定ローソク足チャートがクエリ要件を満たしていない場合は、固定ローソク足チャートに基づいて動的ローソク足チャートを作成できます。たとえば、分単位のローソク足チャートに基づいて 30 分単位のローソク足チャートを作成できます。

SELECT
    market_id,                                 -- 市場 ID
    (EXTRACT(EPOCH FROM time_bucket('30 min', candle_ts)) * 1000)::BIGINT AS candle_ts, -- 30分間隔のバケット、エポック秒に変換
    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                      -- 市場 ID でフィルタリング
GROUP BY time_bucket('30 min', candle_ts), market_id
ORDER BY candle_ts DESC                       -- タイムスタンプの降順でソート
limit 10;                                     -- 上位 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 秒ごとに生成されます。
        tm = tm + '0.1 sec';                   -- タイムスタンプを 0.1 秒進める
        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);                -- 1,000,000 件のデータを作成

ローソク足チャートを表示する

秒単位のローソク足チャート

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 が 100001 のデータをタイムスタンプの昇順で上位 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)

分単位のローソク足チャート

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 が 100001 のデータをタイムスタンプの昇順で上位 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)

時間単位のローソク足チャート

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 が 100001 のデータをタイムスタンプの昇順で上位 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)

日単位のローソク足チャート

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 が 100001 のデータをタイムスタンプの昇順で上位 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 分単位のローソク足チャート

SELECT
    market_id,                                   -- 市場 ID
    time_bucket('30 min', candle_ts) AS candle_ts, -- 30分間隔のバケット
    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                        -- 市場 ID でフィルタリング
GROUP BY time_bucket('30 min', candle_ts), market_id
ORDER BY candle_ts DESC                           -- タイムスタンプの降順でソート
limit 10;                                       -- 上位 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 日間の 1 時間ごとの最大値	100,000,000 行	12 秒	0.2 秒	60
過去 1 年間の 1 日ごとの平均値	1,000,000,000 行	タイムアウト (5 分以上)	0.5 秒	600 以上