Hi all!
I’m currently trying to calculate the nth percentage in one of my collections.
I’ve setup an index that returns all the numeric-values in an ascending order.
Then I want to select the n-th number inside that index.
Is that currently possible in an performant way?
I’ve included the way I currently do this by using the paginate
with a specific size set and fetching the max value of that.
This seems brittle since it will fail when having collections where the n-th index is over 100 000 documents, and it seems like it will incur a lot of read-operations this way.
Underneath is the way I’m currently doing this using the javascript driver.
async function getPercentileForListOfDates(dates: string[]): Promise<IPoint[]> {
const client = getClientForDatabase()
// Returns a set of values for a given date
const valuesOnDate = q.Match(q.Index("values_by_date"), q.Date(q.Var('x')))
// Gets the 95 percentile for a given date, or null if no values are present.
const percentileOnDate = q.If(q.IsEmpty(valuesOnDate), null, q.Select(['data', 0], q.Max(q.Paginate(valuesOnDate, { size: q.ToInteger(q.Ceil(q.Multiply(q.Count(valuesOnDate), 0.95))) })), null))
// Loop over and do this for each of the dates given.
const query = q.Map(dates, q.Lambda('x', percentileOnDate))
const result = await client.query<number[]>(query)
return result.map<IPoint>((value, index) => ({ time: dates[index], value }))
}