Re: SQL question, Can I do something like Rest[] and Most[]
- To: mathgroup at smc.vnet.net
- Subject: [mg110287] Re: SQL question, Can I do something like Rest[] and Most[]
- From: antononcube <antononcube at gmail.com>
- Date: Sat, 12 Jun 2010 05:30:27 -0400 (EDT)
- References: <husjtd$6fi$1@smc.vnet.net>
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@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... at 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.