MySQL съхранена процедура за намиране на най-близката съвпадаща скорост на повикване за телефонно обаждане

Имам два телефонни номера, a-party и b-party, за които искам да намеря най-близката съвпадаща тарифа за разговори.

Има таблица с тарифи за разговори, съдържаща различни номера на a и b страни и тарифи за минута.

Когато търсите процент на разговори, първо се намира най-специфичното съвпадение в a-party. Ако бъде намерено съвпадение на b-party, се използва скоростта от най-конкретното съвпадение на b-party. Ако не бъде намерено съвпадение на 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.

Вижте http://sqlfiddle.com/#!2/b402c/13 за работещ пример.

person Ross Smith II    schedule 04.10.2012
comment
Не бих казал, че това се поддава на разбираемост или добър дизайн, ръчното изброяване на всички тези опции за изявлението за случая е ужасно за създаване и още по-лошо за поддържане или отстраняване на неизправности, също много податливо на грешки поради човешкия елемент. - person bdx; 09.10.2012
comment
Наистина сте прави за примерния процент на обаждане от a-party: 6495631111 до b-party: 619234566, който е 0,12. Благодаря ви, че посочихте това. - person MalcolmDurling; 10.10.2012