Prev: 3D plot of hemisphere pushing into a triangular membrane or surface
Next: Setting Attributes for Function Generated Parameters
From: Jagra on 11 Jun 2010 02:08
I need some help not directly related to doing something in
Mathematica, but rather in SQL (another functional language) and
wondered if anyone here had some insight into it.

I have a simple data base table "timeseries" with three fields

id
values
datetime

The table has a few thousand rows of data

I need to calculate the ratio of each value to its previous value.

In Mathematica I'd sort values by datetime than do something simple
like

Most[values] / Rest[values]

I wondered if anyone had any idea of how to do this efficiently in
SQL?

Also, if anyone has any recommendation for an SQL forum comparable to
what this forum provides for Mathematica. Please advise. (Even as I
ask the question I realize that I've never seen anything as helpful or
as sophisticated as this forum in any other subject area, but I can
always hope).

Thanks to all.

From: antononcube on 12 Jun 2010 05:30
Hi,

Here is one way to compute what you want using SQL which follows very
closely the formula Most[values] / Rest[values] .

SELECT @c1:=1
SELECT @c2:=1

SELECT t1.values / t2.values FROM
(SELECT @c1:=@c1+1 AS c, values FROM data_table ORDER BY datetime) t1
JOIN
(SELECT @c2:=@c2+1 AS c, values FROM data_table ORDER BY datetime) t2
WHERE t1.c+1 = t2.c


If say data is defined as:

In[34]:= data = {{1000, 23.3, 1}, {1003, 24.2, 2}, {1004, 25.6, 3},
{1005, 28.3, 4}};

basically the SQL code above specifies this Mathematica code

In[35]:=
t1 = MapIndexed[{#2[[1]], #1[[2]]} &, SortBy[data, #[[2]] &]];
Table[t1[[i, 2]]/t1[[i + 1, 2]], {i, 1, Length[t1] - 1}]

Out[36]= {0.96281, 0.945313, 0.904594}

or more precisely this:

In[40]:=
t1 = MapIndexed[{#2[[1]], #1[[2]]} &, SortBy[data, #[[2]] &]];
t2 = MapIndexed[{#2[[1]], #1[[2]]} &, SortBy[data, #[[2]] &]];
Flatten(a)Outer[If[#1[[1]] + 1 == #2[[1]], #1[[2]]/#2[[2]], {}] &, t1,
t2, 1]

Out[42]= {0.96281, 0.945313, 0.904594}

( JOIN is a Cartesian product, hence the use of Outer, and If for the
WHERE clause.)

Anton Antonov


On Jun 11, 2:08 am, Jagra <
jagra24...(a)mypacks.net> wrote:
> I need some help not directly related to doing something in
> Mathematica, but rather in SQL (another functional language) and
> wondered if anyone here had some insight into it.
>
> I have a simple data base table "timeseries" with three fields
>
> id
> values
> datetime
>
> The table has a few thousand rows of data
>
> I need to calculate the ratio of each value to its previous value.
>
> In Mathematica I'd sort values by datetime than do something simple
> like
>
> Most[values] / Rest[values]
>
> I wondered if anyone had any idea of how to do this efficiently in
> SQL?
>
> Also, if anyone has any recommendation for an SQL forum comparable to
> what this forum provides for Mathematica. Please advise. (Even as I
> ask the question I realize that I've never seen anything as helpful or
> as sophisticated as this forum in any other subject area, but I can
> always hope).
>
> Thanks to all.


 | 
Pages: 1
Prev: 3D plot of hemisphere pushing into a triangular membrane or surface
Next: Setting Attributes for Function Generated Parameters