Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Оптимизация join'а большой таблицы с собой же  [new]
K.T.V.
Member

Откуда: РнД
Сообщений: 41
Не могу решить задачу:
есть таблица клиентов и их телефонов. Нужно выбрать тех клиентов, у которых телефоны совпадают. Элементарная задачка, если бы не 380000 записей в этой таблице.
Для наглядности:
create table #tempphones (client_id int, clean_phone varchar(128)

В поле clean_phone содержатся только цифры, у одного клиента может быть 1 телефон, у другого 3. Поэтому искать нужно по вхождению. Выбрать нужно всех клиентов, у которых телефоны совпадают.
Самый логичный вариант запроса не отрабатывает даже за 2 часа:
delete from  c1
from #tempphones c1
where not exists (select 1 from #tempphones c2 
					where c2.Client_Id <> c1.Client_Id 
						and 
						(CHARINDEX(c1.clean_phone, c2.clean_phone) > 0 or CHARINDEX(c2.clean_phone, c1.clean_phone) > 0)
					)

Можно избавиться от повторного вызова CHARINDEX, вызывая его только для поиска меньшего телефона в большем. Но это тоже не помогает.
alter table #tempphones
add clean_phone_length tinyint

update  #tempphones
set clean_phone_length = len(clean_phone)

delete from  c1
from #tempphones c1
where not exists (select 1 from #tempphones c2 
			where c2.Client_Id <> c1.Client_Id 
				and  
				(case when c1.clean_phone_length > c2.clean_phone_length 
				then CHARINDEX(c2.clean_phone, c1.clean_phone)
				else CHARINDEX(c1.clean_phone, c2.clean_phone)
				end) > 0
			) 

Индексы не помогли ни в одном из вариантов:
create clustered index new1
on #tempphones (clean_phone asc)

create nonclustered index new2
on #tempphones (client_id asc, clean_phone_length asc)

Селект тоже не пашет:
select * from #tempphones c1
where  exists (select 1 from #tempphones c2 
					where c2.Client_Id <> c1.Client_Id 
						and 
						(CHARINDEX(c1.clean_phone, c2.clean_phone) > 0)
					)
union all
select * from #tempphones c1
where  exists (select 1 from #tempphones c2 
					where c2.Client_Id <> c1.Client_Id 
						and 
						(CHARINDEX(c2.clean_phone, c1.clean_phone) > 0)
					)

Версия сервера 2014.
Мои варианты закончились :(
4 апр 18, 15:01    [21311953]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация join'а большой таблицы с собой же  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20482
K.T.V.
Нужно выбрать тех клиентов, у которых телефоны совпадают.

K.T.V.
Самый логичный вариант запроса не отрабатывает даже за 2 часа:
delete from  c1
Ппц логика... надо выбрать? так давайте удалим...

K.T.V.
у одного клиента может быть 1 телефон, у другого 3. Поэтому искать нужно по вхождению

А сначала нормализовать данные - не?
4 апр 18, 15:13    [21311983]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация join'а большой таблицы с собой же  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
K.T.V.,

Сравнивать 1 к 3м телефонам ещё ладно, но 3 к одному просто CHARINDEX явно нельзя. Нормализовать хоть в "право" хоть вниз и потом уже искать, ну и удалять кого из них тоже надо определить
4 апр 18, 15:16    [21311994]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация join'а большой таблицы с собой же  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
K.T.V.,

WITH
pn AS (
SELECT
  [clean_phone]
FROM
  #tempphones
GROUP BY
  [clean_phone]
HAVING
  --COUNT( DISTINCT [client_id] ) > 1
  COUNT( * ) > 1
)
SELECT
  t.*
FROM
  pn
  INNER JOIN #tempphones t ON (
        t.[clean_phone] = pn.[clean_phone] )
4 апр 18, 15:16    [21311996]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация join'а большой таблицы с собой же  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Руслан Дамирович,

агонь! Только 3 телефона в одном поле
4 апр 18, 15:18    [21312001]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация join'а большой таблицы с собой же  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
TaPaK
Руслан Дамирович,
агонь! Только 3 телефона в одном поле

