       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:= 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:=
t1 = MapIndexed[{#2[], #1[]} &, SortBy[data, #[] &]];
Table[t1[[i, 2]]/t1[[i + 1, 2]], {i, 1, Length[t1] - 1}]

Out= {0.96281, 0.945313, 0.904594}

or more precisely this:

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

Out= {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.

```

• Prev by Date: Re: Lists: Row Vectors vs. Column Vectors. (feels like such a silly
• Next by Date: Re: difficulty using FindRoot
• Previous by thread: SQL question, Can I do something like Rest[] and Most[]
• Next by thread: Setting Attributes for Function Generated Parameters (with package