Postgres percentile calculation example

percentile_example.sql

with
  /* first generate random metric serie for past 1 day, 360 per hour */
  series as
  (
    select
      cast(
        now() - '1 day'::interval + n*'10 seconds'::interval
        as timestamp with time zone
      ),
      n*random()::double precision

    /* generate appropriate number of series */
    from
      generate_series(1, 24*360) n
  ),
----------------------------------------------------------------------------

  /* second do TSDB's time_bucket('5 minutes'::interval, time) simulation */
  buckets as
  (
    select
      cast(
        to_timestamp(300*floor(extract(epoch from serie.time)/300))
        as timestamp with time zone
      ),
      serie.value::double precision

    /* the previous CTE above */
    from
      series serie(time, value)

    group by
      serie.time,
      serie.value
  )
----------------------------------------------------------------------------

/* third query time serie for last 30 minutes by the 5 minutes buckets */
select
  metric.bucket time,

  /* standard min, avg, max and stddev */
  min(metric.value),
  avg(metric.value),
  max(metric.value),
  stddev(metric.value),

  /* percentiles (cont interpolates) */
  percentile_disc(0.01) within group(order by metric.value) p1,
  percentile_cont(0.01) within group(order by metric.value) p1i,
  percentile_disc(0.10) within group(order by metric.value) p10,
  percentile_cont(0.10) within group(order by metric.value) p10i,
  percentile_disc(0.50) within group(order by metric.value) p50,
  percentile_cont(0.50) within group(order by metric.value) p50i,
  percentile_disc(0.99) within group(order by metric.value) p99,
  percentile_cont(0.99) within group(order by metric.value) p99i

from
  buckets metric(bucket, value)

where
  bucket >= now() - '30 minutes'::interval and
  bucket < now()

group by
  metric.bucket

order by
  metric.bucket desc;