Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Сложный Like. Идентификация по подобию  [new]
_raddd
Guest
Доброго всем времини суток.
Есть такие 2-е таблички

Etalons
idEtalon ModelEtalon qlModelEtalon
1 MB-100 AB MB100AB
2 MB-100 AB CD MB100ABCD
3 MB-100 CD MB100CD
4 MB-10 MB10


Price
idPrice ModelPrice qlModelPrice
1 MB/100AB MB100AB
2 MB-100\@@@AB MB100AB
3 MB 10 AB MB10AB
4 MB-100 ABCD qwerty MB100ABCDQWERTY
5 MB(100) AB DE MB100ABDE
6 MB-100 qq MB100QQ
7 MB-10AB MB10AB
8 MB-100 MB100
9 MB@100ABDE MB100ABDE


требуется получить
idPrice idEtalon ModelPrice ModelEtalon
1 1 MB/100AB MB-100 AB
2 1 MB-100\@@@AB MB-100 AB
3 4 MB 10 AB MB-10
4 2 MB-100 ABCD qwerty MB-100 AB CD
5 1 MB(100) AB DE MB-100 AB
6 MB-100 qq
7 MB-10AB
8 MB-100
9 MB@100ABDE

т.е. ModelPrice соответствует ModelEtalon если
1. qlModelPrice = qlModelEtalon или qlModelEtalon полностью входит в qlModelPrice (с первого символа) - qlModelEtalon LIKE qlModelPrice + '%'
2. символу, с которого начинается различие в qlModelEtalon и qlModelPrice, в поле ModelPrice - предшествует НЕбуква и НЕцифра - т.е.: <пробел>, /, -, @ и т.д.

Вопрос:
Можно ли получить результирующую таблицу одним запросом, не используя UDF?
Если без UDF - неполучится, то как лучше реализовать?
Спасибо.
SELECT @@VERSION
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
Microsoft SQL Server  2000 - 8.00.194 (Intel X86) 
	Aug  6 2000 00:57:48 
	Copyright (c) 1988-2000 Microsoft Corporation
	Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)
--
DECLARE  @Price TABLE (idPrice int, ModelPrice varchar(100), qlModelPrice varchar(100))

INSERT INTO @Price
SELECT 1, 'MB/100AB', 'MB100AB' UNION ALL
SELECT 2, 'MB-100\@@@AB', 'MB100AB' UNION ALL 
SELECT 3, 'MB 10 AB', 'MB10AB' UNION ALL
SELECT 4, 'MB-100 ABCD qwerty', 'MB100ABCDQWERTY' UNION ALL
SELECT 5, 'MB(100) AB DE', 'MB100ABDE' UNION ALL
SELECT 6, 'MB-100 qq', 'MB100QQ' UNION ALL
SELECT 7, 'MB-10AB', 'MB10AB' UNION ALL
SELECT 8, 'MB-100', 'MB100' UNION ALL
SELECT 9, 'MB@100ABDE',	'MB100ABDE'

SELECT * FROM @Price

--
DECLARE  @Etalons TABLE (idEtalon int, ModelEtalon varchar(100), qlModelEtalon varchar(100))

INSERT INTO @Etalons
SELECT 1, 'MB-100 AB', 'MB100AB' UNION ALL
SELECT 2, 'MB-100 AB CD', 'MB100ABCD' UNION ALL 
SELECT 3, 'MB-100 CD', 'MB100CD' UNION ALL
SELECT 4, 'MB-10', 'MB10'

SELECT * FROM @Etalons

--
DECLARE  @Result TABLE (idPrice int, idEtalon int, ModelPrice varchar(100), ModelEtalon varchar(100))

