In a recent post (https://victorwiebe.com/2021/02/09/node-red-and-grafana/) I had written about putting together a small Grafana dashboard, powered by a Node Red and MySQL combination (actually, MariaDB, but the difference is academic). The dashboard has one simple purpose: display a chart of the USD price of a few cryptocurrencies that I like to follow (primarily altcoins). The dashboard does precisely what I want it to do, but I found that I also wanted a table that shows quickly what the most recent price for all the coins was at a quick glance. Like this:
Grafana definitely has a table panel type, so building this out was not going to require much in the way of customisation.
What I needed to do this was a way to pull out the most recent value for each of the coins. In pseudo-query language, the request would read something like this:
GET the most recent value for EACH coin, regardless of WHEN that value was put into the database.
The rationale behind the “regardless” clause is that each entry might be a second apart: 12:01.01, 12:01.01, and 12:01.02, for instance.
My database schema currently consists of only two tables: coin_names, and coin_values:
Because I cannot guarantee that the most recent timestamp for each of the coin_id,value combinations – for each coin – is universal, I had to essentially build a single table join on itself. It’s somewhat self-referential, but the jist of it is to think of the table as two different tables with identical data, and then join that. If I had multiple tables – one for each coin – my approach would be different, but I don’t.
In short, my approach is to build a join across three tables: coin_names, coin_values(one) and coin_values(two).
The query ended up being this (and yes, I know I’m using an archaic method of joining the tables; it need not be pointed out).
SELECT t3.coin_name,t1.value FROM coin_values t1,coin_names t3 WHERE t1.coin_id = t3.coin_id AND t1.timestamp = (SELECT max(t2.timestamp) FROM coin_values t2 WHERE t1.coin_id = t2.coin_id)
Believe me, I only want to type that once, and that’s a simple query for what I’ve done in the past. The Thing To Do was to make an SQL View out of it, so I only need to query the view:
CREATE VIEW "last_value" AS (SELECT t3.coin_name,t1.value FROM coin_values t1,coin_names t3 WHERE t1.coin_id = t3.coin_id AND t1.timestamp = (SELECT max(t2.timestamp) FROM coin_values t2 WHERE t1.coin_id = t2.coin_id))
Now, when I want to query for the most recent values, I have a view that I can query that contains only the information I want.
MariaDB [coins]> SELECT * FROM last_value; +-----------------------+------------+ | coin_name | value | +-----------------------+------------+ | Hive | 0.210398 | | Basic Attention Token | 0.445418 | | Bitcoin Cash | 502.4 | | Doge Coin | 0.072883 | | Steem | 0.326043 | | Appics | 0.0275567 | | Presearch | 0.073745 | | Blurt | 0.00559335 | +-----------------------+------------+
And this is the query that I dumped into the new Grafana Table panel. Now, when I choose a coin to view, two panels update, and the table stays static to show all the coins.