ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Оптимизация join'а большой таблицы с собой же
25 сообщений из 33, страница 1 из 2
  1  все
Оптимизация join'а большой таблицы с собой же
    #39625554
K.T.V.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
K.T.V.
Гость
Не могу решить задачу:
есть таблица клиентов и их телефонов. Нужно выбрать тех клиентов, у которых телефоны совпадают. Элементарная задачка, если бы не 380000 записей в этой таблице.
Для наглядности:
Код: sql
1.
create table #tempphones (client_id int, clean_phone varchar(128)


В поле clean_phone содержатся только цифры, у одного клиента может быть 1 телефон, у другого 3. Поэтому искать нужно по вхождению. Выбрать нужно всех клиентов, у которых телефоны совпадают.
Самый логичный вариант запроса не отрабатывает даже за 2 часа:
Код: sql
1.
2.
3.
4.
5.
6.
7.
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, вызывая его только для поиска меньшего телефона в большем. Но это тоже не помогает.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
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
			)


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


Код: sql
1.
2.
create nonclustered index new2
on #tempphones (client_id asc, clean_phone_length asc)


Селект тоже не пашет:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
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.
Мои варианты закончились :(
...
Рейтинг: 0 / 0
04.04.2018, 15:01
| Ответить | Цитировать | Написать  
Оптимизация join'а большой таблицы с собой же
    #39625562
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
K.T.V.Нужно выбрать тех клиентов, у которых телефоны совпадают.
K.T.V.Самый логичный вариант запроса не отрабатывает даже за 2 часа:
Код: sql
1.
delete from  c1

Ппц логика... надо выбрать? так давайте удалим...

K.T.V.у одного клиента может быть 1 телефон, у другого 3. Поэтому искать нужно по вхождению
А сначала нормализовать данные - не?
...
Рейтинг: 0 / 0
04.04.2018, 15:13
| Ответить | Цитировать | Написать  
Оптимизация join'а большой таблицы с собой же
    #39625567
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
K.T.V.,

Сравнивать 1 к 3м телефонам ещё ладно, но 3 к одному просто CHARINDEX явно нельзя. Нормализовать хоть в "право" хоть вниз и потом уже искать, ну и удалять кого из них тоже надо определить
...
Рейтинг: 0 / 0
04.04.2018, 15:16
| Ответить | Цитировать | Написать  
Оптимизация join'а большой таблицы с собой же
    #39625568
Руслан Дамирович
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Руслан Дамирович
Участник
K.T.V.,

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
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] )
...
Рейтинг: 0 / 0
04.04.2018, 15:16
| Ответить | Цитировать | Написать  
Оптимизация join'а большой таблицы с собой же
    #39625569
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
Руслан Дамирович,

агонь! Только 3 телефона в одном поле
...
Рейтинг: 0 / 0
04.04.2018, 15:18
| Ответить | Цитировать | Написать  
Оптимизация join'а большой таблицы с собой же
    #39625573
Руслан Дамирович
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Руслан Дамирович
Участник
TaPaKРуслан Дамирович,
агонь! Только 3 телефона в одном поле
K.T.V.В поле clean_phone содержатся только цифры, у одного клиента может быть 1 телефон, у другого 3
Ну, не очевидно, что ВСЕ ТРИ в одном поле :)
...
Рейтинг: 0 / 0
04.04.2018, 15:25
| Ответить | Цитировать | Написать  
Оптимизация join'а большой таблицы с собой же
    #39625579
Руслан Дамирович
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Руслан Дамирович
Участник
K.T.V.,
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
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] )
...
Рейтинг: 0 / 0
04.04.2018, 15:32
| Ответить | Цитировать | Написать  
Оптимизация join'а большой таблицы с собой же
    #39625580
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
K.T.V.,

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
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
	      )
...
Рейтинг: 0 / 0
04.04.2018, 15:37
| Ответить | Цитировать | Написать  
Оптимизация join'а большой таблицы с собой же
    #39625598
