mysql select query for candle stick market data

Question

I am trying to select a summary of candle data from a mysql table that represents trading prices and volume of markets.

The table holds data for 1 minute candle data. I would like to get a 5 minute summary (essentially interpreting it as a 5 minute candle).

The table looks like this:

+--------+------+------+------+-------+--------+-------+
| market | open | high | low  | close | volume | time  |
+--------+------+------+------+-------+--------+-------+
| ABC    | 13.6 | 15.4 | 11.2 | 14.1  | 299.4  | 11:59 |
| ABC    | 14.1 | 16.8 | 12.5 | 15.3  | 342.2  | 12:00 |
| ABC    | 15.3 | 16.3 | 11.2 | 14.2  | 351.5  | 12:01 |
| ABC    | 14.2 | 15.5 | 12.3 | 13.2  | 374.2  | 12:02 |
| DEF    | 93.7 | 94.3 | 90.7 | 93.2  | 1123.3 | 11:59 |
| DEF    | 93.2 | 96.5 | 91.1 | 96.3  | 1232.8 | 12:00 |
| DEF    | 96.3 | 98.2 | 95.3 | 95.4  | 1390.4 | 12:01 |
| DEF    | 95.4 | 97.6 | 93.7 | 94.3  | 1360.9 | 12:02 |
+--------+------+------+------+-------+--------+-------+

I need to select from this table to generate a new candle that represents a larger time frame. For example, to represent a 5 minute candle I need to combine data from five 1 minute candles.

The new candle needs to follow the following rules

open column is from first(candles[open])
high column is from max(candles[high])
low column is from min(candles[low])
close column is from last(candles[close])
volume column is from sum(candles[volume])

In this example I have just collected the 3 latest candles

+--------+------+------+------+-------+--------+-------+
| market | open | high | low  | close | volume | time  |
+--------+------+------+------+-------+--------+-------+
| ABC    | 14.1 | 16.8 | 11.2 | 13.2  | 1067.9 | 12:00 |
| DEF    | 93.2 | 98.2 | 91.1 | 94.3  | 3984.1 | 12:00 |
+--------+------+------+------+-------+--------+-------+

So far I have

SELECT
    market,
    MAX(high) AS 'high', 
    MIN(low) AS 'low', 
    SUM(volume) AS 'volume'
FROM
    candles
WHERE
    time > (UNIX_TIMESTAMP() - 300) 
GROUP BY
    market

This works correctly when ignoring the open and close columns. I cannot figure out how to include the open and close columns in this query. I have tried using subqueries but have had no success.

score 3 · Accepted Answer · edited Apr 01 '18 at 08:00

3

SELECT T2.MARKET,t1.open,T2.High,T2.low,T2.Volume,T3.close,t1.time
FROM 
(SELECT market,open,time FROM candles t1 WHERE TIME IN 
 (SELECT MIN(TIME) FROM Candles WHERE time > (UNIX_TIMESTAMP() - 300) GROUP BY market))
 as T1
Inner Join
(
SELECT
    market,
    MAX(high) AS 'high', 
    MIN(low) AS 'low', 
    SUM(volume) AS 'volume'
FROM
    candles
  WHERE
    time > (UNIX_TIMESTAMP() - 300)
GROUP BY
    market
) AS t2
on t1.market=t2.market
INNER JOIN (SELECT market,Close,Time FROM candles t1 WHERE TIME IN 
 (SELECT MAX(TIME) FROM Candles GROUP BY market)) as t3
 on t2.market=t3.market

In DEMO I removed where time > (UNIX_TIMESTAMP() - 300)

http://sqlfiddle.com/#!9/8f090/7

edited Apr 01 '18 at 08:00

jamesrogers93

335
1
5
15

answered Mar 31 '18 at 04:08

Jay Shankar Gupta

5,918
1
10
27

Everything worked correctly except the 'open ' column. This was retrieving the open column from the very first row, not within the time frame. I changed the T1 inner select to include `WHERE time > (UNIX_TIMESTAMP() - 300)`. This appears to work correctly! – jamesrogers93 Mar 31 '18 at 14:10
If you got your answer please accept and upvote @jamesrogers93 – Jay Shankar Gupta Mar 31 '18 at 14:45
How to use this, if you want to get several 5min-candlestics out of 1min-datasets? For example the last seven 5min-candlestics? So you would have to get the data of 35 1min-datasets, but aggregated in seven pieces with each 5 1min-datasets. How to solve this? – Dong3000 Jun 30 '21 at 12:04

mysql select query for candle stick market data

1 Answers1