Home » SQL & PL/SQL » SQL & PL/SQL » Rank based on multiple conditions (Oracle 11.2.0.3, Linux)
| Rank based on multiple conditions [message #597401] |
Fri, 04 October 2013 07:56  |
sspn2010
Messages: 167
Registered: October 2008
|
Senior Member |
|
|
Hi,
I've below table.
with tmp_tbl as
(select 'H1' as hh_id, 'C1' as cust_id, 2 as f_rnk, 'F' as gender, to_date('20130102','YYYYMMDD') as purch_dt, to_date('20100203','YYYYMMDD') first_dt from dual
union
select 'H1' as hh_id, 'C2' as cust_id, 1 as f_rnk, 'M' as gender, to_date('20130102','YYYYMMDD') as purch_dt, to_date('20100303','YYYYMMDD') first_dt from dual
union
select 'H1' as hh_id, 'C3' as cust_id, cast(null as number) as f_rnk, 'U' as gender, to_date('20130103','YYYYMMDD') as purch_dt, to_date('20100403','YYYYMMDD') first_dt from dual
union
select 'H2' as hh_id, 'C4' as cust_id, cast(null as number) as f_rnk, 'F' as gender, to_date('20130403','YYYYMMDD') as purch_dt, to_date('20091002','YYYYMMDD') first_dt from dual
union
select 'H2' as hh_id, 'C5' as cust_id, cast(null as number) as f_rnk, 'M' as gender, to_date('20130505','YYYYMMDD') as purch_dt, to_date('20100808','YYYYMMDD') first_dt from dual
union
select 'H3' as hh_id, 'C6' as cust_id, cast(null as number) as f_rnk, 'F' as gender, cast(null as date) as purch_dt, to_date('20080506','YYYYMMDD') first_dt from dual
union
select 'H3' as hh_id, 'C6' as cust_id, cast(null as number) as f_rnk, 'M' as gender, cast(null as date) as purch_dt, to_date('20100708','YYYYMMDD') first_dt from dual
)
select a.*
from tmp_tbl a
;
HH_ID CUST_ID F_RNK GENDER PURCH_DT FIRST_DT
----- ------- ---------- ------ ----------- -----------
H1 C1 2 F 1/2/2013 2/3/2010
H1 C2 1 M 1/2/2013 3/3/2010
H1 C3 U 1/3/2013 4/3/2010
H2 C4 F 4/3/2013 10/2/2009
H2 C5 M 5/5/2013 8/8/2010
H3 C6 F 5/6/2008
H3 C6 M 7/8/2010
Now i need to rank each cust_id in each hh_id based on below conditions.
1) If atleaset one cust_id in hh_id has f_rnk then gender 'F' with highest f_rnk (more then one F with same f_rnk then the one with oldest first_dt), if no 'F' then gender 'U' with highest f_rnk ((more then one F with same f_rnk then the one with oldest first_dt)), if no 'F' and 'U' then consider 'M' (more then one M with same f_rnk then the one with oldest first_dt).
2) If the above is not met (no cust_id in hh_id has f_rnk populated) then i've to rank based on purch_dt. Gender 'F' with recent purch_dt (if more than one F in household with same purch_dt then the one with oldest first_dt), if no 'F' then gender 'U' with recent purch_dt (if more than one U in household with same purch_dt then one with oldest first_dt), if no 'F' and 'U' then consider 'M' (more than one M in household with same purch_dt then the one with oldest first_dt).
3) If the above criteria is also not met, then rank based on gender_cd. Gender 'F' will have first preference then 'U' and then 'M'.
My output :
HH_ID CUST_ID F_RNK GENDER PURCH_DT FIRST_DT F_RNK_RANK PURCH_RANK GENDER_ONLY_RANK
----- ------- ---------- ------ ----------- ----------- ------------ ------------ -----------------
H1 C1 2 F 1/2/2013 2/3/2010 1
H1 C2 1 M 1/2/2013 3/3/2010 2
H1 C3 U 1/3/2013 4/3/2010 3
H2 C4 F 4/3/2013 10/2/2009 2
H2 C5 M 5/5/2013 8/8/2010 1
H3 C6 F 5/6/2008 1
H3 C6 M 7/8/2010 2
I've tried below query with one condition, but it's giving f_rnk_rank for all records. How can i include multiple conditions in the rank function. Please help me out.
with tmp_tbl as
(select 'H1' as hh_id, 'C1' as cust_id, 2 as f_rnk, 'F' as gender, to_date('20130102','YYYYMMDD') as purch_dt, to_date('20100203','YYYYMMDD') first_dt from dual
union
select 'H1' as hh_id, 'C2' as cust_id, 1 as f_rnk, 'M' as gender, to_date('20130102','YYYYMMDD') as purch_dt, to_date('20100303','YYYYMMDD') first_dt from dual
union
select 'H1' as hh_id, 'C3' as cust_id, cast(null as number) as f_rnk, 'U' as gender, to_date('20130103','YYYYMMDD') as purch_dt, to_date('20100403','YYYYMMDD') first_dt from dual
union
select 'H2' as hh_id, 'C4' as cust_id, cast(null as number) as f_rnk, 'F' as gender, to_date('20130403','YYYYMMDD') as purch_dt, to_date('20091002','YYYYMMDD') first_dt from dual
union
select 'H2' as hh_id, 'C5' as cust_id, cast(null as number) as f_rnk, 'M' as gender, to_date('20130505','YYYYMMDD') as purch_dt, to_date('20100808','YYYYMMDD') first_dt from dual
union
select 'H3' as hh_id, 'C6' as cust_id, cast(null as number) as f_rnk, 'F' as gender, cast(null as date) as purch_dt, to_date('20080506','YYYYMMDD') first_dt from dual
union
select 'H3' as hh_id, 'C6' as cust_id, cast(null as number) as f_rnk, 'M' as gender, cast(null as date) as purch_dt, to_date('20100708','YYYYMMDD') first_dt from dual
)
select a.*, rank() over(partition by hh_id order by case when f_rnk is not null then
case gender when 'F' then 3
when 'U' then 2
when 'M' then 1
end
else coalesce(f_rnk,-1) end desc,
f_rnk,
first_dt asc ) f_rnk_rank
from tmp_tbl a
;
HH_ID CUST_ID F_RNK GENDER PURCH_DT FIRST_DT F_RNK_RANK
----- ------- ---------- ------ ----------- ----------- ----------
H1 C1 2 F 1/2/2013 2/3/2010 1
H1 C2 1 M 1/2/2013 3/3/2010 2
H1 C3 U 1/3/2013 4/3/2010 3
H2 C4 F 4/3/2013 10/2/2009 1
H2 C5 M 5/5/2013 8/8/2010 2
H3 C6 F 5/6/2008 1
H3 C6 M 7/8/2010 2
7 rows selected
Appreicate your help!!!
Thanks
Sri
|
|
|
|
|
|
| Re: Rank based on multiple conditions [message #597648 is a reply to message #597459] |
Mon, 07 October 2013 05:57  |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You were thinking along the right lines, you just didn't take it far enough:
SQL> with tmp_tbl as
2 (select 'H1' as hh_id, 'C1' as cust_id, 2 as f_rnk, 'F' as gender, to_date('20130102','YYYYMMDD') as purch_dt, to_date('20100203','YYYYMMDD') first_dt from dual
3 union
4 select 'H1' as hh_id, 'C2' as cust_id, 1 as f_rnk, 'M' as gender, to_date('20130102','YYYYMMDD') as purch_dt, to_date('20100303','YYYYMMDD') first_dt from dual
5 union
6 select 'H1' as hh_id, 'C3' as cust_id, cast(null as number) as f_rnk, 'U' as gender, to_date('20130103','YYYYMMDD') as purch_dt, to_date('20100403','YYYYMMDD') first_dt from dual
7 union
8 select 'H2' as hh_id, 'C4' as cust_id, cast(null as number) as f_rnk, 'F' as gender, to_date('20130403','YYYYMMDD') as purch_dt, to_date('20091002','YYYYMMDD') first_dt from dual
9 union
10 select 'H2' as hh_id, 'C5' as cust_id, cast(null as number) as f_rnk, 'M' as gender, to_date('20130505','YYYYMMDD') as purch_dt, to_date('20100808','YYYYMMDD') first_dt from dual
11 union
12 select 'H3' as hh_id, 'C6' as cust_id, cast(null as number) as f_rnk, 'F' as gender, cast(null as date) as purch_dt, to_date('20080506','YYYYMMDD') first_dt from dual
13 union
14 select 'H3' as hh_id, 'C6' as cust_id, cast(null as number) as f_rnk, 'M' as gender, cast(null as date) as purch_dt, to_date('20100708','YYYYMMDD') first_dt from dual
15 ),
16 mod_tbl AS
17 (select a.*,
18 MAX(f_rnk) OVER (PARTITION BY hh_id) f_rnk_present,
19 MAX(purch_dt) OVER (PARTITION BY hh_id) purch_present,
20 DECODE(gender, 'F', 1, 'U', 2, 'M', 3) gender_order
21 from tmp_tbl a
22 )
23 SELECT hh_id, cust_id, f_rnk, gender, purch_dt, first_dt,
24 CASE WHEN f_rnk_present IS NOT NULL
25 THEN RANK () OVER (PARTITION BY hh_id ORDER BY CASE WHEN f_rnk IS NOT NULL THEN gender_order
26 ELSE gender_order + 3
27 END,
28 f_rnk,
29 first_dt)
30 END f_rnk_rank,
31 CASE WHEN f_rnk_present IS NULL AND purch_present IS NOT NULL
32 THEN RANK () OVER (PARTITION BY hh_id ORDER BY gender_order, purch_dt)
33 END purch_rank,
34 CASE WHEN f_rnk_present IS NULL AND purch_present IS NULL
35 THEN RANK () OVER (PARTITION BY hh_id ORDER BY gender_order)
36 END gender_only_rank
37 FROM mod_tbl
38 ;
HH_ID CUST_ID F_RNK GENDER PURCH_DT FIRST_DT F_RNK_RANK PURCH_RANK GENDER_ONLY_RANK
----- ------- ---------- ------ ----------- ----------- ---------- ---------- ----------------
H1 C1 2 F 02/01/2013 03/02/2010 1
H1 C3 U 03/01/2013 03/04/2010 3
H1 C2 1 M 02/01/2013 03/03/2010 2
H2 C4 F 03/04/2013 02/10/2009 1
H2 C5 M 05/05/2013 08/08/2010 2
H3 C6 F 06/05/2008 1
H3 C6 M 08/07/2010 2
7 rows selected
SQL>
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 05:26:09 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
