Re: sort by percent matched

On Fri, 28 Sep 2001 22:47:26 -0500 (CDT)
"Thomas T. Thai" <tom@minnesota.com> wrote:

> instead of doing a query to match table2 to table1 exactly, i would like
> to match it based on and sorted by percent matched. so if a person was
> looking for a blue, am/fm, power window car, but there isn't one exactly
> like that. instead there is one that's RED, am/fm, power window. it would
> turn up as a result, but it wouldn't be 100%, but say 90%.
>
> how do i set that up so that the results can be returned by percent
> matched?

 Hi,Thomas.
 I hit on the solution, which you hope, that can be returned what
 percentage of people's requests match the items of table1.
 The following query is the case of only one people, but it can
 be extend by using GROUP BY in case of more than two people.


-- (on v7.1.3)
create table table1(ucar_name varchar(20),
                    exterior_color varchar(10),
                    interior_color varchar(10),
                    transmission varchar(10),
                    am_fm varchar(10),
                    power_window varchar(10));

create table table2(peolpe_name varchar(20),
                    exterior_color varchar(10),
                    interior_color varchar(10),
                    transmission varchar(10),
                    am_fm varchar(10),
                    power_window varchar(10));

insert into table1
         values('BMW1', 'blue', 'brown', 'AT', 'am/fm', 'exist');
insert into table1
         values('BMW2', 'red',  'white', 'MT', 'am/fm', 'exist');
insert into table1
         values('BMW3', 'red',  'white', 'AT', 'am/fm', 'none' );
insert into table1
         values('Benz1','red',  'white', 'AT', 'am',    'exist');
insert into table1
         values('Benz2','blue', 'black', 'AT', 'am/fm', 'exist');


-- cars features people are looking for
-- 'any' means features people aren't looking for
insert into table2
         values('Ben', 'blue', 'any', 'any', 'am/fm', 'exist');


-- return the percentage of being matched table2 to table1
-- and including the case of 'any' in table1
select t.ucar_name,
       (t.exterior + t.interior + t.trans + t.af + t.power)/5.0*100
          as "match[%]"
 from  (select t1.ucar_name,
                case when(t1.exterior_color = t2.exterior_color
                                or t2.exterior_color ='any')
                         then 1 else 0
                end as exterior,
                case when(t1.interior_color = t2.interior_color
                                or t2.interior_color= 'any')
                         then 1 else 0
                end as interior,
                case when(t1.transmission = t2.transmission
                                or t2.transmission = 'any')
                         then 1 else 0
                end as trans,
                case when(t1.am_fm = t2.am_fm or t2.am_fm = 'any')
                       then 1 else 0
                end as af,
                case when(t1.power_window = t2.power_window
                                or t2.power_window = 'any')
                       then 1 else 0
                end as power
           from table1 as t1, table2 as t2
         ) as t
 order by 2 DESC
;


 ucar_name | match[%]
-----------+----------
 Benz2     |      100
 BMW1      |      100
 BMW2      |       80
 Benz1     |       60
 BMW3      |       60
(5 rows)



Regards.

----------------------
Masaru Sugawara
rk73@echna.or.jp