mysql - 关于 : creating calculated tables based on scraped data 我最好的方法是什么

Question

我的 vps 上运行一些蜘蛛，每天抓取数据，并将数据存储在 MySQL 中。

我需要根据不同数据源的数据构建一个非常复杂的时间序列模型。

在这里我遇到了一个问题:

我需要根据我抓取的数据创建一个新的计算表。该模型相当复杂，涉及历史原始数据和计算数据。我本来打算写一个 python 脚本来做到这一点，但它似乎不够高效。

然后我意识到我可以在 MySQL 中创建一个 View 并以嵌套 SQL 查询的格式编写我的模型。也就是说，我希望 View 能够具体化(MySQL 现在不支持)，并且每天有新数据进来时可以刷新 View 。

我知道有一个第三方插件叫flex***，但是我在网上搜索了一下，似乎不太容易安装和维护。

我最好的方法是什么？

感谢您的帮助。

================================================== =========================

补充一点，我做的时间序列模型非常复杂，它涉及到:

• 原始数据的滚动平均值
• 对上述滚动平均数据进行滚动平均

所以这取决于原始数据和之前计算的数据。

时间戳解决方案并没有真正解决问题的复杂性。

我只是不确定最好的方法。

Answer 1

暂且不谈是否应该使用专用的时间序列工具，例如 rrdtool或carbon ，mysql提供了实现半物化 View 所需的功能，例如给定的数据批处理按日期合并:

SELECT DATE(event_time), SUM(number_of_events) AS events, 
, SUM(metric) AS total
, SUM(metric)/SUM(number_of_events) AS average
FROM (
  SELECT pc.date AS event_time, events AS number_of_events
  , total AS metric
  FROM pre_consolidated pc
  UNION
  SELECT rd.timestamp, 1
  , rd.metric
  FROM raw_data rd
  WHERE rd.timestamp>@LAST_CONSOLIDATED_TIMESTAMP 
) 
GROUP BY DATE(event_time)

(请注意，尽管您可以将其创建为 View 并访问 IME，但 MySQL 并不是最擅长优化涉及 View 的查询，您可能最好使用上述等效项作为构建查询的模板)

维护 @LAST_CONSOLIDATED_TIMESTAMP 准确记录的最灵活方法是向 raw_data 表添加一个状态列(以避免锁定并使用事务来确保一致性)以及事件时间戳上的索引，然后定期添加:

UPDATE raw_data 
SET state='PROCESSING' 
WHERE timestamp>=@LAST_CONSOLIDATED_TIMESTAMP
AND state IS NULL;

INSERT INTO pre_consolidated (date, events, total)
SELECT DATE(rd.timestamp), COUNT(*), SUM(rd.metric)
FROM raw_data
WHERE timestamp>@LAST_CONSOLIDATED_TIMESTAMP
AND state='PROCESSING'
GROUP BY DATE(rd.timestamp);

SELECT @NEXT_CONSOLIDATED_TIMESTAMP := MAX(timestamp)
FROM raw_data
WHERE timestamp>@LAST_CONSOLIDATED_TIMESTAMP
AND state='PROCESSING';

UPDATE raw_data
SET state='CONSOLIDATED'
WHERE timestamp>@LAST_CONSOLIDATED_TIMESTAMP
AND state='PROCESSING';

SELECT @LAST_CONSOLIDATED_TIMESTAMP := @NEXT_CONSOLIDATED_TIMESTAMP;

(您应该想办法在 DBMS session 之间保留 LAST_CONSOLIDATED_TIMESTAMP)

因此基本查询(以允许多个具有相同时间戳的事件)应该是:

SELECT DATE(event_time), SUM(number_of_events) AS events, 
, SUM(metric) AS total
, SUM(metric)/SUM(number_of_events) AS average
FROM (
  SELECT pc.date AS event_time, events AS number_of_events
  , total AS metric
  FROM pre_consolidated pc
  UNION
  SELECT rd.timestamp, 1
  , rd.metric
  FROM raw_data rd
  WHERE rd.timestamp>@LAST_CONSOLIDATED_TIMESTAMP
  AND state IS NULL
) 
GROUP BY DATE(event_time)

只要您相当频繁地应用合并，将状态变量添加到时间戳索引可能会降低更新的整体性能。