K.T.V.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
K.T.V.
Гость
TaPaK, спасибо, но нет. like, тем более с % перед тем что ищем, никак не ускоряет выборку
...
Рейтинг: 0 / 0
04.04.2018, 16:07
| Ответить | Цитировать | Написать  
Оптимизация join'а большой таблицы с собой же
    #39625600
K.T.V.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
K.T.V.
Гость
К сожалению, данные нормализовать нельзя, только работать над проблемой as is. Нет никакого универсального разделителя телефонов, поэтому нельзя определить, 3 штуки их в поле или 1. Хорошо что договорились, что ищем только вхождение 1го в 3, а не наоборот :)
...
Рейтинг: 0 / 0
04.04.2018, 16:09
| Ответить | Цитировать | Написать  
Оптимизация join'а большой таблицы с собой же
    #39625609
K.T.V.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
K.T.V.
Гость
Руслан Дамирович, я просто убираю из поля телефон все посторонние символы кроме цифр. Поэтому в вашем варианте это будут телефоны
( 1, '12345612345678' ),
( 2, '12345623456789' )
что значительно усложняет задачу
...
Рейтинг: 0 / 0
04.04.2018, 16:13
| Ответить | Цитировать | Написать  
Оптимизация join'а большой таблицы с собой же
    #39625613
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
K.T.V.TaPaK, спасибо, но нет. like, тем более с % перед тем что ищем, никак не ускоряет выборку
а я вот совсем не уверен что CHARINDEX быстрее LIKE +BIN в вашем случае
...
Рейтинг: 0 / 0
04.04.2018, 16:15
| Ответить | Цитировать | Написать  
Оптимизация join'а большой таблицы с собой же
    #39625643
K.T.V.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
K.T.V.
Гость
TaPaK, ну даже не вдаваясь в абстрактные рассуждения, практика показала, что like все равно выполняется недопустимо долго (больше часа)
...
Рейтинг: 0 / 0
04.04.2018, 16:58
| Ответить | Цитировать | Написать  
Оптимизация join'а большой таблицы с собой же
    #39625646
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
K.T.V.TaPaK, ну даже не вдаваясь в абстрактные рассуждения, практика показала, что like все равно выполняется недопустимо долго (больше часа)
я так понимаю вы думаете что есть волшебный рашпиль который насравнивает вам значения?

А как вы собираетесь жить когда указано 2 телефона? а + b и у другого b + a? Ну и ещё с десяток таких вариантов
...
Рейтинг: 0 / 0
04.04.2018, 17:01
| Ответить | Цитировать | Написать  
Оптимизация join'а большой таблицы с собой же
    #39625656
Руслан Дамирович
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Руслан Дамирович
Участник
K.T.V.Руслан Дамирович, я просто убираю из поля телефон все посторонние символы кроме цифр. Поэтому в вашем варианте это будут телефоны
( 1, '12345612345678' ),
( 2, '12345623456789' )
что значительно усложняет задачу
Ну так облегчите себе задачу, и сначала разделите данные, а потом зачистите.
А то прям комсомолом попахивает - стоя в гамаке... лайкаете.
...
Рейтинг: 0 / 0
04.04.2018, 17:31
| Ответить | Цитировать | Написать  
Оптимизация join'а большой таблицы с собой же
    #39625667
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
K.T.V.К сожалению, данные нормализовать нельзя, только работать над проблемой as is. Нет никакого универсального разделителя телефонов, поэтому нельзя определить, 3 штуки их в поле или 1.Имеем три номера телефона - 1111, 22222 и 333333
Телефоны клиента1, согласно вашим условиям - 111122222
клиента2 - 22222333333

Как и чего будете искать?
...
Рейтинг: 0 / 0
04.04.2018, 17:49
| Ответить | Цитировать | Написать  
Оптимизация join'а большой таблицы с собой же
    #39625673
Владислав Колосов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владислав Колосов
Участник
K.T.V.,

