pg.ddx.io pgsql-sql@postgresql.org mailing list archive help / color / mirror / Atom feed From: roopa perumalraja <roopabenzer@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Solution to retrieve first and last row for each minute
Date: Wed, 22 Aug 2007 00:25:47 -0700 (PDT)
Message-ID: <729161.83589.qm@web50810.mail.re2.yahoo.com> (raw )
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.
view thread (4+ messages) latest in thread
Message-ID: <729161.83589.qm@web50810.mail.re2.yahoo.com>
Permalink: ../729161.83589.qm@web50810.mail.re2.yahoo.com/
Also on: postgresql.org/message-id/729161.83589.qm@web50810.mail.re2.yahoo.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: roopabenzer@yahoo.com
Subject: Re: Solution to retrieve first and last row for each minute
In-Reply-To: <729161.83589.qm@web50810.mail.re2.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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