MySQL 存储过程查找最接近匹配的电话通话费率

Question

我有两个电话号码，一个是甲方，一个是乙方，我想找到最匹配的通话费率。

有一个调用费率表，其中包含各种甲方和乙方号码以及每分钟的费率。

查找调用率时，首先会找到 a 方中最具体的匹配项。如果找到 b 方匹配，则使用最具体的 b 方匹配的费率。如果未找到与 a 方匹配的 b 方匹配项，则查看下一个最具体的 a 方匹配项以查看是否有 b 方匹配项。如果未找到匹配项，则必须返回单独的消息。

i.e 
call_rates
a-party     b-party    rate_per_min
6495631234  619234     0.10
6495631     6192       0.12
649         61923      0.09
649         61         0.16

对于上述通话费率，通话来自:

a-party: 6495631234 到 b-party: 619234567 将返回 rate_per_min: 0.10

a-party: 6495631111 到 b-party: 619234567 将返回 rate_per_min: 0.12

a-party: 6495631111 到 b-party: 611112345 将返回 rate_per_min: 0.16

a-party: 6495631111 到 b-party: 619234566 将返回 rate_per_min: 0.09

到目前为止，这就是我尝试的方法。这是一个非常粗略的轮廓。我在编写存储过程方面的经验有限。

我只是在寻找一些关于是否有更好的方法来解决这个问题的建议。调用率表将非常大，因此我可以想象使用双 for 循环将非常低效。

如有任何反馈，我们将不胜感激。

DELIMITER //
DROP PROCEDURE IF EXISTS `get_rate`;
CREATE PROCEDURE `get_rate` (a VARCHAR(45), b VARCHAR(45), OUT rate VARCHAR(45))
get_r:BEGIN
    DECLARE i, j INT;
    DECLARE match_string, result, temp_string VARCHAR(255);

    SET j = LENGTH(b);
    SET i = LENGTH(a);

    WHILE i > 0 DO
        SET temp_string = SUBSTRING(a,0,i);
        SET result = (SELECT * FROM call_rate_overrides WHERE a_party LIKE CONCAT(temp_string, '%'));
        WHILE j > 0 DO
            SET temp_string = SUBSTRING(b,0,j);
            SET match_string = (SELECT * FROM call_rate_overrides WHERE b_party LIKE CONCAT(temp_string,'%'));
            IF ISNOTNULL(match_string) THEN
                SET rate = match_string;
                LEAVE get_r;
            END IF;  
            SET j = j - 1;
        END WHILE;
    
        SET i = i - 1;
    END WHILE;
END //

Answer 1

最终解决问题是这样的:

SELECT     s.a_party, s.b_party, (
           SELECT  cr.rate_per_min
           FROM call_rate_overrides cr
           WHERE s.a_party LIKE CONCAT(cr.a_party, '%')
           AND s.b_party   LIKE CONCAT(cr.b_party,  '%')
           ORDER BY LENGTH(cr.a_party) + LENGTH(cr.b_party) DESC, cr.rate_per_min
           LIMIT 1
     ) as rate_per_min
FROM call_usages s 

其中调用用法是 a 方和 b 方的表。它根据匹配的长度对全长各方进行排序，从而匹配最长的数据库条目。