Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Analytical functions question (Oracle 9i)
Oracle Analytical functions question [message #396087] Fri, 03 April 2009 15:07 Go to next message
ktanya
Messages: 10
Registered: July 2007
Junior Member
Hi everybody,
I am trying to find out if I can use the analytical functions to perform the following:
I have a table that has two columns
---------------------------
customer_id | vendor_id |
---------------------------
Customer_id has the unique index.
Hence one vendor can have more than one customer.

If I need to have the data for the disctribution or hystogram, I can easily do it w/ simple SQL code:

select total_customers, count(total_customers) from (
select count(vendor_id) total_customers, vendor_id from contact group by vendor_id order by total_customers desc )
group by total_customers

And I will be able to do have the distribution of customers/ per partners

Is that possible to do the same using Oracle analytical functions.

Also, I need to know if I need a scale of 5, not 1? :) e.g. how many partners have 1-5 contacts, 6-10 contacts etc.

Any help would be greatly appreciated!!!
thanks,
Tanya
Re: Oracle Analytical functions question [message #396089 is a reply to message #396087] Fri, 03 April 2009 15:13 Go to previous messageGo to next message
user2004
Messages: 33
Registered: April 2009
Member
Quote:


Is that possible to do the same using Oracle analytical functions.



Yes it is possible..

You can look at the below link
http://www.psoug.org/reference/OLD/analytic_functions.html?PHPSESSID=19fb4bea6a9a27c36cd3459d6c0432ce

Search in google ..you will find more about analytical function.

Re: Oracle Analytical functions question [message #396090 is a reply to message #396087] Fri, 03 April 2009 15:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you want to use analytical functions?
It is a standard aggregation, you just have to change the groups to fit your requirements.

Here's a clue:
SQL> with data as (select round(dbms_random.value(1,20)) val from dual connect by level<=10)
  2  select val, 
  3         to_char(5*trunc(val/5))||'->'||to_char(5*(trunc(val/5)+1)-1) grp
  4  from data
  5  order by 1
  6  /
       VAL GRP
---------- ------------
         2 0->4
         3 0->4
         7 5->9
         9 5->9
        11 10->14
        12 10->14
        12 10->14
        13 10->14
        14 10->14
        15 15->19

10 rows selected.


Regards
Michel

Re: Oracle Analytical functions question [message #396091 is a reply to message #396087] Fri, 03 April 2009 15:54 Go to previous messageGo to next message
ktanya
Messages: 10
Registered: July 2007
Junior Member
Hi Michael,
Thank you.
I do not understand why do I need to use dbms_random
dbms_random package. I do not need to generate ramdom data. I use the set of values that I described above.
Also, the below SQL will never give the desired scale of 5
with data as (select round(dbms_random.value(1,20)) val from dual connect by level<=10)
2 select val,
3 to_char(5*trunc(val/5))||'->'||to_char(5*(trunc(val/5)+1)-1) grp
4 from data
5 order by 1
-----------
Here is an example:
I have the sql the gave the following hystogram
number_of_customers number_of partners
----------------------------------------
3 2
2 8
10 3
11 5
12 4
17 2

---------
I need to receive the output
scale of customers number_of_partners
0->5 10
5->10 3
10->15 9
15->20 2
...

Do you know how to implemented?
Thank you!
Re: Oracle Analytical functions question [message #396092 is a reply to message #396087] Fri, 03 April 2009 16:00 Go to previous messageGo to next message
ktanya
Messages: 10
Registered: July 2007
Junior Member
I know how to write the algorithm and call the function, but I want to see if that possible to do using SQL and better if analytical functions. All the examples that I read about analytical function do not related topic Sad((
thanks,
Tanya
Re: Oracle Analytical functions question [message #396093 is a reply to message #396087] Fri, 03 April 2009 16:47 Go to previous messageGo to next message
ktanya
Messages: 10
Registered: July 2007
Junior Member
Michael.
I own you a bunch!!!
Thank you!
Using your example I got the following sql which is very good
select scale, total_vendors from (
select scale, sum(count_vendors) total_vendors, order_num from (
with data as (select total_customers, count(total_customers) count_vendors from (
select count(vendor_id) total_customers, vendor_id from contact group by vendor_id order by total_customers desc )
group by total_customers order by 1)
select total_customers, to_number(5*trunc(total_customers/5))||'->'||to_number(5*(trunc(total_customers/5)+1)-1) scale,
to_number(5*trunc(total_customers/5)) order_num,
count_vendors
from data order by order_num)
group by scale, order_num
order by order_num )

--
Thank you!!!
But I still want to learn if the same is possible to accomplish using the analytical functions?
== Tanya
Re: Oracle Analytical functions question [message #396118 is a reply to message #396091] Sat, 04 April 2009 01:15 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I do not understand why do I need to use dbms_random
dbms_random package. I do not need to generate ramdom data. I use the set of values that I described above.

Just to make you understand: YOU do not need to generate data but I need it to show you this is the only purpose of dbms_random and data subquery.
Take care that groups are not exactly the ones you want: 0-4 instead of 1-5 and so on, so slighty modify the formulas.

Quote:
But I still want to learn if the same is possible to accomplish using the analytical functions?

It is not the purpose of them.
You need aggregate function, you do not need analytical function.
Use a screwdriver for a screw don't try to use a hammer even if you desire to use it.

Regards
Michel
Previous Topic: Calling a table through a varchar variable.
Next Topic: strange results set
Goto Forum:
  


Current Time: Sun Dec 04 17:04:58 CST 2016

Total time taken to generate the page: 0.08865 seconds