mysql - 使用 MySQL 5.6/7 查找局部最小值、最大值和区间样本

Question

数据点的单表时间序列（例如功率或温度读数）。如何有效地选择一个样本来生成包含所有局部最小值、最大值和使用 MySQL 5.x 的其他数据的第 n 个采样的图形？

其他 SQL 解决方案使用：

1. 自连接，在 id-1 和 id+1 上使用连接，选择 prev.val < val 和 next.val < val 最大值（> 最小值）的值。这假定一个连续的唯一 ID

2. 使用 LEAD 和 LAG 函数选择上一条和下一条记录的类似逻辑

3. 对 ID 进行模数运算以选择一个样本，除非偶然选择，否则该样本会错过最大值/最小值

MySQL 5.x - 临时表上没有自连接，没有 LEAD 和 LAG 函数，并且此数据没有顺序 ID - 因此需要使用临时表来顺序重新分配 ID。

样本数据：

id      datetime            val
194038  2018-07-01 11:30:20 102
194050  2018-07-01 11:31:40 86
194054  2018-07-01 11:31:50 101
194065  2018-07-01 11:33:51 98
194075  2018-07-01 11:35:50 97
194085  2018-07-01 11:37:50 99
194095  2018-07-01 11:39:50 114
194100  2018-07-01 11:40:40 158
194105  2018-07-01 11:40:50 116
194115  2018-07-01 11:41:00 139
194124  2018-07-01 11:41:30 110
194128  2018-07-01 11:41:40 121
194132  2018-07-01 11:41:50 168
194143  2018-07-01 11:43:51 135
194153  2018-07-01 11:45:50 128
194163  2018-07-01 11:47:00 95
194173  2018-07-01 11:49:00 69
194186  2018-07-01 11:51:00 64
194194  2018-07-01 11:51:31 16
194198  2018-07-01 11:51:41 45
194209  2018-07-01 11:53:40 60
194219  2018-07-01 11:55:40 59
194229  2018-07-01 11:57:40 68
194246  2018-07-01 11:59:40 67

使用临时表的解决方案有效，但效率极低：（请参阅内联注释）

DROP TEMPORARY TABLE IF EXISTS tmp;
DROP TEMPORARY TABLE IF EXISTS tmp2;
DROP TEMPORARY TABLE IF EXISTS tmp3;
DROP TEMPORARY TABLE IF EXISTS tmp4;

# Get data of interest into tmp with sequential ID
CREATE TEMPORARY TABLE tmp (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY)
SELECT datetime, val 
FROM log
ORDER BY datetime;

# Make copies of tmp (MySql can't self join on temporary tables)
CREATE TEMPORARY TABLE tmp2
SELECT * FROM tmp;

CREATE TEMPORARY TABLE tmp3
SELECT * FROM tmp;

SELECT A.datetime, A.val
FROM tmp A, tmp2 P, tmp3 N
# Join next and previous records (emulates LAG() and LEAD())
WHERE A.id = P.id + 1
AND A.id = N.id - 1
AND (
        (
        # select local maxima
        A.val >= P.val
        AND A.val >= N.val
        )
    OR (
        # select local minima
        A.val <= P.val
        AND A.val <= N.val
        )
    # select a sampling of other records
    # mod value changed by calling code depending on zoom level of graph     
    OR A.id mod 4 = 0
    )
ORDER BY A.datetime

预期和实际结果：

datetime            val
2018-07-01 11:31:40 86
2018-07-01 11:31:50 101
2018-07-01 11:33:51 98
2018-07-01 11:35:50 97
2018-07-01 11:40:40 158
2018-07-01 11:40:50 116
2018-07-01 11:41:00 139
2018-07-01 11:41:30 110
2018-07-01 11:41:40 121
2018-07-01 11:41:50 168
2018-07-01 11:47:00 95
2018-07-01 11:51:31 16
2018-07-01 11:51:41 45
2018-07-01 11:53:40 60
2018-07-01 11:55:40 59
2018-07-01 11:57:40 68

有没有更好的方法来做到这一点（或优化）？

标签： mysqlgraph