2014.09.23 mysql 1本のsql文実装検索機能(重み付けソート)

 、      

 、   SQL  (    case when xx then x else x end  mysql   )

 
検索機能の実装:重み付けソート原理
 
1.各キーワードがタイトル、ラベル、要約に一致すると、重み値が得られ、この重み値をソートの根拠とする.2.複数のキーワードを入力した場合、各キーワードが一致した重み値を積上して最終的な重み値を得ることで、「複数のキーワードが一致しているものが上位にある」ことを満たし、この最終的な重み値でソートすることができます.具体的には、次のようになります.
 
ウェイトの既定値(0.1)
ケース1:2つのキーワードk 1,k 2を入力し、そのうちk 1は1つの文章の要約に一致し、k 2も同じ文章の要約に一致する
一致
スコア
k1
k2
見出し
4
×
×
タブ
3
×
×
サマリ
2
√
√
ウェイト値(Weight Value)
 
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
 
ケース2:2つのキーワードk 1,k 2を入力し、k 1は1つの文章のタイトル、ラベル、要約に一致し、k 2はいずれの文章にも一致しない
一致
スコア
k1
k2
見出し
4
√
×
タブ
3
√
×
サマリ
2
√
×
ウェイト値(Weight Value)
 
4+3+2 = 9
0.1+0.1+0.1 = 0.3
最終ウェイト値(積算)
 
9×0.3 = 2.7
最終ウェイト値(合計)
 
9+0.3 = 9.3
 
ケース3:2つのキーワードk 1,k 2を入力し、そのうちk 1は1つの文章のタイトル、ラベル、要約に一致し、k 2は同じ文章の要約に一致する
一致
スコア
k1
k2
見出し
4
√
×
タブ
3
√
×
サマリ
2
√
√
ウェイト値(Weight Value)
 
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