Home » SQL & PL/SQL » SQL & PL/SQL » Compare rows (11.2.0.3)
Compare rows [message #643362] Tue, 06 October 2015 03:06 Go to next message
Amine
Messages: 371
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
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 #643368 is a reply to message #643364] Tue, 06 October 2015 05:38 Go to previous messageGo to next message
Amine
Messages: 371
Registered: March 2010
Senior Member

Fantastic Michel, as always !
Re: Compare rows [message #684444 is a reply to message #643368] Mon, 07 June 2021 15:01 Go to previous messageGo to next message
Amine
Messages: 371
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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.
Re: Compare rows [message #684448 is a reply to message #684447] Tue, 08 June 2021 09:39 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

/forum/fa/2115/0/

Previous Topic: View creation
Next Topic: CHR(n) equivalent function for double-byte characters
Goto Forum:
  


Current Time: Thu Mar 28 05:35:46 CDT 2024