El 24/11/2011 19:18, Rafael Garcia Leiva escribió:
El 24/11/2011 14:49, 雷钦 escribió:
On 2011-11-24 08:02:21 +0000, Simon Slavin wrote:
On 24 Nov 2011, at 7:42am, Rafael Garcia Leiva wrote:
CREATE TABLE eurusd (
date TEXT NOT NULL PRIMARY KEY,
open REAL NOT NULL,
high REAL NOT NULL,
low REAL NOT NULL,
close REAL NOT NULL
);
The granularity of the data is one minute, for example:
INSERT INTO eurusd (date, open, high, low, close) VALUES (
'2011-11-01 00:01:00', '1.1212', '1.2323', '1.3434', '1.4545'
);
For the analysis of the data it is important to aggreate table rows
using other timeframes. If I want to do analysis of data aggregated
by months I can use the following query:
SELECT MAX(high) AS High, MIN(low) as Low,
STRFTIME("%Y-%m-%d", date) as Date
FROM eurusd GROUP BY STRFTIME("%Y-%m-%d", date);
In the same way I can aggregate the data by days and hours.
The problem is that I have to aggregate and analyze the data with
other less conventional time frames, like 5 minutes, 15 minutes, or
even 23 minutes.
I recommend that you store the datestamp in a numeric form. You can
do either keep your existing column and add a new one, writing the
data to both columns, or replace the existing text datestamp.
Two easy-to-convert formats would be Julian Day and Unix Epoch.
Julian Days are floats where 1 = 1 dayr; Unix Epochs generated by
SQLite are floats where 1 = 1 second. It appears that you're
interested in sub-day units so the unix format might be most useful
for you.
See
<http://www.sqlite.org/lang_datefunc.html>
Your SELECT would be something like
SELECT MAX(high) AS High, MIN(low) as Low,
STRFTIME("%Y-%m-%d", date) as Date
FROM eurusd GROUP BY round(timestamp / 23 * 60)
I think it is the same as
SELECT MAX(high) AS High, MIN(low) as Low,
STRFTIME('%Y-%m-%d',date) as Date
FROM eurusd GROUP BY round(STRFTIME('%s',date) / (23 * 60))
Many thanks for the answers. That's exactly what I was looking for!
Just one final remark, the round() function groups minutes from, from
example, 3 to 7, but I really want to group minutes from 0 to 4. That
should be the work of the floor() function, but unfortunately it is
not part of the standard sqlite distribution (I have to learn this
loadable extensions mechanism). But I think that I can get the same
result performing a CAST(... AS INTEGER).
SELECT MAX(high) AS High, MIN(low) as Low,
STRFTIME('%Y-%m-%d %H', date) as Date
FROM eurusd GROUP BY CAST(STRFTIME('%s', date) / (5 * 60) AS INTEGER);
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