SELECT 1, 1, 'MB/100AB', 'MB-100 AB' UNION ALL
SELECT 2, 1, 'MB-100\@@@AB', 'MB-100 AB' UNION ALL
SELECT 3, 4, 'MB 10 AB', 'MB-10' UNION ALL
SELECT 4, 2, 'MB-100 ABCD qwerty', 'MB-100 AB CD' UNION ALL
SELECT 5, 1, 'MB(100) AB DE', 'MB-100 AB' UNION ALL
SELECT 6, Null, 'MB-100 qq', Null UNION ALL
SELECT 7, Null, 'MB-10AB', Null UNION ALL
SELECT 8, Null, 'MB-100', Null UNION ALL
SELECT 9, Null, 'MB@100ABDE', Null UNION ALL

SELECT * FROM @Result
4 июн 09, 20:06    [7267350]     Ответить | Цитировать Сообщить модератору
 Re: Сложный Like. Идентификация по подобию  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Согласно условиям первого поста вроде бы должен работать такой запрос:
DECLARE  @Price TABLE (idPrice int, ModelPrice varchar(100), qlModelPrice varchar(100))

INSERT INTO @Price
SELECT 1, 'MB/100AB', 'MB100AB' UNION ALL
SELECT 2, 'MB-100\@@@AB', 'MB100AB' UNION ALL 
SELECT 3, 'MB 10 AB', 'MB10AB' UNION ALL
SELECT 4, 'MB-100 ABCD qwerty', 'MB100ABCDQWERTY' UNION ALL
SELECT 5, 'MB(100) AB DE', 'MB100ABDE' UNION ALL
SELECT 6, 'MB-100 qq', 'MB100QQ' UNION ALL
SELECT 7, 'MB-10AB', 'MB10AB' UNION ALL
SELECT 8, 'MB-100', 'MB100' UNION ALL
SELECT 9, 'MB@100ABDE',	'MB100ABDE'

SELECT * FROM @Price

--
DECLARE  @Etalons TABLE (idEtalon int, ModelEtalon varchar(100), qlModelEtalon varchar(100))

INSERT INTO @Etalons
SELECT 1, 'MB-100 AB', 'MB100AB' UNION ALL
SELECT 2, 'MB-100 AB CD', 'MB100ABCD' UNION ALL 
SELECT 3, 'MB-100 CD', 'MB100CD' UNION ALL
SELECT 4, 'MB-10', 'MB10'

SELECT * FROM @Etalons


SELECT P.idPrice, E.idEtalon, P.ModelPrice, E.ModelEtalon
FROM @Price P JOIN @Etalons E ON E.ModelEtalon LIKE P.ModelPrice+'%'
OR EXISTS
(
 SELECT *
 FROM master.dbo.spt_values V
 WHERE V.type='P' AND V.number<LEN(P.ModelPrice) AND V.number<LEN(E.ModelEtalon)
 AND
 (
     E.ModelEtalon LIKE LEFT(P.ModelPrice,V.number+1)+'[^0-9A-Za-zА-Яа-я]%' COLLATE Cyrillic_General_BIN
  OR P.ModelPrice LIKE LEFT(E.ModelEtalon,V.number+1)+'[^0-9A-Za-zА-Яа-я]%' COLLATE Cyrillic_General_BIN
 )
);
Но как из получившихся записей отобрать перечисленные в Вашем результате?
Ума не приложу...
Может, у Вас самого какие-то мысли появились по этому поводу?
4 июн 09, 21:28    [7267541]     Ответить | Цитировать Сообщить модератору
 Re: Сложный Like. Идентификация по подобию  [new]
_raddd
Guest
Уважаемый iap, большое спасибо за ответ.

я действительно упустил в условиях соответствия один момент
помимо

т.е. ModelPrice соответствует ModelEtalon если
1. qlModelPrice = qlModelEtalon или qlModelEtalon полностью входит в qlModelPrice (с первого символа) - qlModelEtalon LIKE qlModelPrice + '%'
2. символу, с которого начинается различие в qlModelEtalon и qlModelPrice, в поле ModelPrice - предшествует НЕбуква и НЕцифра - т.е.: <пробел>, /, -, @ и т.д.

есть
3. При соответствии нескольких ModelEtalon одному ModelPrice - нужно выбрать такой который имеет наибольшую длину qlModelEtalon

