OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Query help (Oracle 11.2.0.4, Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query help [message #623958]

Tue, 16 September 2014 14:02

sspn2010
Messages: 167
Registered: October 2008

Senior Member

Hi,

Could someone please help me on writing below query.

I've a table in following layout.


with tmp_tbl as
(
select 1 as cust_id, date '2013-01-01' txn_dt, 123 as store_nbr, 1 as dept_cd from dual
union all
select 1 as cust_id, date '2013-01-01' txn_dt, 123 as store_nbr, 2 as dept_cd from dual
union all
select 1 as cust_id, date '2013-02-01' txn_dt, 345 as store_nbr, 4 as dept_cd from dual
union all
select 1 as cust_id, date '2013-03-01' txn_dt, 456 as store_nbr, 6 as dept_cd from dual
union all
select 1 as cust_id, date '2013-10-01' txn_dt, 123 as store_nbr, 3 as dept_cd from dual
union all
select 1 as cust_id, date '2013-10-01' txn_dt, 123 as store_nbr, 3 as dept_cd from dual
union all
select 2 as cust_id, date '2014-11-11' txn_dt, 789 as store_nbr, 3 as dept_cd from dual
union all
select 2 as cust_id, date '2014-11-11' txn_dt, 789 as store_nbr, 6 as dept_cd from dual
) 
select * from tmp_tbl;

Customer can have purchase from multiple/single departments with in a transaction. Now i need to find out the single department purchase percent out of all the purchases for each customer.

Let's say cust_id 2 has purchased in 2 departments (3 and 6) in one transaction so his single department purchase would be 0.
Cust_id 1 has 4 purchases (1/1/2013, 2/1/2013, 3/1/2013 and 10/1/2013). Out of these 2 were single department purchases (txn_dt 2/1/2013 and 3/1/2013), so the single department purchase percent would be 0.5%.

I've below SQL but it's running very long on the large volumes. Is there a better way to write this


with tmp_tbl as
(
select 1 as cust_id, date '2013-01-01' txn_dt, 123 as store_nbr, 1 as dept_cd from dual
union all
select 1 as cust_id, date '2013-01-01' txn_dt, 123 as store_nbr, 2 as dept_cd from dual
union all
select 1 as cust_id, date '2013-02-01' txn_dt, 345 as store_nbr, 4 as dept_cd from dual
union all
select 1 as cust_id, date '2013-03-01' txn_dt, 456 as store_nbr, 6 as dept_cd from dual
union all
select 1 as cust_id, date '2013-10-01' txn_dt, 123 as store_nbr, 3 as dept_cd from dual
union all
select 1 as cust_id, date '2013-10-01' txn_dt, 123 as store_nbr, 3 as dept_cd from dual
union all
select 2 as cust_id, date '2014-11-11' txn_dt, 789 as store_nbr, 3 as dept_cd from dual
union all
select 2 as cust_id, date '2014-11-11' txn_dt, 789 as store_nbr, 6 as dept_cd from dual
) 
SELECT cust_id,
                      SUM (DECODE (max_ranking, 1, 1, 0))
                    / SUM (DECODE (max_ranking, 1, 1, 1))
                       AS single_dept_percent
               FROM (
 SELECT /*+ parallel(x,16) */
                             x.cust_id,
                              x.txn_dt,
                              x.store_nbr,
                              MAX (x.ranking) AS max_ranking
from (                              
 SELECT 
                                       cust_id,
                                        txn_dt,
                                        store_nbr,
                                        dept_cd,
                                        ROW_NUMBER() OVER(PARTITION BY cust_id, txn_dt, store_nbr
                                           ORDER BY dept_cd)
                               ranking
              from tmp_tbl
 group by cust_id, txn_dt, store_nbr, dept_cd) x group by x.cust_id, x.txn_dt, x.store_nbr
 ) group by cust_id;

Appreciate your help!

Thanks
SS

Report message to a moderator