How to get timestamp associated with percentile(x) value using timescale db time_bucket

Viewed 93

I need find percentile(50) value and its timestamp using timescale db time-bucket. Finding P50 is easy but I don't know how to get the time stamp.

    Select time_bucket('120 sec',timestamp_utc) as interval_size,
    
    first(timestamp_utc,int_val) as minTime,
    min(int_val) as minVal,
        
    last(timestamp_utc,int_val) as maxTime,
    max(int_val) as maxVal,
    
    -- timestamp of percentile value below.
    percentile_disc(0.5) within group (order by int_val) as medianVal
            
    from timeseries.raw
    where timestamp_utc > NOW() - INTERVAL '10 min'
    AND tag_id = 59560544877390423
    group by interval_size
    order by interval_size desc
1 Answers

I think what you're looking for we can do by selecting where the int_val is equal to the median value in a lateral (percentile_disc does ensure that there is a value exactly equal to that value, there may be more than one depending on what you want there you could deal with the more than one case in different ways), building on a previous answer and making it work a bit better I think would look something like this:

WITH p50 AS (
 Select time_bucket('120 sec',timestamp_utc) as interval_size,
    
    first(timestamp_utc,int_val) as minTime,
    min(int_val) as minVal,
        
    last(timestamp_utc,int_val) as maxTime,
    max(int_val) as maxVal,
    
    -- timestamp of percentile value below.
    percentile_disc(0.5) within group (order by int_val) as medianVal
            
    from timeseries.raw
    where timestamp_utc > NOW() - INTERVAL '10 min'
    AND tag_id = 59560544877390423
    group by interval_size
    order by interval_size desc
) SELECT p50.*, rmed.* 
FROM p50, LATERAL (SELECT * FROM timeseries.raw r
-- copy over the same where clause from above so we're dealing with the same subset of data
   WHERE timestamp_utc > NOW() - INTERVAL '10 min'
    AND tag_id = 59560544877390423
-- add a where clause on the median value
   AND r.int_val = p50.medianVal
-- now add a where clause to account for the time bucket
   AND r.timestamp_utc >= p50.interval_size
   AND r.timestamp_utc < p50.interval_size + '120 sec'::interval
-- Can add an order by something desc limit 1 if you want to avoid ties
) rmed;

Note that this will do a second scan of the table, it should be reasonably efficient, especially if you have an index on that column, but it will cause another scan, there isn't a great way that I know of of doing it without a second scan.

Related