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

How to join tables with different periodicity – Mysql

I have two tables, one is for measurements and every 3 seconds or so a value is written in a very simple structure:

Measurements:

timestamp value
1679474929 516
1679474933 501
1679474936 505

While the second contains all meta information, trigger values, etc. This one is changed sporadically, but randomly. Meta information is valid from the moment of change to a different change that would be sometimes a week and sometimes a month. Thousands of measurements are done between each change.

What I need is to JOIN them together in the way that all the measurements would have meta information from a relevant period in the meta table.

I’m imagining some sort of conditional comparison, something like this.

First, try:

ON measurements.timestamp=meta.timestamp

then

ON DATE_FORMAT(FROM_UNIXTIME(measurements.timestamp), '%Y-%m-%d %H:%i')=DATE_FORMAT(FROM_UNIXTIME(meta.timestamp), '%Y-%m-%d %H:%i')

then

ON DATE_FORMAT(FROM_UNIXTIME(measurements.timestamp), '%Y-%m-%d %H')=DATE_FORMAT(FROM_UNIXTIME(meta.timestamp), '%Y-%m-%d %H')

then

ON DATE_FORMAT(FROM_UNIXTIME(measurements.timestamp), '%Y-%m-%d')=DATE_FORMAT(FROM_UNIXTIME(meta.timestamp), '%Y-%m-%d')

etc. until the condition is satisfied.

Is something like this possible?

Tags:

2

Answers


  1. 0

    You could do it by using the LEAD() window function to get the value of the next meta row’s timestamp:

    SELECT *
FROM measurements
JOIN (
    SELECT *, LEAD(timestamp) OVER (ORDER BY timestamp ASC) AS next_timestamp
    FROM meta
) meta
    ON measurements.timestamp >= meta.timestamp
    AND (
        measurements.timestamp < meta.next_timestamp OR
        meta.next_timestamp IS NULL
    )

    It may be worth testing the performance of the lateral derived table version of Bernd’s solution, if you want to return multiple columns from meta:

    SELECT *
FROM measurements
JOIN LATERAL (
    SELECT *
    FROM meta
    WHERE measurements.timestamp >= meta.timestamp
    ORDER BY meta.timestamp DESC
    LIMIT 1
) m;
    Login or Signup to reply.
  2. 0

    did you mean something like this ?

    SELECT mea.*
 ,( SELECT meta from meta WHERE meta.ts <= mea.ts ORDER by meta.ts DESC LIMIT 1) as meta
FROM `Measurements` mea;

    ** Sample **

    mysql> SELECT * from Measurements;
+----+------------+------+
| id | ts         | val  |
+----+------------+------+
|  1 | 1679474929 |  516 |
|  2 | 1679474933 |  501 |
|  3 | 1679474936 |  505 |
+----+------------+------+
3 rows in set (0.00 sec)

mysql> select * from meta;
+----+------------+-------------+
| id | ts         | meta        |
+----+------------+-------------+
|  1 | 1679474920 | First meta  |
|  2 | 1679474935 | Second Meta |
+----+------------+-------------+
2 rows in set (0.00 sec)

mysql> SELECT mea.*, ( SELECT meta from meta WHERE meta.ts <= mea.ts ORDER by meta.ts DESC LIMIT 1) as meta
    -> FROM `Measurements` mea;
+----+------------+------+-------------+
| id | ts         | val  | meta        |
+----+------------+------+-------------+
|  1 | 1679474929 |  516 | First meta  |
|  2 | 1679474933 |  501 | First meta  |
|  3 | 1679474936 |  505 | Second Meta |
+----+------------+------+-------------+
3 rows in set (0.00 sec)

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

Back To Top
Search