Select to subtract current value from previous and return column with result

Question

Select to subtract current value from previous and return column with result

Navigation

#1 by (5 votes)
#2 by (0 votes)

2

I have two tables: Samples and Values, which have columns as follows:

TABLE Samples

Amostra_id 
Data 
Operador_Id
Carta_Id

TABLE Values

Amostra_Id 
Valor

As you can see, the Values table relates to the Samples table from Sample_id. Operator_Id and Chart_Id comes from other tables that are beside the point.

What I need is the following: I need to have a SELECT that tells me how to return the Date, Value, and moreover a column called AMPLITUDE. AMPLITUDE is the value minus the previous value.

I'll try to exemplify:

VALOR     l 60   l 50   l 30   l 80   
AMPLITUDE l 10   l 20   l -50  l

Note: MySQL database.

I tried the following to get a column offset from the other (I did this, then I do the amplitude calculation from these columns):

Select * from(
         select *, curRank := @curRank + 1 as rank
         from(
              select * from valores
              left join (amostras)
                     on (amostras.AMOSTRAS_ID like valores.AMOSTRAS_AMOSTRAS_ID)
              order by amostras.AMOSTRAS_ID desc
          ) t, (select @curRank := 0) r
          where t.CARTA_CARTA_ID = 2
 )
 left join 
 (
         select u.valor as value2, @curRank  := @curRank + 1 as rank from
         (
                select * from valores
                left join (amostras)
                       on (amostras.AMOSTRAS_ID like valores.AMOSTRAS_AMOSTRAS_ID)
                order by amostras.AMOSTRAS_ID desc
         ) u, (select @curRank :=0) r
         where u.CARTA_CARTA_ID = 2
         limit 1,25
  ) x
  on (v.rank like x.rank)

I forgot to explain, but my select should only return the last 25 values - so I used LIMIT.

In this Select that I did, it seems to be very close, but the data of the second select (value2 and rank - of the left join) returns everything as NULL. Maybe my mistake is to use left join .

Any ideas? Thankful.

mysql select

asked by anonymous 14.08.2014 / 03:15

2 answers

How to duplicate this jQuery function Query with related data in the Entity Framework

score 5 · Answer 1

You can do with a variable:

select *, ( v.valor - @lastValue ) as amplitude,
      @lastValue := v.valor
   from
      valores v,
      ( select @lastValue := 0 ) SQLVars
   order by
      v.amostra_id

sqlfiddle: link

score 0 · Answer 2

Finally I got the code below:

select d.rank, d.DATA, d.VALOR, z.VALOR2, ABS(d.VALOR - z.VALOR2) AMPLITUDE
from (
    select * from
       (
        select *, @curRank :=@curRank + 1 as rank
        from 
        (
            select * from valores
            left join (amostras) 
            on (amostras.AMOSTRAS_ID like valores.AMOSTRAS_AMOSTRAS_ID)
            ORDER BY amostras.DATA desc
        ) a, (select @curRank :=0) b
        where a.CARTA_CARTA_ID = ? (id desejado aqui)                
        limit 0,25 (limitando nas 25 amostras)
       ) c
     ) d
(select VALOR2, rank as r from(
     select v.valor as VALOR2, @curRank := @curRank + 1 as rank
     from(
          select * from valores  
          left join (amostras)
          on (amostras.AMOSTRAS_ID like valores.AMOSTRAS_AMOSTRAS_ID)
          order by amostras.DATA desc   
         ) v, (select @curRank :=0) x
      where v.CARTA_CARTA_ID = ? (id desejado aqui)
      limit 1,25) y
 ) z
 where z.r like d.rank

That's it!