数字资产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获批,机构投资者将大规模进入市场。时序数据库凭借高性能和扩展性,将在数字资产全生命周期的记录和分析中发挥重要作用,助力机构投资者洞察市场趋势、预测走向、开发交易策略。