K.T.V.
В поле clean_phone содержатся только цифры, у одного клиента может быть 1 телефон, у другого 3

Ну, не очевидно, что ВСЕ ТРИ в одном поле :)
4 апр 18, 15:25    [21312024]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация join'а большой таблицы с собой же  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
K.T.V.,
IF OBJECT_ID( 'tempdb..#tempphones' ) IS NOT NULL
  DROP TABLE #tempphones
;
IF OBJECT_ID( 'tempdb..#phones' ) IS NOT NULL
  DROP TABLE #phones
;
CREATE TABLE #tempphones ( [client_id] INT, [clean_phone] VARCHAR(128) )
INSERT INTO #tempphones VALUES
( 1, '123456 12345678' ),
( 2, '123456 23456789' )
;
WITH
x AS (
SELECT
  [client_id],
  -- заменить пробел на символ разделителя телефонов
  [xml] = CONVERT( XML, '<i>' + REPLACE( [clean_phone], ' ', '</i><i>' ) + '</i>' )
FROM
  #tempphones
)
SELECT
  [client_id],
  [phone] = n.value( 'text()[1]', 'VARCHAR(10)' )
INTO
  #phones
FROM
  x
  CROSS APPLY x.[xml].[nodes]( '//i' ) t( n )
;
WITH
pn AS (
SELECT
  [phone]
FROM
  #phones
GROUP BY
  [phone]
HAVING
  --COUNT( DISTINCT [client_id] ) > 1
  COUNT( * ) > 1
)
SELECT
  t.*
FROM
  pn
  INNER JOIN #phones t ON (
        t.[phone] = pn.[phone] )
4 апр 18, 15:32    [21312060]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация join'а большой таблицы с собой же  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
K.T.V.,

вообще в простом случае достаточно, хотя может и упускаю чего...

SELECT * 
FROM #tempphones c1
WHERE EXISTS (
		SELECT  1 
		FROM #tempphones c2 
		WHERE  
			c2.Client_Id <> c1.Client_Id	AND
			c2.clean_phone	LIKE '%' + c1.clean_phone '%' COLLATE Latin1_General_100_BIN2
	      )		
4 апр 18, 15:37    [21312073]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация join'а большой таблицы с собой же  [new]
K.T.V.
Member

Откуда: РнД
Сообщений: 41
TaPaK, спасибо, но нет. like, тем более с % перед тем что ищем, никак не ускоряет выборку
4 апр 18, 16:07    [21312198]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация join'а большой таблицы с собой же  [new]
K.T.V.
Member

Откуда: РнД
Сообщений: 41
К сожалению, данные нормализовать нельзя, только работать над проблемой as is. Нет никакого универсального разделителя телефонов, поэтому нельзя определить, 3 штуки их в поле или 1. Хорошо что договорились, что ищем только вхождение 1го в 3, а не наоборот :)
4 апр 18, 16:09    [21312211]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация join'а большой таблицы с собой же  [new]
K.T.V.
Member

Откуда: РнД
Сообщений: 41
Руслан Дамирович, я просто убираю из поля телефон все посторонние символы кроме цифр. Поэтому в вашем варианте это будут телефоны
( 1, '12345612345678' ),
( 2, '12345623456789' )
что значительно усложняет задачу
4 апр 18, 16:13    [21312240]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация join'а большой таблицы с собой же  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
K.T.V.
TaPaK, спасибо, но нет. like, тем более с % перед тем что ищем, никак не ускоряет выборку

а я вот совсем не уверен что CHARINDEX быстрее LIKE +BIN в вашем случае
4 апр 18, 16:15    [21312249]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация join'а большой таблицы с собой же  [new]
K.T.V.
Member

Откуда: РнД
Сообщений: 41
TaPaK, ну даже не вдаваясь в абстрактные рассуждения, практика показала, что like все равно выполняется недопустимо долго (больше часа)
4 апр 18, 16:58    [21312422]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация join'а большой таблицы с собой же  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
K.T.V.
TaPaK, ну даже не вдаваясь в абстрактные рассуждения, практика показала, что like все равно выполняется недопустимо долго (больше часа)

я так понимаю вы думаете что есть волшебный рашпиль который насравнивает вам значения?

