UPDATE по совпадению в подстроке

Victor
Дата: 14.03.2001 15:45:09
Существует такая проблема

Есть две таблицы

Calls (DialedNumber,Duration,RateId,Cost) - телефонные звонки (50000-1млн записей)

Например
70957777777 60 5 NULL
78122222222 60 6 NULL
78123222222 60 6 NULL

Rates (RateId,DestinationCode,Rate) - тарифы (>200000 записей)
Например
5 7095 0,5
6 7095 0,5
6 7812 0,5 - действителен для всех звонков вида 7812xxx кроме 78122
6 78122 0,1

таким образом звонки должны тарифицироваться согласно тарифу для кода который
наиболее совпадает с набранным номером т.е. 7812222222 будет по тарифу 0,1 ,а
7812322222 по тарифц 0,5.

Собственоо вопрос - как это лучше всего реализовать на SQL Server?

Пробовал три способа
1. Цикл по длинне кода в таблице rates - оказался самый быстрый
DECLARE @codelen tinyint
SELECT @codelen=10
WHILE @codelen>0
BEGIN
UPDATE calls
SET cost=duration*Rate
WHERE substring(DialedNumber,1,datalength(DestinationCode))=DestinationCode
and datalength(destinationcode)=@codelen
and calls.rateid=rates.rateid
and cost is null
SELECT @codelen=@codelen-1
END
2. Один запрос - очень медленно
UPDATE calls
SET cost=duration*rates.rate
from rates
WHERE rates.Destinationcode=(select max(destinationcode) from rates r1 where dialednumber like code+'%' and rates.rateid=r1.rateid)
and rates.rateid=calls.rateid

3 Перебор звонков в курсоре - еще медленнее.

Ести ли у кого идеи как это еще можно реализовать поменяв структуру базы и/или запросы?
Genady
Дата: 14.03.2001 16:33:44
Если есть возможность, то менять нужно структуру базы, табличка Calls у Вас явно не укладывается даже в 1-ю НФ, поскольку поле DialedNumber содержит составные данные а именно - Код звонка и Номер Звонка. Пэтому и такие трудности с выборкой.
Genady
Дата: 14.03.2001 16:37:48
Недообъяснял

Я имел в виду то, что вам нужно как минимум разделить столбец DialedNumber на два, хотя подозреваю что вся схема данных непродумана и несоответствует предметной области. Если Ваша система сейчас только разрабатывается я бы посоветовал серьезно взяться за разработку схемы данных. Почитайте Дейта.
Dmitry
Дата: 14.03.2001 17:15:06
Согласенс Genady. Добавлю еще что такая постановка не верна в принципе. Например, в Вашем примере был звонок 78123222222. Т.е. (7812)3222222. А как будет выглядеть номер звонка в этот же город с местным телефоном 2222222? (7812)2222222. А в Вашем представлении - 78122222222, что ничем не отличается от (781)22222222.
SergSuper
Дата: 14.03.2001 17:30:13
Я так понимаю что номера приходят без разделения их на код города и собственно номер. В принципе можно их разделять при вставке. Но наверное не стоит, т.к. таблица меняется оперативно и тормозить тут нельзя.

Можно таблицу Rates записывать примерно так:
5 7095% 0,5
6 7095% 0,5
6 7812[^2]% 0,5
6 78122% 0,1
и тогда всё будет делаться одним запросом ... WHERE DialedNumber like DestinationCode ...
правда замучиешься вести таблицу Rates

Я бы остановился на твоём первом варианте, только написал бы WHERE DialedNumber like DestinationCode+'%' and datalength(DestinationCode)=@codelen... но это дело вкуса

С приветом Сергей
Victor
Дата: 15.03.2001 15:26:49
Сергей,
вы правы - номера приходят с АТС в неразделенном виде.

Генадий,
Идея разделять номер на код+остаток была, но она не годится в силу того что для разных тарифных планов могут использоватся разные набор кодов, например,

Набраны
781223456 -для клиента с планом N1
781223456 -для клиента с планом N2

В Плане 1 задан только
код 7 -$0,2
В Плане 2 заданы коды
7812 - 0,10
78122 -0,05
То есть получаем что один и тот же номер должен разбится в одном случае как (7)81223456 а в другом как (78122)3456.
таким образом мы не можем заранее сказать что номер состоит из кода и остатка не посмотрев на тарифный план.

Дмитрий,
не совсем понял ваш пример.
Genady
Дата: 15.03.2001 15:59:24
>Идея разделять номер на код+остаток была, но она не годится в силу того что для разных тарифных планов могут использоватся >разные набор кодов, например

Это не повод запихивать два атрибута в один, отношение многие ко многим никто не отменял
Анализ предметной области при правильном определении сущностей и их атрибутов спасут "отцов русской демократии"
Как я писал уже ранне нужно просто серьезно отнестись к проектированию схемы данных, начиная с концептуальной модели
да помогут вам ER диаграммы
Victor
Дата: 15.03.2001 16:54:29
>>Это не повод запихивать два атрибута в один, отношение многие ко многим никто не отменял.
В том-то все и дело, что это изначально один атрибут -номер "физически" набранный на телефоне.
Задача в том и состоит чтобы наиболее эффективным способом разделить этот номер на два атрибута- "значащий код" на основе которого звонку ставится в соответствие тариф и "балласт" -остаток номера.
victor
Дата: 15.03.2001 17:12:13
Может есть какие нибудь идеи по поводу модификации таблицы Rates?
Genady
Дата: 15.03.2001 17:20:34
Вобщем я тут посмотрел внимательно беру свои слова назад, правда не совсем, ошибка все таки у Вас есть, поскольку я не вижу связи Rates c Calls если бы RateID был PK, то все можно было бы сделать просто:

update Calls set Calls.Cost = Calls.Duration * Rates.Rate from Calls, Rates where Calls.RateID = Rates.RateID

В Вашем случае можно обойтись, но не советую, таким запросом:

update Calls set Calls.Cost = Calls.Duration * Rates.Rate from Calls, Rates
where Calls.RateID = Rates.RateID and Rates.DestinationCode = substring(Calls.DialingNumber, 1, len(Rates.DestinationCode))

Вобщем совет - определитесь с ключиками

P.S. Вопрос: а вычисляемое поле сознательно храните?