DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: sql query

  1. #1
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349

    Arrow sql query

    Hi,

    I've a table it has data for each 5 minutes.so table looks like this.
    BEGINDATE AVGCPU
    ----------------------- -----------
    11/6/2005 12:05:00 AM 10
    11/6/2005 12:10:00 AM 12
    11/6/2005 12:15:00 AM 15
    11/6/2005 12:20:00 AM 08
    so on.....

    My requirement is to average the AVGCPU for each 15 minutes starting from 11/6/2005 12:05:00 AM.

    Any expert advice.

    Thanks in advance.
    http://www.perf-engg.com
    A performance engineering forum

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Can you post the output you want?

    Tamil

  3. #3
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    output like

    BEGINDATE AVGCPU
    ----------------------- -----------
    11/6/2005 12:05:00 AM 12.3
    11/6/2005 12:20:00 AM 08
    http://www.perf-engg.com
    A performance engineering forum

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Thanks for posting an interesting query.
    Here I say .....
    Code:
    SQL> select * from r1 ;
    
    RDATE                      CPU
    ------------------- ----------
    09-11-2005 12:05:00         10
    09-11-2005 12:10:00         12
    09-11-2005 12:15:00         15
    09-11-2005 12:20:00          8
    
    SQL> get 3rows_avg.sql
      1  select *
      2  from (
      3  select rdate,
      4         cpu,
      5         avg(cpu) over ( order by rdate
      6          rows  between 0 preceding 
                  and  2 following) rows_3_avg
      7  from r1
      8  )
      9* where to_char(rdate,'MI') in ( '05','20')
    SQL> /
    
    RDATE                      CPU ROWS_3_AVG
    ------------------- ---------- ----------
    09-11-2005 12:05:00         10      12.33
    09-11-2005 12:20:00          8       8.00
    
    


    Note: you can add relevant Minutes in the WHERE clause.

    Tamil
    Last edited by tamilselvan; 11-09-2005 at 07:10 PM.

  5. #5
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Thanks a lot. It worked.
    http://www.perf-engg.com
    A performance engineering forum

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width