Geek asks and answers

How to get mysql match result as percentage?

I am using Match (Col1) Against (Val) in mysql.

select match (body) against (body_var) from articles;

now in the case of a complete match, I get the result as a number (for example, 14.43). What does this number mean? and the main question: I can get the result as a percentage (for example, 0.94)
thanks for the help

+3
source share
2 answers

There is probably an easier way to do this. Somehow I fell into a rabbit hole on this .. But it was tested and works (returns a percentage of the results)

SELECT (mthCount / ttlCount) AS mPercent
FROM (
  SELECT COUNT( * ) AS mthCount
  FROM articles WHERE (
     MATCH(body) AGAINST(body_var) 
     )
) AS MCount JOIN (
  SELECT COUNT( * ) AS ttlCount
  FROM articles
) AS TCount;

it returns one record / result with mPercent column

You can also use it round to two decimal places ...

SELECT FORMAT((mthCount / ttlCount),2) AS mPercent
FROM (
  SELECT COUNT( * ) AS mthCount
  FROM articles WHERE (
     MATCH(body) AGAINST(body_var) 
     )
) AS MCount JOIN (
  SELECT COUNT( * ) AS ttlCount
  FROM articles
) AS TCount;

. 358 50 50/350 = 0.1396648... ( ) 0.14

- ...

MATCH/AGAINST . . " "...

, 100% - , ...

, PHP _text - ...

?

http://forums.mysql.com/read.php?107,125239,146610#msg-146610

http://seminex.blogspot.com/2005/06/mysql-relevance-in-fulltext-search.html

+1

, , - , , , , , . , , , , , .

:

SELECT
MAX(MATCH (table.col1)  AGAINST ('text 1'  IN NATURAL LANGUAGE MODE)) AS  bscore_col1 ,
MAX(MATCH (table.col2) AGAINST ('text 2'                          
IN NATURAL LANGUAGE MODE)) AS bscore_col2
FROM table
ORDER BY bscore_name col1 DESC, bscore_col2 DESC) AS bests

, , .

, 0,5, 0,5 , > 50% , , .

SELECT *,
MATCH (table.col1) AGAINST ('text 1' IN NATURAL LANGUAGE MODE)/bests.bscore_col1 AS score_col1 ,
MATCH (table.col2) AGAINST ('text 2' IN NATURAL LANGUAGE MODE)/bests.bscore_col2 AS score_col2
FROM (table,
(SELECT
MAX(MATCH (table.col1)  AGAINST ('text 1'  IN NATURAL LANGUAGE MODE)) AS bscore_col1 ,
MAX(MATCH (table.col2) AGAINST ('text 2' IN NATURAL LANGUAGE MODE)) AS bscore_col2
FROM table
ORDER BY bscore_col2 DESC, bscore_col1 DESC) AS bests)
WHERE
MATCH (table.col1)  AGAINST ('text 1'  IN NATURAL LANGUAGE MODE)/bests.bscore_col1 > 0.5 AND
MATCH (table.col2) AGAINST ('text 2'IN NATURAL LANGUAGE MODE)/bests.bscore_col2 > 0.5
ORDER BY score_col2 DESC, score_col1 DESC

, , .

0

More articles:


All Articles