MySQL - 将数据点转换为范围

Question

我有一个测量数据库，其中指示传感器、读数以及读数的时间戳。仅当发生变化时才会记录测量结果。我想生成一个结果集，显示每个传感器正在读取特定测量值的范围。

时间戳以毫秒为单位，但我以秒为单位输出结果。

这是表格:

CREATE TABLE `raw_metric` (
    `row_id` BIGINT NOT NULL AUTO_INCREMENT,
    `sensor_id` BINARY(6) NOT NULL,
    `timestamp` BIGINT NOT NULL,
    `angle` FLOAT NOT NULL,
    PRIMARY KEY (`row_id`)
)

现在我正在使用子查询获得我想要的结果，但是当有很多数据点时，它相当慢:

SELECT row_id,
    HEX(sensor_id),
    angle,
    (
        COALESCE((
                SELECT MIN(`timestamp`)
                FROM raw_metric AS rm2
                WHERE rm2.`timestamp` > rm1.`timestamp`
                    AND rm2.sensor_id = rm1.sensor_id
                ), UNIX_TIMESTAMP() * 1000) - `timestamp`
        ) / 1000 AS duration
FROM raw_metric AS rm1

本质上，为了获取范围，我需要获取下一个读数(或者如果没有另一个读数，则使用当前时间)。子查询查找晚于当前时间戳但来自同一传感器的最小时间戳。

此查询不会经常发生，因此我不想在时间戳列上添加索引并减慢插入速度。我希望有人能提出有关替代方法的建议。

更新:row_id 的应该随着时间戳一起递增，但由于网络延迟问题无法保证。因此，具有较低 row_id 的条目可能会出现在较晚的 row_id 之后，尽管可能性不大。

Answer 1

这也许作为评论比作为解决方案更合适，但对于评论来说太长了。

您正尝试在MySQL 中实现lead() 函数，不幸的是，MySQL 没有窗口函数。您可以切换到 Oracle、DB2、Postgres、SQL Server 2012 并使用其中的内置(和优化)功能。好吧，这可能不现实。

因此，根据您的数据结构，您需要执行相关子查询或非等值连接(实际上是部分等值连接，因为 sensor_id 上存在匹配)。除非添加索引，否则这些操作将是昂贵的操作。除非您每秒添加测量数十次，否则索引上的额外开销应该不是什么大问题。

您还可以更改数据结构。如果您有一个“传感器计数器”，它是枚举读数的连续数字，那么您可以将其用作等值连接(尽管为了获得良好的性能，您可能仍然需要索引)。将其添加到表中需要有一个触发器——并且在插入时这可能比索引的性能更差。

如果您只有少量传感器，您可以为每个传感器创建一个单独的表。哦，我能感受到这个建议带来的呻吟。但是，如果您这样做了，那么自动递增的 id 将发挥相同的作用。老实说，只有当我能数出每只手上的传感器数量时，我才会这样做。

最后，我可能建议您在插入期间进行命中，并在每个记录上设置“有效”和“结束”时间(以及传感器 ID 和时间戳或 ID 上的索引)。使用这些附加列，您可能会发现该表的更多用途。

如果您只对一个传感器执行此操作，则为该信息创建一个临时表并使用自动递增的 id 列。然后将数据插入其中:

insert into temp_rawmetric (orig_row_id, sensor_id, timestamp, angle)
    select orig_row_id, sensor_id, timestamp, angle
    from raw_metric
    order by sensor_id, timestamp;

确保您的表具有自动递增的 temp_rawmetric_id 列和主键(自动创建索引)。 order by 确保它根据时间戳递增。

然后您可以执行查询:

select trm.sensor_id, trm.angle,
       trm.timestamp as startTime, trmnext.timestamp as endTime
from temp_rawmetric trm left outer join
     temp_rawmetric trmnext
     on trmnext.temp_rawmetric_id = trm.temp_rawmetric_id+1;

这将需要传递原始数据以额外的数据，然后在临时表上进行主键联接。第一个可能需要一些时间。第二个应该很快。