Хранимая процедура MySQL для поиска ближайшей подходящей скорости вызова для телефонного звонка

У меня есть два телефонных номера, a-party и b-party, для которых я хочу найти ближайшую совпадающую скорость вызова.

Существует таблица тарифов на звонки, содержащая различные номера a-party и b-party и тарифы за минуту.

При поиске скорости вызова сначала находится наиболее точное совпадение в a-стороне. Если найдено совпадение b-стороны, используется скорость наиболее конкретного совпадения b-стороны. Если совпадение b-стороны не найдено для a-стороны, просматривается следующее наиболее конкретное совпадение 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 //

mysql sql performance stored-procedures string-matching
person MalcolmDurling    schedule 04.10.2012    source источник

Ответы (2)


arrow_upward
2
arrow_downward

В итоге решил проблему так:

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. Он сопоставляет самую длинную запись базы данных для полных партий, упорядочивая их в зависимости от продолжительности совпадения.

person MalcolmDurling    schedule 09.10.2012
comment
К сожалению, этот запрос возвращает 0.16 для всех 4 совпадений. См. sqlfiddle.com/#!2/b402c/1 См. stackoverflow.com/a/12720493/1432614 и sqlfiddle.com/#!2/b402c/13 для ответа, дающего результат. - person Ross Smith II; 09.10.2012
comment
Ты прав. На данный момент он упорядочивает результаты внутреннего запроса по ASC, поэтому самое короткое совпадение, то есть 0,16, всегда находится вверху, поэтому возвращается. Я обновил ответ, чтобы заказать внутренний запрос по DESC, теперь выводится правильная скорость. Спасибо за указание на это. - person MalcolmDurling; 10.10.2012
comment
Малькольм, теперь твой запрос отлично работает и намного менее сложен, чем мой. Отличная работа! Единственное изменение, которое вы можете внести, это ORDER BY (LENGTH(cr.a_party) * 10) + (LENGTH(cr.b_party) * 9) DESC. Это придаст больший вес a_party, чем b_party. - person Ross Smith II; 10.10.2012

arrow_upward
0
arrow_downward

Этот запрос:

SELECT 
  s.a_party,
  s.b_party,
  cr.rate_per_min
FROM
    call_rate_overrides cr
INNER JOIN
    call_usages s ON 
        s.a_party LIKE CONCAT(cr.a_party, '%') AND
        s.b_party LIKE CONCAT(cr.b_party, '%')
INNER JOIN
(
  SELECT 
    s.a_party,
    s.b_party,
    MAX(LENGTH(cr.a_party) + LENGTH(cr.b_party)) as len
  FROM
    call_rate_overrides cr
  INNER JOIN
    call_usages s ON 
      s.a_party LIKE CONCAT(cr.a_party, '%') AND
      s.b_party LIKE CONCAT(cr.b_party, '%')
  GROUP BY
    s.a_party,
    s.b_party
) d ON
    d.a_party = s.a_party AND
    d.b_party = s.b_party AND
    d.len = LENGTH(cr.a_party) + LENGTH(cr.b_party)
ORDER BY
  d.a_party,
  d.b_party

возвращается

a_party     b_party     rate_per_min
6495631111  611112345   0.16
6495631111  619234566   0.12
6495631111  619234567   0.12
6495631234  619234567   0.10

который соответствует всем, кроме одного из результатов в вашем вопросе:

a-party: 6495631111 to b-party: 619234566 would return the rate_per_min: 0.09

Если вы посмотрите внимательно, то увидите, что правильный ответ действительно равен 0,12.

См. рабочий пример.

person Ross Smith II    schedule 04.10.2012
comment
Я бы не сказал, что это поддается понятности или хорошему дизайну, вручную перечисляя все эти параметры для оператора case, ужасно создавать и еще хуже поддерживать или устранять неполадки, а также очень подвержено ошибкам из-за человеческого фактора. - person bdx; 09.10.2012
comment
Вы действительно правы в отношении примера вызова a-party: 6495631111 на b-party: 619234566, равного 0,12. Спасибо, что указали на это. - person MalcolmDurling; 10.10.2012