From: | "Rajesh Kumar Mallah(dot)" <mallah(at)trade-india(dot)com> |
---|---|
To: | teknokrat(at)yahoo(dot)com (teknokrat), pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Can this be done with sql? |
Date: | 2002-07-04 14:50:29 |
Message-ID: | 200207042020.29657.mallah@trade-india.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)postgresql(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Achilleus Mantzios | 2002-07-04 15:56:03 | Re: how to write procedures |
Previous Message | Rajesh Kumar Mallah. | 2002-07-04 14:12:24 | Re: how to write procedures |