可以使用contextby和rowNo函数,例如：
update tt set ret = (close - open)/open where contextby(rowNo,instrument,[instrument,tradingday])=0
2 个回答
// 方案一：使用 contextby 函数（仅支持内存表）
update tt set ret = (close - open)/open where contextby(rowNo,instrument,[instrument,tradingday])=0
// 方案二：使用 iif 函数（支持内存表和分布式表）
UPDATE tt SET ret = iif(rowNo(instrument) == 0, (close - open) / open, NULL) CONTEXT BY instrument, tradingday
性能测试
数据量：10,000,000 行 x 6 列的表
测试方案：通过 timer 函数统计10次计算的耗时，计算单次平均耗时
测试结果
方案一：176.06ms
方案二：224.23ms
数据构造：
dbName = "dfs://test"
if(existsDatabase(dbName)) {
dropDatabase(dbName)
}
test = database(dbName, VALUE, 2019.11.07..2019.11.08)
tradesSchema = table(1:0, `instrument`tradingday`timestamp`open`close`volume`ret, [SYMBOL, DATE, TIMESTAMP, DOUBLE, DOUBLE, LONG, DOUBLE])
trades = test.createPartitionedTable(tradesSchema, 'trades', 'tradingday')
n = 10000000
tradingday = take(2019.11.07 2019.11.08, n)
time = (09:30:00.000 + rand(2*60*60*1000, n/2)).sort!() join (13:00:00.000 + rand(2*60*60*1000, n/2)).sort!()
timestamp = concatDateTime(tradingday, time)
open = 100 + cumsum(rand(0.02, n) - 0.01)
close = 100 + cumsum(rand(0.02, n) - 0.01)
volume = rand(1000, n)
instrument = rand(`600519`600001`600000`601766, n)
tt = table(instrument, tradingday, timestamp, open, close, volume).sortBy!(`instrument`timestamp)
tt['ret'] = double(NULL)
trades.append!(tt)