CODEWITHSUNDEEP
mysqlsliding-window
shankshera
shankshera

Reputation: 985

MySQL - Time Series Sliding Window

I have a MySQL table containing financial market prices.

+------------+------+--------+--------+--------+--------+
|    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 |
+------------+------+--------+--------+--------+--------+

I want to get something like this. This is filtered by pair (where pair = 1). Every row consists of two consecutive rows.

+--------+--------+--------+--------+--------+--------+--------+--------+
| 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 |
+--------+--------+--------+--------+--------+--------+--------+--------+

I tried this query from https://stackoverflow.com/a/5084722/1487781 to get two consecutive dates.

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

It worked but I can't rewrite it to suit my need as I need values and I can't just use max() to do that. Also I need to know how to generalize the solution. For example how if I need three or four consecutive rows.

Upvotes: 2

Views: 2709

Answers (1)

krokodilko
krokodilko

Reputation: 36117

Try this query:

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

Demo: http://www.sqlfiddle.com/#!2/f490d/2

Here is a version with a subquery that determines a next date for given pair number (next date = lowest date that is greater than given date):

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
       )

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

Upvotes: 4

Related Questions