Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Поиск повторяющихся значений  [new]
leprechaun
Member

Откуда:
Сообщений: 70
Добрый день, не соображу пока, мне нужно в списке наименований найти к примеру те, у которых совпадает 5 последовательных символов. Как это оформить, спасибо
26 сен 17, 18:44    [20824835]     Ответить | Цитировать Сообщить модератору
 Re: Поиск повторяющихся значений  [new]
dmdmdm
Member

Откуда: Нижний Новгород
Сообщений: 851
Как это оформить


with t as (
       select 'abcde12345' name from dual union all
       select 'bcde1234567' name from dual union all
       select 'qwecde123777' name from dual union all
       select '12345678abc' name from dual union all
       select '678abcdeqwe' name from dual
     )
select t1.name, t2.name from t t1, t t2


Дальше специалисты по REGEXP подтянутся.
26 сен 17, 19:46    [20824940]     Ответить | Цитировать Сообщить модератору
 Re: Поиск повторяющихся значений  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 4809
leprechaun,

Регулярные выражения Oracle
26 сен 17, 19:55    [20824952]     Ответить | Цитировать Сообщить модератору
 Re: Поиск повторяющихся значений  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 4809
with t(name) as
(select 'abbbbc' from dual
union all select 'abbbbbc' from dual
union all select 'abbbbbbc' from dual)
select t.*,
case when regexp_like(name, '(.)\1{4}') then 1 end "5_or_more"
from t
26 сен 17, 20:00    [20824960]     Ответить | Цитировать Сообщить модератору
 Re: Поиск повторяющихся значений  [new]
Fogel
Member

Откуда:
Сообщений: 227
dmdmdm
Как это оформить


with t as (
       select 'abcde12345' name from dual union all
       select 'bcde1234567' name from dual union all
       select 'qwecde123777' name from dual union all
       select '12345678abc' name from dual union all
       select '678abcdeqwe' name from dual
     )
select t1.name, t2.name from t t1, t t2


Дальше специалисты по REGEXP подтянутся.


Если понимать задачу, как поиск совпадения последовательности из 5 символов в разных полях, то можно и без regexp:
with t as
 (select 'abcde12345' name, 1 as n
    from dual
  union all
  select 'bcde1234567' name, 2
    from dual
  union all
  select 'qwecde123777' name, 3
    from dual
  union all
  select '12345678abc' name, 4
    from dual
  union all
  select '678abcdeqwe' name, 5
    from dual)
select t1.name, t2.name
  from t t1, t t2
 where t1.name != t2.name
   and t1.name like '%' || substr(t2.name, level, 5) || '%'
connect by t1.name != t2.name
       and level <= length(t2.name) - 5
       and prior t2.name = t2.name
       and prior t1.name = t1.name
       and prior t1.name = t2.name
 order by t1.n

номер и сортировку по нему добавил для облегчения визуальной сверки
26 сен 17, 21:14    [20825104]     Ответить | Цитировать Сообщить модератору
 Re: Поиск повторяющихся значений  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 8624
Fogel,

and level <= length(t2.name) - 4 -- a не and level <= length(t2.name) - 5


SY.
26 сен 17, 22:46    [20825283]     Ответить | Цитировать Сообщить модератору
 Re: Поиск повторяющихся значений  [new]
leprechaun
Member

Откуда:
Сообщений: 70
Спасибо! Не то чтоб код был для меня прозрачен, но опробую завтра.
27 сен 17, 00:29    [20825496]     Ответить | Цитировать Сообщить модератору
 Re: Поиск повторяющихся значений  [new]
Sergei.Agalakov
Member

Откуда:
Сообщений: 551
Насколько часто надо искать? В принципе можно создать доменные индексы, если надо часто выполнять такие запросы.
27 сен 17, 02:03    [20825552]     Ответить | Цитировать Сообщить модератору
 Re: Поиск повторяющихся значений  [new]
leprechaun
Member

Откуда:
Сообщений: 70
Довольно часто, регулярный запрос
27 сен 17, 02:30    [20825556]     Ответить | Цитировать Сообщить модератору
 Re: Поиск повторяющихся значений  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 354
leprechaun,

символы в том числе буквы?
тогда нужено учитывать на каком языке алфавит
напр на украинском "зиіїй" подряд пьять букв, такая строка должна попасть в выборку?

опять ж, upper/lower учитывать?

или "у которых совпадает 5 последовательных символов" ето "ашшшшшб"?

