Yes, that works, even with missing data. The final query is:
SELECT STRFTIME('%Y-%m-%d %H %M', MIN(date)) AS Date,
(SELECT open from eurusd e2
where CAST(STRFTIME('%s', e2.date) AS INTEGER) >=
CAST(STRFTIME('%s', e1.date) AS INTEGER) / (5 * 60) * 5 * 60
order by e2.date asc limit 1) AS Open,
MAX(high) as High,
MIN(low) as Low,
(SELECT close from eurusd e3
where CAST(STRFTIME('%s', e3.date) AS INTEGER) <
(CAST(STRFTIME('%s', e1.date) AS INTEGER) / (5 * 60) + 1) * 5 * 60
order by e3.date desc limit 1) AS Close
FROM eurusd e1
GROUP BY CAST(STRFTIME('%s', e1.date) / (5 * 60) AS INTEGER)
The problem is that is veeeeery slooooow. It takes nearly 24 hours to
query 1 year of Forex data in my laptop (and I have to work with 10
years periods). I will spend a couple of days learning about sqlite
optimization.
Many thanks for all the answers.
Rafael
El 13/12/2011 15:06, 雷钦 escribió:
I think this can work
SELECT STRFTIME('%Y-%m-%d %H %M', date) AS Date,
(SELECT open from eurusd e2
where STRFTIME(e2.date)>= CAST(STRFTIME('%s', e1.date) / (5
* 60) AS INTEGER) * 5 * 60
order by e2.date asc limit 1) AS Open,
MAX(high) as High,
MIN(low) as Low,
(SELECT close from eurusd e3
where STRFTIME(e3.date)< (CAST(STRFTIME('%s', e1.date) /
(5 * 60) AS INTEGER) + 1) * 5 * 60
order by e3.date desc limit 1) AS Close
FROM eurusd e1
GROUP BY CAST(STRFTIME('%s', e1.date) / (5 * 60) AS INTEGER)
On 2011-12-12 15:45:41 +0100, Rafael Garcia Leiva wrote:
El 24/11/2011 19:18, Rafael Garcia Leiva escribió:
Dear all,
I'm still working on this problem :-(
Now I need the Open and the Close of the 5 minutes interval, where
Open is the Open of the first minute of the interval, and Close is
the Close of the last minute of the interval. I know how to get that
information with individual queries, for example:
SELECT open, date FROM eurusd WHERE date IN
(select min(date) from eurusd group by STRFTIME("%Y-%m-%d", date));
But it would be very nice (and perhaps more computationally
efficient?) to get all the information, that is Open, High, Low and
Close in just one single query. I have tried something like:
SELECT STRFTIME('%Y-%m-%d %H %M', date) AS Date,
(SELECT open from eurusd e2 where e2.date = MIN(e1.date)) AS Open,
MAX(high) as High,
MIN(low) as Low,
(SELECT close from eurusd e3 where e3.date = MAX(e1.date)) AS Close
FROM eurusd e1
GROUP BY CAST(STRFTIME('%s', e1.date) / (5 * 60) AS INTEGER)
but I have got an error of "misuse of aggregate function MIN()".
Any help would be very welcome. Also I would like to apologize if
this question is not relevant to the sqlite mailing list.
Best regards
Rafael
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
