Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Эфективный подход к обработке DEF кода  [new]
Haz
Member

Откуда:
Сообщений: 7
Кратко о DEF кодах.
Телефонный план нумерации — система, позволяющая пользователям телефонов совершать и принимать междугородные и международные телефонные звонки. Код зоны нумерации (называемый ABC для географически определяемой зоны нумерации или DEF — для географически не определяемой зоны нумерации) — 3 десятичных знака для Российской Федерации — та часть телефонного номера, которая указывает междугородный узел связи
Таблица DEF кодов имеет следующий вид (количество записей 60 тыс):
создан NONCLASTERED INDEX по столбцам First,From,To

Пример данных в таблице DEF:
First From To Firma Oblast
914 1200000 1499999 СИБИНТЕРТЕЛЕКОМ Читинская область
914 1500000 2199999 Мобильные ТелеСистемы Хабаровский край
914 2200000 3099999 Мобильные ТелеСистемы Республика Саха (Якутия)
914 3100000 3199999 Мобильные ТелеСистемы Хабаровский край

Свойства таблицы
Имя Тип
First char(3)
From char(7)
To char(7)
Firma nvarchar(255)
Oblast nvarchar(255)

Есть большой список уникальных номеров телефонов несколько десятков тысяч
и скалярная функция DEF_GETOpisanie на входе получающая номер телефона,
на выходе выдающая описание оператора + региона.

CREATE FUNCTION [dbo].[DEF_GETOpisanie]
(
	@phone varchar(50)
)
RETURNS nvarchar(510)
AS
BEGIN
   DECLARE @DEF nvarchar(510)
   SET @DEF=''
   IF len(@phone)>=15 SET @DEF=''
   ELSE
     BEGIN
      SELECT @Phone=[Stat2].[dbo].Filter98(@phone)
      IF LEN(@Phone)=10 
        SELECT @DEF=[Firma]+' , '+[Oblast] FROM [Stat2].[dbo].[DEF] 
        WHERE [First]= SubString(@Phone,1,3)  and  [Second1]<=SubString(@Phone,4,7)  and  [Second2]>=SubString(@Phone,4,7)     
      ELSE Select @DEF=''  
    END  
RETURN @DEF
END


Стал вопрос оптимизации данной функции, есть ли более оптимальное решение чтобы проанализировать номер телефона
или изменить структуру таблицы с DEF кодами?
24 апр 15, 06:26    [17556909]     Ответить | Цитировать Сообщить модератору
 Re: Эфективный подход к обработке DEF кода  [new]
aleks2
Guest
Осподе, мало что скалярная функция, дык ишо и погано написанная скалярка.

CREATE FUNCTION [dbo].[DEF_GETOpisanie]
(
	@phone varchar(50)
)
RETURNS nvarchar(510)
AS
BEGIN
   IF len(@phone)>=15 return '';

   SELECT @Phone=[Stat2].[dbo].Filter98(@phone);
   IF LEN(@Phone)=10 
      return ( SELECT isnull( [Firma]+' , '+[Oblast] FROM [Stat2].[dbo].[DEF] , '' )
                     WHERE [First]= SubString(@Phone,1,3)   
                        and  [Second1]<=SubString(@Phone,4,7)  
                        and  [Second2]>=SubString(@Phone,4,7)     
                );

RETURN '';
END


Если ж у вас "список уникальных номеров телефонов несколько десятков тысяч" и вы колбасите его регулярно - лучше вообще отказаться от UDF.
24 апр 15, 06:43    [17556926]     Ответить | Цитировать Сообщить модератору
 Re: Эфективный подход к обработке DEF кода  [new]
Haz
Member

Откуда:
Сообщений: 7
Это актуальная версия фунции
CREATE FUNCTION [dbo].[DEF_GETOpisanie]
(
	@phone varchar(50)
)
RETURNS nvarchar(510)
AS
BEGIN
    --проверка длины номера (больше 15 в принципе не должно быть)
   IF len(@phone)>=15 return '';
    -- Функция обрезающая первые цифры номера если они равны 7 или 8
   SELECT @Phone=[Stat2].[dbo].Filter98(@phone)

   IF LEN(@Phone)=10 
      return ( SELECT isnull( [Firma]+' , '+[Oblast] FROM [dbo].[DEF] , '' )
                     WHERE [First]= SubString(@Phone,1,3)   
                        and   [From]<=SubString(@Phone,4,7)  
                        and   [To]>=SubString(@Phone,4,7)     
                );

RETURN '';
END
24 апр 15, 07:18    [17556959]     Ответить | Цитировать Сообщить модератору
 Re: Эфективный подход к обработке DEF кода  [new]
Добрый Э - Эх
Guest
Haz,

