Re: [sqlite] Status analyze with Sqlite
On 11/22/2012 4:47 PM, Steffen Mangold wrote: HI sqlite community, I have a problem I get stucked, maybe someone can help me. :( My issue: For instance if we have 10 rows with following data ID | TimeStamp | Status 0 | 2012-07-24 22:23:00 | status1 1 | 2012-07-24 22:23:05 | status1 2 | 2012-07-24 22:23:10 | status2 3 | 2012-07-24 22:23:16 | status2 4 | 2012-07-24 22:23:21 | status2 5 | 2012-07-24 22:23:26 | status2 6 | 2012-07-24 22:23:32 | status2 7 | 2012-07-24 22:23:37 | status3 8 | 2012-07-24 22:23:42 | status3 9 | 2012-07-24 22:23:47 | status3 What I want as result is ID | Begin | End | Status --- 0 | 2012-07-24 22:23:00 | 2012-07-24 22:23:05 | status1 1 | 2012-07-24 22:23:10 | 2012-07-24 22:23:32 | status2 2 | 2012-07-24 22:23:37 | 2012-07-24 22:23:47 | status3 Hmmm, the ID in the result bears virtually no relation to the ID in the data. Is that intentional? Anyhow, some of what you want could come from select min(TimeStamp) as Begin, max(TimeStamp) as End, Status from Data group by Status order by Status HTH, Gerry ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Status analyze with Sqlite
HI sqlite community, I have a problem I get stucked, maybe someone can help me. :( My issue: For instance if we have 10 rows with following data ID | TimeStamp | Status 0 | 2012-07-24 22:23:00 | status1 1 | 2012-07-24 22:23:05 | status1 2 | 2012-07-24 22:23:10 | status2 3 | 2012-07-24 22:23:16 | status2 4 | 2012-07-24 22:23:21 | status2 5 | 2012-07-24 22:23:26 | status2 6 | 2012-07-24 22:23:32 | status2 7 | 2012-07-24 22:23:37 | status3 8 | 2012-07-24 22:23:42 | status3 9 | 2012-07-24 22:23:47 | status3 What I want as result is ID | Begin | End | Status --- 0 | 2012-07-24 22:23:00 | 2012-07-24 22:23:05 | status1 1 | 2012-07-24 22:23:10 | 2012-07-24 22:23:32 | status2 2 | 2012-07-24 22:23:37 | 2012-07-24 22:23:47 | status3 What I have so far is SELECT ou. ID AS ID, ou.Status AS Status, ou.TimeStamp AS Begin, (SELECT MAX(TimeStamp) FROM Data WHERE TimeStamp >= ou.TimeStamp AND Status = ou.Status AND TimeStamp < '2010-02-24 00:00:00') AS End FROM Data ou But don't work :( Can you please help me? Regards Steffen Mangold ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is wrong with this update SQL
On 22 Nov 2012, at 5:53pm, Bart Smissaert wrote: > Had a look at this, but it looks the method with select case etc. is > faster, maybe some 20%. This is even without setting up the lookup > table. Fair enough. > Also I am not what the benefit is of the order by in your sql. > Doing this: > update xxx set band = > (select band from convert_age ca > where xxx.band = ca.age) > seems slightly faster and looks simpler. I didn't know that your ca.age value was always at the bottom of a range, but if it is, then you're right and this will be faster. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is wrong with this update SQL
Had a look at this, but it looks the method with select case etc. is faster, maybe some 20%. This is even without setting up the lookup table. Also I am not what the benefit is of the order by in your sql. Doing this: update xxx set band = (select band from convert_age ca where xxx.band = ca.age) seems slightly faster and looks simpler. RBS On 11/22/12, Simon Slavin wrote: > > On 22 Nov 2012, at 10:47am, Bart Smissaert > wrote: > >> Ignore this e-mail, it was a simple mistake from my side and nil to do >> with SQLite. > > It takes a big man to admit a mistake. Thanks for saving us the time of > figuring out your problem. > > By the way ... your original question talks about a problem which can be > solved far more quickly using SQL and a second table. Put a table into your > database which does the conversion for you: > > lowerLimitdescriptionText > 0 0 - 10 > 1111 - 20 > ... > 9191 - 100 > 101 over 100 > > And create an index on lowerLimit. Then look up the description you want > using > > SELECT descriptionText FROM ageDescriptions WHERE lowerLimit <= [myvar] > ORDER BY lowerLimit DESC LIMIT 1 > > You will always get get one row which has the description you want. You can > do the lookup either before you do your INSERT (to put the description into > the database) or the modern way would be to do the lookup only when you > actually need to know the age band, probably just before you show the data > on the display. > > 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
Re: [sqlite] What is wrong with this update SQL
> far more quickly using SQL and a second table Not sure this is so in my particular case as the age to ageband conversion is variable, so the lookup table will need to be created and populated every time. I suppose common ones such as the 0 to 10, 11 to 20 etc. could be kept for future use, but that would make it all lot more complex coding-wise. Will have a look though at the speed of creating a temp table in memory and converting that way, but my guess is that it is slower. RBS On Thu, Nov 22, 2012 at 12:58 PM, Simon Slavin wrote: > > On 22 Nov 2012, at 10:47am, Bart Smissaert wrote: > >> Ignore this e-mail, it was a simple mistake from my side and nil to do >> with SQLite. > > It takes a big man to admit a mistake. Thanks for saving us the time of > figuring out your problem. > > By the way ... your original question talks about a problem which can be > solved far more quickly using SQL and a second table. Put a table into your > database which does the conversion for you: > > lowerLimit descriptionText > 00 - 10 > 11 11 - 20 > ... > 91 91 - 100 > 101 over 100 > > And create an index on lowerLimit. Then look up the description you want > using > > SELECT descriptionText FROM ageDescriptions WHERE lowerLimit <= [myvar] ORDER > BY lowerLimit DESC LIMIT 1 > > You will always get get one row which has the description you want. You can > do the lookup either before you do your INSERT (to put the description into > the database) or the modern way would be to do the lookup only when you > actually need to know the age band, probably just before you show the data on > the display. > > 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
Re: [sqlite] What is wrong with this update SQL
On 22 Nov 2012, at 10:47am, Bart Smissaert wrote: > Ignore this e-mail, it was a simple mistake from my side and nil to do > with SQLite. It takes a big man to admit a mistake. Thanks for saving us the time of figuring out your problem. By the way ... your original question talks about a problem which can be solved far more quickly using SQL and a second table. Put a table into your database which does the conversion for you: lowerLimit descriptionText 00 - 10 11 11 - 20 ... 91 91 - 100 101 over 100 And create an index on lowerLimit. Then look up the description you want using SELECT descriptionText FROM ageDescriptions WHERE lowerLimit <= [myvar] ORDER BY lowerLimit DESC LIMIT 1 You will always get get one row which has the description you want. You can do the lookup either before you do your INSERT (to put the description into the database) or the modern way would be to do the lookup only when you actually need to know the age band, probably just before you show the data on the display. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is wrong with this update SQL
:) Thanks. On Thu, Nov 22, 2012 at 12:28 PM, Simon Davies wrote: > On 22 November 2012 11:20, Staffan Tylen wrote: > > I run this (sql.txt): > > > > create table table1 (age_band); > > insert into table1 values(31); > > UPDATE table1 SET age_band = (case when age_band <= 10 then ' 0 - 10' > > when age_band BETWEEN 11 AND 20 then ' 11 - 20' when age_band BETWEEN > > 21 AND 30 then ' 21 - 30' when age_band BETWEEN 31 AND 40 then ' 31 > > - 40' when age_band BETWEEN 41 AND 50 then ' 41 - 50' when age_band > > BETWEEN 51 AND 60 then ' 51 - 60' when age_band BETWEEN 61 AND 70 > > then ' 61 - 70' when age_band BETWEEN 71 AND 80 then ' 71 - 80' when > > age_band BETWEEN 81 AND 90 then ' 81 - 90' when age_band BETWEEN 91 > > AND 100 then ' 91 - 100' when age_band > 100 then ' over 100' else > > age_band end); > > select * from table1; > > > > and get this: > > > > SQLite version 3.7.15 2012-11-09 21:40:02 > > Enter ".help" for instructions > > Enter SQL statements terminated with a ";" > > sqlite> .read sql.txt > > age_band > > -- > > 31 > > - 40 > > > > To sure what might be causing the line break, it only seems to happen for > > this age band. > > The literal selected contains the line break > > sqlite> select '31 >...> - 40'; > 31 > - 40 > > sqlite> select '31 - 40'; > 31 - 40 > sqlite> > > > > Staffan > > > > > Regards, > 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
Re: [sqlite] What is wrong with this update SQL
On 22 November 2012 11:20, Staffan Tylen wrote: > I run this (sql.txt): > > create table table1 (age_band); > insert into table1 values(31); > UPDATE table1 SET age_band = (case when age_band <= 10 then ' 0 - 10' > when age_band BETWEEN 11 AND 20 then ' 11 - 20' when age_band BETWEEN > 21 AND 30 then ' 21 - 30' when age_band BETWEEN 31 AND 40 then ' 31 > - 40' when age_band BETWEEN 41 AND 50 then ' 41 - 50' when age_band > BETWEEN 51 AND 60 then ' 51 - 60' when age_band BETWEEN 61 AND 70 > then ' 61 - 70' when age_band BETWEEN 71 AND 80 then ' 71 - 80' when > age_band BETWEEN 81 AND 90 then ' 81 - 90' when age_band BETWEEN 91 > AND 100 then ' 91 - 100' when age_band > 100 then ' over 100' else > age_band end); > select * from table1; > > and get this: > > SQLite version 3.7.15 2012-11-09 21:40:02 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> .read sql.txt > age_band > -- > 31 > - 40 > > To sure what might be causing the line break, it only seems to happen for > this age band. The literal selected contains the line break sqlite> select '31 ...> - 40'; 31 - 40 sqlite> select '31 - 40'; 31 - 40 sqlite> > Staffan > Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is wrong with this update SQL
I run this (sql.txt): create table table1 (age_band); insert into table1 values(31); UPDATE table1 SET age_band = (case when age_band <= 10 then ' 0 - 10' when age_band BETWEEN 11 AND 20 then ' 11 - 20' when age_band BETWEEN 21 AND 30 then ' 21 - 30' when age_band BETWEEN 31 AND 40 then ' 31 - 40' when age_band BETWEEN 41 AND 50 then ' 41 - 50' when age_band BETWEEN 51 AND 60 then ' 51 - 60' when age_band BETWEEN 61 AND 70 then ' 61 - 70' when age_band BETWEEN 71 AND 80 then ' 71 - 80' when age_band BETWEEN 81 AND 90 then ' 81 - 90' when age_band BETWEEN 91 AND 100 then ' 91 - 100' when age_band > 100 then ' over 100' else age_band end); select * from table1; and get this: SQLite version 3.7.15 2012-11-09 21:40:02 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .read sql.txt age_band -- 31 - 40 To sure what might be causing the line break, it only seems to happen for this age band. Staffan On Thu, Nov 22, 2012 at 12:14 PM, Clemens Ladisch wrote: > Bart Smissaert wrote: > > Have a table with one field holding numbers from 0 to about 110. > > The field has no data type, so it is not integer or text. > > [...] > > I thought that as the field has no date affinity, it should be handled > > as integer numbers > > Fields without affinity are never converted without need, and any > number compares less than any string. I'd guess you have strings. > See: > SELECT DISTINCT typeof(age_band) FROM table1 > > > Regards, > Clemens > ___ > 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] What is wrong with this update SQL
Bart Smissaert wrote: > Have a table with one field holding numbers from 0 to about 110. > The field has no data type, so it is not integer or text. > [...] > I thought that as the field has no date affinity, it should be handled > as integer numbers Fields without affinity are never converted without need, and any number compares less than any string. I'd guess you have strings. See: SELECT DISTINCT typeof(age_band) FROM table1 Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is wrong with this update SQL
The simple explanation was that I had make the field text instead of no data affinity. RBS On 11/22/12, Bart Smissaert wrote: > Ignore this e-mail, it was a simple mistake from my side and nil to do > with SQLite. > Apologies for this. > > RBS > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is wrong with this update SQL
Ignore this e-mail, it was a simple mistake from my side and nil to do with SQLite. Apologies for this. RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is wrong with this update SQL
Interesting. I run the same SQL (except I used table t1 instead of table1 'cos I'm lazy) and got this (SQLite 3.7.15): sqlite> delete from t1; sqlite> select * from t1; sqlite> insert into t1 values(1); sqlite> insert into t1 values(10); sqlite> insert into t1 values(11); sqlite> insert into t1 values(21); sqlite> insert into t1 values(31); sqlite> insert into t1 values(41); sqlite> insert into t1 values(51); sqlite> insert into t1 values(61); sqlite> insert into t1 values(71); sqlite> insert into t1 values(81); sqlite> insert into t1 values(91); sqlite> insert into t1 values(99); sqlite> insert into t1 values(100); sqlite> insert into t1 values(101); sqlite> select * from t1; age_band -- 1 10 11 21 31 41 51 61 71 81 91 99 100 101 sqlite> .read sql.txt sqlite> select * from t1; age_band -- 0 - 10 0 - 10 11 - 20 21 - 30 31 - 40 41 - 50 51 - 60 61 - 70 71 - 80 81 - 90 91 - 100 91 - 100 91 - 100 over 100 I guess some intermediate results are being picked up on the way. It will be interesting to hear the explanation. Staffan On Thu, Nov 22, 2012 at 11:30 AM, Bart Smissaert wrote: > Have a table with one field holding numbers from 0 to about 110. > The field has no data type, so it is not integer or text. > Now I run the following SQL: > > UPDATE table1 SET age_band = (case when age_band <= 10 then ' 0 - 10' > when age_band BETWEEN 11 AND 20 then ' 11 - 20' when age_band BETWEEN > 21 AND 30 then ' 21 - 30' when age_band BETWEEN 31 AND 40 then ' 31 > - 40' when age_band BETWEEN 41 AND 50 then ' 41 - 50' when age_band > BETWEEN 51 AND 60 then ' 51 - 60' when age_band BETWEEN 61 AND 70 > then ' 61 - 70' when age_band BETWEEN 71 AND 80 then ' 71 - 80' when > age_band BETWEEN 81 AND 90 then ' 81 - 90' when age_band BETWEEN 91 > AND 100 then ' 91 - 100' when age_band > 100 then ' over 100' else > age_band end) > > Now there are 2 things wrong with the result. > Firstly there are no ' 91 - 100' results and there should be as there > are numbers in > this range. > Secondly, the number 100 remains as it is, so it shows as 100 in the > output. > I thought that as the field has no date affinity, it should be handled > as integer > numbers and still should hold the text conversions fine. > > Thanks for any advice as to what is going on here. > > RBS > ___ > 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] What is wrong with this update SQL
Have a table with one field holding numbers from 0 to about 110. The field has no data type, so it is not integer or text. Now I run the following SQL: UPDATE table1 SET age_band = (case when age_band <= 10 then ' 0 - 10' when age_band BETWEEN 11 AND 20 then ' 11 - 20' when age_band BETWEEN 21 AND 30 then ' 21 - 30' when age_band BETWEEN 31 AND 40 then ' 31 - 40' when age_band BETWEEN 41 AND 50 then ' 41 - 50' when age_band BETWEEN 51 AND 60 then ' 51 - 60' when age_band BETWEEN 61 AND 70 then ' 61 - 70' when age_band BETWEEN 71 AND 80 then ' 71 - 80' when age_band BETWEEN 81 AND 90 then ' 81 - 90' when age_band BETWEEN 91 AND 100 then ' 91 - 100' when age_band > 100 then ' over 100' else age_band end) Now there are 2 things wrong with the result. Firstly there are no ' 91 - 100' results and there should be as there are numbers in this range. Secondly, the number 100 remains as it is, so it shows as 100 in the output. I thought that as the field has no date affinity, it should be handled as integer numbers and still should hold the text conversions fine. Thanks for any advice as to what is going on here. RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
