I have a table that contains this (HIght value, LOw value data according to a DatetTime):
mysql> select dt, hi, lo from mytable where dt >='2022-10-03 09:20:00' limit 20;
+---------------------+--------+--------+
| dt | hi | lo |
+---------------------+--------+--------+
| 2022-10-03 09:21:00 | 4.1200 | 4.1180 |
| 2022-10-03 09:24:00 | 4.1080 | 4.1040 |
| 2022-10-03 09:25:00 | 4.1040 | 4.1000 |
| 2022-10-03 09:26:00 | 4.0960 | 4.0940 |
| 2022-10-03 09:28:00 | 4.0940 | 4.0920 |
| 2022-10-03 09:29:00 | 4.0980 | 4.0940 |
| 2022-10-03 09:31:00 | 4.1020 | 4.0980 |
| 2022-10-03 09:32:00 | 4.1000 | 4.1000 |
| 2022-10-03 09:33:00 | 4.0940 | 4.0940 |
| 2022-10-03 09:36:00 | 4.0720 | 4.0720 |
| 2022-10-03 09:37:00 | 4.0600 | 4.0500 |
| 2022-10-03 09:39:00 | 4.0620 | 4.0560 |
| 2022-10-03 09:42:00 | 4.0660 | 4.0580 |
| 2022-10-03 09:47:00 | 4.0620 | 4.0620 |
| 2022-10-03 09:48:00 | 4.0620 | 4.0620 |
| 2022-10-03 09:50:00 | 4.0580 | 4.0580 |
| 2022-10-03 09:51:00 | 4.0580 | 4.0580 |
| 2022-10-03 09:52:00 | 4.0560 | 4.0540 |
| 2022-10-03 09:53:00 | 4.0460 | 4.0460 |
| 2022-10-03 09:55:00 | 4.0420 | 4.0360 |
+---------------------+--------+--------+
20 rows in set (0,00 sec)
I want to obtain, in MySQL (because I’m learning a little), for each line the differance (rdif) between the ‘hi’ of the line and the ‘lo’ of 10 minutes ago.
I get it like this:
(I know, the ‘as’ are optional, but it helps to understand better)
mysql> select dt as rdt, hi as rhi, (select lo from mytable where dt = rdt-interval 10 minute) as rlo, (hi-(select rlo)) as rdif from mytable where dt >= '2022-10-03 09:20:00' limit 20;
+---------------------+--------+--------+---------+
| rdt | rhi | rlo | rdif |
+---------------------+--------+--------+---------+
| 2022-10-03 09:21:00 | 4.1200 | 3.9900 | 0.1300 |
| 2022-10-03 09:24:00 | 4.1080 | 4.0180 | 0.0900 |
| 2022-10-03 09:25:00 | 4.1040 | 4.0500 | 0.0540 |
| 2022-10-03 09:26:00 | 4.0960 | 4.0800 | 0.0160 |
| 2022-10-03 09:28:00 | 4.0940 | NULL | NULL |
| 2022-10-03 09:29:00 | 4.0980 | NULL | NULL |
| 2022-10-03 09:31:00 | 4.1020 | 4.1180 | -0.0160 |
| 2022-10-03 09:32:00 | 4.1000 | NULL | NULL |
| 2022-10-03 09:33:00 | 4.0940 | NULL | NULL |
| 2022-10-03 09:36:00 | 4.0720 | 4.0940 | -0.0220 |
| 2022-10-03 09:37:00 | 4.0600 | NULL | NULL |
| 2022-10-03 09:39:00 | 4.0620 | 4.0940 | -0.0320 |
| 2022-10-03 09:42:00 | 4.0660 | 4.1000 | -0.0340 |
| 2022-10-03 09:47:00 | 4.0620 | 4.0500 | 0.0120 |
| 2022-10-03 09:48:00 | 4.0620 | NULL | NULL |
| 2022-10-03 09:50:00 | 4.0580 | NULL | NULL |
| 2022-10-03 09:51:00 | 4.0580 | NULL | NULL |
| 2022-10-03 09:52:00 | 4.0560 | 4.0580 | -0.0020 |
| 2022-10-03 09:53:00 | 4.0460 | NULL | NULL |
| 2022-10-03 09:55:00 | 4.0420 | NULL | NULL |
+---------------------+--------+--------+---------+
20 rows in set (0,00 sec)
The ‘NULL’ is normal because there is not always the previous minute that corresponds (in addition it suits me in this case)
But I have questions:
1)
In "(hi-(select rlo)) as rdif" why can’t I just use ‘rlo’ (have to add the select)?
(Even if you answer the question below, please answer this one anyway)
2)
How to avoid double select-from in table? (is that a subquery?)
There must be better… What do you suggest?
3)
I then consider other operations, in this style, more or less simple but complicated for me in MySQL. (calculations plus column updates…, not necessarily selections to display…)
Do I have a better way to simply read/write the table and code these calculations in my app in nodejs (which I master: I’m learning a little MySQL, ok, but I still want to finsh my app…)
(How much would run time be?)
Thanks !
3
Answers
Thanks to Barmar and Mihe, I understood LEFT JOIN and INNER JOIN.
And I think that INNER JOIN is what I often used without knowing it (without specifying INNER JOIN in the query):
-> Me, before:
-> Me, now:
Same result ! But I suppose 'INNER JOIN' version is better.
-->> Is it realy the same execution in MySQL ? Speed ?
Thanks again !
You have to use
(select rlo)because you can’t refer to a column alias in the same queryThe more proper way to do this is with a self-join, then you don’t need multiple selects for each calculation. You need to use
LEFT JOINto get null values when there’s no matching row.DEMO
ad 1) you’ve got to think of the logical execution order of SQL queries which is roughly: FROM (data source), WHERE (filter), SELECT (projection).
That is, the expressions in the SELECT clause are applied to the filtered data specified in the FROM and WHERE clauses. Therefore it makes perfect sense that an expression can’t refer directly to another expression in the SELECT clause.
But there’s an exception: subqueries. They are evaluated on their own, have their own expression list and can contain backward references to column aliases, that is
is allowed, since
xwas declared before it was selected, whilewill result in an error, because forward references are not supported.
Although this is poorly documented, you can find more information on subqueries in section 13.2.11 of the MySQL 8.0 Reference Manual.
ad 2) see the answer of user Barmar. The LEFT JOIN means: give me all record of the left table and join them with the records of the right table based on the ON condition. If there’s no matching record in the right table, use NULL values.
An INNER JOIN would only return matching records, that is records from the left table which have a matching record in the right table (based on the ON condition).
ad 3) Of course, you can calculate in MySQL, too. That’s what you already do, e.g. by
t1.dt - INTERVAL 10 MINUTE. Some people try to do anything in SQL (if possible all kind of things in a single query…) which is something I wouldn’t do. Keep it simple.