ищИте только точные совпадения, остальное - это мусор, а не номера.
...
Рейтинг: 0 / 0
04.04.2018, 17:59
| Ответить | Цитировать | Написать  
Оптимизация join'а большой таблицы с собой же
    #39625675
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
Владислав КолосовK.T.V.,

ищИте только точные совпадения, остальное - это мусор, а не номера.
действительно, просто удалите все записи где длинна больше стандартного нормера и всё
...
Рейтинг: 0 / 0
04.04.2018, 18:03
| Ответить | Цитировать | Написать  
Оптимизация join'а большой таблицы с собой же
    #39625676
K.T.V.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
K.T.V.
Гость
ну это не я придумываю идиотские условия.
Нужно, чтобы работал вариант найти телефон 55555 в телефоне 1111111555552222. Остальными пренебрегаем.
...
Рейтинг: 0 / 0
04.04.2018, 18:04
| Ответить | Цитировать | Написать  
Оптимизация join'а большой таблицы с собой же
    #39625679
Гавриленко Сергей Алексеевич
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гавриленко Сергей Алексеевич
Участник
K.T.V.ну это не я придумываю идиотские условия.
Нужно, чтобы работал вариант найти телефон 55555 в телефоне 1111111555552222. Остальными пренебрегаем.Заодно 55555 будет искаться в паре 44555 и 55666
...
Рейтинг: 0 / 0
04.04.2018, 18:09
| Ответить | Цитировать | Написать  
Оптимизация join'а большой таблицы с собой же
    #39625682
Руслан Дамирович
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Руслан Дамирович
Участник
K.T.V.ну это не я придумываю идиотские условия.
Нужно, чтобы работал вариант найти телефон 55555 в телефоне 1111111555552222. Остальными пренебрегаем.
Зато в ваших силах обосновать, что условия идиотские, и потому невыполнимые.
Это может повлечь за собой увольнение:
1. а оно вам нужно - дальше хуже;
2. развивайте навыки дипломатии;
3. вы лукавите, и условия совсем не такие, как вы их "не придумали".
...
Рейтинг: 0 / 0
04.04.2018, 18:17
| Ответить | Цитировать | Написать  
Оптимизация join'а большой таблицы с собой же
    #39625683
Гавриленко Сергей Алексеевич
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гавриленко Сергей Алексеевич
Участник
K.T.V.,

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

123456712345681234569 и 12345234561234560

?
...
Рейтинг: 0 / 0
04.04.2018, 18:20
| Ответить | Цитировать | Написать  
Оптимизация join'а большой таблицы с собой же
    #39625691
K.T.V.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
K.T.V.
Гость
Гавриленко Сергей Алексеевич, нет, потому что один телефон клиента полностью должен входить в телефон другого клиента
...
Рейтинг: 0 / 0
04.04.2018, 18:35
| Ответить | Цитировать | Написать  
Оптимизация join'а большой таблицы с собой же
    #39625692
Гавриленко Сергей Алексеевич
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гавриленко Сергей Алексеевич
Участник
K.T.V.Гавриленко Сергей Алексеевич, нет, потому что один телефон клиента полностью должен входить в телефон другого клиентаА где здесь телефоны клиентов? Здесь две строки, к которым не прилагается алгоритм разбития из на подстроки (телефоны), чтобы потом сравнить хотя бы с неразбитой второй строкой.
...
Рейтинг: 0 / 0
04.04.2018, 18:38
| Ответить | Цитировать | Написать  
Оптимизация join'а большой таблицы с собой же
    #39625693
blonduser
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
blonduser
Участник
K.T.V.,
За какое время вы планируете перебрать 144 лярда вариантов?
...
Рейтинг: 0 / 0
04.04.2018, 18:44
| Ответить | Цитировать | Написать  
25 сообщений из 33, страница 1 из 2
  1  все
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Оптимизация join'а большой таблицы с собой же
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]