package cn.vsx.hamster.baseservice.utils;


//package com.qinsoft.test;
public class Levenshtein
{
    private int compare(String str, String target)
    {
        int d[][];              // 矩阵
        int n = str.length();
        int m = target.length();
        int i;                  // 遍历str的
        int j;                  // 遍历target的
        char ch1;               // str的
        char ch2;               // target的
        int temp;               // 记录相同字符,在某个矩阵位置值的增量,不是0就是1
        if (n == 0) { return m; }
        if (m == 0) { return n; }
        d = new int[n + 1][m + 1];
        for (i = 0; i <= n; i++)
        {                       // 初始化第一列
            d[i][0] = i;
        }

        for (j = 0; j <= m; j++)
        {                       // 初始化第一行
            d[0][j] = j;
        }

        for (i = 1; i <= n; i++)
        {                       // 遍历str
            ch1 = str.charAt(i - 1);
            // 去匹配target
            for (j = 1; j <= m; j++)
            {
                ch2 = target.charAt(j - 1);
                if (ch1 == ch2 || ch1 == ch2+32 || ch1+32 == ch2)
                {
                    temp = 0;
                } else
                {
                    temp = 1;
                }
                // 左边+1,上边+1, 左上角+temp取最小
                d[i][j] = min(d[i - 1][j] + 1, d[i][j - 1] + 1, d[i - 1][j - 1] + temp);
            }
        }
        return d[n][m];
    }

    private int min(int one, int two, int three)
    {
        return (one = one < two ? one : two) < three ? one : three;
    }

    /**
     * 获取两字符串的相似度
     */

    public float getSimilarityRatio(String str, String target)
    {
        return 1 - (float) compare(str, target) / Math.max(str.length(), target.length());
    }

    public static void main(String[] args)
    {
        Levenshtein lt = new Levenshtein();
        String str = "你好吗";
        String target = "你好";
        System.out.println("similarityRatio=" + lt.getSimilarityRatio(str, target));
    }
}

匹配度算法

简言：
我们在用Mysql进行模糊查询的时候，Mysql自己做不到按照匹配度去排序，那么要按匹配度排序的话，我们自己要自己对sql进行改造
正常模糊查询sql如下

运行sql可以看到，包含罗马的信息有很多，但是我只搜了罗马，而罗马这个城市排在20以后，找起来就比较麻烦了
按匹配度排序后的结果如下

按照匹配度排序后，罗马排在最上面，这才是合理的做法
解析：
a模糊搜索后，入参出参完全一致的 排在最上面，然后再按左精确排，最后再按全模糊排，当然，也可以再加一条(按右精确排序)

星座匹配度规则表如下：



