Question 113362 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL: Operation on current line and other lines

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 !

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    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:

    mysql> SELECT t1.dt as rdt, t1.hi as rhi, t2.lo AS rlo, t1.hi - t2.lo AS rdif
    ->  FROM valtest_xpar_dbg_i AS t1, valtest_xpar_dbg_i AS t2
    ->   where t1.dt >= '2022-10-03 09:20:00' and t2.dt = t1.dt - INTERVAL 10 MINUTE
    ->    limit 10;
+---------------------+--------+--------+---------+
| 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:31:00 | 4.1020 | 4.1180 | -0.0160 |
| 2022-10-03 09:36:00 | 4.0720 | 4.0940 | -0.0220 |
| 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:52:00 | 4.0560 | 4.0580 | -0.0020 |
+---------------------+--------+--------+---------+
10 rows in set (0,00 sec)

    -> Me, now:

    mysql> SELECT t1.dt as rdt, t1.hi as rhi, t2.lo AS rlo, t1.hi - t2.lo AS rdif
    -> FROM valtest_xpar_dbg_i AS t1
    -> INNER JOIN valtest_xpar_dbg_i AS t2 ON t2.dt = t1.dt - INTERVAL 10 MINUTE
    -> where t1.dt >= '2022-10-03 09:20:00'
    -> limit 10;
+---------------------+--------+--------+---------+
| 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:31:00 | 4.1020 | 4.1180 | -0.0160 |
| 2022-10-03 09:36:00 | 4.0720 | 4.0940 | -0.0220 |
| 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:52:00 | 4.0560 | 4.0580 | -0.0020 |
+---------------------+--------+--------+---------+
10 rows in set (0,00 sec)

    Same result ! But I suppose 'INNER JOIN' version is better.

    -->> Is it realy the same execution in MySQL ? Speed ?

    Thanks again !


  2. 0

    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

    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search