Normalizing Grafana charts with window functions

Cool market data splash image. It's very colourful and fun. Credit to DALL-E.
QuestDB is a high performance time series database with SQL analytics that can power through data ingestion and analysis. It's open source and integrates with many tools and languages. Give us a try!

In a previous post, we looked at how to create dynamic lists of symbols and charts in Grafana. While this is great to watch individual charts for different symbols, sometimes you may want to merge all the charts together to compare changes in a visual manner.

One of our community members had been struggling with this over time and had tried various approaches. As a result, we created an implementation of the first_value() window function which easily solves the underlying issues with these types of visualizations. This article explains how it's applied.

The simple approach#

Unfortunately … does not work. If we were to create a chart with the prices of ETH-USD and BTC-USD, then we would end up with something like this…

SELECT
timestamp,
symbol,
price
FROM
trades
WHERE
$__timeFilter(timestamp)
AND symbol IN ('ETH-USD', 'BTC-USD')
A price chart, one line very high up, one very low down. It's clear there's some volatile action on a very granular scale, but the lines are virtually flat across the X axis.

The chart uses the partition by values Grafana transformation to generate two series. As you can see, the price of 'BTC-USD' is roughly 17x greater than 'ETH-USD'. On the same scale, the time series are hard to compare. If we wanted to look at volatility or another moving metric, we'd need lots of patience and a very strong magnifying glass.

Overriding the axis#

One solution to create comparable data is to assign each series to its own axis. We can achieve this with an override on the price ETH-USD series, setting the axis placement property to the right:

An improvement, the two lines are overlapped over each other and through two axis cover two very different time ranges. We can compare them now, but is it accurate?
Click to zoom

While it makes it easier to see how each series is moving, we are missing any sense of scale because both axes scales remain independent. As a result, the two series do not start at the same point. It is hard to tell if one is moving relatively more than the other in a given direction. In addition, this can quickly become messy if we need to compare more than two series.

The overkill#

Before window functions, our community member found a trick to achieve what they wanted. It consisted of using sub-queries to do the following…

  • Get the first value of the series for each symbol first
  • Get all the values for each symbol for the time period current
  • Cross join both of the above based on symbol
  • Calculate the percentage change for all symbols, for example current/first * 100

While this achieved the desired results, it was a heavy query to write and run due to the heavy joins and multiple sub-queries. Over large time periods and with many symbols, this could result in too many data points for Grafana dashboards and thus necessitate the use of SAMPLE BY to further reduce its time frame. While adequate, it further complicates the query. We can do better.

The first_value() window function#

The first_value() window function returns the first value of a metric over a time window. With it, we can do two things:

  1. Easily access the value as of the first timestamp of the series
  2. Use the resultant value as a normalizing factor to compare the evolution of the series
WITH series AS (
SELECT
timestamp,
symbol,
price,
first_value(price) OVER (
PARTITION BY symbol
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
FROM
trades
WHERE
$__timeFilter(timestamp)
AND symbol IN ('ETH-USD', 'BTC-USD')
)
SELECT
timestamp,
symbol,
price / first_value AS perf
FROM
series
A price comparison across two appropriately scoped ranges. The data diverge and tell a different story than the former graph.
Click to zoom

Our new approach revealed that our earlier twin-Y axis chart was misleading. It showed us that ETH and BTC moved up by roughly the same relative amount. However, with the normalized chart above we can see everything in the same scale. ETH significantly outperformed BTC over the time interval!

This sort of visualization is quite powerful as it can synthesize market activity across many instruments in a more simple chart. But we can do even better:

WITH data AS (
SELECT
timestamp,
symbol,
price,
first_value(price) OVER (
PARTITION BY symbol
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
FROM
trades
WHERE
$__timeFilter(timestamp)
)
SELECT
timestamp,
symbol,
price / first_value AS perf
FROM
data

Let's unpack the differences.

In the prior query, the expression named series filters the trades table to include only rows where the symbol is either 'ETH-USD' or 'BTC-USD' and the timestamp satisfies the condition specified by $__timeFilter(timestamp).

In our new query, the expression named data filters the trades table to include only rows where the timestamp satisfies the condition specified by $__timeFilter(timestamp). It does not filter based on the symbol.

The prior query will only calculate and return performance (perf) for 'ETH-USD' and 'BTC-USD', while our new query will calculate and return performance (perf) for all symbols in the trades table that satisfy the time filter condition.

Now, we can configure Grafana as follows:

  • Legend mode = Table
  • Legend placement = Right
  • Legend values = Last

We then end up with the following chart summarizing how each crypto pair performed in relative terms in the last few hours:

A dizzy array of price pairs and movements. It is a strong example of how many assets can be correlated across various ranges.
Click to zoom

The chart makes it pretty apparent that SOL pairs are up, while MATIC and DOT are strongly down. While it's easy to get this by computing the ratio of first and last prices over the interval, having a full time series helps understand what's going on with higher fidelity. We can ask: Did the pair jump up quickly, did it trend upwards, and so on.

Next steps#

Window functions greatly simplify query complexity and performance. Perhaps most important: It leads to higher quality analysis. While we've made progress in making it easier to compare values across time, our approach still requires a sub-query. QuestDB is considering a separate function such as normalised_value(field, base) where base is the scale. For example: Does the series start at 100, at 1, or somewhere else.

If you're interested in that functionality or have any other feedback, please drop by our open source repository or community Slack and let us know.

Download QuestDB Open source under Apache 2.0. Blazing fast ingest. SQL analytics.