python - Django/MySQL 中的间隙检测

Question

我将时间序列数据存储在 MySQL InnoDB 中，我使用 Django 的对象关系映射器访问它。

我的问题是:如何才能最好地识别和定位此时间序列数据中的差距？

编辑澄清:虽然获取所有缺失数据点的列表相当容易，但这并不能完全解决我的问题。我只想要差距的开始和结束。连续周期的开始和结束也同样有效。

编辑以进一步说明:该表的 mysql 列如下。 time 是一个标准的 Django DateTimeField。相关数据每 15 分钟采样一次。

mysql> show columns from datalogging_datapoint;
+----------------------+------------+------+-----+---------+----------------+
| Field                | Type       | Null | Key | Default | Extra          |
+----------------------+------------+------+-----+---------+----------------+
| id                   | int(11)    | NO   | PRI | NULL    | auto_increment |
| new_since_parsing    | tinyint(1) | NO   |     | NULL    |                |
| non_public           | tinyint(1) | NO   |     | NULL    |                |
| time                 | datetime   | NO   |     | NULL    |                |
| value                | double     | NO   |     | NULL    |                |
| parent_timeseries_id | int(11)    | NO   | MUL | NULL    |                |
+----------------------+------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

Answer 1

您必须提供某种样本数据以及您喜欢的处理方式。告诉我们您将其存储在 MySQL 中或使用 innodb 不是问题的核心(例如，ORM 处理该问题)。我假设您能够将时间序列数据提取为整数列表，并且您正在尝试从该列表中找出差距的开始/结束位置。

def gaps(seq):
    seq_set = set(seq) # e.g., set([0, 1, 2, 3, 7, 8, 9, 10, 16, 17, 18])
    full_set = set(range(seq[-1]+1)) # set([0,1,2,3,..., 17, 18])
    missing_pts = list(seq_set ^ full_set) # [4, 5, 6, 11, 12, 13, 14, 15]
    missing_pts.sort() # EDIT: originally didn't have this; 
                       # should have as sets are unordered.
    missing_pt_pairs = []
    first_pt = missing_pts[0]
    prev_pt = missing_pts[0]
    for pt in missing_pts:
        if pt - prev_pt > 1:
            missing_pt_pairs.append((first_pt, prev_pt))
            first_pt = pt
        prev_pt = pt
    missing_pt_pairs.append((first_pt, pt))
    return missing_pt_pairs

time_pts = [0,1,2,3,7,8,9,10,16,17,18]
gaps(time_pts) # returns [(4,6), (11,15)], 
# indicating that two gaps are present starting from [4,6] and [11,15]