MySQL - 时间序列滑动窗口

Question

我有一个包含金融市场价格的 MySQL 表。

+------------+------+--------+--------+--------+--------+
|    date    | pair |  open  |  high  |  low   | close  |
+------------+------+--------+--------+--------+--------+
| 12/9/2009  |    1 | 1.4703 | 1.4783 | 1.4668 | 1.4727 |
| 12/9/2009  |    2 | 1.6287 | 1.6378 | 1.6167 | 1.6262 |
| 12/9/2009  |    3 | 0.9038 | 0.9116 | 0.9015 | 0.9086 |
| 12/9/2009  |    4 | 88.435 | 88.71  | 87.36  | 87.865 |
| 12/9/2009  |    5 | 1.064  | 1.0664 | 1.0515 | 1.0545 |
| 12/10/2009 |    1 | 1.4725 | 1.4761 | 1.4683 | 1.4732 |
| 12/10/2009 |    2 | 1.6261 | 1.6348 | 1.6214 | 1.6279 |
| 12/10/2009 |    3 | 0.9086 | 0.9192 | 0.908  | 0.9166 |
| 12/10/2009 |    4 | 87.87  | 88.47  | 87.73  | 88.2   |
| 12/10/2009 |    5 | 1.0546 | 1.0584 | 1.0479 | 1.0517 |
| 12/11/2009 |    1 | 1.4733 | 1.4778 | 1.4586 | 1.4615 |
| 12/11/2009 |    2 | 1.6278 | 1.634  | 1.6197 | 1.6262 |
| 12/11/2009 |    3 | 0.9164 | 0.9197 | 0.909  | 0.9128 |
| 12/11/2009 |    4 | 88.2   | 89.82  | 88.195 | 89.115 |
| 12/11/2009 |    5 | 1.0517 | 1.0624 | 1.0483 | 1.0602 |
+------------+------+--------+--------+--------+--------+

我想得到这样的东西。这是按对过滤的(where pair = 1)。每行由两个连续的行组成。

+--------+--------+--------+--------+--------+--------+--------+--------+
| open1  | high1  |  low1  | close1 | open2  | high2  |  low2  | close2 |
+--------+--------+--------+--------+--------+--------+--------+--------+
| 1.4703 | 1.4783 | 1.4668 | 1.4727 | 1.4725 | 1.4761 | 1.4683 | 1.4732 |
| 1.4725 | 1.4761 | 1.4683 | 1.4732 | 1.4733 | 1.4778 | 1.4586 | 1.4615 |
+--------+--------+--------+--------+--------+--------+--------+--------+

我尝试了来自 https://stackoverflow.com/a/5084722/1487781 的查询获得两个连续的日期。

select (
    select max(t1.date)
    from data as t1
    where t1.date < t2.date
    and t1.pair = 1
    ) as date1,
t2.date as date2
from data as t2

它有效，但我无法重写它以满足我的需要，因为我需要值而且我不能只使用 max() 来做到这一点。我还需要知道如何概括解决方案。例如，如果我需要连续三行或四行。

Answer 1

试试这个查询:

SELECT d1.date date1,
       d2.date date2,
       d1.pair,
       d1.open open1,
       d1.high high1,
       d1.low low1,
       d1.close close2,
       d2.open open2,
       d2.high high2,
       d2.low low2,
       d2.close close2
FROM table1 d1
JOIN table1 d2
ON d1.pair = d2.pair
   AND d1.date = d2.date - interval 1 day

演示:http://www.sqlfiddle.com/#!2/f490d/2

---

这是一个带有子查询的版本，它确定给定对数的下一个日期(下一个日期 = 大于给定日期的最低日期):

SELECT d1.date date1,
       d2.date date2,
       d1.pair,
       d1.open open1,
       d1.high high1,
       d1.low low1,
       d1.close close2,
       d2.open open2,
       d2.high high2,
       d2.low low2,
       d2.close close2
FROM table1 d1
JOIN table1 d2
ON d1.pair = d2.pair
   AND d2.date = (
          SELECT min(date)
          FROM table1 t
          WHERE t.date > d1.date
            AND t.pair = d1.pair
       )

演示:--> http://www.sqlfiddle.com/#!2/f490d/9