Hi,
Could someone tell me how I could have multiple group by based on the different count function?
Here's what I'm trying to do.
select x.prev_categ, x.next_categ,
count(distinct user_id) as countprev2next,
count(distinct user_id) as countprev2any,
count(distinct user_id) as countany2next,
count(distinct user_id) as countany2any
(
select user_id, prev_categ, next_categ,
dense_rank() over (order by prev_categ, next_categ) as rankprev2next,
dense_rank() over (order by prev_categ) as rankprev2any,
dense_rank() over (order by next_categ) as rankany2next,
dense_rank() over() as rankany2any
from next_categ_data
where x.prev_categ IS NOT NULL and x.next_categ IS NOT NULL
)x
group by x.prev_categ, x.next_categ
;
In the group by clause, I'd like to have group by based on the following conditions:
1) prev_categ and next_categ as shown in the query
2) only prev_categ
3) only next_categ
4) user_id
By this query, I'm trying to accomplish the following:
For instance, I've a transaction in which category A is switched to category B.
I'd like to count the distinct users who switched from category:
1) A to B (A2B)
2) A to any category (A2X)
3) Any to B (X2B)
4) Any to any (X2X)
This should be done for all possible transactions.
Sample Data
create table final as
(
select 1 user_id,2 product_id,A categ_id, to_Date('1/1/2009','MM/DD/YYYY') dt from dual union all
select 1 user_id,3 product_id,B categ_id, to_Date('1/1/2009','MM/DD/YYYY') dt from dual union all
select 1 user_id,4 product_id,C categ_id, to_Date('1/3/2009','MM/DD/YYYY') dt from dual union all
select 1 user_id,5 product_id,D categ_id, to_Date('1/3/2009','MM/DD/YYYY') dt from dual union all
select 1 user_id,6 product_id,E categ_id, to_Date('1/3/2009','MM/DD/YYYY') dt from dual union all
select 1 user_id,7 product_id,F categ_id, to_Date('1/10/2009','MM/DD/YYYY') dt from dual union all
select 1 user_id,8 product_id,G categ_id, to_Date('1/11/2009','MM/DD/YYYY') dt from dual union all
select 2 user_id,2 product_id,A categ_id, to_Date('1/1/2009','MM/DD/YYYY') dt from dual union all
select 2 user_id,3 product_id,B categ_id, to_Date('1/2/2009','MM/DD/YYYY') dt from dual union all
select 2 user_id,4 product_id,C categ_id, to_Date('1/4/2009','MM/DD/YYYY') dt from dual union all
select 2 user_id,5 product_id,F categ_id, to_Date('1/5/2009','MM/DD/YYYY') dt from dual union all
select 2 user_id,6 product_id,H categ_id, to_Date('1/6/2009','MM/DD/YYYY') dt from dual union all
select 2 user_id,7 product_id,F categ_id, to_Date('1/12/2009','MM/DD/YYYY') dt from dual union all
select 2 user_id,8 product_id,G categ_id, to_Date('1/15/2009','MM/DD/YYYY') dt from dual union all
select 3 user_id,2 product_id,A categ_id, to_Date('1/11/2009','MM/DD/YYYY') dt from dual union all
select 3 user_id,3 product_id,C categ_id, to_Date('1/12/2009','MM/DD/YYYY') dt from dual union all
select 3 user_id,4 product_id,B categ_id, to_Date('1/13/2009','MM/DD/YYYY') dt from dual union all
) ;
Sample output
Prev_categ | Next_categ | countprev2next | countprev2any | countany2next | countany2any
---------------------------------------------------------------------------------------
A B 2 3 3 3
A C 1 - 3 3
B C 2 2 - 3
C B 1 3 - 3
C D 1 - 1 3
C F 1 - 2 3
D E 1 1 1 3
E F 1 1 - 3
F G 2 2 2 3
F H 1 - 1 3
H F 1 1 - 3
Could you also tell me how I could make the count to be repeated? For instance, I want count 3 to be printed for both A to B and A to C
under prev2any column.
I appreciate any help.
Thanks again,