Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
Топик располагается на нескольких страницах: [1] 2 вперед Ctrl→ все |
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] Ответить | Цитировать Сообщить модератору |
Akina Member Откуда: Зеленоград, Москва, Россия Сообщений: 20973 |
А сначала нормализовать данные - не? |
||||||
4 апр 18, 15:13 [21311983] Ответить | Цитировать Сообщить модератору |
TaPaK Member Откуда: Kiev Сообщений: 6801 |
K.T.V., Сравнивать 1 к 3м телефонам ещё ладно, но 3 к одному просто CHARINDEX явно нельзя. Нормализовать хоть в "право" хоть вниз и потом уже искать, ну и удалять кого из них тоже надо определить |
4 апр 18, 15:16 [21311994] Ответить | Цитировать Сообщить модератору |
Руслан Дамирович 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] Ответить | Цитировать Сообщить модератору |
TaPaK Member Откуда: Kiev Сообщений: 6801 |
Руслан Дамирович, агонь! Только 3 телефона в одном поле |
4 апр 18, 15:18 [21312001] Ответить | Цитировать Сообщить модератору |
Руслан Дамирович Member Откуда: Резиновая нерезиновая Сообщений: 940 |
Ну, не очевидно, что ВСЕ ТРИ в одном поле :) |
||||
4 апр 18, 15:25 [21312024] Ответить | Цитировать Сообщить модератору |
Руслан Дамирович 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] Ответить | Цитировать Сообщить модератору |
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] Ответить | Цитировать Сообщить модератору |
K.T.V. Member Откуда: РнД Сообщений: 41 |
TaPaK, спасибо, но нет. like, тем более с % перед тем что ищем, никак не ускоряет выборку |
4 апр 18, 16:07 [21312198] Ответить | Цитировать Сообщить модератору |
K.T.V. Member Откуда: РнД Сообщений: 41 |
К сожалению, данные нормализовать нельзя, только работать над проблемой as is. Нет никакого универсального разделителя телефонов, поэтому нельзя определить, 3 штуки их в поле или 1. Хорошо что договорились, что ищем только вхождение 1го в 3, а не наоборот :) |
4 апр 18, 16:09 [21312211] Ответить | Цитировать Сообщить модератору |
K.T.V. Member Откуда: РнД Сообщений: 41 |
Руслан Дамирович, я просто убираю из поля телефон все посторонние символы кроме цифр. Поэтому в вашем варианте это будут телефоны ( 1, '12345612345678' ), ( 2, '12345623456789' ) что значительно усложняет задачу |
4 апр 18, 16:13 [21312240] Ответить | Цитировать Сообщить модератору |
TaPaK Member Откуда: Kiev Сообщений: 6801 |
а я вот совсем не уверен что CHARINDEX быстрее LIKE +BIN в вашем случае |
||
4 апр 18, 16:15 [21312249] Ответить | Цитировать Сообщить модератору |
K.T.V. Member Откуда: РнД Сообщений: 41 |
TaPaK, ну даже не вдаваясь в абстрактные рассуждения, практика показала, что like все равно выполняется недопустимо долго (больше часа) |
4 апр 18, 16:58 [21312422] Ответить | Цитировать Сообщить модератору |
TaPaK Member Откуда: Kiev Сообщений: 6801 |
я так понимаю вы думаете что есть волшебный рашпиль который насравнивает вам значения? А как вы собираетесь жить когда указано 2 телефона? а + b и у другого b + a? Ну и ещё с десяток таких вариантов |
||
4 апр 18, 17:01 [21312430] Ответить | Цитировать Сообщить модератору |
Руслан Дамирович Member Откуда: Резиновая нерезиновая Сообщений: 940 |
Ну так облегчите себе задачу, и сначала разделите данные, а потом зачистите. А то прям комсомолом попахивает - стоя в гамаке... лайкаете. |
||
4 апр 18, 17:31 [21312545] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9646 |
Телефоны клиента1, согласно вашим условиям - 111122222 клиента2 - 22222333333 Как и чего будете искать? |
||
4 апр 18, 17:49 [21312618] Ответить | Цитировать Сообщить модератору |
Владислав Колосов Member Откуда: Сообщений: 8346 |
K.T.V., ищИте только точные совпадения, остальное - это мусор, а не номера. |
4 апр 18, 17:59 [21312654] Ответить | Цитировать Сообщить модератору |
TaPaK Member Откуда: Kiev Сообщений: 6801 |
действительно, просто удалите все записи где длинна больше стандартного нормера и всё |
||
4 апр 18, 18:03 [21312665] Ответить | Цитировать Сообщить модератору |
K.T.V. Member Откуда: РнД Сообщений: 41 |
ну это не я придумываю идиотские условия. Нужно, чтобы работал вариант найти телефон 55555 в телефоне 1111111555552222. Остальными пренебрегаем. |
4 апр 18, 18:04 [21312669] Ответить | Цитировать Сообщить модератору |
Гавриленко Сергей Алексеевич Member Откуда: Moscow Сообщений: 37155 |
|
||
4 апр 18, 18:09 [21312679] Ответить | Цитировать Сообщить модератору |
Руслан Дамирович Member Откуда: Резиновая нерезиновая Сообщений: 940 |
Зато в ваших силах обосновать, что условия идиотские, и потому невыполнимые. Это может повлечь за собой увольнение: 1. а оно вам нужно - дальше хуже; 2. развивайте навыки дипломатии; 3. вы лукавите, и условия совсем не такие, как вы их "не придумали". |
||
4 апр 18, 18:17 [21312699] Ответить | Цитировать Сообщить модератору |
Гавриленко Сергей Алексеевич Member Откуда: Moscow Сообщений: 37155 |
K.T.V., Вот. к примеру, в этих двух склееных строказх (в каждой по 3 телефонных номера) есть совпадения по-вашему: 123456712345681234569 и 12345234561234560 ? |
4 апр 18, 18:20 [21312704] Ответить | Цитировать Сообщить модератору |
K.T.V. Member Откуда: РнД Сообщений: 41 |
Гавриленко Сергей Алексеевич, нет, потому что один телефон клиента полностью должен входить в телефон другого клиента |
4 апр 18, 18:35 [21312744] Ответить | Цитировать Сообщить модератору |
Гавриленко Сергей Алексеевич Member Откуда: Moscow Сообщений: 37155 |
|
||
4 апр 18, 18:38 [21312756] Ответить | Цитировать Сообщить модератору |
blonduser Member Откуда: Сообщений: 133 |
K.T.V., За какое время вы планируете перебрать 144 лярда вариантов? |
4 апр 18, 18:44 [21312766] Ответить | Цитировать Сообщить модератору |
Топик располагается на нескольких страницах: [1] 2 вперед Ctrl→ все |
Все форумы / Microsoft SQL Server | ![]() |