Author Topic: analytical function question  (Read 3760 times)

alext

  • Newbie
  • *
  • Posts: 1
    • View Profile
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
    • View Profile
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> -- 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