calculate the percentile rank of the maximum value duration (DolphinDB)

Question

Suppose there are 4800 ticks per day for a stock. All the ticks are evenly spread over time. How can I calculate the time percentile rank of the maximum value within the time window in DolphinDB?

Answer 1

Please see the code below, you can use context by clause and function rank to achieve it.

n = 40
date = take(2019.11.07, n)
time = (09:30:00.000 + rand(int(6.5*60*60*1000), n)).sort!()
timestamp = concatDateTime(date, time)
price = 100+cumsum(rand(0.02, n)-0.01)
volume = rand(1000, n)
symbol = rand(`AAPL`FB`AMZN`MSFT, n)
trade = table(symbol, date, time, timestamp, price, volume).sortBy!(`symbol`timestamp)
 select  max(price),atImax(price,timestamp) as max_buyprice01,rank(timestamp,percent=true) as rank1,rank(timestamp,percent=false) as rank2,count(timestamp)
from trade as t context by symbol,date