с учетом этого , я немного изменил Ваш запрос, и результат - практически тот что требуется получить ))
SELECT P.idPrice, E.idEtalon, P.ModelPrice, E.ModelEtalon
FROM @Price P JOIN @Etalons E ON P.qlModelPrice LIKE E.qlModelEtalon + '%'
AND EXISTS
(
 SELECT *
 FROM master.dbo.spt_values V
 WHERE V.type='P' AND V.number<LEN(P.ModelPrice) AND V.number<LEN(E.ModelEtalon)
 AND P.ModelPrice LIKE LEFT(E.ModelEtalon,V.number+1)+'[^0-9A-Za-zА-Яа-я]%' COLLATE Cyrillic_General_BIN
)
WHERE LEN(E.qlModelEtalon) =
(SELECT MAX(LEN(E.qlModelEtalon)) 
FROM @Price P1 JOIN @Etalons E ON P1.qlModelPrice LIKE E.qlModelEtalon + '%'
AND EXISTS
(
 SELECT *
 FROM master.dbo.spt_values V
 WHERE V.type='P' AND V.number<LEN(P1.ModelPrice) AND V.number<LEN(E.ModelEtalon)
 AND P1.ModelPrice LIKE LEFT(E.ModelEtalon,V.number+1)+'[^0-9A-Za-zА-Яа-я]%' COLLATE Cyrillic_General_BIN
)
WHERE P.idPrice = P1.idPrice)
idPrice idEtalon ModelPrice ModelEtalon qlModelPrice qlModelEtalon
1 1 MB/100AB MB-100 AB MB100AB MB100AB
2 1 MB-100\@@@AB MB-100 AB MB100AB MB100AB
3 4 MB 10 AB MB-10 MB10AB MB10
4 2 MB-100 ABCD qwerty MB-100 AB CD MB100ABCDQWERTY MB100ABCD
5 1 MB(100) AB DE MB-100 AB MB100ABDE MB100AB
6 4 MB-100 qq MB-10 MB100QQ MB10
7 4 MB-10AB MB-10 MB10AB MB10
8 4 MB-100 MB-10 MB100 MB10
9 1 MB@100ABDE MB-100 AB MB100ABDE MB100AB

т.е. - для элментов с idPrice с 6 по 9 - не выполняется условие 2:

2. символу, с которого начинается различие в qlModelEtalon и qlModelPrice, в поле ModelPrice - предшествует НЕбуква и НЕцифра - т.е.: <пробел>, /, -, @ и т.д.

....насколько я понимаю, это условие реализуется в подзапросе для EXISTS
но что там нужно изменить, - я что-то несоображу....
5 июн 09, 14:14    [7269953]     Ответить | Цитировать Сообщить модератору
 Re: Сложный Like. Идентификация по подобию  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
А версия Вашего сервера не засекречена?
_raddd
3. При соответствии нескольких ModelEtalon одному ModelPrice - нужно выбрать такой который имеет наибольшую длину qlModelEtalon
Итак, надо брать по одному ModelPrice и для него подбирать один ModelEtalon из всех подходящих по некоторому правилу?
Если так, то уточните: ModelEtalon подбирается по максимально длинному совпадению с ModelPrice или просто по своей максимальной длине?
5 июн 09, 14:24    [7270018]     Ответить | Цитировать Сообщить модератору
 Re: Сложный Like. Идентификация по подобию  [new]
_raddd
Guest
iap
А версия Вашего сервера не засекречена?
_raddd
3. При соответствии нескольких ModelEtalon одному ModelPrice - нужно выбрать такой который имеет наибольшую длину qlModelEtalon
Итак, надо брать по одному ModelPrice и для него подбирать один ModelEtalon из всех подходящих по некоторому правилу?
Если так, то уточните: ModelEtalon подбирается по максимально длинному совпадению с ModelPrice или просто по своей максимальной длине?

