3 ms·
Not entirely the same use case, but I've had some success displaying charts of time series data using Postgres's width_bucket function to group values into a hi
by chrisdalke 6y ago
Not entirely the same use case, but I've had some success displaying charts of time series data using Postgres's width_bucket function to group values into a histogram. width_bucket computes a histogram bucket for a value, which then can be used in a "group by" clause to group and calculate aggregate statistics for each bucket. This is pretty similar to the approach detailed in the post, but it's providing averaged values instead of a randomly sampled distribution.
If you want to display a line chart that is 500px wide, for example, you know you really only need 500 data points. You can treat this chart as a histogram, and compute a min/max/avg for each value. The chart could display the min & max as a high-low band, and if you zoom in further, adjust the histogram interval and requery.
This is what a query would look like with this strategy:
start_timestamp: The start of the histogram range
end_timestamp: The end of the histogram range
num_divisions: The number of histogram divisions
select
start_timestamp + ((bin_id - 1) * ((end_timestamp - start_timestamp) / num_divisions)) as bin_start_at,
start_timestamp + ((bin_id) * ((end_timestamp - start_timestamp) / num_divisions)) as bin_end_at,
average_value,
min_value,
max_value,
num_values
from (
select
avg(value) as average_value,
min(value) as min_value,
max(value) as max_value,
count(*) as num_values,
width_bucket(data_points.timestamp, start_timestamp, end_timestamp, num_divisions) as bin_id
from data_points
where
timestamp > start_timestamp and timestamp < end_timestamp
group by bin_id
order by bin_id asc
) t;
I'm interested to see if anyone else has taken this approach, and if there are any performance considerations I haven't considered!