А как вы собираетесь жить когда указано 2 телефона? а + b и у другого b + a? Ну и ещё с десяток таких вариантов
4 апр 18, 17:01    [21312430]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация join'а большой таблицы с собой же  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
K.T.V.
Руслан Дамирович, я просто убираю из поля телефон все посторонние символы кроме цифр. Поэтому в вашем варианте это будут телефоны
( 1, '12345612345678' ),
( 2, '12345623456789' )
что значительно усложняет задачу

Ну так облегчите себе задачу, и сначала разделите данные, а потом зачистите.
А то прям комсомолом попахивает - стоя в гамаке... лайкаете.
4 апр 18, 17:31    [21312545]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация join'а большой таблицы с собой же  [new]
invm
Member

Откуда: Москва
Сообщений: 9265
K.T.V.
К сожалению, данные нормализовать нельзя, только работать над проблемой as is. Нет никакого универсального разделителя телефонов, поэтому нельзя определить, 3 штуки их в поле или 1.
Имеем три номера телефона - 1111, 22222 и 333333
Телефоны клиента1, согласно вашим условиям - 111122222
клиента2 - 22222333333

Как и чего будете искать?
4 апр 18, 17:49    [21312618]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация join'а большой таблицы с собой же  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7641
K.T.V.,

ищИте только точные совпадения, остальное - это мусор, а не номера.
4 апр 18, 17:59    [21312654]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация join'а большой таблицы с собой же  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Владислав Колосов
K.T.V.,

ищИте только точные совпадения, остальное - это мусор, а не номера.

действительно, просто удалите все записи где длинна больше стандартного нормера и всё
4 апр 18, 18:03    [21312665]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация join'а большой таблицы с собой же  [new]
K.T.V.
Member

Откуда: РнД
Сообщений: 41
ну это не я придумываю идиотские условия.
Нужно, чтобы работал вариант найти телефон 55555 в телефоне 1111111555552222. Остальными пренебрегаем.
4 апр 18, 18:04    [21312669]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация join'а большой таблицы с собой же  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36889
K.T.V.
ну это не я придумываю идиотские условия.
Нужно, чтобы работал вариант найти телефон 55555 в телефоне 1111111555552222. Остальными пренебрегаем.
Заодно 55555 будет искаться в паре 44555 и 55666
4 апр 18, 18:09    [21312679]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация join'а большой таблицы с собой же  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
K.T.V.
ну это не я придумываю идиотские условия.
Нужно, чтобы работал вариант найти телефон 55555 в телефоне 1111111555552222. Остальными пренебрегаем.

Зато в ваших силах обосновать, что условия идиотские, и потому невыполнимые.
Это может повлечь за собой увольнение:
1. а оно вам нужно - дальше хуже;
2. развивайте навыки дипломатии;
3. вы лукавите, и условия совсем не такие, как вы их "не придумали".
4 апр 18, 18:17    [21312699]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация join'а большой таблицы с собой же  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36889
K.T.V.,

Вот. к примеру, в этих двух склееных строказх (в каждой по 3 телефонных номера) есть совпадения по-вашему:

123456712345681234569 и 12345234561234560

?
4 апр 18, 18:20    [21312704]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация join'а большой таблицы с собой же  [new]
K.T.V.
Member

Откуда: РнД
Сообщений: 41
Гавриленко Сергей Алексеевич, нет, потому что один телефон клиента полностью должен входить в телефон другого клиента
4 апр 18, 18:35    [21312744]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация join'а большой таблицы с собой же  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36889
K.T.V.
Гавриленко Сергей Алексеевич, нет, потому что один телефон клиента полностью должен входить в телефон другого клиента
А где здесь телефоны клиентов? Здесь две строки, к которым не прилагается алгоритм разбития из на подстроки (телефоны), чтобы потом сравнить хотя бы с неразбитой второй строкой.
4 апр 18, 18:38    [21312756]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация join'а большой таблицы с собой же  [new]
blonduser
Member

Откуда:
Сообщений: 132
K.T.V.,
За какое время вы планируете перебрать 144 лярда вариантов?
4 апр 18, 18:44    [21312766]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить