Wiki @ SAI Virtual Observatory

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Ranking search results ======

==== tf-idf ====

<code>
CREATE OR REPLACE FUNCTION my_rank (tsvector, tsquery, int ) RETURNS float AS $$
use List::Util qw(min max);
my ($string, $query) = @_;

my $maxpos = 0;

# Parse tsvector, taking into account positional information
foreach (split / /, $string){
  if(/^'(\w+)'(:([0-9,]+))?$/){
    foreach(split /,/, $3){
      $vector{$_} = $1;
      $maxpos = max($maxpos, $_);
    }
  }
}

my @vector = map {$vector{$_}} sort {$a <=> $b} keys(%vector);

# Parse tsquery, assuming it to be ORed only
$query =~ s/[()|'']/ /g;
$query =~ s/^\s+(.*?)\s+$/$1/;
my @query = split /\s+/, $query;

my %ndoc = {};
my %nentry = {};

my $rv = spi_exec_query("SELECT word,ndoc,nentry FROM wordstat WHERE ".join(' OR ', map {"word='$_'"} @query));
foreach my $i (0 .. $rv->{processed} - 1){
  $ndoc{$rv->{rows}[$i]->{word}} = $rv->{rows}[$i]->{ndoc};
  $nentry{$rv->{rows}[$i]->{word}} = $rv->{rows}[$i]->{nentry};
}

my $rank = 0;

foreach my $q (@query) {
  my $tf = scalar(grep {/^$q$/} @vector) / $maxpos;
  my $df = $ndoc{$q} || 0;
  if($tf > 0 && $df > 0) {
    $rank += $tf / log($df);
  }
}

return $rank;
$$ LANGUAGE plperlU;

</code>

==== simple ====

<code>
CREATE OR REPLACE FUNCTION my_rank (tsvector, tsquery) RETURNS float AS $$
my ($string, $query) = @_;

# Parse tsvector. Use only words, not positional information
my @vector = map /'(\w+)'/, split / /, $string;

# Parse tsquery, assuming it to be ORed only
$query =~ s/[()|'']/ /g;
$query =~ s/^\s+(.*?)\s+$/$1/;
my @query = split /\s+/, $query;

# Number of query terms in the vector
my $qlength = scalar(@query);
# Number of matched terms
my $qfound = scalar(map {my $q=$_; grep {/$q/} @vector} @query);

# The more matched terms - the better
my $rank_more = $qfound/$qlength;
# The less matched terms - the better
my $rank_less = ($qlength - $qfound + 1)/$qlength;
# Middle way - prefer both single and all matches
#my $rank_mid = 1 - $qfound*($qlength - $qfound + 1)/$qlength**2;
my $rank_mid = ($rank_more > $rank_less ? $rank_more : $rank_less);

return $rank_mid;
$$ LANGUAGE plperl;
</code>
<code sql>
SELECT title, my_rank(fts, q) rank FROM apod, to_tsquery('english','sombrero|supernovae|milky|way|<b style="color:black;background-color:#ffff66">meteor</b>') q WHERE fts @@ q ORDER BY rank DESC LIMIT 100;
</code>
ranking.txt · Last modified: 2010/07/02 00:48 by karpov
Back to top