Re: [sqlite] aggregate by break in sequence
Well, not easy in a programming language - however, certainly an option. I was hoping that there would be some kind of break in sequence aggregation available (or a trick) in Sqlite. My problem I suspect is not so uncommon. I know that I can do this in INFO (an outdated database of ArcInfo) and I believe in SqlPlus (oracle?). Thanks again for the pointers. Prasad On Tue, Aug 16, 2011 at 5:41 PM, Simon Slavin slav...@bigfraud.org wrote: On 16 Aug 2011, at 9:58pm, Anantha Prasad wrote: The data is sorted by Longitude and then by Distance. Whenever there are repeated values of Distance within a Longitude, I want the Sum and Count to be added and divided - for example, Here is the table: Longitude Distance AvgColz SumColz CountColz 71.0 1050. 1002 71.0 1092.8571 2600 28 71.0 2083.8710 5200 62 71.0 2082.6389 11900 144 71.0 3062.0425 2916 47 71.0 3081.7119 9642 118 71.0 4017.8723 840 47 71.0 4049.8868 2644 53 71.0 508.581436943 71.0 605.0238 211 42 This is manipulation of data, and the way you want it done is not going to be the way the next programmer is going to want it done. Whatever kind of averaging or interpolation you want done, do it in your programming language, either by pre-scanning your database and replacing what you find, or by writing your own sum and count routines which understand what you want done. 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
[sqlite] aggregate by break in sequence
Wanted to know if Sqlite can do simple math when there is a break in sequence in the data. For example the foll. table is sorted by Longitude and then Distance: Id Longitude Distance IVmean IVsum IVcount 42 71.0 10 10.5000 221 43 71.0 10 29.4286 28 824 44 71.0 20 9.467762 587 45 71.0 20 11.6667 1441680 46 71.0 30 3.553247 167 47 71.0 40 4.573249 67 I want to compute replace the IVmean for each repeated Distance by IVsum/IVcount - for repeated Distance - for example, Record 42 and 44 should be replaced by 71.0 10 (2+28)/(21+824) Record 44 and 45 should be replaced by 71.0 20 (62+144)/(587+1680) Is this possible to do this in Sqlite. Thanks much. Pras ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] aggregate by break in sequence
You can't replace multiple rows in a single insert/update/delete statement. You might consider copying the duplicates to a temp table, delete them from the old then use a select on the temp table to generate the new rows for the old table. The select portion would be something like select longitude, distance, SUM(IVSUM)/SUM(IVCount), SUM(IVSUM), SUM(IVCount) FROM temp GROUP By longitude, Distance. On 8/16/2011 9:05 AM, Anantha Prasad wrote: Wanted to know if Sqlite can do simple math when there is a break in sequence in the data. For example the foll. table is sorted by Longitude and then Distance: Id Longitude Distance IVmean IVsum IVcount 42 71.0 10 10.5000 221 43 71.0 10 29.4286 28 824 44 71.0 20 9.467762 587 45 71.0 20 11.6667 1441680 46 71.0 30 3.553247 167 47 71.0 40 4.573249 67 I want to compute replace the IVmean for each repeated Distance by IVsum/IVcount - for repeated Distance - for example, Record 42 and 44 should be replaced by 71.0 10 (2+28)/(21+824) Record 44 and 45 should be replaced by 71.0 20 (62+144)/(587+1680) Is this possible to do this in Sqlite. Thanks much. Pras ___ 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] aggregate by break in sequence
I think it will have better performance if you do that in your programming language. But if you insist on SQL it would look like this: update table_name set IVmean = (select sum(IVsum)/sum(IVcount) from table_name t where t.Longitude = table_name.Longitude and t.Distance = table_name.Distance); I intentionally made all rows to be updated because adding WHERE condition will most probably only slowdown the update. Pavel On Tue, Aug 16, 2011 at 12:05 PM, Anantha Prasad fab...@gmail.com wrote: Wanted to know if Sqlite can do simple math when there is a break in sequence in the data. For example the foll. table is sorted by Longitude and then Distance: Id Longitude Distance IVmean IVsum IVcount 42 71.0 10 10.5000 2 21 43 71.0 10 29.4286 28 824 44 71.0 20 9.4677 62 587 45 71.0 20 11.6667 144 1680 46 71.0 30 3.5532 47 167 47 71.0 40 4.5732 49 67 I want to compute replace the IVmean for each repeated Distance by IVsum/IVcount - for repeated Distance - for example, Record 42 and 44 should be replaced by 71.0 10 (2+28)/(21+824) Record 44 and 45 should be replaced by 71.0 20 (62+144)/(587+1680) Is this possible to do this in Sqlite. Thanks much. Pras ___ 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] aggregate by break in sequence
Thanks much for the responses - they were helpful but was not quite what i wanted. Perhaps I was not clear. So, here goes... The data is sorted by Longitude and then by Distance. Whenever there are repeated values of Distance within a Longitude, I want the Sum and Count to be added and divided - for example, Here is the table: Longitude Distance AvgColz SumColz CountColz 71.0 1050. 1002 71.0 1092.8571 2600 28 71.0 2083.8710 5200 62 71.0 2082.6389 11900 144 71.0 3062.0425 2916 47 71.0 3081.7119 9642 118 71.0 4017.8723 84047 71.0 4049.8868 2644 53 71.0 508.581436943 71.0 605.023821142 71.0 702.930212643 71.0 802.170789 41 71.0 901.850074 40 71.0 100 1.575063 40 Notice that the Distance is repeated for the Longitude...I want the repeated Distance rows to be replaced by a weighted average of SumColz and CountColz. So, the output should be: Longitude Distance AvgColz 71.0 10 90.00 71.0 20 83.01 71.0 30 76.11 71.0 40 34.84 71.0 50 8.58 71.0 60 5.02 71.0 70 2.93 71.0 80 2.17 71.0 90 1.85 71.0 100 1.57 Example the 90.00 in the first row above is (100+2600)/(2+28) and so on...there could be multiple repeats in Distance although I have shown only two here... Thanks much!! Prasad On Tue, Aug 16, 2011 at 12:35 PM, Pavel Ivanov paiva...@gmail.com wrote: I think it will have better performance if you do that in your programming language. But if you insist on SQL it would look like this: update table_name set IVmean = (select sum(IVsum)/sum(IVcount) from table_name t where t.Longitude = table_name.Longitude and t.Distance = table_name.Distance); I intentionally made all rows to be updated because adding WHERE condition will most probably only slowdown the update. Pavel On Tue, Aug 16, 2011 at 12:05 PM, Anantha Prasad fab...@gmail.com wrote: Wanted to know if Sqlite can do simple math when there is a break in sequence in the data. For example the foll. table is sorted by Longitude and then Distance: Id Longitude Distance IVmean IVsum IVcount 42 71.0 10 10.5000 221 43 71.0 10 29.4286 28 824 44 71.0 20 9.467762 587 45 71.0 20 11.6667 1441680 46 71.0 30 3.553247 167 47 71.0 40 4.573249 67 I want to compute replace the IVmean for each repeated Distance by IVsum/IVcount - for repeated Distance - for example, Record 42 and 44 should be replaced by 71.0 10 (2+28)/(21+824) Record 44 and 45 should be replaced by 71.0 20 (62+144)/(587+1680) Is this possible to do this in Sqlite. Thanks much. Pras ___ 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
Re: [sqlite] aggregate by break in sequence
On 16 Aug 2011, at 9:58pm, Anantha Prasad wrote: The data is sorted by Longitude and then by Distance. Whenever there are repeated values of Distance within a Longitude, I want the Sum and Count to be added and divided - for example, Here is the table: Longitude Distance AvgColz SumColz CountColz 71.0 1050. 1002 71.0 1092.8571 2600 28 71.0 2083.8710 5200 62 71.0 2082.6389 11900 144 71.0 3062.0425 2916 47 71.0 3081.7119 9642 118 71.0 4017.8723 84047 71.0 4049.8868 2644 53 71.0 508.581436943 71.0 605.023821142 This is manipulation of data, and the way you want it done is not going to be the way the next programmer is going to want it done. Whatever kind of averaging or interpolation you want done, do it in your programming language, either by pre-scanning your database and replacing what you find, or by writing your own sum and count routines which understand what you want done. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] aggregate by break in sequence
On 8/16/2011 4:58 PM, Anantha Prasad wrote: The data is sorted by Longitude and then by Distance. Whenever there are repeated values of Distance within a Longitude, I want the Sum and Count to be added and divided select Longitude, Distance, sum(SumColz) / sum(CountColz) as AvgColz from my_table group by Longitude, Distance; If you want to replace the original table with this processed data, you can do it in several steps: 1) Create a new table to hold the result. 2) Use INSERT INTO new_table SELECT ... statement to save the result of the aforementioned select. 3) Drop the old table. 4) Use ALTER TABLE statement to rename the new table to the old name. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
