Normalizing Grafana charts with window functions
Published: 2024-01-10 16:33:17

> QuestDB is a high-performance time series database with SQL analytics that can power through market data ingestion and analysis. It's [open source](https://github.com/questdb/questdb) and integrates well with the tools and languages you use. Check us out!

In a previous post, we looked at how to [create dynamic lists of symbols and charts in Grafana](https://questdb.io/blog/manage-large-symbol-lists-questdb-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()`](https://questdb.io/docs/reference/function/window/#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…

```sql
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.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gndknzbii2wc6dxrv635.png)

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?](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wo8b0omsambjqnyemp79.png)

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`](https://questdb.io/docs/reference/sql/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

```sql
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.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bd3nagbbzqyxufx6uo2c.png)

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:

```sql
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 dizzying array of price pairs and movements. It is a strong example of how many assets can be correlated across various ranges.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j3borfc0iiz8dfgt1jnc.png)

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](https://github.com/questdb/questdb/) or [community Slack](https://slack.questdb.io/) and let us know.