获取匹配度代码如下：

    /** 星座匹配分值 */
    private static Map<String, String> constellationScoreMap = new HashMap<>();

    /**
     * 初始化星座匹配分数
     * 1水瓶座 2双鱼座 3白羊座 4金牛座 5双子座 6巨蟹座 7狮子座 8处女座 9天秤座 10天蝎座 11射手座 12摩羯座
     * 每个key以两星座编号用-分隔；分值分两个，第一个分数为前男后女时的匹配分数，第二个分数前女后男时的分数
     */
    static {
        //1水瓶座
        constellationScoreMap.put("1-1", "87,87");
        constellationScoreMap.put("1-2", "69,60");
        constellationScoreMap.put("1-3", "88,72");
        constellationScoreMap.put("1-4", "66,72");
        constellationScoreMap.put("1-5", "99,91");
        constellationScoreMap.put("1-6", "74,58");
        constellationScoreMap.put("1-7", "84,78");
        constellationScoreMap.put("1-8", "55,64");
        constellationScoreMap.put("1-9", "95,96");
        constellationScoreMap.put("1-10", "57,51");
        constellationScoreMap.put("1-11", "78,82");
        constellationScoreMap.put("1-12", "57,69");
        //2双鱼座
        constellationScoreMap.put("2-1", "60,69");
        constellationScoreMap.put("2-2", "88,88");
        constellationScoreMap.put("2-3", "79,71");
        constellationScoreMap.put("2-4", "81,78");
        constellationScoreMap.put("2-5", "48,46");
        constellationScoreMap.put("2-6", "97,93");
        constellationScoreMap.put("2-7", "62,61");
        constellationScoreMap.put("2-8", "84,65");
        constellationScoreMap.put("2-9", "64,74");
        constellationScoreMap.put("2-10", "92,99");
        constellationScoreMap.put("2-11", "44,54");
        constellationScoreMap.put("2-12", "77,82");
        //3白羊座
        constellationScoreMap.put("3-1", "72,88");
        constellationScoreMap.put("3-2", "71,79");
        constellationScoreMap.put("3-3", "90,90");
        constellationScoreMap.put("3-4", "68,75");
        constellationScoreMap.put("3-5", "79,82");
        constellationScoreMap.put("3-6", "52,47");
        constellationScoreMap.put("3-7", "97,94");
        constellationScoreMap.put("3-8", "61,65");
        constellationScoreMap.put("3-9", "85,85");
        constellationScoreMap.put("3-10", "60,70");
        constellationScoreMap.put("3-11", "92,99");
        constellationScoreMap.put("3-12", "43,58");
        //4金牛座
        constellationScoreMap.put("4-1", "41,66");
        constellationScoreMap.put("4-2", "78,81");
        constellationScoreMap.put("4-3", "75,68");
        constellationScoreMap.put("4-4", "88,88");
        constellationScoreMap.put("4-5", "76,72");
        constellationScoreMap.put("4-6", "82,75");
        constellationScoreMap.put("4-7", "56,45");
        constellationScoreMap.put("4-8", "91,97");
        constellationScoreMap.put("4-9", "74,57");
        constellationScoreMap.put("4-10", "80,78");
        constellationScoreMap.put("4-11", "70,61");
        constellationScoreMap.put("4-12", "97,93");
        //5双子座
        constellationScoreMap.put("5-1", "91,99");
        constellationScoreMap.put("5-2", "46,48");
        constellationScoreMap.put("5-3", "82,79");
        constellationScoreMap.put("5-4", "72,76");
        constellationScoreMap.put("5-5", "89,89");
        constellationScoreMap.put("5-6", "78,71");
        constellationScoreMap.put("5-7", "79,81");
        constellationScoreMap.put("5-8", "76,57");
        constellationScoreMap.put("5-9", "98,93");
        constellationScoreMap.put("5-10", "68,69");
        constellationScoreMap.put("5-11", "81,70");
        constellationScoreMap.put("5-12", "70,86");
        //6巨蟹座
        constellationScoreMap.put("6-1", "58,74");
        constellationScoreMap.put("6-2", "93,97");
        constellationScoreMap.put("6-3", "47,52");
        constellationScoreMap.put("6-4", "75,82");
        constellationScoreMap.put("6-5", "71,78");
        constellationScoreMap.put("6-6", "89,89");
        constellationScoreMap.put("6-7", "69,61");
        constellationScoreMap.put("6-8", "88,84");
        constellationScoreMap.put("6-9", "58,66");
        constellationScoreMap.put("6-10", "97,92");
        constellationScoreMap.put("6-11", "65,70");
        constellationScoreMap.put("6-12", "80,87");
        //7狮子座
        constellationScoreMap.put("7-1", "78,84");
        constellationScoreMap.put("7-2", "61,62");
        constellationScoreMap.put("7-3", "94,97");
        constellationScoreMap.put("7-4", "45,56");
        constellationScoreMap.put("7-5", "81,79");
        constellationScoreMap.put("7-6", "61,69");
        constellationScoreMap.put("7-7", "87,87");
        constellationScoreMap.put("7-8", "66,72");
        constellationScoreMap.put("7-9", "88,81");
        constellationScoreMap.put("7-10", "65,45");
        constellationScoreMap.put("7-11", "98,92");
        constellationScoreMap.put("7-12", "59,77");
        //8处女座
        constellationScoreMap.put("8-1", "64,55");
        constellationScoreMap.put("8-2", "65,84");
        constellationScoreMap.put("8-3", "65,61");
        constellationScoreMap.put("8-4", "97,91");
        constellationScoreMap.put("8-5", "57,76");
        constellationScoreMap.put("8-6", "84,88");
        constellationScoreMap.put("8-7", "72,66");
        constellationScoreMap.put("8-8", "89,89");
        constellationScoreMap.put("8-9", "77,48");
        constellationScoreMap.put("8-10", "84,81");
        constellationScoreMap.put("8-11", "58,72");
        constellationScoreMap.put("8-12", "92,95");
        //9天秤座
        constellationScoreMap.put("9-1", "96,95");
        constellationScoreMap.put("9-2", "74,64");
        constellationScoreMap.put("9-3", "85,85");
        constellationScoreMap.put("9-4", "57,74");
        constellationScoreMap.put("9-5", "93,98");
        constellationScoreMap.put("9-6", "66,58");
        constellationScoreMap.put("9-7", "81,88");
        constellationScoreMap.put("9-8", "48,77");
        constellationScoreMap.put("9-9", "90,90");
        constellationScoreMap.put("9-10", "73,71");
        constellationScoreMap.put("9-11", "86,80");
        constellationScoreMap.put("9-12", "51,47");
        //10天蝎座
        constellationScoreMap.put("10-1", "51,57");
        constellationScoreMap.put("10-2", "99,92");
        constellationScoreMap.put("10-3", "70,60");
        constellationScoreMap.put("10-4", "78,80");
        constellationScoreMap.put("10-5", "69,68");
        constellationScoreMap.put("10-6", "92,97");
        constellationScoreMap.put("10-7", "45,65");
        constellationScoreMap.put("10-8", "81,84");
        constellationScoreMap.put("10-9", "71,73");
        constellationScoreMap.put("10-10", "87,87");
        constellationScoreMap.put("10-11", "68,47");
        constellationScoreMap.put("10-12", "85,76");
        //11射手座
        constellationScoreMap.put("11-1", "82,78");
        constellationScoreMap.put("11-2", "54,44");
        constellationScoreMap.put("11-3", "99,92");
        constellationScoreMap.put("11-4", "61,70");
        constellationScoreMap.put("11-5", "86,81");
        constellationScoreMap.put("11-6", "70,65");
        constellationScoreMap.put("11-7", "92,98");
        constellationScoreMap.put("11-8", "72,58");
        constellationScoreMap.put("11-9", "80,86");
        constellationScoreMap.put("11-10", "47,68");
        constellationScoreMap.put("11-11", "89,89");
        constellationScoreMap.put("11-12", "64,75");
        //12摩羯座
        constellationScoreMap.put("12-1", "69,74");
        constellationScoreMap.put("12-2", "82,77");
        constellationScoreMap.put("12-3", "58,43");
        constellationScoreMap.put("12-4", "93,97");
        constellationScoreMap.put("12-5", "64,70");
        constellationScoreMap.put("12-6", "87,80");
        constellationScoreMap.put("12-7", "77,59");
        constellationScoreMap.put("12-8", "95,92");
        constellationScoreMap.put("12-9", "47,51");
        constellationScoreMap.put("12-10", "76,85");
        constellationScoreMap.put("12-11", "75,64");
        constellationScoreMap.put("12-12", "88,88");
    }

 