.....
stax
27 сен 17, 10:24    [20825919]     Ответить | Цитировать Сообщить модератору
 Re: Поиск повторяющихся значений  [new]
Fogel
Member

Откуда:
Сообщений: 227
SY
Fogel,

and level <= length(t2.name) - 4 -- a не and level <= length(t2.name) - 5


SY.


не.
хоть минус 10000
единственное НО - этот запрос до первого уникального вхождения, если совпадения по нескольким последовательностям - все пары не покажет.
Чтобы увидеть все совпадения (в том числе дубли полей) - лучше тогда отталкиваться от общей последовательности и оформить, например так:
+
with t as
 (select 'abcde12345' name
    from dual
  union all
  select '12345' name
    from dual
  union all
  select 'xxxxxx12345zzz678abzz' name
    from dual
  union all
  select 'bcde1234567' name
    from dual
  union all
  select 'qwecde123777' name
    from dual
  union all
  select '12345678abc' name
    from dual
  union all
  select '678abcdeqwe' name
    from dual),
list_seq as (select substr(t2.name, level, 5) as a5
  from t t1, t t2
 where t1.name != t2.name
   and instr(t1.name, substr(t2.name, level, 5)) > 0
connect by level < length(t2.name)
       and prior t2.name = t2.name
       and prior t1.name = t1.name
       and prior t1.name = t2.name
       group by substr(t2.name, level, 5)) 
select l.a5, listagg(t.name,',') within group (order by t.name) from t, list_seq l where instr(t.name,l.a5) > 0
group by l.a5

ну или наоборот, в каком поле какой набор общих последовательностей:
...
select t.name, listagg(l.a5,',') within group (order by l.a5) as seq from t, list_seq l where instr(t.name,l.a5) > 0
group by t.name 
27 сен 17, 13:26    [20826564]     Ответить | Цитировать Сообщить модератору
 Re: Поиск повторяющихся значений  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 354
Fogel
не.
хоть минус 10000


  1  with t1 as (
  2  select '1234567' name from dual)
  3  select rownum l,substr(name, level, 5) s from t1
  4* connect by level < length(t1.name)
SQL> /

         L S
---------- -----
         1 12345
         2 23456
         3 34567
         4 4567
         5 567
         6 67

6 rows selected.


для 4, 5, 6 нет смысла искать вхождение, поетому length-3


......
stax
27 сен 17, 17:17    [20827396]     Ответить | Цитировать Сообщить модератору
 Re: Поиск повторяющихся значений  [new]
leprechaun
Member

Откуда:
Сообщений: 70
to Fogel подвис у меня что-то такой код, не хватило ресурсов выполнить (Connect by filtering phase runs out of temp tablespace)

Еще раз уточню вопрос, есть список допустим номенклатуры.
И нужно передавая в параметре количество искомых символов, выводить все позиции
к примеру 3 передал, получил

Молоко
Молоток
Виноград Молдова
27 сен 17, 18:08    [20827510]     Ответить | Цитировать Сообщить модератору
 Re: Поиск повторяющихся значений  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 4809
leprechaun,

Наибольшая общая подпоследовательность
На англоязычной википедии статья намного полезнее.

В интере можно найти несколько SQL решений которые не стоит воспринимать серьезно.
Чуть улучшить можно используя model clause, но если задача имеет практический смысл - лучше сделать PL/SQL или C function.
27 сен 17, 18:33    [20827541]     Ответить | Цитировать Сообщить модератору
 Re: Поиск повторяющихся значений  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 354
leprechaun,

сколько "последовательностей"?
select sum(length(name)-:n+1) from t

и скоко строк в справочнике?

если не надо выводить из-за чего повторение,
попробовать переписать через exists в надежде что, если пересеклось то дальше не ищем

.....
stax
28 сен 17, 10:35    [20828428]     Ответить | Цитировать Сообщить модератору
 Re: Поиск повторяющихся значений  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 354
leprechaun
Еще раз уточню вопрос

с кем совпадает надо вывадить?

.....
stax
28 сен 17, 11:03    [20828546]     Ответить | Цитировать Сообщить модератору
 Re: Поиск повторяющихся значений  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 354
leprechaun,

