Thread: Solution to retrieve first and last row for each minute

Hi!

Do something like this

http://fimi.cvs.sourceforge.net/fimi/database/defaults/indicators/myinttick2bar.sql?revision=1.3&view=markup

and replace the max / min calculation with a count calculation.


Cheers
Chris


On Wed, August 22, 2007 9:25 am, roopa perumalraja wrote:
> Hi all,
> 
>   I have a table trans with the data
> 
>    price | volume | date  |  time

>    :
>    :
> 
>   I need to get the first and last price per every minute along with
> count, average, maximum, minumum of the price and sum of the volume .
> Right now I have my query which calculates count, maximum, minimum and
> average.
> 
>    select trnew.date, trnew.trunc_time, count(*) as count,
> avg(trnew.price) as avg_price,
>        sum(trnew.price*trnew.volume)/sum(trnew.volume) as vwap,
>        max(trnew.price) as high_price, min(trnew.price) as low_price,
>        sum(trnew.volume) as sum_volume from (select tr.date,
>        date_trunc('minute', tr.time) - interval '4 hour' as trunc_time,
>        tr.price, tr.volume from trans tr
>        where tr.time between '13:30:00.00' and '20:00:0.00' ) as trnew
>        group by trnew.date, trnew.trunc_time order by trnew.date,
> trnew.trunc_time;
> 
>   How do I add first and last price for each minute to this query?
> 
>   Thanks a lot in advance.
> 
>   Cheers.
> 
> 
> ---------------------------------
> Fussy? Opinionated? Impossible to please? Perfect.  Join Yahoo!'s user
> panel and lay it on us.

-- 
cu
Chris

Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten 
Browser-Versionen downloaden: http://www.gmx.net/de/go/browser

Hi!

Do something like this

http://fimi.cvs.sourceforge.net/fimi/database/defaults/indicators/myinttick2bar.sql?revision=1.3&view=markup

and replace the max / min calculation with a count calculation.


Cheers
Chris


On Wed, August 22, 2007 9:25 am, roopa perumalraja wrote:
> Hi all,
>
> I have a table trans with the data
>
> price | volume | date | time

> :
> :
>
> I need to get the first and last price per every minute along with
> count, average, maximum, minumum of the price and sum of the volume .
> Right now I have my query which calculates count, maximum, minimum and
> average.
>
> select trnew.date, trnew.trunc_time, count(*) as count,
> avg(trnew.price) as avg_price,
> sum(trnew.price*trnew.volume)/sum(trnew.volume) as vwap,
> max(trnew.price) as high_price, min(trnew.price) as low_price,
> sum(trnew.volume) as sum_volume from (select tr.date,
> date_trunc('minute', tr.time) - interval '4 hour' as trunc_time,
> tr.price, tr.volume from trans tr
> where tr.time between '13:30:00.00' and '20:00:0.00' ) as trnew
> group by trnew.date, trnew.trunc_time order by trnew.date,
> trnew.trunc_time;
>
> How do I add first and last price for each minute to this query?
>
> Thanks a lot in advance.
>
> Cheers.
>
>
> ---------------------------------
> Fussy? Opinionated? Impossible to please? Perfect. Join Yahoo!'s user
> panel and lay it on us.

--
cu
Chris

Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten
Browser-Versionen downloaden: http://www.gmx.net/de/go/browser

Its really slow but what you can do is something like the following:

select count(a.*), b.* from foo.bar a,
(
select price from foo.bar order by time asc limit 1
union 
select price from foo.bar order by time desc limit 1
) as b
group by b.price
... just do the "wheres" as you need  ...

Chris

PS its untested maybe there are some syntax miss-spells



-- 
cu
Chris

Ist Ihr Browser Vista-kompatibel? Jetzt die neuesten 
Browser-Versionen downloaden: http://www.gmx.net/de/go/browser