mysql - 在不完全同步时对齐时间戳

Question

我有 3 个进程 A、B 和 C，如下表系列中所定义:

http://sqlfiddle.com/#!2/48f54

CREATE TABLE processA
(date_time datetime, valueA int);

INSERT INTO processA
                (date_time, valueA)
VALUES
('2013-1-8  22:10:00', 100),
('2013-1-8  22:15:00', 100),
('2013-1-8  22:30:00', 100),
('2013-1-8  22:35:00', 100),
('2013-1-8  22:40:00', 100),
('2013-1-8  22:45:00', 100),
('2013-1-8  22:50:00', 100),
('2013-1-8  23:05:00', 100),
('2013-1-8  23:10:00', 100),
('2013-1-8  23:20:00', 100),
('2013-1-8  23:25:00', 100),
('2013-1-8  23:35:00', 100),
('2013-1-8  23:40:00', 100),
('2013-1-9  00:05:00', 100),
('2013-1-9  00:10:00', 100);


CREATE TABLE processB
(date_time datetime, valueB decimal(4,2));

INSERT INTO processB
                (date_time, valueB)
VALUES
('2013-1-08  21:46:00', 3),
  ('2013-1-08  22:11:00', 4),
  ('2013-1-08  22:31:00', 5),
  ('2013-1-08  22:36:00', 6),
  ('2013-1-08  22:41:00', 7),
  ('2013-1-08  23:06:00', 8),
  ('2013-1-08  23:20:00', 2),
  ('2013-1-08  23:46:00', 3),
  ('2013-1-09  00:34:00', 9);


CREATE TABLE processC
(date_time datetime, status varchar(4));

INSERT INTO processC

VALUES
('2013-1-08 18:00:00', 'yes'),
('2013-1-08 19:00:00', 'yes'),
('2013-1-08 20:00:00', 'yes'),
('2013-1-08 21:00:00', 'yes'),
('2013-1-08 22:00:00', 'yes'),
('2013-1-08 23:00:00', 'no'),
('2013-1-08 00:00:00', 'no'),
('2013-1-08 01:00:00', 'no');

如您所见，每个进程的读数发生时间都不相同。

1. ProcessA，如果它发生，每隔 5 分钟发生一次

2. ProcessB，读数发生在不可预测的时间，但通常在一小时内发生多次

3. ProcessC 始终有一个小时值(是或否)。

首先，我想转换 processB，以便每隔 5 分钟就有一个读数，以便数据与 processA 对齐，这样我就可以在 5 分钟的时间间隔标记处对两个表进行简单的连接。对于转换，每 5 分钟的数据应设置为在 [-30,30) 分钟窗口内可用的最近 processB 观察。如果值是等距的，则取平均值。如果在 30 分钟窗口内没有可用的，则将其设置为空。

一旦我有了它，我就可以使用 ProcessC 在 %Y%m%d%H 上进行简单的连接，使用类似下面的方法来获得所有数据在 5 分钟间隔标记处对齐的最终表:

date_format(date_time, '%Y%m%d%H') = date_format(date_time, '%Y%m%d%H')

如果有人有任何指示/指导，我将不胜感激。我很感激。

示例输出:

'2013-1-8  22:10:00', 100, 4, yes    <--- closer to 22:11 than 21:46
'2013-1-8  22:15:00', 100, 4, yes    <--- closer to 22:11 than 21:31
'2013-1-8  22:30:00', 100, 5, yes    <--- closer to 22:31 than 22:11
'2013-1-8  22:35:00', 100, 6, yes    <--- closer to 22:36 than 22:31
'2013-1-8  22:40:00', 100, 7, yes    <--- closer to 22:41 than 22:36
'2013-1-8  22:45:00', 100, 7, yes    <--- closer to 22:41 than 23:06
'2013-1-8  22:50:00', 100, 7, yes    <--- closer to 22:41 than 23:06
'2013-1-8  23:05:00', 100, 8, yes    <--- closer to 23:06 than 23:06
'2013-1-8  23:10:00', 100, 8, no     <--- closer to 23:06 than 23:20
'2013-1-8  23:20:00', 100, 2, no     <--- closer to 23:20 than 23:10
'2013-1-8  23:25:00', 100, 2, no    <--- closer to 23:20 than 23:10
'2013-1-8  23:35:00', 100, 3, no    <--- closer to 23:46 than 23:20
'2013-1-9  00:05:00', 100, 3, no    <--- closer to 23:46 than 00:34
'2013-1-9  00:10:00', 100, 6, no    <--- takes the avg of 3 and 9

