From: Tim Johnson <tim@celestialdesign.co.uk>
To: Oliver Mueschke <o@mueschke.de>
To: pgsql-sql@postgresql.org
Cc: Stephan Szabo <sszabo@kick.com>
Cc: eloehr@austin.rr.com
Subject: RE: Re: Matching and Scoring with multiple fields
Date: Thu, 13 Jul 2000 01:13:21 +0100
Message-ID: <NBBBLHOPPPPCHCIABAFGOENLCMAA.tim@celestialdesign.co.uk> (raw)
In-Reply-To: <20000710221454.A16918@ompc3.dom100.de>
Thanks to all of you that replied. I think Oliver's idea (which is pretty
close to Stephan's) will probably do the trick I think.
I will maybe look in the future to add the ability to allow users to weight
fields with more priority. So customers could number the top five most
important fields and then pick how they feel. I still worry about the
results being skewed by extreme data in certain fields but I guess there's
no way around that.
Thanks again.
Tim Johnson,
-- http://www.theinkfactory.co.uk
-----Original Message-----
From: Oliver Mueschke [mailto:o@mueschke.de]
Sent: 10 July 2000 21:15
To: pgsql-sql@postgresql.org
Cc: tim@lincolnshirenow.com
Subject: Re: [SQL] Re: Matching and Scoring with multiple fields
I'm not sure, but it seems you could calculate a column like:
SELECT a,b,c,...,
abs(<feel_a>-a)+abs(<feel_b>-b)+abs(<feel_c>-c)+... AS weight
FROM t
ORDER BY weight
This way the closest matches would come first.
On Mon, Jul 10, 2000 at 07:56:08PM +0100, Tim Johnson wrote:
> I have a problem. Ok I'll rephrase that, a challenge.
>
> I have a table like this:
>
> a,b,c,d,e,f,g,h
> ---------------
> 2,5,3,4,4,5,2,2
> 1,1,1,1,1,1,1,1
> 5,5,5,5,5,5,5,5
> 3,3,2,4,5,1,1,3
> 1,1,5,5,5,5,1,4
> 1,5,5,5,4,4,2,1
> 5,5,5,5,1,1,1,1
> 1,1,1,1,5,5,5,5
> (rows 8)
>
> a to h are of type int.
>
>
> I want to take input values which relate to this table say:
> how do you feel about a:
> how do you feel about b:
> how do you feel about c:
> ...
>
> and the answers will be 1 to 5.
>
> Now I want to take those answers for my incoming a to h and scan down the
> table pulling out the closest matches from best to worst. There will be
> about 2000 rows in the final table and I will LIMIT the rows in blocks of
10
> or so.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: tim@celestialdesign.co.uk, o@mueschke.de, sszabo@kick.com, eloehr@austin.rr.com
Subject: Re: Matching and Scoring with multiple fields
In-Reply-To: <NBBBLHOPPPPCHCIABAFGOENLCMAA.tim@celestialdesign.co.uk>
References: <20000710221454.A16918@ompc3.dom100.de> <NBBBLHOPPPPCHCIABAFGOENLCMAA.tim@celestialdesign.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox