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> -- Your original query
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
Topic: analytical function question (Read 2552 times)