Build a histogram in Grafana
Histograms show how data is distributed. You can use one to graph the number of data points that fall into buckets on some scale. For example, histograms are often used to show the spread of financial instruments.
They can answer questions like:
- What is the distribution of the Meta stock price today?
- What was the transaction volume distribution of AMD stock last week?
- What was the distribution of daily returns of the S&P in the past year?
With Grafana, you can plot a histogram by providing data in 1 of 3 formats. Each comes with its own benefits and challenges:
- Raw data: This method does not require you to pre-bucket or pre-aggregate the data. It increases histogram accuracy. But it requires more CPU, memory, and network usage, because all bucketing is done in the browser. This could lead to severe performance issues.
- Pre-bucketed data: You need to configure the data source to pre-bucket your data. According to the Grafana documentation, any source can output pre-bucketed data for a histogram, as long as it meets the data format requirements. For example, it suggests that you can use Elastic Search's histogram bucket aggregation or Prometheus' histogram metric.
- Aggregated data: Grafana also accepts pre-aggregated
time-bucket data. You can aggregate your data using
time_bucketfunction or PostgreSQL's
date_truncfunction. To create the histogram, Grafana further buckets the aggregated data. It automatically selects a bucket size, which is about 10% of your data's total range.
This tutorial shows you how to:
- Create a price/transaction histogram from raw data
- Create a price/transaction histogram from pre-aggregated data
- Create a panel showing multiple histograms
- Create a price/volume histogram
Before you begin, make sure you have:
- Installed Grafana version 8.5 or higher
- Installed TimescaleDB
- Imported the stock trade data from the Getting Started Tutorial
If you are new to Grafana, see the Grafana tutorials to get familiar with creating your first dashboard and visualizations before you start.
The examples in this section use these variables and Grafana functions:
$symbol: a variable used to filter results by stock symbols.
$bucket_interval: the interval size to pass to the
time_bucketfunction when aggregating data.
$__timeTo()::timestamptz: Grafana variables. You change the values of these variables by using the dashboard's date chooser when viewing your graph.
A common histogram for evaluating stock trade data is a price/transaction volume
histogram. This shows the number of trades occurring at a
given price range, within some time interval. To make this
histogram, select the raw
transactions data from the
Creating a price/transaction histogram with raw data
Add this query to a Grafana dashboard:
SELECT time, price FROM stocks_real_time srt WHERE symbol = '$symbol' AND time >= $__timeFrom()::timestamptz and time < $__timeTo()::timestamptz ORDER BY time;
Select a stock from the dashboard variable. Adjust the time range of your dashboard if desired.
The returned data looks like this:
time |price | -----------------------------+--------+ 2022-03-02 17:01:07.000 -0700| 166.33| 2022-03-02 17:01:26.000 -0700|165.8799| 2022-03-02 17:01:31.000 -0700|165.8799| 2022-03-02 17:01:46.000 -0700| 166.43| 2022-03-02 17:02:22.000 -0700| 166.49| 2022-03-02 17:02:40.000 -0700|166.6001| … | … |
The key feature with any time-series data used by Grafana is that it must have a column named
timewith timestamp data. The other columns used for graphing data can have different names, but each time-series chart must have a
timecolumn in the results. For the Histogram visualization, the timestamp values must be in ascending order or you will receive an error.
Select "Histogram" as your visualization type.
Grafana turns the query into a histogram that looks like this:
The histogram shows that the price of $AAPL ranges between $154 and $176. Grafana automatically picks a bucket size for us, in this case $2.
To increase the granularity of the histogram, change the bucket size from 2 to 0.1.
The histogram now looks similar, but shows more detail.
In the previous example, we queried raw data for Apple stock, which often trades
~40 000 times a day. The query returns more than 40 000 rows of data for
Grafana to bucket every refresh interval, which is 30 seconds by default.
This uses a lot of CPU, memory, and network bandwidth. In extreme cases, Grafana
will show you the message:
Results have been limited to 1000000 because the SQL row limit was reached
This means Grafana has decided not to display all rows returned by the query. To solve this problem,
pre-aggregate the data in your query using TimescaleDB's
time_bucket function. With
you need to add a new variable called
Creating a price/transaction histogram with pre-aggregated data
In Grafana, add a new variable called
$bucket_interval, of type
$bucket_intervalvariable to aggregate the price for the selected interval:
SELECT time_bucket('$bucket_interval', time) as time, AVG(price) avg_price FROM stocks_real_time srt WHERE symbol = '$symbol' AND time >= $__timeFrom()::timestamptz and time < $__timeTo()::timestamptz GROUP BY time_bucket('$date_range',time);
This query yields the following histogram:
This second histogram looks similar to the example that uses raw transaction data. But the query returns only around 1000 rows of data with each request. This reduces the network load and Grafana processing time.
To compare the distributions of 2 or more different stocks, create a panel with
multiple histograms. Change the
$symbol variable from a text variable to a
query variable, and enable the multi-value option. This allows you to select
more than one value for
$symbol. The database returns the transactions for all
selected values, and Grafana buckets them in separate histograms.
Creating a panel with multiple price/transaction histograms
Fetch all company symbols from the dataset:
SELECT DISTINCT symbol FROM company ORDER BY symbol ASC;
Create a new dashboard variable with the previous query:
Update the main query to the following:
SELECT time_bucket('$bucket_interval', time) AS time, symbol, AVG(price) AS avg_price FROM stocks_real_time srt WHERE symbol in ($symbol) AND time >= $__timeFrom()::timestamptz AND time < $__timeTo()::timestamptz GROUP BY time_bucket('$bucket_interval', time), symbol;
This query results in the following histograms:
We can clearly see the 3 distinct histograms but it's impossible to tell them apart from each other.
Click on the green line at the left side of the legend and pick a color:
The plot clearly shows the 3 price distributions in different colors.
Besides transaction price, you can also look at trade volumes. The distribution of trade volume shows you how often and how much people are buying a stock.
stocks_real_time hypertable contains a column with the daily cumulative
volume. You can use this to calculate the volume of data for each bucket. First,
find the maximum
day_volume value for a symbol within a bucket. Then subtract
each maximum from the previous bucket's maximum. The difference equals the
volume for that bucket.
You can do this with a pre-aggregation query, using:
lagfunction. Use this to subtract each from from the previous, when the rows are ordered by descending
Creating a price/volume histogram
Create a new histogram panel with the following query:
WITH buckets AS ( SELECT time_bucket('$bucket_interval', time) AS time, symbol, MAX(day_volume) AS dv_max FROM stocks_real_time WHERE time >= $__timeFrom()::timestamptz AND time < $__timeTo()::timestamptz AND day_volume IS NOT NULL AND symbol in ($symbol) GROUP BY time_bucket('$bucket_interval', time), symbol ) SELECT TIME, symbol, CASE WHEN lag(dv_max ,1) OVER (PARTITION BY symbol ORDER BY time) IS NULL THEN dv_max WHEN (dv_max - lag(dv_max, 1) OVER (PARTITION BY symbol ORDER BY time)) < 0 THEN dv_max ELSE (dv_max - lag(dv_max, 1) OVER (PARTITION BY symbol ORDER BY time)) END vol FROM buckets;
This query results in the following histogram:
The plot shows a left-skewed distribution for the AMZN symbol. For many symbols, you might see a distorted distribution, due to outliers representing a few very large volume transactions. There are 2 solutions:
- Limit your query to transactions with volumes less than a certain threshold.
- Use a logarithmic scale. Unfortunately these are not yet supported in Grafana histogram panels.
Found an issue on this page?Report an issue!