Home » SQL & PL/SQL » SQL & PL/SQL » Compare rows (11.2.0.3)
Compare rows [message #643362] |
Tue, 06 October 2015 03:06 |
Amine
Messages: 376 Registered: March 2010
|
Senior Member |
|
|
Hi all,
drop table user_points;
create table user_points
(
id_user int ,
point_date date ,
points int
)
;
alter table user_points add constraint PK_user_points primary key (id_user, point_date);
insert into user_points values (1, to_date('01/02/2005', 'dd/mm/yyyy'), 10);
insert into user_points values (1, to_date('01/02/2004', 'dd/mm/yyyy'), 8);
insert into user_points values (1, to_date('01/02/2003', 'dd/mm/yyyy'), 6);
insert into user_points values (1, to_date('01/02/2000', 'dd/mm/yyyy'), 4);
insert into user_points values (2, to_date('01/02/2005', 'dd/mm/yyyy'), 10);
insert into user_points values (2, to_date('01/02/2004', 'dd/mm/yyyy'), 8);
insert into user_points values (2, to_date('01/02/2000', 'dd/mm/yyyy'), 4);
insert into user_points values (3, to_date('01/02/2008', 'dd/mm/yyyy'), 12);
insert into user_points values (3, to_date('01/02/2005', 'dd/mm/yyyy'), 10);
insert into user_points values (3, to_date('01/02/2004', 'dd/mm/yyyy'), 8);
insert into user_points values (3, to_date('01/02/1999', 'dd/mm/yyyy'), 4);
insert into user_points values (4, to_date('01/02/2005', 'dd/mm/yyyy'), 10);
insert into user_points values (4, to_date('01/02/2004', 'dd/mm/yyyy'), 8);
insert into user_points values (4, to_date('01/02/2001', 'dd/mm/yyyy'), 4);
insert into user_points values (5, to_date('01/02/2005', 'dd/mm/yyyy'), 10);
insert into user_points values (5, to_date('01/02/2004', 'dd/mm/yyyy'), 8);
insert into user_points values (5, to_date('01/02/2001', 'dd/mm/yyyy'), 4);
We want to compare users to each other and decide through a "rule" who is the biggest.
user "A" is bigger than user "B" means that
- the latest point of "A" is bigger than the latest point of "B"
- if the latest points are equal, then we look to the latest dates
- if the latest date of "A" is before the latest date of "B" then "A" is the biggest
- if the latest dates are equal then we look to the preceding points and we re-apply the rule.
if users are totally equal then there is no winner.
Examples :
--------
- user 1 is bigger than user 2 because : 10 = 10 and 01/02/1999 is before 01/02/2000.
- user 3 is bigger than user 2 because 12 > 10
- user 2 is bigger than user 4 because
- 10 = 10 and 01/02/2005 = 01/02/2005
- 8 = 8 and 01/02/2004 = 01/02/2004
- 4 = 4 and 01/02/2000 (of user 2) is before 01/02/2001 (of user 4), then 2 is bigger than 4
- users 4 and 5 are equal.
The expected result is :
user_A user_B biggest
------ ------ -------
1 2 1
1 3 3
1 4 1
1 5 1
2 3 ..
2 4 ..
2 5 ..
...
Thanks in advance,
Amine
|
|
|
Re: Compare rows [message #643364 is a reply to message #643362] |
Tue, 06 October 2015 05:01 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
With '-1' for even users:
SQL> with
2 data1 as (
3 select id_user, point_date, points,
4 row_number() over (partition by id_user order by point_date desc) rn
5 from user_points
6 ),
7 data2 as (
8 select id_user,
9 sys_connect_by_path(to_char(1000000*points+(trunc(sysdate)-point_date),'fm0000000000'),'/') p
10 from data1
11 where connect_by_isleaf = 1
12 connect by prior rn = rn - 1 and prior id_user = id_user
13 start with rn = 1
14 )
15 select d1.id_user u1, d2.id_user u2,
16 case
17 when d1.p > d2.p then d1.id_user
18 when d1.p < d2.p then d2.id_user
19 else -1
20 end best
21 from data2 d1, data2 d2
22 where d2.id_user > d1.id_user
23 order by u1, u2
24 /
U1 U2 BEST
----- ----- -----
1 2 1
1 3 3
1 4 1
1 5 1
2 3 3
2 4 2
2 5 2
3 4 3
3 5 3
4 5 -1
|
|
|
|
Re: Compare rows [message #684444 is a reply to message #643368] |
Mon, 07 June 2021 15:01 |
Amine
Messages: 376 Registered: March 2010
|
Senior Member |
|
|
Nearly six (06) years later, I just want to thank you again Michel Cadot for this genius solution.
And in general, thank to all of you experts (Barbara Bohemer, SY, Blackswan, Little foot, james Watson ...) and I probably forgot some of them, for making my job easier, and make me learn each time I come here.
The pleasure to appreciate a beautiful SQL query for a non obvious problem is simply orgasmic !
Thanks again, and keep going !
|
|
|
Re: Compare rows [message #684447 is a reply to message #643362] |
Tue, 08 June 2021 08:36 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Hierarchical solution Michel provided is simple and very readable approach and works very well when you have relatively small number of rows per ID_USER and/or decision is made on a deeper levels (all previous levels result in a tie). Otherwise it might be not as efficient since it traverses hierarchy all the way from root to leaf and only then compares complete paths. Recursive solution might be a more efficient solution if there is a larger number of user pairs where decision can be made on higher levels thus eliminating need to traverse all the way down to the leaf. I added column lvl to show at what recursion level decision was made:
with t as (
select u.*,
row_number() over(partition by id_user order by point_date desc) lvl,
count(*) over(partition by id_user) cnt
from user_points u
),
data as (
select t.*,
row_number() over(order by cnt desc) id_rn
from t
),
r(
id_user1,
id_user2,
lvl,
cnt,
best
) as (
select d1.id_user id_user1,
d2.id_user id_user2,
d1.lvl,
d1.cnt,
case
when d2.lvl is null then d1.id_user
when d1.points > d2.points then d1.id_user
when d1.points < d2.points then d2.id_user
when d1.point_date < d2.point_date then d1.id_user
when d1.point_date > d2.point_date then d2.id_user
end best
from data d1,
data d2
where d2.id_rn > d1.id_rn
and d2.lvl = d1.lvl
and d1.lvl = 1
union all
select d1.id_user,
d2.id_user id_user2,
d1.lvl,
d1.cnt,
case
when d2.lvl is null then r.id_user1
when d1.points > d2.points then d1.id_user
when d1.points < d2.points then d2.id_user
when d1.point_date < d2.point_date then d1.id_user
when d1.point_date > d2.point_date then d2.id_user
end best
from r,
data d1,
data d2
where d1.id_user = r.id_user1
and d1.lvl = r.lvl + 1
and d2.id_user(+) = r.id_user2
and d2.lvl(+) = d1.lvl
and r.best is null
)
select least(id_user1,id_user2) id_user1,
greatest(id_user1,id_user2) id_user2,
nvl(best,-1) best,
lvl
from r
where best is not null
or lvl = cnt
order by id_user1,
id_user2
/
ID_USER1 ID_USER2 BEST LVL
---------- ---------- ---------- ----------
1 2 1 3
1 3 3 1
1 4 1 3
1 5 1 3
2 3 3 1
2 4 2 3
2 5 2 3
3 4 3 1
3 5 3 1
4 5 -1 3
10 rows selected.
SQL>
As you can see 4 out of 10 decisions were made right away on level 1 saving time on drilling down all the way to leaf.
SY.
|
|
|
|
Goto Forum:
Current Time: Sun Jun 30 00:16:22 CDT 2024
|