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: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Rafael Garcia Leiva [rafael.gar...@entropycs.com]
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
>> 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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
