Title
g
Gabriel Mocan
05/04/2023, 6:34 PMHello everybody, I have a question regarding percentiles. I know there’s roadmap for percentile functions, which are going to be very welcome as a valuable tool for network monitoring (which should be a big use case for QuestDB).
Still, there are any way of doing it purely with SQL as of now? Even with not-so-good performance.
I have this SQL query for Grafana dashboard:
SELECT
$__time(timestamp),
sum(value)/($__interval_ms/60000) AS "AGREGADO"
FROM "bytinDstNet"
WHERE
$__timeFilter(timestamp) AND
net in($net)
SAMPLE BY $__interval FILL(LINEAR);Building upon @Bolek Ziobrowski previous answer:
WITH aggregateSum AS ( SELECT timestamp, sum(value) AS value FROM bytinDstNet
WHERE $__timeFilter(timestamp) AND
net in($net) SAMPLE BY 1m ORDER BY value ASC),
aggregateSumRow AS ( SELECT timestamp, value, row_number() OVER () AS rownum FROM aggregateSum),
cnt AS ( SELECT count() val FROM aggregateSum )
SELECT $__time(timestamp), value AS "95th percentile" FROM cnt JOIN aggregateSumRow ON CAST (cnt.val*0.95 AS INT) = rownumMany thanks my friend
Beautiful indeed