Answer 1

其中棘手的部分是从 processB 中检索适当的一行或多行对应于 processA 的每一行如您所想。

让我们一步一步来。

首先，我们需要能够连接 processA 和 processB 以检索候选时间戳对。让我们这样做:

               SELECT a.date_time a, 
                      TIMESTAMPDIFF(SECOND, a.date_time, b.date_time) timediff
                 FROM processA a
                 JOIN processB b 
                   ON TIMESTAMPDIFF(SECOND, a.date_time, b.date_time) >= -1800
                  AND TIMESTAMPDIFF(SECOND, a.date_time, b.date_time) <   1800

这使我们得到满足 [-30, 30) 标准的 a 和 b 时间。这个结果中有很多行；但我们可以检查它以确保我们正确地完成了范围比较。 http://sqlfiddle.com/#!2/48f54/47/0

现在我们需要生成时间窗口来为您的一个或多个匹配的 b 记录搜索每个 a 记录。就像这样。

       SELECT a, 
              MIN(ABS(timediff)) windowsize
          FROM (
               SELECT a.date_time a, 
                      TIMESTAMPDIFF(SECOND, a.date_time, b.date_time) timediff
                 FROM processA a
                 JOIN processB b 
                   ON TIMESTAMPDIFF(SECOND, a.date_time, b.date_time) >= -1800
                  AND TIMESTAMPDIFF(SECOND, a.date_time, b.date_time) <   1800
         ) d
        GROUP BY a  

这会产生两列:第一列是来自 a 的时间戳，第二列是范围内最近的 b 时间戳(或时间戳，如果要对多个时间戳进行平均)的时间范围。此结果集没有任何记录的行没有足够近的 b 记录来考虑。 http://sqlfiddle.com/#!2/48f54/46/0

最后，我们需要检索每个 a 记录的 b 记录值并求平均值。这是。

SELECT processA.date_time date_time,
       processA.valueA valueA,
       AVG(processB.valueB) valueB
  FROM processA
  LEFT JOIN (
        SELECT a, 
               MIN(ABS(timediff)) windowsize
              FROM (
                   SELECT a.date_time a, 
                          TIMESTAMPDIFF(SECOND, a.date_time, b.date_time) timediff
                     FROM processA a
                     JOIN processB b 
                       ON TIMESTAMPDIFF(SECOND, a.date_time, b.date_time) >= -1800
                      AND TIMESTAMPDIFF(SECOND, a.date_time, b.date_time) <   1800
             ) d
          GROUP BY a
    ) j ON processA.date_time = j.a
   LEFT JOIN processB ON (    processB.date_time >= j.a - INTERVAL j.windowsize SECOND
                          AND processB.date_time <= j.a + INTERVAL j.windowsize SECOND
                          AND processB.date_time < j.a + INTERVAL 1800 SECOND)
  GROUP BY processA.date_time, processA.valueA

注意这里有几个开放范围(< 运算符而不是 <= 运算符)。那些是为了适应你的 [-30, 30) 开放范围。这是查询。 http://sqlfiddle.com/#!2/48f54/45/0

最后一个查询将三个表连接在一起:processA ，我们的虚拟表显示了每个时间戳的搜索范围，以及 process B .最后ON子句执行实际的范围搜索。开放范围使它稍微复杂一些。

看看进展如何？从内到外构建查询很有帮助。

不要忘记在 processB.date_time 上添加索引。

我冒昧地离开了 processC 的连接到这个虚拟表给你。