Re: sort by percent matched - Mailing list pgsql-general
From | Masaru Sugawara |
---|---|
Subject | Re: sort by percent matched |
Date | |
Msg-id | 20010929210004.76B6.RK73@echna.ne.jp Whole thread Raw |
In response to | sort by percent matched ("Thomas T. Thai" <tom@minnesota.com>) |
List | pgsql-general |
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
pgsql-general by date: