Possible causes.
#1 You need indexes (quite possible) -- though it appears to me that all the casting is going to kill using indexes. #2 You need to increase cache (likely) -- try "pragam cache_size=2000000" #3 You're hitting swap space (doubtful) #4 You need to do this in your own code instead of SQL. Likely to be a LOT faster. Can you show the "explain query plan" for this? Can't you use the dates without casting them? Then you could use an index which would speed things up a lot too. Each one of your selects if probably doing a complete table scan due to the cast and math. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems ________________________________ From: [email protected] [[email protected]] on behalf of Rafael Garcia Leiva [[email protected]] Sent: Tuesday, December 13, 2011 10:31 AM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] Aggregating Forex data 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 >> [email protected] >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
