grouping 2007-11-02 - By Baron Schwartz
Back
Hi,
Octavian Rasnita wrote:
> Hi,
>
> I have a table with the following columns:
>
> symbol
> date
> value
>
> I want to select all the symbols (grouping by symbols) between 2
> specified dates, and calculate the percent of change for each symbol.
>
> The percent of change is (the value from the last date of the symbol -
> the value from the first date) / the value from the first date.
>
> For example I have:
>
> SMB1, 2007-01-01, 1000
> SMB1, 2007-03-15, 2100
> SMB1, 2007-10-10, 1300
> ... (other symbols)
>
> And the result of the select should be:
> SMB1, 0.3
>
> Is it possible to do this with an MySQL query?
Yes. You need to find the first row per group and the last row per
group, then join these two results so the values are all in the same
row. Then you can just do the math as usual.
I will assume the primary key is on (symbol, date). This won't work if
there are duplicated symbols and dates. Find the extrema:
select symbol, min(date) as mindate, max(date) as maxdate
from tbl
group by symbol
Now join the table against this result, and do the math in the SELECT list:
select fl.symbol, (tbl_l.value - tbl_f.value)/tbl_f.value
from (
select symbol, min(date) as mindate, max(date) as maxdate
from tbl
group by symbol
) as fl
inner join tbl as tbl_f
on fl.symbol=tbl_f.symbol and fl.mindate = tbl_f.date
inner join tbl as tbl_l
on fl.symbol=tbl_l.symbol and fl.maxdate = tbl_l.date
Disclaimer: I haven't tried this code.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
