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

Question

如下所示代码，我用函数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中有什么简单的方法吗？

Answer 1

使用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