джойн по лайку "большой" таблицы номеров на таблицу кодов + взятие для каждого номера строки с максимальной длиной префикса кода (top 1 with ties) - не рассматривался?
24 апр 15, 07:25    [17556973]     Ответить | Цитировать Сообщить модератору
 Re: Эфективный подход к обработке DEF кода  [new]
Добрый Э - Эх
Guest
Haz,

ещё как вариант - многократный лефт джойн таблицы кодов на таблицу номеров по сабстрингу. в селект-листе выбирать данные из кодов по coalesce() от самого длинного к самому короткому коду
24 апр 15, 07:29    [17556975]     Ответить | Цитировать Сообщить модератору
 Re: Эфективный подход к обработке DEF кода  [new]
aleks2
Guest
Haz
Это актуальная версия фунции
CREATE FUNCTION [dbo].[DEF_GETOpisanie]
(
	@phone varchar(50)
)
RETURNS nvarchar(510)
AS
BEGIN
    --проверка длины номера (больше 15 в принципе не должно быть)
   IF len(@phone)>=15 return '';
    -- Функция обрезающая первые цифры номера если они равны 7 или 8
   SELECT @Phone=[Stat2].[dbo].Filter98(@phone)

   IF LEN(@Phone)=10 
      return ( SELECT isnull( [Firma]+' , '+[Oblast] FROM [dbo].[DEF] , '' )
                     WHERE [First]= SubString(@Phone,1,3)   
                        and   [From]<=SubString(@Phone,4,7)  
                        and   [To]>=SubString(@Phone,4,7)     
                );

RETURN '';
END


Если вы поведаете нам содержимое =[Stat2].[dbo].Filter98(@phone), то вся эта хрень вполне может быть переписана в inline-функцию.
24 апр 15, 07:44    [17556990]     Ответить | Цитировать Сообщить модератору
 Re: Эфективный подход к обработке DEF кода  [new]
Haz
Member

Откуда:
Сообщений: 7
 SELECT @filteredphone=PATINDEX('[78]%',@phone)=1 THEN STUFF(@phone,1,PATINDEX('[78]%',@phone),'')
24 апр 15, 08:24    [17557057]     Ответить | Цитировать Сообщить модератору
 Re: Эфективный подход к обработке DEF кода  [new]
Haz
Member

Откуда:
Сообщений: 7
aleks2,
ALTER FUNCTION [dbo].[Filter98]
(
	@phone varchar(50)
)
RETURNS varchar(50)
AS
BEGIN
   DECLARE @filteredphone varchar(50)
   IF len(@phone)>=15 SET @filteredphone=@phone
   ELSE
    SELECT @filteredphone=
             CASE 
                WHEN len(@phone)>11 and PATINDEX('78%',@phone)=1 THEN STUFF(@phone,1,2,'')
                WHEN len(@phone)>10 and PATINDEX('[78]%',@phone)=1 THEN STUFF(@phone,1,PATINDEX('[78]%',@phone),'')
             ELSE @phone
            END 
	RETURN @filteredphone
END
24 апр 15, 08:28    [17557066]     Ответить | Цитировать Сообщить модератору
 Re: Эфективный подход к обработке DEF кода  [new]
virtuOS
Member

Откуда: большая деревня
Сообщений: 265
Таблица DEF это же справочник. Сделайте два столбца, объединив First + From и First + To, тогда избавитесь от лишних вычислений с телефоном.

А что будете делать с телефона ми в международном формате 1084951234567?
24 апр 15, 10:43    [17557643]     Ответить | Цитировать Сообщить модератору
 Re: Эфективный подход к обработке DEF кода  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31420
virtuOS
Таблица DEF это же справочник. Сделайте два столбца, объединив First + From и First + To, тогда избавитесь от лишних вычислений с телефоном.

А что будете делать с телефона ми в международном формате 1084951234567?
Это не международный формат
В международном формате это записывается 74951234567

А 1084951234567 - это порядок нажатия кнопочек для конкретной АТС, которая это понимает.
Haz
Функция обрезающая первые цифры номера если они равны 7 или 8
Лучше всё таки хранить номера в нормализованном виде, напимер, в международном 74951234567 или внутрироссийском 4951234567
Тогда не нужны будут такие функции, кроме как при сохранении данных.
24 апр 15, 10:49    [17557672]     Ответить | Цитировать Сообщить модератору
 Re: Эфективный подход к обработке DEF кода  [new]
Haz
Member

Откуда:
Сообщений: 7
virtuOS
Таблица DEF это же справочник. Сделайте два столбца, объединив First + From и First + To, тогда избавитесь от лишних вычислений с телефоном.


Спасибо за совет, намного все упростило.
24 апр 15, 13:50    [17558956]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить