oracle/sql help... Summing only the top-N values?

[vshah]

So, i've got the following tables:

index_constituents(indices, constituents, weights)
idx_ric | const_ric | weight

index_configuration (indices, top_N value)
idx_ric | top_n_limit

each index in the constituents table may have an entry in the configuration table.

.HSI has a top_n_limit of 15
.KS200 has a top_n_limit of 10


What i'm trying to do is select the index, the top_n_limit, and the weight of the top_N constituents in one query.

starts out easy:

select idx_ric, top_n_limit 
        from index_configuration,
        where ric in ('.KS200','.HSI')
        and top_n_limit > 0;

this gets me the index and the top_N number.

I can easily get the top N weight for a single index with the following:

select idx_ric sum(weight) weight from 
  (select idx_ric, const_ric, weight from index_constituents where idx_ric = '.HSI'
  order by idx_ric, weight desc)
where rownum < (select top_n_limit from index_configuration where ric = '.HSI') + 1
group by idx_ric;

however i'm having trouble integrating the two so that I get one query with index, top_N_limit, and the top N weight as calculated in the 2nd query.

the problem is that I have to use the inner query to select and order the index constituents, such that when I use rownum in the outer query, I'll get only the top-N constituents, ordered by weight. I'm having trouble expanding this to support multiple indices.

i'm kinda a sql noob beyond the basics....any insight on how to expand the 2nd query to multiple indices, while still summing only the top-N weights for each index would be much appreciated. Note that the N can be different for each index, so that's why there's a select after the rownum.

 

[Ka0t1x]

There's a high chance that I'm wrong but I believe all of that syntax will work correctly with a Union.

ie:

select idx_ric sum(weight) weight from 
  (select idx_ric, const_ric, weight from index_constituents where idx_ric = '.HSI'
  order by idx_ric, weight desc)
where rownum < (select top_n_limit from index_configuration where ric = '.HSI') + 1
group by idx_ric
UNION
select idx_ric sum(weight) weight from 
  (select idx_ric, const_ric, weight from index_constituents where idx_ric = '.KS200'
  order by idx_ric, weight desc)
where rownum < (select top_n_limit from index_configuration where ric = '.KS200') + 1
group by idx_ric
order by 2;

You have to use order by col # at the very end to order by the columns (as a whole, not query segment specific).

There might be a better way to do it via left outer joins, but I can't think of it ATM.

edit: .. maybe..

select idx_ric sum(weight) weight, top_n_limit from 
  (select index_constituents.idx_ric, index_constituents.const_ric, index_constituents.weight, index_configuration.top_n_limit from index_constituents 
where idx_ric = '.HSI'
    left outer join index_configuration on (index_constituents.idx_ric=index_configuration.idx_ric)
  order by idx_ric, weight desc)
where rownum < top_n_limit + 1
group by idx_ric, top_n_limit
UNION
select idx_ric sum(weight) weight, top_n_limit from 
  (select index_constituents.idx_ric, index_constituents.const_ric, index_constituents.weight, index_configuration.top_n_limit from index_constituents 
where idx_ric = '.KS200'
    left outer join index_configuration on (index_constituents.idx_ric=index_configuration.idx_ric)
  order by idx_ric, weight desc)
where rownum < top_n_limit + 1
group by idx_ric, top_n_limit
order by 2;

FWIW, you should probably create a VIEW of the subquery with all indexes, and query against that with your index value. 🙂

 

[ForumMaster]

So, i've got the following tables:

index_constituents(indices, constituents, weights)
idx_ric | const_ric | weight

index_configuration (indices, top_N value)
idx_ric | top_n_limit

each index in the constituents table may have an entry in the configuration table.

.HSI has a top_n_limit of 15
.KS200 has a top_n_limit of 10


What i'm trying to do is select the index, the top_n_limit, and the weight of the top_N constituents in one query.

starts out easy:

select idx_ric, top_n_limit 
        from index_configuration,
        where ric in ('.KS200','.HSI')
        and top_n_limit > 0;

this gets me the index and the top_N number.

I can easily get the top N weight for a single index with the following:

select idx_ric sum(weight) weight from 
  (select idx_ric, const_ric, weight from index_constituents where idx_ric = '.HSI'
  order by idx_ric, weight desc)
where rownum < (select top_n_limit from index_configuration where ric = '.HSI') + 1
group by idx_ric;

however i'm having trouble integrating the two so that I get one query with index, top_N_limit, and the top N weight as calculated in the 2nd query.

the problem is that I have to use the inner query to select and order the index constituents, such that when I use rownum in the outer query, I'll get only the top-N constituents, ordered by weight. I'm having trouble expanding this to support multiple indices.

i'm kinda a sql noob beyond the basics....any insight on how to expand the 2nd query to multiple indices, while still summing only the top-N weights for each index would be much appreciated. Note that the N can be different for each index, so that's why there's a select after the rownum.

i'll start by admitting that your explanation of your tables is really quite poor. No offense intended, but i'm still confused.

Nevertheless, what i think you want is this.

You mention both top-n reporting and that you're using oracle. What you should use, is analytical functions. Specifically, the dense / rank functions. These functions can also be used with partition by and sliding windows clauses should you need it.

I believe this can help make your query easier. Good luck!


I'm confused here. Why bump four month old threads and then address the OP as if he was still waiting for replies?

Similar to Common Courtesy's action on the other thread you bumped, I'll leave this because the information is on-topic, but I would prefer you not make this a regular practice. Thanks.

Markbnj
Programming moderator