这两个函数可以返回查询文本的相似度和匹配的结果,使用场景非常足,先把文档翻译出来放在这里,以后再慢慢补充使用过程中的经验和玩法
Scoring
- 用法
zdb.score(tid) RETURNS real - 作用 返回当前对比列的得分
tutorial=#
SELECT zdb.score(ctid), *
FROM products
WHERE products ==> 'sports box'
ORDER BY score desc;
score | id | name | keywords | short_summary | long_description | price |
----------+----+----------+--------------------------------------+--------------------------------+-------------------------------------------------------------------------------------+-------+-
1.00079 | 4 | Box | {wooden,box,"negative space",square} | Just an empty box made of wood | A wooden container that will eventually rot away. Put stuff it in (but not a cat). | 17000 |
0.698622 | 2 | Baseball | {baseball,sports,round} | It's a baseball | Throw it at a person with a big wooden stick and hope they don't hit it | 1249 |
ctid 是pg系统里面的隐藏唯一id列,作为zdb.score的参数
zdb.score()不可以用于where但可以用于order by
实现where功能需要使用dsl.min_score()
SELECT * FROM (
SELECT zdb.score(ctid), *
FROM products WHERE products ==> 'sports box' )
x WHERE x.score > 1.0;
- 错误示范
# SELECT zdb.score(ctid), * FROM products
# WHERE products ==> 'sports box' AND zdb.score(ctid) > 1.0;
ERROR: zdb.score() can only be used as a target entry or as a sort
Highlighting
- 用法
zdb.highlight(tid, fieldname [, json_highlight_descriptor]) RETURNS text[] - 功能 返回带标注的重点结果,es的默认标注结果,第三个参数可以自定义highligh标注的方法
tutorial=#
SELECT
zdb.score(ctid),
zdb.highlight(ctid, 'long_description'),
long_description
FROM products
WHERE products ==> 'wooden person'
ORDER BY score desc;
score | highlight | long_description
----------+--------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------
0.882384 | {"Throw it at a <em>person</em> with a big <em>wooden</em> stick and hope they don't hit it"} | Throw it at a person with a big wooden stick and hope they don't hit it
0.224636 | {"A <em>wooden</em> container that will eventually rot away. Put stuff it in (but not a cat)."} | A wooden container that will eventually rot away. Put stuff it in (but not a cat).