产品智能推介。找匹配度最高的记录。
 
select *  
from(select id,org_id,sum(  
(case when instr(product_type,'2')>0 then 1 else 0 end)  
+(case when interest>5 and interest<11 then 1 else 0 end)  
+(case when 2000 between amount_low and amount_high then 1 else 0 end)  
)  
as cnt from hideseek.OrgProduct group by id) as temp  
where cnt>0  
order by cnt desc,id asc  
 
 
 
//按照产品名称分组，每组取匹配度最高的记录出来
select * from (
select 
    *
from
    (select 
        p . *,
            logo.org_logo,
            sum((case
                when instr(product_type, '1') > 0 then 1
                else 0
            end) + (case
                when interest between 1 and 1 then 1
                else 0
            end) + (case
                when instalment between 2 and 12 then 1
                else 0
            end) + (case
                when mobile <= 0 then 1
                else 0
            end) + (case
                when e_business <= 0 then 1
                else 0
            end) + (case
                when housing_fund <= 0 then 1
                else 0
            end) + (case
                when social_insurance <= 0 then 1
                else 0
            end)) as matchingDegree
    from
        tradeplatform.OrgProduct p
    left join tradeplatform.OrgLogo logo ON p.id = logo.pro_id
    where
        p.plat_status = 1 and product_type = '1'
    group by id) as temp
where
    matchingDegree > 0
        and org_id not in ('juxinli')
 
order by matchingDegree desc 
) A GROUP BY product_name ORDER BY matchingDegree DESC