Another Duplicate question. This time, a little more complicated 2

[daddypost]

Okay I've got a keyword. Under each keyword can be multiple retail categories with a specific number of orders per category.

Keyword category orders
diamond gemstones 100
diamond jewelry 20
diamond supplies 8
ruby gemstone 10
ruby jewelry 9
ruby books 1
sapphire jewelry 10


What I need to do is show which category is most prevelant, but only if there is a variance of 20% or more between the first and second. And if there is only one, then show the one.

the result from above should be

keyword category percentage
Diamond gemstones 78.125%(100/128)
ruby - -
sapphire jewelry 100%


I'm assuming a cursor but not sure how to compare if there are more than 2(could be as many as 6) categories. Your help is greatly appreciated.

 

[SantaMufasa]

Daddy,

Pardon my ignorance, but could you please confirm what calculation you want to determine if "there is a variance of 20% or more between the first and second"?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

Daddy,

Since I haven't heard back from you re: the definition of variance, I'll presume that what you actually mean is simply the difference between the percent of total orders of the max(orders) amount (for a keyword) and the second-place orders amount for the same keyword.

Given that presumption, here is code that does what you want, plus the results of that code using the sample data you provided:

col percentage format a10
select detail.keyword
      ,nvl(decode(sign((((summary.mx/summary.tot)*100)-((second_place.mx/summary.tot)*100))-20)
              ,1,detail.category
              ,0,detail.category
              ,-1,'-')
          ,detail.category) category
      ,nvl(decode(sign((((summary.mx/summary.tot)*100)-((second_place.mx/summary.tot)*100))-20)
              ,1,to_char(((summary.mx/summary.tot)*100),'999.999')||'%'
              ,0,to_char(((summary.mx/summary.tot)*100),'999.999')||'%'
              ,-1,'-')
          ,' 100.000%') percentage
  from keyword_orders detail
      ,(select keyword
              ,sum(orders) tot
              ,max(orders) mx
          from keyword_orders
         group by keyword) summary
      ,(select keyword
              ,max(orders) mx
          from keyword_orders x
         where orders < (select max(orders) mx
                           from keyword_orders y
                          where x.keyword = y.keyword)
         group by keyword) second_place
 where detail.keyword  = summary.keyword
   and detail.orders   = summary.mx
   and summary.keyword = second_place.keyword(+)
/

KEYWORD    CATEGORY   PERCENTAGE
---------- ---------- ----------
diamond    gemstones    78.125%
ruby       -          -
sapphire   jewelry     100.000%

3 rows selected.

Let us know if you have questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Dagon]

Another possibility:

select keyword, 
(case when prev_pct is null or prev_pct * 1.2 < pct then category else '-' end) as category,
(case when prev_pct is null or (prev_pct * 1.2) < pct then to_char(pct) else '-' end) as orders_pct
from
(
select keyword, category, pct, lag(pct) over (partition by keyword order by pct) as prev_pct, rn 
from
(select keyword, rank() over (partition by keyword order by orders desc) as rn, category,
(round(sum(orders) over (partition by keyword, category)/sum(orders) over (partition by keyword),5))*100 as pct
from keywords)
where rn <=2
)
where rn=1

 

[daddypost]

Thank you both for your help. It worked perfectly. I would have never thought of doing it that way. I was for sure that a cursor would be needed, but I guess that shows you how little I know. Again, thank you so much. Santa, you've saved me a couple of times now. And I'm learing everytime I read one of your posts.