时间戳

首页 > 解决方案 > SQL 语句根据标识符获取时间范围内的所有条目

database - SQL 语句根据标识符获取时间范围内的所有条目

问题描述

我将通过 SNMP 收集的路由器接口数据存储在 MariaDB 中。数据结构如下（简化）：

id     TIMESTAMP              READING              VALUE
============================================================
100    2020-04-15 11:29:51    if03_name            eth0
101    2020-04-15 11:29:51    if03_totalBytesRx    654321
102    2020-04-15 11:29:51    if03_totalBytesTx    123456
103    2020-04-15 11:30:51    if03_totalBytesRx    765432
104    2020-04-15 11:30:51    if03_totalBytesTx    234567

现在要获取接收和传输的数据，eth0可以很容易地选择READING if03_totalBytesRx或if03_totalBytesTx。

例如，我可以执行以下查询来获取在任何给定时间每秒接收的平均比特数：

SELECT
    TIMESTAMP,
    GREATEST(0,(VALUE - LAG(VALUE,1) OVER (ORDER BY TIMESTAMP))*8/(UNIX_TIMESTAMP(TIMESTAMP) - UNIX_TIMESTAMP(LAG(TIMESTAMP,1) OVER (ORDER BY TIMESTAMP)))) as value
FROM history
WHERE
    READING = "if03_totalBytesRx"

不幸的是，有时（我相信当一个接口再次出现故障时）READING和接口之间的映射会发生变化，这样if03就不再是eth0（if03例如可能是eth1或也noSuchInstance），而eth0被分配了另一个READING，例如if29：

id     TIMESTAMP              READING              VALUE
============================================================
200    2020-04-15 12:16:51    if03_totalBytesRx    876543
201    2020-04-15 12:16:51    if03_totalBytesTx    345678
202    2020-04-15 12:17:51    if03_name            noSuchInstance
203    2020-04-15 12:17:51    if03_totalBytesRx    noSuchInstance
204    2020-04-15 12:17:51    if03_totalBytesTx    noSuchInstance
205    2020-04-15 12:17:51    if29_name            eth0
206    2020-04-15 12:17:51    if29_totalBytesRx    987654
207    2020-04-15 12:17:51    if29_totalBytesTx    456789

请注意，if03_name仅在更改时存储在数据库中，而不是每分钟存储一次。

显然，这会导致查询时没有数据READING if03_totalBytesRx（在 的情况下noSuchInstance）或错误数据，即来自另一个接口的数据。

什么是一种可行的方法来选择所有对应的时间范围ifXX_totalBytesRx和ifXX_totalBytesTx对应ifXX_name等于的时间范围eth0？（例如，在上面的例子中 11:29:51 到 12:17:50 usingif03加上从 12:17:51 到NOW()using的所有内容if29，假设 id 207 是数据库中的最后一个条目）

标签： databasemariadb

解决方案

好的，我想我自己找到了一个解决方案，但是它有点慢，所以我对任何改进感到高兴：

首先，我创建了一个视图ubnt，将原始视图拆分READING为一个新字段INTERFACE（下划线之前的部分）和另一个字段READING（下划线之后的部分，重用名称）：

SELECT
    id,
    TIMESTAMP,
    substring_index(READING,'_',1) AS INTERFACE,
    substring_index(READING,'_',-1) AS READING,
    VALUE
FROM history
WHERE READING like 'if%'

第二步是从这个调用创建另一个视图ubnt_if，显示逻辑到物理接口映射的时间范围：

SELECT
    TIMESTAMP AS START,
    COALESCE(LEAD(TIMESTAMP,1) OVER ( PARTITION BY INTERFACE ORDER BY TIMESTAMP),NOW()) AS END,
    INTERFACE AS LOGICAL,
    VALUE AS PHYSICAL
FROM ubnt
WHERE READING = 'name'

第三步是根据时间戳将接口加入原始数据。为此，我使用了另一个名为ubnt_int：

SELECT
    a.TIMESTAMP AS TIMESTAMP,
    a.READING AS READING,
    a.VALUE AS VALUE,
    b.PHYSICAL AS INTERFACE
FROM (
    ubnt a
    LEFT JOIN ubnt_if b ON(
        a.INTERFACE = b.LOGICAL AND
        a.TIMESTAMP BETWEEN b.START AND b.END
    )
)

这会生成一个数据表，我可以根据物理接口进行过滤，而不是可以更改的“逻辑”接口。

我受到另一个问题的答案的启发。

推荐阅读