[SQL] Solution to retrieve first and last row for each minute
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.
Re: [SQL] 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
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
[SQL] Database creation script
Hi all! I'm building a database creation script, and i want to modularize it. Now, I'm trying something like: /* - Customer creation script - Version : 1.0.0 */ \set ON_ERROR_STOP 1 \set AUTOCOMMIT off BEGIN; \i languages_and_countries.sql \i app_1.sql \i app_2.sql [...] END; What do you think about this way of doing? Which is the best option to do it? Thanks in advance! Dani Castaños ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] raw data into table process
Hi,
I'm not sure if you have access to a scripting language (like perl or
ruby) but my experience is that if you transform the source text file
into a secondary text file that postgres "copy" can read
natively/directly into the data formats you want, the copy command will
move everything into your primary table and way faster than using sql
to transform.
I've had great experience with using Ruby/regex to do text file
transforms such as this - it's amazingly fast (considering many say
it's a performance dog).
The best way to get started is copy some sample data OUT of PG to a
file and then use your scripting language to build a transformation
that formats exactly like your sample. (I've imported polygon, circle
and point types using this method, as well as dates, like what you're
trying to do).
I hope that helps - drop me a line off-list if you'd like some sample
Ruby code to read/write/transform your source.
Steve
At 06:33 AM 8/22/2007, [EMAIL PROTECTED] wrote:
Date: Wed, 22 Aug 2007 14:36:15 +1000
From: novice <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED], pgsql-sql@postgresql.org
Subject: raw data into table process
Message-ID:
<[EMAIL PROTECTED]>
I am trying to record the following entries into a table. I'm curious
to know if there's an efficient/effective way of doing this? This
entries come from an ancient datalogger (note: separated by space and
uses YY/MM/DD format to record date)
Plain file sample.dat
3665 OK BS 07/08/16 07:28
3665 CC BS 07/08/16 07:29
3665 CS BS 07/08/16 07:29
3665 CS BS 07/08/16 07:29
4532 OK BS 07/08/16 07:34
4004 OK BS 07/08/16 07:51
3991 OK BS 07/08/16 07:54
This is the table that I'm adding the entries to
CREATE TABLE maintenance
(
maintenance_id SERIAL PRIMARY KEY,
meter_id integer,
status text,
inspector text,
inspection_date timestamp with time zone,
)
-- Begin SQL Script
-- First table to dump the records in
CREATE TABLE dataload1
(data text)
-- Dump records using \copy
\copy dataload1 FROM sample.dat
-- Second table to import unique records ONLY
CREATE TABLE dataload2 AS
SELECT DISTINCT
data FROM dataload1;
-- Now I update unique records into the maintenance table
-- maintenance_id is SERIAL so it will be populated automatically
INSERT INTO maintenance(meter_id, status, inspector, inspection_date)
SELECT substr("data", 1, 4)::int
, substr("data", 8, 3)
, substr("data", 21, 2)
, (20||substr("data", 24, 2) ||'-'|| substr("data", 27, 2) ||'-'||
substr("data", 30, 2)||' '||substr("data", 33, 5))::timestamp as
inspection_date
FROM dataload2
-- So the new records will also be in timestamp order
ORDER BY inspection_date ;
-- Some housekeeping
VACUUM FULL VERBOSE ANALYZE maintenance;
-- Finally, drop the temporary tables
DROP TABLE dataload1
DROP TABLE dataload2
-- End SQL script
Any thoughts and suggestions welcome.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Solution to retrieve first and last row for each minute
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 <[EMAIL PROTECTED]> wrote:
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
-
Fussy? Opinionated? Impossible to please? Perfect. Join Yahoo!'s user panel
and lay it on us.
[SQL] Partial index and query plan
Hello all,
Imagine having this table:
create table user_history (
rec_id SERIAL not null,
date TIMESTAMPnot null,
action INT2 not null,
uid INT4 not null,
name CHAR(10) null default NULL,
constraint PK_USER_HISTORY primary key (rec_id),
constraint AK_DATE_USER_HIS unique (date)
);
and this partial index:
create unique index indx_date_action12_uid_user_his
on user_history (date, uid)
where action <> 0;
and this query:
select date
from "user_history"
where date > '2007-08-18 14:33'
and date <= '2007-08-18 16:30'
and uid = 1388
and action <> 0
limit 1;
The question is why "explain analyze" shows a 'Filter: ("action" <> 0)' in plan:
Limit (cost=0.00..3.05 rows=1 width=8) (actual time=4.798..4.798
rows=0 loops=1)
-> Index Scan using indx_date_action12_uid_user_his on
user_history (cost=0.00..6.10 rows=2 width=8) (actual
time=4.791..4.791 rows=0 loops=1)
Index Cond: ((date > '2007-08-18 14:33:40.60664'::timestamp
without time zone) AND (date <= '2007-08-18 16:30:00'::timestamp
without time zone) AND (uid = 138658))
Filter: ("action" <> 0)
when this is a "where" case of the index?
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Solution to retrieve first and last row for each minute
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Partial index and query plan
"Aleksandr Vinokurov" <[EMAIL PROTECTED]> writes:
> The question is why "explain analyze" shows a 'Filter: ("action" <> 0)' in
> plan:
Use a newer Postgres release (8.1 or later).
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Partial index and query plan
Thank you Tom,
but does it means that this is only an explain's problem or the plan
is actually such a hard, and postmaster actually checks each record
found by the index with this "filter"?
I'm using 8.0.1 version, but upgrading can become a work with expense.
On 22/08/07, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Aleksandr Vinokurov" <[EMAIL PROTECTED]> writes:
> > The question is why "explain analyze" shows a 'Filter: ("action" <> 0)' in
> > plan:
>
> Use a newer Postgres release (8.1 or later).
>
> regards, tom lane
>
With best wishes, Aleksandr.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Partial index and query plan
On 8/22/07, Aleksandr Vinokurov <[EMAIL PROTECTED]> wrote:
>
> create table user_history (
>rec_id SERIAL not null,
>date TIMESTAMPnot null,
>action INT2 not null,
>uid INT4 not null,
>name CHAR(10) null default NULL,
>constraint PK_USER_HISTORY primary key (rec_id),
>constraint AK_DATE_USER_HIS unique (date)
> );
>
> create unique index indx_date_action12_uid_user_his
> on user_history (date, uid)
> where action <> 0;
>
> and this query:
>
> select date
> from "user_history"
> where date > '2007-08-18 14:33'
> and date <= '2007-08-18 16:30'
> and uid = 1388
> and action <> 0
> limit 1;
>
>
> The question is why "explain analyze" shows a 'Filter: ("action" <> 0)' in
> plan:
>
> Limit (cost=0.00..3.05 rows=1 width=8) (actual time=4.798..4.798
> rows=0 loops=1)
>-> Index Scan using indx_date_action12_uid_user_his on
> user_history (cost=0.00..6.10 rows=2 width=8) (actual
> time=4.791..4.791 rows=0 loops=1)
> Index Cond: ((date > '2007-08-18 14:33:40.60664'::timestamp
> without time zone) AND (date <= '2007-08-18 16:30:00'::timestamp
> without time zone) AND (uid = 138658))
> Filter: ("action" <> 0)
I don't see the issue here. The index being used is the same partial
index you created. Maybe it's just a question of semantics?
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Partial index and query plan
Scott Marlowe wrote: > > I don't see the issue here. The index being used is the same partial > index you created. Maybe it's just a question of semantics? > As I understand final filter is: a) pointed at the index creation b) is redundant as all the indexed records have action <> 0. So checking of it is a time wasting. And the plan should be this: >> Limit (cost=0.00..3.05 rows=1 width=8) (actual time=4.798..4.798 >> rows=0 loops=1) >>-> Index Scan using indx_date_action12_uid_user_his on >> user_history (cost=0.00..6.10 rows=2 width=8) (actual >> time=4.791..4.791 rows=0 loops=1) >> Index Cond: ((date > '2007-08-18 14:33:40.60664'::timestamp >> without time zone) AND (date <= '2007-08-18 16:30:00'::timestamp >> without time zone) AND (uid = 138658)) I suggest that this 'Filter' check will not be noticed as it always return TRUE, and so will be checked only once, -- because of the "limit 1". :) But thanks, Aleksandr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] minimum bounding circle for a triangle/polygon
before i start reinventing the wheel does anyone have handy a function (or straight SQL) for calculating the centerpoint coordinates and the radius of the minimum bounding circle (not the the circumcircle) of a triangle (coordinates of 3 points given as inputs). a bonus would be a generalization for any polygon, but that's probably too much. thanks in advance! george
