[sqlite] Smart merge
Hi guys, I have two tables T1, T2 - identical in fields [Index1 (INT), Index2(INT), Count(INT), Value(INT)] I would like to merge the content of T1 into T2 using the following ruels: 1. Copy into T2 data from T1 where Index1 AND Index2 (like two keys, PK and SK) do not exist in T2 2. In case that there are common rows between T1 and T2, where common means T1[Index1, Index2] and T2[Index1, Index2] are the same - update T2.Count and T2.Value ONLY IF T1.Count has a greater value I am looking for an efficient SQLite statement to make it work. Any ideas? Nadav. -- View this message in context: http://old.nabble.com/Smart-merge-tp32873851p32873851.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Smart merge
Hi guys, I have two tables T1, T2 - identical in fields [Index1 (INT), Index2(INT), Count(INT), Value(INT)] I would like to merge the content of T1 into T2 using the following ruels: 1. Copy into T2 data from T1 where Index1 AND Index2 (like two keys, PK and SK) do not exist in T2 2. In case that there are common rows between T1 and T2, where common means T1[Index1, Index2] and T2[Index1, Index2] are the same - update T2.Count and T2.Value ONLY IF T1.Count has a greater value I am looking for an efficient SQLite statement to make it work. Any ideas? Nadav. -- View this message in context: http://old.nabble.com/Smart-merge-tp32873853p32873853.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Smart merge
On 23 November 2011 11:23, nadavius wrote: > > Hi guys, > > I have two tables T1, T2 - identical in fields [Index1 (INT), Index2(INT), > Count(INT), Value(INT)] > I would like to merge the content of T1 into T2 using the following ruels: > 1. Copy into T2 data from T1 where Index1 AND Index2 (like two keys, PK and > SK) do not exist in T2 > 2. In case that there are common rows between T1 and T2, where common means > T1[Index1, Index2] and T2[Index1, Index2] are the same - update T2.Count and > T2.Value ONLY IF T1.Count has a greater value > > I am looking for an efficient SQLite statement to make it work. Can't speak for its efficiency, but this appears to do what you want: SQLite version 3.4.2 Enter ".help" for instructions sqlite> create table t1( i1 integer, i2 integer, cnt integer, val integer, primary key( i1, i2 ) ); sqlite> create table t2( i1 integer, i2 integer, cnt integer, val integer, primary key( i1, i2 ) ); sqlite> insert into t1 values( 1,1,1,1 ); sqlite> insert into t1 values( 2,1,2,2 ); sqlite> insert into t1 values( 3,1,1,3 ); sqlite> insert into t1 values( 4,1,5,4 ); sqlite> sqlite> insert into t2 values( 1,1,0,111 ); sqlite> insert into t2 values( 4,1,6,444 ); sqlite> sqlite> create trigger t2_from_t1 before insert on t2 begin update t2 set cnt=new.cnt, val=new.val where t2.i1=new.i1 and t2.i2=new.i2 and new.cnt > t2.cnt; end; sqlite> sqlite> select * from t1; 1|1|1|1 2|1|2|2 3|1|1|3 4|1|5|4 sqlite> select * from t2; 1|1|0|111 4|1|6|444 sqlite> insert or ignore into t2 select i1, i2, cnt, val from t1; sqlite> sqlite> select * from t1; 1|1|1|1 2|1|2|2 3|1|1|3 4|1|5|4 sqlite> select * from t2; 1|1|1|1 <== updated 4|1|6|444 <== unchanged 2|1|2|2 <== inserted 3|1|1|3 <== inserted sqlite> > > Any ideas? > > Nadav. Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Smart merge
nadavius wrote: > I have two tables T1, T2 - identical in fields [Index1 (INT), Index2(INT), > Count(INT), Value(INT)] > I would like to merge the content of T1 into T2 using the following ruels: > 1. Copy into T2 data from T1 where Index1 AND Index2 (like two keys, PK and > SK) do not exist in T2 > 2. In case that there are common rows between T1 and T2, where common means > T1[Index1, Index2] and T2[Index1, Index2] are the same - update T2.Count and > T2.Value ONLY IF T1.Count has a greater value If there a unique index on T1(Index1, Index2), then something like this should work: insert or replace into T1 select T2.Index1, T2.Index2, coalesce(alt.Count, T2.Count), coalesce(alt.Value, T2.Value) from T2 left join T1 alt on (T2.Index1=alt.Index1 and T2.Index2=alt.Index2 and alt.Count <= T2.Count); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] updating table only if key match in other table
update a set location='new york ' where a.location isnull and a.mz_tik in(select mz_tik from c); -- View this message in context: http://old.nabble.com/updating-table-only-if-key-match-in-other-table-tp32873978p32873978.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite: Database or disk full
On Wed, Nov 23, 2011 at 2:10 AM, Gaurav Vyas wrote: > There is no optimization as of now. I am just slitting the code into > various independent parts. And one more thing I found, I have installed > SQLite3 3.7.9 and when I am using sqlite3_open_v2 it gives error that says > undefined symbol sqlite3_open_v2, and I have linked the correct library. How did you check that you linked correct library? Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug
SQLite version 3.7.9 2011-11-01 00:52:41 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table t(a); sqlite> select avg(a) from t order by avg(a); -- order by aggregate possible sqlite> select 1 from t order by a; -- order by column not in result possible sqlite> select 1 from t group by 1 order by avg(a); -- order by aggregate not in result possible sqlite> select 1 from t order by avg(a); -- should be possible Error: misuse of aggregate: avg() sqlite> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug
On 23 Nov 2011, at 4:17pm, Wiktor Adamski wrote: > sqlite> select 1 from t order by avg(a); -- should be possible Why should this be possible ? For an 'ORDER BY' you need a value for each row. But aggregate functions produce only one value for the whole SELECT command. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug
On Nov 23, 2011, at 5:17 PM, Wiktor Adamski wrote: > sqlite> select 1 from t order by avg(a); -- should be possible > Error: misuse of aggregate: avg(); As it says on the tin: nonsensical. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug
On Wed, Nov 23, 2011 at 11:28 AM, Simon Slavin wrote: > On 23 Nov 2011, at 4:17pm, Wiktor Adamski wrote: > >> sqlite> select 1 from t order by avg(a); -- should be possible > > Why should this be possible ? For an 'ORDER BY' you need a value for each > row. But aggregate functions produce only one value for the whole SELECT > command. My first reaction was the same. But although it's completely senseless just syntactically it looks correct - should produce just one row and thus ORDER BY will be a no-op. And FWIW, this query works as expected on MS SQL (query with GROUP BY 1 doesn't work though). Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug
> And FWIW, this query works as expected on MS SQL Works on Firebird and produces one record with value 1. RBS On Wed, Nov 23, 2011 at 4:35 PM, Pavel Ivanov wrote: > On Wed, Nov 23, 2011 at 11:28 AM, Simon Slavin wrote: >> On 23 Nov 2011, at 4:17pm, Wiktor Adamski wrote: >> >>> sqlite> select 1 from t order by avg(a); -- should be possible >> >> Why should this be possible ? For an 'ORDER BY' you need a value for each >> row. But aggregate functions produce only one value for the whole SELECT >> command. > > My first reaction was the same. But although it's completely senseless > just syntactically it looks correct - should produce just one row and > thus ORDER BY will be a no-op. And FWIW, this query works as expected > on MS SQL (query with GROUP BY 1 doesn't work though). > > > Pavel > ___ > 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] Bug
On Nov 23, 2011, at 5:35 PM, Pavel Ivanov wrote: > But although it's completely senseless > just syntactically it looks correct - should produce just one row and > thus ORDER BY will be a no-op. Well, if this is about Alice in Wonderland, then, what about: > select max( 1 ) from t order by avg( a ); 1 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug
On Wed, Nov 23, 2011 at 11:50 AM, Petite Abeille wrote: > On Nov 23, 2011, at 5:35 PM, Pavel Ivanov wrote: > >> But although it's completely senseless >> just syntactically it looks correct - should produce just one row and >> thus ORDER BY will be a no-op. > > Well, if this is about Alice in Wonderland, then, what about: > >> select max( 1 ) from t order by avg( a ); > 1 Well, apparently you did this on non-empty table. This query gives different and kind of unexpected result on empty table. ;) Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug
On Nov 23, 2011, at 6:05 PM, Pavel Ivanov wrote: >> Well, if this is about Alice in Wonderland, then, what about: >> >>> select max( 1 ) from t order by avg( a ); >> 1 > > Well, apparently you did this on non-empty table. This query gives > different and kind of unexpected result on empty table. ;) Well, it returns null, no? What else would you expect? Getting deeper and deeper in the rabbit hole... :)) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug
On Nov 23, 2011, at 6:05 PM, Pavel Ivanov wrote: > This query gives > different and kind of unexpected result on empty table. ;) Ooops... I see what you mean... on an empty table... this returns one row with a null value: sqlite> select max( 1 ) from t; That would qualify as a bug I guess :)) [SQLite version 3.7.9 2011-11-01 00:52:41] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug
On 11/23/2011 11:17 AM, Wiktor Adamski wrote: SQLite version 3.7.9 2011-11-01 00:52:41 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table t(a); sqlite> select avg(a) from t order by avg(a); -- order by aggregate possible sqlite> select 1 from t order by a; -- order by column not in result possible sqlite> select 1 from t group by 1 order by avg(a); -- order by aggregate not in result possible sqlite> select 1 from t order by avg(a); -- should be possible Error: misuse of aggregate: avg() sqlite> Using an aggregate function anywhere in the list of selected fields, and/or specifying a GROUP BY clause, turns a regular SELECT statement into an aggregate one. Apparently, using such a function in ORDER BY clause alone doesn't make the statement aggregate (whether it should is perhaps debatable), so the condition in ORDER BY applies to each row, not to each group. But then, it is an error to use an aggregate function there (for the same reason that using one in a WHERE clause is an error). The other two queries in your example that have ORDER BY avg(a) are made in fact aggregate statements, either by using avg() in the SELECT clause, or by having GROUP BY. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug
On Wed, Nov 23, 2011 at 12:20 PM, Petite Abeille wrote: > On Nov 23, 2011, at 6:05 PM, Pavel Ivanov wrote: > >> This query gives >> different and kind of unexpected result on empty table. ;) > > Ooops... I see what you mean... on an empty table... this returns one row > with a null value: > > sqlite> select max( 1 ) from t; > > > That would qualify as a bug I guess :)) No, it's not a bug. It's SQL standard that such form of aggregate query always returns one row. And when there's no rows in the table it should return NULL (for all aggregate functions except count() which returns 0). I said it's kind of unexpected because it seems like max(1) should always return 1 but it turns out it doesn't always do so. Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug
On Nov 23, 2011, at 6:28 PM, Pavel Ivanov wrote: > No, it's not a bug. It's SQL standard that such form of aggregate > query always returns one row. And when there's no rows in the table it > should return NULL (for all aggregate functions except count() which > returns 0). I said it's kind of unexpected because it seems like > max(1) should always return 1 but it turns out it doesn't always do > so. Right you are. Lets forget about this entire nonsense then :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug
On Wed, Nov 23, 2011 at 08:17:17AM -0800, Wiktor Adamski scratched on the wall: > SQLite version 3.7.9 2011-11-01 00:52:41 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> create table t(a); > sqlite> select avg(a) from t order by avg(a); > -- order by aggregate possible > sqlite> select 1 from t order by a; > -- order by column not in result possible > sqlite> select 1 from t group by 1 order by avg(a); > -- order by aggregate not in result possible > sqlite> select 1 from t order by avg(a); > -- should be possible > Error: misuse of aggregate: avg() Not a bug. As documented (kind of): http://sqlite.org/lang_select.html#resultset Also a non-sense query. The core issue is when an aggregate can be used without a GROUP BY clause. Normally the use of an aggregate requires a GROUP BY clause so it knows *what* to aggregate. There is a special exception that if an aggregate appears IN THE RESULT SET, then an implied group is made over the whole data set. However, any query with an implied group will always return exactly one row. This makes any kind of ORDER BY statement meaningless. In the case of the last query, the ORDER BY is doubly meaningless, as your asking the result to be ordered by an expression that will always be the same for every row. In the first query, there is an aggregate in the result set, so an implicit GROUP BY is used. The ORDER BY is meaningless, but not an error (and could be more easily written "ORDER BY 1"; see below). The second query has no aggregates. The third query has an explicit GROUP BY, allowing aggregate expressions. The final expression has an aggregate, but no GROUP BY, nor any aggregate in the result set. It is an invalid query, which is fine, because it is also a nonsense query. Get rid of the ORDER BY statement and you'll get the same single-row result. It should also be pointed out that the third query is likely not doing what you think it is doing. In this specific case, the GROUP BY 1 will provide the same result as an implied, whole-dataset grouping. That is not because of the "1" in the GROUP BY clause, however, but the "1" in the SELECT clause. Literal integers passed to GROUP BY or ORDER BY are assumed to be column indexes, not literal values. "GROUP BY 1" means "group by the first column", not "group by the literal value '1'". Now it happens that in this specific query the first column is a literal value '1', resulting in a GROUP BY with a static, literal value. That, in turn, results in a single group across all rows, returning exactly one row, just as an implied GROUP BY does. This is not universally true, however. You have no idea how many rows a "GROUP BY 1" statement may return unless you know the expression used to compute the first column of the result set. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug
> In the first query, there is an aggregate in the result set, so an > implicit GROUP BY is used. The ORDER BY is meaningless, but not an > error (and could be more easily written "ORDER BY 1"; see below). The order is not meaningless. It can return an error or do nothing. If aggregate in order by isn't allowed it should return an error. From the first query we can see that it is allowed. It would be nice if database behaved consistently. We already know that MS SQL and Firebird do. Same with MySQL. I'm not sure, but I think I'v tried also postgres with same result. I'm sure that in sqlite it was missed and it is not an intentional behaviour. And yes - obviously the query with a bug makes no sense and there is no reason to ever use it. Just like adding order by to any other query that return 1 row before ordering. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite Locking Issue From Another Process
We have Process A which writes constantly to our SQLite database and we have Process B which occasionally reads from this same database. When Process B opens the database for read-only access and performs a select statement on the database it causes Process A to get SQLITE_BUSY errors when executing the insert statements and will continue to return busy until Process B closes the connection to the database. So my basic question is why is Process B locking the database when it is opening the file for read-only access and not performing any writes on the database being written to by Process A. Is it possible that a select statement can lock a table? Thanks, Dave. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug
> Apparently, using such a function in ORDER BY > clause alone doesn't make the statement aggregate (whether it should is > perhaps debatable) I suppose this may be in the standart. I'm 100% sure that this one is allowed by standart: ... ORDER BY avg(a) OVER() so likely ORDER BY avg(a) is also allowed. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Locking Issue From Another Process
You need WAL mode http://www.sqlite.org/draft/wal.html 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 David Levinson [dlevin...@worldnow.com] Sent: Tuesday, November 22, 2011 9:07 AM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] SQLite Locking Issue From Another Process We have Process A which writes constantly to our SQLite database and we have Process B which occasionally reads from this same database. When Process B opens the database for read-only access and performs a select statement on the database it causes Process A to get SQLITE_BUSY errors when executing the insert statements and will continue to return busy until Process B closes the connection to the database. So my basic question is why is Process B locking the database when it is opening the file for read-only access and not performing any writes on the database being written to by Process A. Is it possible that a select statement can lock a table? Thanks, Dave. ___ 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] SQLite Locking Issue From Another Process
On 22 Nov 2011, at 3:07pm, David Levinson wrote: > So my basic question is why is Process B locking the database when it is > opening the file for read-only access and not performing any writes on > the database being written to by Process A. > > > > Is it possible that a select statement can lock a table? Yep. SELECT always locks a table. Suppose process A a SELECT which has an ORDER BY clause, and returns 100 rows. Just as process A gets to row 50 process B writes to row 75, and changes a value which moves it to position 25 in the list. Then the SELECT completes. But unfortunately, it now returns only 99 rows, because the changed row was never where it was looking. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug
On 11/23/2011 1:29 PM, Wiktor Adamski wrote: Apparently, using such a function in ORDER BY clause alone doesn't make the statement aggregate (whether it should is perhaps debatable) I suppose this may be in the standart. If I recall correctly, the standard doesn't allow ORDER BY to reference anything that doesn't also appear in SELECT, which renders the issue moot. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug
On Wed, Nov 23, 2011 at 1:29 PM, Wiktor Adamski wrote: >> Apparently, using such a function in ORDER BY >> clause alone doesn't make the statement aggregate (whether it should is >> perhaps debatable) > > I suppose this may be in the standart. I'm 100% sure that this one is > allowed by standart: > ... ORDER BY avg(a) OVER() > so likely ORDER BY avg(a) is also allowed. I believe OVER() is an Oracle-specific extension to SQL, not a standard in any way. Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug
On Nov 23, 2011, at 7:58 PM, Pavel Ivanov wrote: > I believe OVER() is an Oracle-specific extension to SQL, not a > standard in any way. Well, over( partition by... order by ... ) is part of the analytical syntax of Oracle... nothing to do with ordering a result set... Function(arg1,..., argn) OVER ( [PARTITION BY <...>] [ORDER BY <>] [] ) http://orafaq.com/node/55 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug
> I believe OVER() is an Oracle-specific extension to SQL, not a > standard in any way. ISO/IEC 9075-2:2003: ::= OVER ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug
On Nov 23, 2011, at 8:50 PM, Wiktor Adamski wrote: > ISO/IEC 9075-2:2003: > ::= OVER specification> This is related to so-called analytics in Oracle parlance. Not quite related to the topic at hand. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Free c code for embedded sqlite3
A base of code that implements about every embedded function for sqlite3, source code on my blog. It is a triple machine, looks at the world as ripples for ontology. I tries to follow SQLITE standards. The control program pops triple off of the configure table and executes them, installing more complex sql sequences and binding them to triple operators. Comes complete with a triple command line. It supports multi-line embedded SQL procedures. One can control non-triple tables using is as a general SQL batch processor. The main engine is only 250 lines of code, and It is using windows DLL at the moment. Its patent free and it has a pointer function implemented so SQL can traverse nested ontologies by having the machine compute the next rowid. http://bettereconomics.blogspot.com/p/g-engine-page.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Free c code for embedded sqlite3
Docs would help people understand what you're up to... Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Aggregating Forex data
Dear all,
This is my first post to this mailing list, I hope it is a relevant
question.
I have a table with Forex (currency exchange) data with the following
schema:
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.
Is is possible to do that with an SQLite Select? Could somebody send me
an example? If not, is it possible to solve the problem writting my own
aggretation function with SQLite C API?
Many thanks for your help
Rafael Garcia
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [sqlite TOO slow
I am trying to get a chunk of rows from a table which has 16 million rows. The table is indexed. I am passing the query as "SELECT * FROM persons WHERE hid = 5;" and it takes a few minutes to get me the results. Can anyone suggest how to make it faster? Gaurav ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [sqlite TOO slow
On 24 Nov 2011, at 7:45am, Gaurav Vyas wrote: > I am trying to get a chunk of rows from a table which has 16 million rows. > The table is indexed. I am passing the query as "SELECT * FROM persons > WHERE hid = 5;" and it takes a few minutes to get me the results. Can > anyone suggest how to make it faster? Is the table indexed on that column ? Are you doing anything involving multi-user, multi-thread, or multi-process ? What type of values are stored in that column ? In other words, if you do something like SELECT hid,typeof(hid) FROM persons WHERE hid = 5 what does it give for the 'typeof()' ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [sqlite TOO slow
I am using multi threads but using single thread to test the speed. And when I do typeof(hid), it gives "real". Table is indexed on that column. I dont have a primary key, will that make any difference? Gaurav On Thu, Nov 24, 2011 at 1:48 AM, Simon Slavin wrote: > > On 24 Nov 2011, at 7:45am, Gaurav Vyas wrote: > > > I am trying to get a chunk of rows from a table which has 16 million > rows. > > The table is indexed. I am passing the query as "SELECT * FROM persons > > WHERE hid = 5;" and it takes a few minutes to get me the results. Can > > anyone suggest how to make it faster? > > Is the table indexed on that column ? > > Are you doing anything involving multi-user, multi-thread, or > multi-process ? > > What type of values are stored in that column ? In other words, if you do > something like > > SELECT hid,typeof(hid) FROM persons WHERE hid = 5 > > what does it give for the 'typeof()' ? > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Gaurav Vyas Graduate Research Assistant, Transportation Engineering University of Texas at Austin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [sqlite TOO slow
On Nov 24, 2011, at 8:48 AM, Simon Slavin wrote: > Is the table indexed on that column ? And if it is... what's its selectivity? What 's the query plan? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [sqlite TOO slow
I used the following syntax to create index "CREATE UNIQUE INDEX persons_1x ON persons (pid,hid);" Gaurav On Thu, Nov 24, 2011 at 1:54 AM, Petite Abeille wrote: > > On Nov 24, 2011, at 8:48 AM, Simon Slavin wrote: > > > Is the table indexed on that column ? > > And if it is... what's its selectivity? > > What 's the query plan? > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Gaurav Vyas Graduate Research Assistant, Transportation Engineering University of Texas at Austin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
