數字資產ETF在港上市，時序數據庫助力機構分析應用

香港數字資產ETF於4月15日正式推出,爲數字資產市場注入強勁動力,也爲投資者帶來新的投資機會。作爲一種投資產品,數字資產正以不可阻擋之勢在全球範圍內快速發展。

過去一個月,主流數字資產如BTC、ETH都經歷了巨幅波動,標志着新一輪牛市的開啓。這不僅吸引了大量投資者關注,也對交易平台的技術提出了更高要求。

數據存儲和處理面臨的挑戰

數字貨幣交易市場有其特殊性:

• 7*24小時不間斷交易,每日產生超10TB行情數據,且持續增長
• 不同幣種行情數據量極不平衡,頭部資產佔據絕大部分
• 盤口深度差異巨大,從十幾檔到上千檔不等
• 價格波動劇烈,對系統延時要求極高

時序數據庫破局之道

面對上述挑戰,時序數據庫成爲理想解決方案:

• 專爲處理時間序列數據設計,高效存儲和查詢海量數據
• 快速處理大量數據寫入和查詢請求,滿足實時需求
• 有效壓縮時間序列數據,降低存儲成本
• 高效查詢歷史數據,支持復雜時間序列分析
• 已廣泛應用於傳統金融機構,爲系統穩定運行提供基礎

8種常用技術指標分析

1. 滑動平均價格(MA)

滑動平均價格用於識別趨勢轉折點、支撐位和阻力位。以下代碼可快速計算該指標:

sql select trade Time, code as pair, price , tmavg(datetime(tradeTime),price,10s) as movingAvg10Sec , tmavg(datetime(tradeTime),price,30s) as movingAvg30Sec , tmavg(datetime(tradeTime),price,45s) as movingAvg45Sec from (select * from aggTradeStream10 where code =BTCUSDT order by id asc) where tradeTime > temporalAdd(now(),-485m)

2. K線圖

K線是最重要的技術指標之一。以下代碼可實現K線的實時計算:

sql select first(price) as open, last(price) as close, min(price) as low, max(price) as high, sum(quantity) as volume from aggTradeStream10 where temporalAdd(now(),-540,'m') < tradeTime and code=BTCUSDT group by bar(tradeTime,1s)

3. 相對強弱指數(RSI)

RSI用於衡量價格變動速度和幅度,可識別超買超賣趨勢。計算代碼如下:

sql use ta select time, rsi(close,20) as RSI, 70 as upperbond, 30 as lowerbond from (select first(price) as open, last(price) as close, min(price) as lo, max(price) as hi, sum(quantity) as vol from aggTradeStream10 where temporalAdd(now(),-32,'H') <= tradeTime and code="BTCUSDT" group by code, bar(tradeTime,1s) as time)

4. 平滑異同平均線(MACD)

MACD用於研判買賣時機,對震蕩行情效果良好。計算代碼如下:

sql use ta select time, macd(close) as DIFDEAMACD, 0 as zeroline from (select first(price) as open, last(price) as close, min(price) as lo, max(price) as hi, sum(quantity) as vol from aggTradeStream10 where temporalAdd(now(),-32,'H') <= tradeTime and code="BTCUSDT" group by code, bar(tradeTime,1s) as time)

5. 布林帶(Bollinger Bands)

布林帶用於分析市場波動性、確認趨勢方向和識別買賣信號。計算代碼如下:

sql use ta select time, bBands(close,5,2,2,2) as LowMidHigh from (select first(price) as open, last(price) as close, min(price) as lo, max(price) as hi, sum(quantity) as vol from aggTradeStream10 where temporalAdd(now(),-32,'H') <= tradeTime and code="BTCUSDT" group by code, bar(tradeTime,1s) as time)

6. 交易對相關性

不同交易對之間相關性的計算代碼如下:

sql a = select avg(price) as price from aggTradeStream10 where code = BTCUSDT or code = ETHUSDT group by bar(tradetime,1s) as time,code b = select corr(BTCUSDT, ETHUSDT) as corrVal from (select price from a pivot by time,code) group by bar(time,1m) as time select time, tmavg(time, corrVal,1H) as corr1h , tmavg(time, corrVal,24H) as corr24h from b

7. 實時交易表

展示實時交易情況的代碼如下:

sql select tradeTime as timestamp, code as pair, case when marketMaker = true then -1 * quantity else quantity end as quantity, case when marketMaker = true then round(-1quantityprice,2) else round(quantity*price,2) end as consideration, case when (temporalAdd(now(), -8H)-tradeTime)\1000000<0.3 then "x" else "" end as new from aggTradeStream10 where tradeTime > temporalAdd(now(), -1, "M") order by tradeTime desc limit 50

8. 實時成交額(買賣方向)

展示實時成交額的代碼如下:

sql defg getA(quantity, price, marketMaker){ a = iif(marketMaker==true, -1,1) return (aquantityprice)[0] } select val from (select getA(quantity, price, marketMaker) as val from aggTradeStream10 where tradeTime between startTime0 and endTime0 group by datetime(tradetime) as tradetime,code) pivot by tradeTime, code

時序數據庫性能展示

以下是某時序數據庫在傳統金融領域的一些性能數據:

• 2700億行數據集內,毫秒級完成查詢和聚合計算
• 2億條數據兩兩相關性計算,秒級完成
• 交易表與報價表亞秒級完成asofjoin與windowjoin
• WorldQuant98號因子全市場日頻數據計算,毫秒級完成
• 65億高頻數據降頻至分鍾級,30秒完成
• 全市場一天2億行數據實時計算OHLC雙均線因子
• 100萬外匯掉期合約估值,400毫秒完成
• 10億數據線性回歸,秒級完成
• ETF盤中淨值單核亞秒級計算

這些案例展示了時序數據庫在海量數據處理、復雜指標計算、多表關聯查詢、實時分析等方面的強大能力,爲數字資產分析和交易提供了有力支持。

隨着數字資產ETF獲批,機構投資者將大規模進入市場。時序數據庫憑藉高性能和擴展性,將在數字資產全生命週期的記錄和分析中發揮重要作用,助力機構投資者洞察市場趨勢、預測走向、開發交易策略。