Re: [sqlite] aggregate by break in sequence

[Anantha Prasad]

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  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

Re: [sqlite] aggregate by break in sequence

[Igor Tandetnik]

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

Re: [sqlite] aggregate by break in sequence

[Simon Slavin]

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

[Anantha Prasad]

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  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  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

[Pavel Ivanov]

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  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

[Jim Morris]

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  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

[sqlite] aggregate by break in sequence

[Anantha Prasad]

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