### Author Topic: analytical function question  (Read 8022 times)

#### alext

• Newbie
• Posts: 1
##### analytical function question
« on: April 06, 2009, 12:08:13 AM »
Hi all,
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   | partner_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 distribution or histogram, I can easily do it with the simple SQL code:

select total_customers, count(total_customers) from (
select count(vendor_id) total_customers, partner_id from table_a group by parnter_id_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,
alex

#### oratune

• Newbie
• Posts: 7
##### Re: analytical function question
« Reply #1 on: May 20, 2009, 01:43:21 PM »
Hi all,
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   | partner_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 distribution or histogram, I can easily do it with the simple SQL code:

select total_customers, count(total_customers) from (
select count(vendor_id) total_customers, partner_id from table_a group by parnter_id_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,
alex

SQL>
SQL> --
SQL> -- Create table
SQL> --
SQL> create table table_a(
2          customer_id  varchar2(16),
3          partner_id varchar2(16)
4  );

Table created.

SQL>
SQL> --
SQL> -- Create unique index
SQL> --
SQL> create unique index table_a_cust
2  on table_a(customer_id);

Index created.

SQL>
SQL> --
SQL> -- Populate table
SQL> --
SQL> begin
2          for i in 1..123456 loop
3                  insert into table_a
4                  values('Cust_'||i, 'Partner_'||mod(i, 9));
5          end loop;
6
7          commit;
8
9  end;
10  /

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> --
SQL> select total_customers, count(total_customers) from (
2                select count(customer_id) total_customers, partner_id from table_a group by partner_id order by total_customers desc )
3  group by total_customers;

TOTAL_CUSTOMERS COUNT(TOTAL_CUSTOMERS)
--------------- ----------------------
13717                      6
13718                      3

SQL>
SQL> --
SQL> -- Your query modified to remove useless columns
SQL> --
SQL> select total_customers, count(total_customers) from (
2                select count(customer_id) total_customers from table_a group by partner_id order by total_customers desc )
3  group by total_customers;

TOTAL_CUSTOMERS COUNT(TOTAL_CUSTOMERS)
--------------- ----------------------
13717                      6
13718                      3

SQL>
SQL> --
SQL> -- One version of analytics
SQL> --
SQL> with ttlcst as(
2          select count(customer_id) total_customers
3          from table_a
4          group by partner_id
5  )
6  select distinct total_customers, count(*) over (partition by total_customers) cust_ct_by_vend
7  from ttlcst
8  order by total_customers desc;

TOTAL_CUSTOMERS CUST_CT_BY_VEND
--------------- ---------------
13718               3
13717               6

SQL>
SQL> --
SQL> -- Another take on analytics
SQL> --
SQL> select distinct count(customer_id) total_customers, count(*) over (partition by count(customer_id)) cust_ct_by_vend
2  from table_a
3  group by partner_id
4  order by total_customers desc;

TOTAL_CUSTOMERS CUST_CT_BY_VEND
--------------- ---------------
13718               3
13717               6

SQL>

David Fitzjarrell