Taos数据库计算k线数据

一、计算1分钟K线数据

1、因成交量是个累计值 ，计算1分钟的成交量，需要每一分钟减去上一分钟才能计算出来，diff函数计算成交量，会将第一分钟的数据去掉，因此要单独拿出来第一分钟 与其他结果合并

2、后期将根据是否有无夜盘，进行每天第一分钟的计算，
商品期货中的无夜盘的第一分钟是
ts>=“2024-06-17 09:00:00.000” and ts<“2024-06-17 09:01:00.000”
金融期货 第一分钟是
ts>=“2024-06-17 09:30:00.000” and ts<“2024-06-17 09:31:00.000”
有夜盘的第一分钟是
ts>=“2024-06-17 21:00:00.000” and ts<“2024-06-17 21:01:00.000”

3、为避免干扰，需要将一些时间段数据清除掉，比如：
（商品期货）日盘：10.15 11.30 15.00以后
（金融期货）： 11.30 15.00以后 几个债卷的再查一下收盘时间

select ts,open,high,low,close ,volume ,openinterest from 
	(
		SELECT _wstart as ts ,first(lastprice) as open,max(lastprice) as high,min(lastprice) as low, last(lastprice) as close   , last(volume)  as volume, last(openinterest)  as openinterest  
		FROM quote.lh2409 
		WHERE  ts>="2024-07-16 09:00:00.000" and ts <"2024-07-16 09:01:00.000"     
		INTERVAL (1m) 
		 
		union  all
		
		select ts,open,high,low,close ,DIFF(volume) as volume,openinterest from 
		(
			SELECT _wstart as ts ,first(lastprice) as open,max(lastprice) as high,min(lastprice) as low, last(lastprice) as close, last(volume)  as volume , last(openinterest)  as openinterest  
			FROM quote.lh2409 
			WHERE 
			   --tradingday="20240617"  and 
			   ts>="2024-07-16 09:00:00.000" and ts <"2024-07-16 15:00:00.000" and 
			   TO_CHAR(ts, 'hh24MI')<>'1015' and TO_CHAR(ts, 'hh24MI')<>'1130'  
			INTERVAL (1m) 
	    )
	)

结果如下图

二、计算N分钟K线数据

因交易时间的不连续性，所以计算N分钟K线数据，在1分钟K线数据的基础上进行合成

select _wstart,  first(open) as open,max(high) as high,min(low) as low, last(close) as close ,sum(volume) as volume, last(openinterest)  as openinterest 
from  
(
    --  1分钟K线计算代码=====开始=============
	select ts,open,high,low,close ,volume ,openinterest from 
	(
		SELECT _wstart as ts ,first(lastprice) as open,max(lastprice) as high,min(lastprice) as low, last(lastprice) as close   , last(volume)  as volume, last(openinterest)  as openinterest  
		FROM quote.lh2409 
		WHERE  ts>="2024-07-16 09:00:00.000" and ts <"2024-07-16 09:01:00.000"     
		INTERVAL (1m) 
		 
		union  all
		
		select ts,open,high,low,close ,DIFF(volume) as volume,openinterest from 
		(
			SELECT _wstart as ts ,first(lastprice) as open,max(lastprice) as high,min(lastprice) as low, last(lastprice) as close, last(volume)  as volume , last(openinterest)  as openinterest  
			FROM quote.lh2409 
			WHERE 
			   --tradingday="20240617"  and 
			   ts>="2024-07-16 09:00:00.000" and ts <"2024-07-16 15:00:00.000" and 
			   TO_CHAR(ts, 'hh24MI')<>'1015' and TO_CHAR(ts, 'hh24MI')<>'1130'  
			INTERVAL (1m) 
	    )
	)
    --  1分钟K线计算代码=====结束=============
)  
count_window(5);

结果如下图：

三、计算MA均线

只计算简单平均，ma均线利用的是每个周期的close，所以将上面的脚本中的只要close就可以

1 计算1分钟收盘价

SELECT _wstart as ts , last(lastprice) as close 
FROM quote.lh2409 
WHERE 
   ts>="2024-07-16 09:00:00.000" and ts <"2024-07-16 15:00:00.000" and 
   TO_CHAR(ts, 'hh24MI')<>'1015' and TO_CHAR(ts, 'hh24MI')<>'1130'  
INTERVAL (1m) 

如下图

2 计算N分钟收盘价

select _wstart as ts,    last(close) as close  
from  
(
	SELECT _wstart as ts , last(lastprice) as close 
	FROM quote.lh2409 
	WHERE 
	   ts>="2024-07-16 09:00:00.000" and ts <"2024-07-16 15:00:00.000" and 
	   TO_CHAR(ts, 'hh24MI')<>'1015' and TO_CHAR(ts, 'hh24MI')<>'1130'  
	INTERVAL (1m) 
)  
count_window(5)

如下图

3、计算ma均线

select ts,close, mavg(close,3) 
from 
(	
	select _wstart as ts,    last(close) as close  
	from  
	(
		SELECT _wstart as ts , last(lastprice) as close 
		FROM quote.lh2409 
		WHERE 
		   ts>="2024-07-16 09:00:00.000" and ts <"2024-07-16 15:00:00.000" and 
		   TO_CHAR(ts, 'hh24MI')<>'1015' and TO_CHAR(ts, 'hh24MI')<>'1130'  
		INTERVAL (1m) 
	)  
	count_window(5)
)  ;

注意：如果计算1分钟k线的ma均线，不能使用计数窗口函数。

例如：

1. 在1分钟K线基础上，计算ma(3)

select ts,close, mavg(close,3) 
from 
(		 
	SELECT _wstart as ts , last(lastprice) as close 
	FROM quote.lh2409 
	WHERE 
	   ts>="2024-07-16 09:00:00.000" and ts <"2024-07-16 15:00:00.000" and 
	   TO_CHAR(ts, 'hh24MI')<>'1015' and TO_CHAR(ts, 'hh24MI')<>'1130'  
	INTERVAL (1m)  
)  ;

2. 在3分钟K线基础上，计算ma(5)

select ts,close, mavg(close,5) 
from 
(	
	select _wstart as ts,    last(close) as close  
	from  
	(
		SELECT _wstart as ts , last(lastprice) as close 
		FROM quote.lh2409 
		WHERE 
		   ts>="2024-07-16 09:00:00.000" and ts <"2024-07-16 15:00:00.000" and 
		   TO_CHAR(ts, 'hh24MI')<>'1015' and TO_CHAR(ts, 'hh24MI')<>'1130'  
		INTERVAL (1m) 
	)  
	count_window(3)
)  ;

• 对TDengine 的这个数据库，还是不太熟悉，只能先凑合实现自己的想法，应当还有更好的脚本写法，后期学到后，再更新。