Блог

Мне нужно найти значение процентиля(50) и его временную метку, используя временную шкалу бд. Найти P50 легко, но я не знаю, как получить отметку времени.

     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
 

Я думаю, что то, что вы ищете мы можем сделать выбрав, где int_val равное значение медианы в боковое ( percentile_disc не убедитесь, что существует значение точности равно этому значению, может быть больше, чем один в зависимости от того, что вы хотите есть, вы могли бы справиться с более чем одном случае по-разному), опираясь на предыдущий ответ и заставить его работать немного лучше, я думаю, будет выглядеть примерно так:

 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;
 

Обратите внимание, что это приведет ко второму сканированию таблицы, оно должно быть достаточно эффективным, особенно если у вас есть индекс в этом столбце, но это вызовет еще одно сканирование, насколько я знаю, нет отличного способа сделать это без второго сканирования.