Re: Can this be done with 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.66666666673655.33333333335957.33333333338139.00000000005635.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
------99706445684795528589
(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.