with t as (
select 1 id, 'Молоко' name from dual union all
select 2,'Молоток'  from dual union all
select 3,'Виноград Молдова'  from dual union all
select 4,'Золотo'  from dual union all
select 5,'1234567'  from dual union all
select 6,'7654321'  from dual union all
select 7,'000654654000'  from dual union all
select 8,'9876'  from dual 
)
select * from t
where
exists (
   select 1 from t tt
   where tt.id<>t.id and instr(t.name,substr(tt.name,level,3))>0
connect by tt.id=prior tt.id and level<=length(tt.name)-2 and prior sys_guid() is not null)
/
SQL> /

        ID NAME
---------- ----------------
         1 Молоко
         2 Молоток
         3 Виноград Молдова
         4 Золотo
         6 7654321
         7 000654654000



.....
stax
28 сен 17, 11:26    [20828617]     Ответить | Цитировать Сообщить модератору
 Re: Поиск повторяющихся значений  [new]
leprechaun
Member

Откуда:
Сообщений: 70
Stax

Да, надо выводить с кем совпадает, и думаю не до первого совпадения, а все. В справочнике может быть тысяч 50 наименований, я пока без понятия как сделать, твой код посмотрю, спасибо.
28 сен 17, 20:17    [20830127]     Ответить | Цитировать Сообщить модератору
 Re: Поиск повторяющихся значений  [new]
xtender
Member

Откуда: Мск
Сообщений: 4502
Можно заюзать текстовые индексы:
например, для поиска от 3-букв:
create table dict as 
select 1 id, 'Молоко' name from dual union all
select 2,'Молоток'  from dual union all
select 3,'Виноград Молдова'  from dual union all
select 4,'Золотo'  from dual union all
select 5,'1234567'  from dual union all
select 6,'7654321'  from dual union all
select 7,'000654654000'  from dual union all
select 8,'9876'  from dual 
/
begin 
   ctx_ddl.create_preference('worlist_substr', 'BASIC_WORDLIST'); 
   ctx_ddl.set_attribute('worlist_substr','PREFIX_INDEX','TRUE');
   ctx_ddl.set_attribute('worlist_substr','PREFIX_MIN_LENGTH', '3');
   ctx_ddl.set_attribute('worlist_substr','PREFIX_MAX_LENGTH', '20');
   ctx_ddl.set_attribute('worlist_substr','SUBSTRING_INDEX', 'YES');
end;
/
create index ix_dict_ctx on dict(name)
indextype is ctxsys.context
parameters('WORDLIST worlist_substr SYNC (on commit)')
/

после этого появится таблица с токенами DR$IX_DICT_CTX$I, по ней и можно легко искать:
+ вариант 1
with tokens as (
   select
     token_text
    ,token_type
    ,sum(token_count) cnt
   from DR$IX_DICT_CTX$I t
   where length(token_text)=3
   group by token_text, token_type
   having sum(token_count)>1
)
select t.*
      ,x.*
      ,d.name
from tokens t
     ,xmltable('/CTXREPORT/TOKEN_INFO/TOKEN_DATA/TOKEN_DATA_ROW/TOKEN_DATA_DOCID'
               passing 
               xmltype(
                        ctx_report.token_info(
                             index_name => 'IX_DICT_CTX'
                            ,token      => t.token_text
                            ,token_type => t.token_type
                            ,report_format=>'XML'
                            )
                      )
               columns rid rowid path '@BASE_ROWID'
              ) x
     ,dict d
where x.rid = d.rowid
/

+ вариант 2
with tokens as (
   select
     token_text
    ,token_type
    ,sum(token_count) cnt
   from DR$IX_DICT_CTX$I t
   where length(token_text)=3
   group by token_text, token_type
   having sum(token_count)>1
)
select t.*
      ,d.name
from tokens t, dict d
where contains(d.name,'%'||t.token_text||'%')>0
28 сен 17, 21:36    [20830211]     Ответить | Цитировать Сообщить модератору
 Re: Поиск повторяющихся значений  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 354
leprechaun
Stax

Да, надо выводить с кем совпадает, и думаю не до первого совпадения, а все. В справочнике может быть тысяч 50 наименований, я пока без понятия как сделать, твой код посмотрю, спасибо.


запрос похож на Fogel (переписал для exists)

по любому надо сравнивать каждую строку с каждой, а ето 50000*50000=2 500 000 000, при етом еще разложить на "5-ти символьные", многовато

можно немного уменьшить с учетом если А пересекается с Б, то и Б с А

как советовали, просить права и создавать ф-цию, и експерементировать напр с pipelined + parallel

если поиск разовый, можно напр ночью искать

.....
stax
29 сен 17, 09:19    [20830823]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить