一、加权排序原理
二、转化为SQL语句(合理使用case when xx then x else x end 等mysql的函数)
搜索功能的实现:加权排序原理 | ||||
1.每一个关键字去匹配标题、标签、摘要都会得到一个权重值,将这个权重值作为排序的依据; 2.如果输入多个关键字,则将每一个关键字匹配所得的权重值求积得到最终的权重值,这样做可以满足“多个关键字均被匹配的排在前面”,然后以这个最终的权重值来排序,具体情况如下。 | ||||
权重默认值(0.1) | ||||
情况一:输入两个关键字k1,k2,其中k1匹配一篇文章的摘要,k2也匹配同一篇文章的摘要 | ||||
匹配项 | 分值 | k1 | k2 | |
标题 | 4 | × | × | |
标签 | 3 | × | × | |
摘要 | 2 | √ | √ | |
权重值 | 0.1+0.1+2 = 2.2 | 0.1+0.1+2 = 2.2 | ||
最终权重值(求积) | 2.2×2.2 = 4.84 | |||
最终权重值(求和) | 2.2+2.2 = 4.4 | |||
情况二:输入两个关键字k1,k2,其中k1匹配一篇文章的标题、标签、摘要,k2不匹配任何一篇文章 | ||||
匹配项 | 分值 | k1 | k2 | |
标题 | 4 | √ | × | |
标签 | 3 | √ | × | |
摘要 | 2 | √ | × | |
权重值 | 4+3+2 = 9 | 0.1+0.1+0.1 = 0.3 | ||
最终权重值(求积) | 9×0.3 = 2.7 | |||
最终权重值(求和) | 9+0.3 = 9.3 | |||
情况三:输入两个关键字k1,k2,其中k1匹配一篇文章的标题、标签、摘要,k2匹配同一篇文章的摘要 | ||||
匹配项 | 分值 | k1 | k2 | |
标题 | 4 | √ | × | |
标签 | 3 | √ | × | |
摘要 | 2 | √ | √ | |
权重值 | 4+3+2 = 9 | 0.1+0.1+2 = 2.2 | ||
最终权重值(求积) | 9×2.2 = 19.8 | |||
最终权重值(求和) | 9+2.2 = 11.2 |
-
SELECT
-
1 * (
-
document.w10 + document.w20 + document.w30
-
) * (
-
document.w11 + document.w21 + document.w31
-
)
AS w,
-
document.*
-
FROM
-
(
-
SELECT
-
0,
-
CASE
WHEN d.abstracts
LIKE
'%1%'
THEN
-
2
ELSE
0.1
-
END
AS w10,
-
CASE
WHEN d.tags
LIKE
'%1%'
THEN
-
3
ELSE
0.1
-
END
AS w20,
-
CASE
WHEN d.
NAME
LIKE
'%1%'
THEN
-
4
ELSE
0.1
-
END
AS w30,
-
CASE
WHEN d.abstracts
LIKE
'%2%'
THEN
-
2
ELSE
0.1
-
END
AS w11,
-
CASE
WHEN d.tags
LIKE
'%2%'
THEN
-
3
ELSE
0.1
-
END
AS w21,
-
CASE
WHEN d.
NAME
LIKE
'%2%'
THEN
-
4
ELSE
0.1
-
END
AS w31,
-
d.*
-
FROM
-
document d
-
)
document
LEFT
JOIN
document dd
-
ON document.id = dd.id
-
WHERE
-
dd.sts =
'normal'
-
AND
to_days(
now()) >=
to_days(dd.protect_time)
-
AND dd.create_time >=
'2014-07-10 00:00:00'
-
AND dd.create_time <=
'2014-09-23 09:13:56'
-
AND dd.category_id
LIKE
'0001%'
-
AND dd.p_id
LIKE
'%'
-
HAVING w >
0.09
-
ORDER
BY
-
w
DESC,
-
dd.create_time
DESC
原文:https://blog.csdn.net/xingxinglaile/article/details/39496755