How to obtain the last n rows of high-frequency tick data in an efficient way?

Question

I have an in-memory table with 3 million records.

Part of the stock data is as follows:

How can I quickly get the last n rows of this table, say 35,000 rows?

Answer 1

Here are two solutions to your problem.

I’ll use the following simulated data:

def createData(num){
    date = take(2022.01.01, num)
    time = 00:00:00.000 + take(0..num, num)
    a1 = rand(1000, num)
    a2 = rand(1000, num)
    a3 = rand(1000, num)
    a4= rand(1000, num)
    a5 = rand(100.0, num)
    a6 = rand(100.0, num)
    a7 = rand(100.0, num)
    a8 = rand(100.0, num)
    return table(date, time, a1, a2, a3, a4, a5, a6, a7, a8)
}
num = 3000000
t = createData(num)

Solution 1:

timer{
    result = (select * from t order by time desc limit 35000).sortBy!(`time)
}
//Time elapsed: 11.732 ms

Solution 2:

timer{
    rowOffset = size(t) - 35000
    rowCount = 35000
    result = select * from t order by time limit rowOffset, rowCount
}
//Time elapsed: 14.013 ms