Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2002
    Atlanta, GA

    Unhappy Unanswered: Complex Top-n Question

    I have a large list of sites that have usage data for each day of the month. My end goal is to retrieve the average of the ten highest days for each site. I can get the averages of each site for the whole month and I can also get the top ten days for an individual site. I just can't seem to close the loop on getting the individual average of the 10 highest days usage. Please see attachment for an illustration. The obstacle seems to be performing the average function for each individual site grouping. Any suggestions would be greatly appreciated!


  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1

    Re: Complex Top-n Question

    Here is my solution:

    create table usage
    ( site varchar2(1) not null
    , udate date not null
    , usage number not null

    REM Populate the table with test data:

    for i in 1..31 loop
    insert into usage values ('A',TRUNC(SYSDATE,'MM')+i-1,i);
    insert into usage values ('B',TRUNC(SYSDATE,'MM')+i-1,i+1);
    end loop;

    REM The query:

    select site, avg(usage)
    select site, udate, usage, rank() over (partition by site order by usage desc) the_rank
    from usage
    where the_rank <= 10
    group by site;

    - ----------
    A 26.5
    B 27.5

    There are some assumptions here, e.g. only 1 usage value recorded per site per day, but these could easily be overcome if that is not the case.

    I hope I have answered the right question!

Posting Permissions

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