MySQL: Operation on current line and other lines-CodePudding

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 !

CodePudding user response：

You have to use (select rlo) because you can't refer to a column alias in the same query

The 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 JOIN to get null values when there's no matching row.

SELECT t1.dt as rdt, t1.hi as rhi, t2.lo AS rlo, t1.hi - t2.lo AS rdif
FROM mytable AS t1
LEFT JOIN mytable AS t2 ON t2.dt = t1.dt - INTERVAL 10 MINUTE
where t1.dt >= '2022-10-03 09:20:00'
limit 20

DEMO

CodePudding user response：

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

SELECT col AS x, (SELECT x) FORM tbl

is allowed, since x was declared before it was selected, while

SELECT (SELECT x), col AS x FORM tbl

will 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.