pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSolution to retrieve first and last row for each minute
4+ messages / 2 participants
[nested] [flat]
* Solution to retrieve first and last row for each minute
@ 2007-08-22 07:25 roopa perumalraja <roopabenzer@yahoo.com>
0 siblings, 0 replies; 4+ messages in thread
From: roopa perumalraja @ 2007-08-22 07:25 UTC (permalink / raw)
To: pgsql-sql
Hi all,
I have a table trans with the data
price | volume | date | time
-------+--------+------------+--------------
79.87 | 500 | 2006-06-01 | 13:30:14.262
79.87 | 900 | 2006-06-01 | 13:30:15.375
79.85 | 200 | 2006-06-01 | 13:30:17.381
79.85 | 500 | 2006-06-01 | 13:30:20.276
79.85 | 1900 | 2006-06-01 | 13:30:21.438
79.83 | 200 | 2006-06-01 | 13:30:23.388
79.83 | 600 | 2006-06-01 | 13:30:25.863
79.82 | 400 | 2006-06-01 | 13:30:27.838
79.82 | 400 | 2006-06-01 | 13:30:29.314
79.82 | 400 | 2006-06-01 | 13:30:30.776
79.81 | 400 | 2006-06-01 | 13:30:31.867
79.82 | 100 | 2006-06-01 | 13:30:32.446
79.82 | 100 | 2006-06-01 | 13:30:41.44
79.82 | 100 | 2006-06-01 | 13:30:42.712
79.82 | 400 | 2006-06-01 | 13:30:45.907
79.83 | 600 | 2006-06-01 | 13:30:48.245
79.83 | 400 | 2006-06-01 | 13:30:49.33
79.85 | 100 | 2006-06-01 | 13:30:53.282
79.89 | 700 | 2006-06-01 | 13:31:06.437
79.89 | 1100 | 2006-06-01 | 13:31:08.513
79.89 | 100 | 2006-06-01 | 13:31:12.318
79.89 | 2200 | 2006-06-01 | 13:31:16.867
79.89 | 2400 | 2006-06-01 | 13:31:19.832
79.89 | 1200 | 2006-06-01 | 13:31:22.361
79.89 | 1000 | 2006-06-01 | 13:31:34.93
79.88 | 600 | 2006-06-01 | 13:31:44.98
79.89 | 3200 | 2006-06-01 | 13:31:46.497
79.88 | 1100 | 2006-06-01 | 13:31:49.345
79.88 | 500 | 2006-06-01 | 13:31:52.362
79.88 | 300 | 2006-06-01 | 13:31:53.286
79.85 | 800 | 2006-06-01 | 13:31:54.309
79.84 | 1900 | 2006-06-01 | 13:31:55.834
79.84 | 100 | 2006-06-01 | 13:32:02.318
79.85 | 700 | 2006-06-01 | 13:32:05.975
79.84 | 600 | 2006-06-01 | 13:32:06.375
79.84 | 500 | 2006-06-01 | 13:32:07.904
79.85 | 500 | 2006-06-01 | 13:32:08.918
79.87 | 400 | 2006-06-01 | 13:32:18.782
79.88 | 200 | 2006-06-01 | 13:32:20.336
79.88 | 1600 | 2006-06-01 | 13:32:30.381
79.88 | 200 | 2006-06-01 | 13:32:34.912
79.88 | 700 | 2006-06-01 | 13:32:36.279
79.88 | 100 | 2006-06-01 | 13:32:36.806
79.88 | 1500 | 2006-06-01 | 13:32:38.795
79.9 | 400 | 2006-06-01 | 13:32:40.992
79.9 | 200 | 2006-06-01 | 13:32:49.892
79.9 | 400 | 2006-06-01 | 13:32:51.391
79.9 | 200 | 2006-06-01 | 13:33:00.274
79.91 | 100 | 2006-06-01 | 13:33:03.862
79.92 | 200 | 2006-06-01 | 13:33:11.787
79.91 | 500 | 2006-06-01 | 13:33:12.781
79.91 | 1000 | 2006-06-01 | 13:33:12.781
79.95 | 1400 | 2006-06-01 | 13:33:14.962
79.94 | 1000 | 2006-06-01 | 13:33:17.429
79.95 | 200 | 2006-06-01 | 13:33:19.865
79.93 | 200 | 2006-06-01 | 13:33:20.91
79.93 | 200 | 2006-06-01 | 13:33:21.281
79.93 | 2200 | 2006-06-01 | 13:33:24.363
79.93 | 600 | 2006-06-01 | 13:33:25.739
79.94 | 200 | 2006-06-01 | 13:33:27.436
79.93 | 1300 | 2006-06-01 | 13:33:29.375
79.93 | 600 | 2006-06-01 | 13:33:30.375
79.93 | 300 | 2006-06-01 | 13:33:32.352
79.92 | 1400 | 2006-06-01 | 13:33:33.279
79.93 | 200 | 2006-06-01 | 13:33:34.825
:
:
:
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Solution to retrieve first and last row for each minute
@ 2007-08-22 08:49 Christian Kindler <christian.kindler@gmx.net>
0 siblings, 0 replies; 4+ messages in thread
From: Christian Kindler @ 2007-08-22 08:49 UTC (permalink / raw)
To: roopa perumalraja <roopabenzer@yahoo.com>; +Cc: pgsql-sql
Hi!
Do something like this
http://fimi.cvs.sourceforge.net/fimi/database/defaults/indicators/myinttick2bar.sql?revision=1.3&...
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Solution to retrieve first and last row for each minute
@ 2007-08-22 12:13 roopa perumalraja <roopabenzer@yahoo.com>
0 siblings, 0 replies; 4+ messages in thread
From: roopa perumalraja @ 2007-08-22 12:13 UTC (permalink / raw)
To: Christian Kindler <christian.kindler@gmx.net>; pgsql-sql
Hi Christian,
Thanks for your reply. Is it possible to use sub query to do this without using the IF ELSE LOOP?
Cheers
Roopa
Christian Kindler <christian.kindler@gmx.net> wrote:
Hi!
Do something like this
http://fimi.cvs.sourceforge.net/fimi/database/defaults/indicators/myinttick2bar.sql?revision=1.3&...
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
---------------------------------
Fussy? Opinionated? Impossible to please? Perfect. Join Yahoo!'s user panel and lay it on us.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Solution to retrieve first and last row for each minute
@ 2007-08-22 13:35 Christian Kindler <christian.kindler@gmx.net>
0 siblings, 0 replies; 4+ messages in thread
From: Christian Kindler @ 2007-08-22 13:35 UTC (permalink / raw)
To: roopa perumalraja <roopabenzer@yahoo.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-08-22 13:35 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-22 07:25 Solution to retrieve first and last row for each minute roopa perumalraja <roopabenzer@yahoo.com>
2007-08-22 08:49 Re: Solution to retrieve first and last row for each minute Christian Kindler <christian.kindler@gmx.net>
2007-08-22 12:13 Re: Solution to retrieve first and last row for each minute roopa perumalraja <roopabenzer@yahoo.com>
2007-08-22 13:35 Re: Solution to retrieve first and last row for each minute Christian Kindler <christian.kindler@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox