How to get 99 Percentile sql druid

Apache Druid Q&A Analytics
#1

Hello!
I’d like to count 99 percentile by using sql druid query. There’s no enough info about function in documentation.

Now I have the next query:

SELECT
  __time as "time",
  AVG(sla) as " ", --!!!!!!!!!!!!!!!!!!!!!!!!!!
  name as "service"
FROM SYTESTER_DRUID
WHERE ('${service}' = 'Show All' OR name in (${service:sqlstring}))
AND __time >= MILLIS_TO_TIMESTAMP(${__from}) AND __time <= MILLIS_TO_TIMESTAMP(${__to})
GROUP BY __time, name

But I need to apply 99 percentile instead of arithmetic mean. In my opinion, query must looks like:

 SELECT
  __time as "time",
DS_GET_QUANTILE(sla, 0.99) as " ", --!!!!!!!!!!!!!!!!!!!!!!!!!!
  name as "service"
FROM SYTESTER_DRUID
WHERE ('${service}' = 'Show All' OR name in (${service:sqlstring}))
AND __time >= MILLIS_TO_TIMESTAMP(${__from}) AND __time <= MILLIS_TO_TIMESTAMP(${__to})
GROUP BY __time, name

What function from:

TDIGEST_QUANTILE
DS_GET_QUANTILE
APPROX_QUANTILE
DS_QUANTILE_SUMMARY

should I use?
And can you share with me with druid sql examples with …QUANTILE… functions?

#2

Sql query
select DS_GET_QUANTILES(select sla from SYTESTER_DRUID where __time >= '2022-06-17T10:03:11.000Z' and __time < '2022-06-17T10:05:15.000Z' GROUP BY __time, 0.99)
does not work as well(

#3

Did you try your own example:

SELECT
  __time as "time",
DS_GET_QUANTILE(sla, 0.99) as " ", --!!!!!!!!!!!!!!!!!!!!!!!!!!
  name as "service"
FROM SYTESTER_DRUID
WHERE ('${service}' = 'Show All' OR name in (${service:sqlstring}))
AND __time >= MILLIS_TO_TIMESTAMP(${__from}) AND __time <= MILLIS_TO_TIMESTAMP(${__to})
GROUP BY __time, name

What was the output? I think DS_GET_QUANTILE(sla, 0.99) should give you the 99th percentile.

#4

@Mark_Herrera hi!
The output:
Error: Plan validation failed
org.apache.calcite.runtime.CalciteContextException: From line 31, column 3 to line 31, column 28: No match found for function signature DS_GET_QUANTILE(<NUMERIC>, <NUMERIC>)
org.apache.calcite.tools.ValidationException

I forgot to mention I use 0.22.1 druid version and sla column has double type.

#5

Hi Nik,
If you see the documentation. DS_GET_QUANTILE is applied on quantile sketch. I believe column sla is not quantile. You may try creating a quantile using the DS_QUANTILES_SKETCH function and then wrap it with DS_GET_QUANTILE

SELECT
  __time as "time",
DS_GET_QUANTILE(DS_QUANTILES_SKETCH (sla), 0.99) as " ds_sla", 
  name as "service"
FROM SYTESTER_DRUID
WHERE ('${service}' = 'Show All' OR name in (${service:sqlstring}))
AND __time >= MILLIS_TO_TIMESTAMP(${__from}) AND __time <= MILLIS_TO_TIMESTAMP(${__to})
GROUP BY __time, name
#6

Thanks so much! Yup are right, before using DS_GET_QUANTILE I needed to apply DS_QUANTILES_SKETCH.