SELECT @@VERSION
----------------------------------------------------------------------------------------------------
Microsoft SQL Server  2000 - 8.00.194 (Intel X86) 
	Aug  6 2000 00:57:48 
	Copyright (c) 1988-2000 Microsoft Corporation
	Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)
в двух словах о полях которые участвуют в запросе
поля ModelEtalon , ModelPrice - это "человеческое" написание модели
поля qlModelEtalon и qlModelPrice - это теже поля, соответственно - ModelEtalon , ModelPrice из которых удалены все НЕцифры и НЕбуквы

соответственно для фиксированного ModelPrice , ModelEtalon выбирается такой что бы выполнялось:
1. qlModelEtalon полностью "попадал" в qlModelPrice - с точностью до позиций символов
Например
qlModelEtalon qlModelPrice
1234 12345 удовлетворяет п.1
1234 13245 НЕ удовлетворяет п.1
1234 012345 НЕ удовлетворяет п.1
1234 123 НЕ удовлетворяет п.1

2. символу, с которого начинается различие (первый символ (попорядку) которого нет в qlModelEtalon ) в qlModelEtalon и qlModelPrice, в поле ModelPrice - предшествует НЕбуква и НЕцифра
т.е. (в примере - первое различие qlModelEtalon и qlModelPrice - 5)
ModelPrice ModelEtalon qlModelEtalon qlModelPrice
12*%@34 5 12 34 1234 12345 удовлетворяет п.2
1234 5 12 34 1234 12345 удовлетворяет п.2
12*%@345 12 34 1234 12345 НЕ удовлетворяет п.2
12*%@3 ##45 12 34 1234 12345 НЕудовлетворяет п.2

3. Из всех эталонов которые удовлетворяют первым 2-м пунктам - в результат выбирается тот у которого длина qlModelEtalon - наибольшая
ModelPrice ModelEtalon qlModelEtalon qlModelPrice Len
12*%@34 5 12 12 12345 2
12*%@34 5 12---------------34 1234 12345 4
12*%@34 5 12 34 5 12345 12345 5

в этом примере будет выбран нижний эталон

по этому ответ на Ваш вопрос

ModelEtalon подбирается по максимально длинному совпадению с ModelPrice или просто по своей максимальной длине?

- ни первое , ни второе
для фиксированной модели - ModelPrice
нужно выбрать те эталоны - ModelEtalon которые удовлетворяют первым 2-м пунктам,
а затем , из них оставить тот длина преобразованной модели которого (qlModelPrice , НЕ ModelEtalon)- максимальна
Спасибо
5 июн 09, 15:43    [7270544]     Ответить | Цитировать Сообщить модератору
 Re: Сложный Like. Идентификация по подобию  [new]
_raddd
Guest
...гм, поправочка
_raddd

для фиксированной модели - ModelPrice
нужно выбрать те эталоны - ModelEtalon которые удовлетворяют первым 2-м пунктам,
а затем , из них оставить тот длина преобразованной модели которого (qlModelPrice , НЕ ModelEtalon)- максимальна

для фиксированной модели - ModelPrice
нужно выбрать те эталоны - ModelEtalon которые удовлетворяют первым 2-м пунктам,
а затем , из них оставить тот длина преобразованной модели которого (qlModelEtalon , НЕ ModelEtalon)- максимальна
5 июн 09, 15:59    [7270637]     Ответить | Цитировать Сообщить модератору
 Re: Сложный Like. Идентификация по подобию  [new]
_raddd
Guest
...я про UDF сразу начал потому, что изначально сделал эту идентификацию через них и в целом всё работало нормально (на тестовых данных: Etalons ~40000 записей и Price~30000 записей) - запрос выполнялся порядка ~1 мин.
но сейчас в реальной БД (Etalons ~70000 записей и Price~120000 записей - и из них реально запросом обрабатываются не больше 40000) - просто какой капец...
влучшем случае мин.30 , а стандартно - запрос просто вылетает...
тестировалось - на угрюмом Celerone 2,2 c 128 памяти
рабочая - на 4-ом Pentium c гигом памяти
и там и там Win XP и соответственно - MSDE
...разве что в рабочем варианте - висят 4-6 юзеров, время от времени "тревожащих" базу )))
тем большее недоумение вызывает такой рост затрат времени при сравнительно не большом увеличении количества данных...
вот как задача решалась через функции
CREATE       FUNCTION dbo.F1
	 (@ИсхСтрока nvarchar(500))
