pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCan this be done with sql?
4+ messages / 2 participants
[nested] [flat]
* Can this be done with sql?
@ 2002-07-03 18:32 teknokrat <teknokrat@yahoo.com>
2002-07-04 14:50 ` Re: Can this be done with sql? Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-07-04 21:05 ` Re: Can this be done with sql? teknokrat <teknokrat@yahoo.com>
0 siblings, 2 replies; 4+ messages in thread
From: teknokrat@yahoo.com @ 2002-07-03 18:32 UTC (permalink / raw)
To: pgsql-sql
In my database i have values recorded in one minute intervals. I would
like a query that can get me results for other time intervals. For
example - return maximum value in each 3 minute interval. Any ideas
how i can do this with sql? I tried writing a procedure in plsql but i
am told it does not support tuples as output. I can get the all the
one minute intervals and process them to get me three minute intervals
in my application but i would rather not do the expensive call for the
one minute intervals in the first place due to the large number of
data. any ideas?
thanks
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Can this be done with sql?
2002-07-03 18:32 Can this be done with sql? teknokrat <teknokrat@yahoo.com>
@ 2002-07-04 14:50 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
1 sibling, 0 replies; 4+ messages in thread
From: Rajesh Kumar Mallah. @ 2002-07-04 14:50 UTC (permalink / raw)
To: teknokrat@yahoo.com; pgsql-sql
Hi ,
you can use GROUP BY , at the expense of adding one more column of SERIAL data type,
say,
select * from t_a limit 10;
access_log=# SELECT * from t_a limit 15;
sno | value
-----+-------
1 | 4533
2 | 2740
3 | 9970
4 | 6445
5 | 2220
6 | 2301
7 | 6847
8 | 5739
9 | 5286
10 | 5556
11 | 9309
12 | 9552
13 | 8589
14 | 5935
15 | 2382
(15 rows)
if you want avg for every third item you can use:
access_log=# SELECT avg(value) from t_a group by (1+(sno-1)/3) limit 5;
avg
-----------------
5747.6666666667
3655.3333333333
5957.3333333333
8139.0000000000
5635.3333333333
(5 rows)
you can replace 3 in the SQL with any number for grouping that many records.
if you need MEAN , STDDEV , MAX, MIN etc you can use approprite AGGREGATE that PGSQL supports
for numbers eg for MAX
access_log=# SELECT MAX(value) from t_a group by (1+(sno-1)/3) limit 5;
max
------
9970
6445
6847
9552
8589
(5 rows)
Regds
MAlz.
On Thursday 04 July 2002 00:02, teknokrat wrote:
> In my database i have values recorded in one minute intervals. I would
> like a query that can get me results for other time intervals. For
> example - return maximum value in each 3 minute interval. Any ideas
> how i can do this with sql? I tried writing a procedure in plsql but i
> am told it does not support tuples as output. I can get the all the
> one minute intervals and process them to get me three minute intervals
> in my application but i would rather not do the expensive call for the
> one minute intervals in the first place due to the large number of
> data. any ideas?
>
> thanks
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Can this be done with sql?
2002-07-03 18:32 Can this be done with sql? teknokrat <teknokrat@yahoo.com>
@ 2002-07-04 21:05 ` teknokrat <teknokrat@yahoo.com>
2002-07-07 13:36 ` Re: Can this be done with sql? Rajesh Kumar Mallah. <mallah@trade-india.com>
1 sibling, 1 reply; 4+ messages in thread
From: teknokrat@yahoo.com @ 2002-07-04 21:05 UTC (permalink / raw)
To: pgsql-sql
""Rajesh Kumar Mallah."" <mallah@trade-india.com> wrote in message
news:200207042020.29657.mallah@trade-india.com...
> Hi ,
>
> you can use GROUP BY , at the expense of adding one more column of SERIAL
d=
> ata type,
>
> say,
>
> select * from t_a limit 10;
> access_log=3D# SELECT * from t_a limit 15;
>
> sno | value
> -----+-------
> 1 | 4533
> 2 | 2740
> 3 | 9970
>
> 4 | 6445
> 5 | 2220
> 6 | 2301
>
> 7 | 6847
> 8 | 5739
> 9 | 5286
>
> 10 | 5556
> 11 | 9309
> 12 | 9552
>
> 13 | 8589
> 14 | 5935
> 15 | 2382
> (15 rows)
>
> if you want avg for every third item you can use:
>
> access_log=3D# SELECT avg(value) from t_a group by (1+(sno-1)/3) limit
5;=
> =20=20
>
yes, thank you, that may help but unfortunately there are are few more
problems to face.
1. I will need to select groups from anywhere in the table so i cannot
assume that 1 will be the start number. They will be contigous however so i
can use another query top get the start number but is it possible to do it
with just one select?
2. I need to display not just aggregates but the first and last value in the
group for two of the fields. I mean by this that i need
opening_value(field1) and closing_value(field2).
3. If this needs to be done via stored procedure how do i get it to return a
result set. I've tried setof record but it doesn't work.
thanks
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Can this be done with sql?
2002-07-03 18:32 Can this be done with sql? teknokrat <teknokrat@yahoo.com>
2002-07-04 21:05 ` Re: Can this be done with sql? teknokrat <teknokrat@yahoo.com>
@ 2002-07-07 13:36 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
0 siblings, 0 replies; 4+ messages in thread
From: Rajesh Kumar Mallah. @ 2002-07-07 13:36 UTC (permalink / raw)
To: teknokrat@yahoo.com; pgsql-sql
Hi,
You cannot easily return datasets from stored procedures.
there has been lots of discussion on it.
regds
mallah.
> yes, thank you, that may help but unfortunately there are are few more
> problems to face.
>
> 1. I will need to select groups from anywhere in the table so i cannot
> assume that 1 will be the start number. They will be contigous however so i
> can use another query top get the start number but is it possible to do it
> with just one select?
>
> 2. I need to display not just aggregates but the first and last value in
> the group for two of the fields. I mean by this that i need
> opening_value(field1) and closing_value(field2).
>
> 3. If this needs to be done via stored procedure how do i get it to return
> a result set. I've tried setof record but it doesn't work.
>
> thanks
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-07-07 13:36 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-07-03 18:32 Can this be done with sql? teknokrat <teknokrat@yahoo.com>
2002-07-04 14:50 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
2002-07-04 21:05 ` teknokrat <teknokrat@yahoo.com>
2002-07-07 13:36 ` Rajesh Kumar Mallah. <mallah@trade-india.com>
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