Thread: Can this be done with 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
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.
""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
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.