In a recent post (https://blurtter.com/blurt/@wwwiebe/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.
And the evil Steem just reached 1/2 a dollar ...... crazy