Mathematica 9 is now available
Services & Resources / Wolfram Forums / MathGroup Archive
-----

MathGroup Archive 2010

[Date Index] [Thread Index] [Author Index]

Search the Archive

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.



  • 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