postgresql - 按精确匹配 THEN 前缀匹配排序全文搜索

Question

如何确保我的全文搜索结果按完全匹配和 THEN 前缀匹配排序？

SELECT ticker, name, ts_rank(document, to_tsquery('english', 'MAT:*')) AS rank
FROM (
  SELECT *, setweight(to_tsvector('english', ticker), 'A') || setweight(to_tsvector('english', name), 'B') AS document
  FROM ( VALUES
     ('MATI-R' , 'MATICHON PCL.NVDR')
    ,('MATCH-R', 'MATCHING MAXIMIZE SLN. NVDR')
    ,('MATV'   , 'MATAV-CABLE SYS.MEDIA SPN.ADR 1:2 DEAD - DELIST.03/07/06')
    ,('MAT'    , 'MATISSE HOLDINGS DEAD - 03/10/06')
    ,('MAT'    , 'MATTEL')
  ) data (ticker,name)
) ss ORDER BY rank DESC

我尝试了关于 https://www.postgresql.org/docs/9.5/static/datatype-textsearch.html 的一些建议像 to_tsquery('english', 'MAT:A & MAT:*B') 但似乎没有一个给我我正在寻找的顺序。当前输出为

 ticker  |                           name                           |   rank   
---------+----------------------------------------------------------+----------
 MATI-R  | MATICHON PCL.NVDR                                        |  1.45903
 MATCH-R | MATCHING MAXIMIZE SLN. NVDR                              |  1.27665
 MATV    | MATAV-CABLE SYS.MEDIA SPN.ADR 1:2 DEAD - DELIST.03/07/06 |  1.09427
 MAT     | MATISSE HOLDINGS DEAD - 03/10/06                         | 0.851098
 MAT     | MATTEL                                                   | 0.851098

当我想要更像的东西时

 ticker  |                           name                           |   rank
---------+----------------------------------------------------------+----------
 MAT     | MATTEL                                                   | ??
 MAT     | MATISSE HOLDINGS DEAD - 03/10/06                         | ??
 MATCH-R | MATCHING MAXIMIZE SLN. NVDR                              | ??
 MATI-R  | MATICHON PCL.NVDR                                        | ??
 MATV    | MATAV-CABLE SYS.MEDIA SPN.ADR 1:2 DEAD - DELIST.03/07/06 | ??

Answer 1

使用LIKE 或ILIKE:

SELECT ticker, name, ts_rank(document, to_tsquery('english', 'MAT:*')) AS rank
FROM (
  SELECT *, setweight(to_tsvector('english', ticker), 'A') || setweight(to_tsvector('english', name), 'B') AS document
  FROM ( VALUES
     ('MATI-R' , 'MATICHON PCL.NVDR')
    ,('MATCH-R', 'MATCHING MAXIMIZE SLN. NVDR')
    ,('MATV'   , 'MATAV-CABLE SYS.MEDIA SPN.ADR 1:2 DEAD - DELIST.03/07/06')
    ,('MAT'    , 'MATISSE HOLDINGS DEAD - 03/10/06')
    ,('MAT'    , 'MATTEL')
  ) data (ticker,name)
) ss ORDER BY name LIKE concat('%', ticker, '%') desc, rank DESC

 ticker  |                           name                           |   rank   
---------+----------------------------------------------------------+----------
 MAT     | MATISSE HOLDINGS DEAD - 03/10/06                         | 0.851098
 MAT     | MATTEL                                                   | 0.851098
 MATI-R  | MATICHON PCL.NVDR                                        |  1.45903
 MATCH-R | MATCHING MAXIMIZE SLN. NVDR                              |  1.27665
 MATV    | MATAV-CABLE SYS.MEDIA SPN.ADR 1:2 DEAD - DELIST.03/07/06 |  1.09427
(5 rows)