RETURNS nvarchar(500)
AS
BEGIN
   -- Результат функции 
   DECLARE @Result nvarchar(500)
   SET @Result = REPLACE(@ИсхСтрока, '!', ' ')
   SET @Result = REPLACE(@Result, '"', ' ')
   SET @Result = REPLACE(@Result, ':', ' ')
   SET @Result = REPLACE(@Result, '-', ' ')
   SET @Result = REPLACE(@Result, '+', ' ')
   SET @Result = REPLACE(@Result, '|', ' ')
   SET @Result = REPLACE(@Result, '\', ' ')
   SET @Result = REPLACE(@Result, '/', ' ')
   SET @Result = REPLACE(@Result, '.', ' ')
   SET @Result = REPLACE(@Result, ',', ' ')
   SET @Result = REPLACE(@Result, '(', ' ')
   SET @Result = REPLACE(@Result, ')', ' ')
   SET @Result = REPLACE(@Result, '{', ' ')
   SET @Result = REPLACE(@Result, '}', ' ')
   SET @Result = REPLACE(@Result, '[', ' ')
   SET @Result = REPLACE(@Result, ']', ' ')
   SET @Result = REPLACE(@Result, '@', ' ')
   -- Возврат результата
   RETURN @Result
END
CREATE         FUNCTION dbo.F2
	 (@ИсхСтрока nvarchar(500))
RETURNS nvarchar(500)
AS
BEGIN
   -- Результат функции 
   DECLARE @Result nvarchar(500)
   DECLARE @Pos int
   DECLARE @Ch nvarchar(1)
   DECLARE @ШаблонНЕБуквНЕЦифр As nvarchar(50) 
   SET @Result = ''
   SET @Pos = 1
   SET @ШаблонНЕБуквНЕЦифр = '%' 
   WHILE @Pos <= LEN(@ИсхСтрока)
   BEGIN
     SET @Ch = SUBSTRING(@ИсхСтрока, @Pos, 1)
     IF (@Ch LIKE '[A-Z]')OR(@Ch LIKE '[a-z]')OR(@Ch LIKE '[А-Я]')OR(@Ch LIKE '[а-я]')OR(@Ch LIKE '[0-9]')
     BEGIN
       SET @Ch = UPPER(@Ch)
       -- Замена русских.букв, сходных с англ. 
       SELECT @Result = @Result + CASE 
                                    WHEN (@Ch='E')OR(@Ch='Е')OR(@Ch='Ё') THEN '[ЕEЁ]'
                                    WHEN (@Ch='I')OR(@Ch='І')OR(@Ch='Ї') THEN '[ІIЇ]'
                                    WHEN (@Ch='T')OR(@Ch='Т') THEN '[ТT]'
                                    WHEN (@Ch='O')OR(@Ch='О') THEN '[ОO]'
                                    WHEN (@Ch='P')OR(@Ch='Р') THEN '[РP]'
                                    WHEN (@Ch='A')OR(@Ch='А') THEN '[AА]'
                                    WHEN (@Ch='H')OR(@Ch='Н') THEN '[НH]'
                                    WHEN (@Ch='K')OR(@Ch='К') THEN '[КK]'
                                    WHEN (@Ch='X')OR(@Ch='Х') THEN '[ХX]'
                                    WHEN (@Ch='C')OR(@Ch='С') THEN '[СC]'
                                    WHEN (@Ch='B')OR(@Ch='В') THEN '[ВB]'
                                    WHEN (@Ch='M')OR(@Ch='М') THEN '[МM]'
                                    ELSE @Ch
                                  END
     END
     ELSE
       SET @Result = @Result + @ШаблонНЕБуквНЕЦифр
     SET @Pos = @Pos + 1 
   END
   -- Возврат результата
   RETURN @Result
END
DECLARE  @Price TABLE (idPrice int, ModelPrice varchar(100), qlModelPrice varchar(100))

INSERT INTO @Price
SELECT 1, 'MB/100AB', 'MB100AB' UNION ALL
SELECT 2, 'MB-100\@@@AB', 'MB100AB' UNION ALL 
SELECT 3, 'MB 10 AB', 'MB10AB' UNION ALL
SELECT 4, 'MB-100 ABCD qwerty', 'MB100ABCDQWERTY' UNION ALL
SELECT 5, 'MB(100) AB DE', 'MB100ABDE' UNION ALL
SELECT 6, 'MB-100 qq', 'MB100QQ' UNION ALL
SELECT 7, 'MB-10AB', 'MB10AB' UNION ALL
SELECT 8, 'MB-100', 'MB100' UNION ALL
SELECT 9, 'MB@100ABDE',	'MB100ABDE'

--SELECT * FROM @Price

--
DECLARE  @Etalons TABLE (idEtalon int, ModelEtalon varchar(100), qlModelEtalon varchar(100))

INSERT INTO @Etalons
SELECT 1, 'MB-100 AB', 'MB100AB' UNION ALL
SELECT 2, 'MB-100 AB CD', 'MB100ABCD' UNION ALL 
SELECT 3, 'MB-100 CD', 'MB100CD' UNION ALL
SELECT 4, 'MB-10', 'MB10'

--SELECT * FROM @Etalons

--
DECLARE  @Result TABLE (idPrice int, idEtalon int, ModelPrice varchar(100), ModelEtalon varchar(100))

INSERT INTO @Result
SELECT 1, 1, 'MB/100AB', 'MB-100 AB' UNION ALL
SELECT 2, 1, 'MB-100\@@@AB', 'MB-100 AB' UNION ALL
SELECT 3, 4, 'MB 10 AB', 'MB-10' UNION ALL
SELECT 4, 2, 'MB-100 ABCD qwerty', 'MB-100 AB CD' UNION ALL
SELECT 5, 1, 'MB(100) AB DE', 'MB-100 AB' UNION ALL
SELECT 6, Null, 'MB-100 qq', Null UNION ALL
SELECT 7, Null, 'MB-10AB', Null UNION ALL
SELECT 8, Null, 'MB-100', Null UNION ALL
SELECT 9, Null, 'MB@100ABDE', Null

SELECT idPrice, idEtalon, ModelPrice, ModelEtalon, dbo.F1(ModelPrice), dbo.F2(ModelEtalon) FROM @Result

--

SELECT     P.idPrice, E.idEtalon, P.ModelPrice, E.ModelEtalon, dbo.F1(P.ModelPrice) AS F1_ModelPrice, dbo.F2(E.ModelEtalon) AS F2_ModelEtalon
FROM                  @Price P
LEFT JOIN             @Etalons E  
  ON     P.qlModelPrice LIKE E.qlModelEtalon + '%' 
    AND  dbo.F1(P.ModelPrice) + ' ' LIKE dbo.F2(E.ModelEtalon) + ' %'
WHERE     (LEN(E.qlModelEtalon) =
                           (SELECT     MAX(LEN(E.qlModelEtalon))
                            FROM                  @Price P1
                            INNER JOIN             @Etalons E  
                              ON     P.qlModelPrice LIKE E.qlModelEtalon + '%' 
                                AND  dbo.F1(P.ModelPrice) + ' ' LIKE dbo.F2(E.ModelEtalon) + ' %'
                            WHERE      P1.idPrice = P.idPrice))
OR E.qlModelEtalon IS NULL
Реально ли что то ускорить в функциях?
Спасибо
5 июн 09, 20:25    [7271737]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить