Re: [sqlite] import TXT file
On 2013-02-21 13:15:16 +0100, Gert Van Assche wrote:
> Roger,
>
> thanks for the advice, but I cannot work like this. I don't have the
> possibility to rewrite the txt files I receive.
> If I use the SQLite Expert, I get an error on "double-quote character" or a
> "range error"...
>
> Any other suggestions?
I think is the "double-quote character" break the line,
I find in the table each row has mutilines all has a double-quote (")
in the first line and another in last line , except the last row
I think you can prehandle the txt file change the " to another string
like:
sed -i.bak 's/"/@@/g' test.txt
and then in sqlite:
.import test.txt Source
UPDATE Source SET Segments = replace(Segments, '@@', '"');
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] creating a summary table
On 2013-02-18 17:02:53 +, Paul Sanderson wrote: > nc > 1a > 2a > 3a > 4b > 5b > 3b > 4b > 2b > 3a > 5b > 2b > > > I have a table as above > > I want to create a summary table that shows in the first column the total > number of occurrences of a value in the first column (n) and in the second > column for each value in n a count of the unique entries in c > > it should look like this > > noccurenceunique > 111 > 232 > 332 > 421 > 522 I think you can do this SELECT n , sum(cou) AS oc, count(*) AS un FROM (SELECT n, c, count(*) AS cou FROM tablename GROUP BY n, c) GROUP BY n; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Aggregating Forex data
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
Re: [sqlite] Aggregating Forex data
On 2011-11-24 08:02:21 +, 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,
> >lowREAL 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
>
>
>
> 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))
>
> Simon.
> ___
> 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
