Hi all,
I store stock market data 1 minute per row, I have 511 1 minute rows for every day (from 0900 up to 1730), and I have this query to perform data aggregation from intraday to daily data
SELECT symbol, date, MAX(high) AS high, MIN(low) as low, SUM(volume) as volume,
(select open from F a2 where a1.symbol = a2.symbol and a1.date = a2.date order by time limit 1) as open,
(select close from F a2 where a1.symbol = a2.symbol and a1.date = a2.date order by time desc limit 1) as close
FROM F a1
WHERE date >= date_sub(CURDATE(), interval 100 day)
GROUP BY symbol, date
ORDER BY symbol, date;
but this return less then 100 daily aggregated rows since it use calendar days.
1) How to modify this query to get 100 daily aggregated rows?
2) How to aggregate to 5 minute data instead of daily (i.e., first row aggregates data from 0900 up to 0904, then from 0905 up to 0909, etc.)?
I use mySQL 5.6.11, data stored are as follow
http://s18.postimg.org/4uj8e0mfd/Z514.png
Thanks
