mariadb 如何连接不同周期的表

wn9m85ua 于 2023-03-23 发布在其他

关注(0)|答案(2)|浏览(95)

我有两个表，一个用于测量，每隔3秒左右就会以非常简单的结构写入一个值：
测量：
| 时间戳|价值|
| - ------|- ------|
| 1679474929|五百一十六|
| 1679474933|小行星501|
| 1679474936|小五|
而秒包含所有 meta信息、触发值等。这一个是零星的，但随机的。元信息从改变的时刻到不同的改变是有效的，有时是一周，有时是一个月。在每次改变之间进行数千次测量。
我需要的是将它们连接在一起，使所有度量都具有元表中相关时期的 meta信息。
我在想象某种有条件的比较，就像这样。
首先，尝试：
ON measurements.timestamp=meta.timestamp
那么
ON DATE_FORMAT(FROM_UNIXTIME(measurements.timestamp), '%Y-%m-%d %H:%i')=DATE_FORMAT(FROM_UNIXTIME(meta.timestamp), '%Y-%m-%d %H:%i')
那么
ON DATE_FORMAT(FROM_UNIXTIME(measurements.timestamp), '%Y-%m-%d %H')=DATE_FORMAT(FROM_UNIXTIME(meta.timestamp), '%Y-%m-%d %H')
那么
ON DATE_FORMAT(FROM_UNIXTIME(measurements.timestamp), '%Y-%m-%d')=DATE_FORMAT(FROM_UNIXTIME(meta.timestamp), '%Y-%m-%d')
直到满足条件为止。
这可能吗？

mariadb

来源：https://stackoverflow.com/questions/75810306/how-to-join-tables-with-different-periodicity

2条答案

按热度按时间

icnyk63a1#

你可以使用LEAD（）window function来获取下一个meta行的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
    )

如果你想从meta返回多个列，可能值得测试一下Bernd解决方案的lateral derived table版本的性能：

SELECT *
FROM measurements
JOIN LATERAL (
    SELECT *
    FROM meta
    WHERE measurements.timestamp >= meta.timestamp
    ORDER BY meta.timestamp DESC
    LIMIT 1
) m;

赞(0）回复(0）举报 2023-03-23

8mmmxcuj2#

你是说像这样的事吗

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

样品

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>

赞(0）回复(0）举报 2023-03-23

我来回答

mariadb 如何连接不同周期的表

2条答案

相关问题

热门标签

最新问答