对分钟k线表pivot by后怎么转成矩阵

如下所示代码,我用函数createKData模拟产生分钟k线表quote_1min在2020.01.01这天1000个股票的分钟k线数据。

def createKData(){

t = cj(cj(table(symbol(lpad(string(1..1000),6,"0")) as Symbol),table(09:00m..11:30m join 13:30m..16:00m as sec)),table(2020.01.01 as date))

t1 = select date,sec.hour()*10000000+sec.minuteOfHour()*100000 as time , datetime(datetime(date) + second(sec)) as Datetime, Symbol from t

quote_1min = select symbol,date,time from t1 order by Datetime desc

quote_1min[`open] = rand(20..30,quote_1min.size())

quote_1min[`high] = rand(20..30,quote_1min.size())

quote_1min[`low] = rand(20..30,quote_1min.size())

quote_1min[`match] = rand(10..100,quote_1min.size())

quote_1min[`vloume] = rand(10..100,quote_1min.size())

quote_1min[`turnover] = rand(10..100,quote_1min.size())

quote_1min[`close] = rand(20..30,quote_1min.size())

return quote_1min

}

quote_1min=createKData()

然后对其进行计算后按日期、股票分组进行pivot by:

vol_max = select  stdOfMinBarRet from (select  std(ratios(close)-1) as stdOfMinBarRet from quote_1min group by date,symbol) pivot by date,symbol;

得到数据如下图:

现在想把它转成一个矩阵,如下图所示:

我试了一下下面代码:

vol_matrix = matrix(DOUBLE,1000,2)

vol_matrix[,0] = int(substr(columnNames(vol_max)[1:size(columnNames(vol_max))],1,7))

vol_matrix[,1] = vol_max[0][vol_max.columnNames()][1:]

比较麻烦,请问在DolphinDB database中有什么简单的方法吗?

回答

使用pivot by生成一个矩阵,只要在sql语句中使用exec替换select。另外没有要使用嵌套的sql语句,pivot的时候,允许使用一个表达式来生成数据。

quote_1min=createKData()

vol_max = exec std(ratios(close)-1) as stdOfMinBarRet from quote_1min pivot by symbol, date

以上是 对分钟k线表pivot by后怎么转成矩阵 的全部内容, 来源链接: utcz.com/a/33059.html

回到顶部