In a recent post (@wwwiebe/node-red-and-grafana-qo8st2) 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.
(c) All images and photographs, unless otherwise specified, are created and owned by me.
(c) Victor Wiebe
About Me
Amateur photographer. Wannabe author. Game designer. Nerd.
General all around problem-solver and creative type.
My Favourite Tags
| #spaceforce3 | #altphoto | #crappycameraphotos |
| #digitalpinhole | #pinhole | #firehydrant |
