Найти дубликат для комбинации строк — Oracle SQL
У меня есть таблица:
Таблица 1
unique_id user_id user_seq col_name value_val position
1 100 1 test1 100 1
1 100 1 test2 123 1
1 100 1 test1 a 2
1 100 1 test2 text 2
1 100 1 test3 1Rw 2
1 100 1 test4 1Tes 2
2 101 1 test1 1 1
2 101 1 test2 1 1
2 101 1 test3 1 1
2 101 1 test4 1 1
2 101 1 test5 1 1
3 100 1 test1 100 1
3 100 1 test2 123 1
3 100 1 test1 a 2
3 100 1 test2 text 2
3 100 1 test3 1Rw 2
3 100 1 test4 1Tes 2
4 101 1 test1 1 1
4 101 1 test2 1 1
4 101 1 test3 1 1
4 101 1 test4 1 1
Мне нужно найти дубликат на основе следующего:
user_id, user_seq, col_name, value_val и position должны быть одинаковыми для разных unique_id.
В приведенном выше примере unique_id - 1 и 3 ТОЧНО одинаковы, поэтому они должны быть возвращены в качестве вывода.
Для unique_id = 2 и 4 разница test5 недоступна для unique_id = 4, поэтому она не будет зафиксирована.
Выход будет:
unique_id
1
3
Кроме того, мой набор данных огромен, около 50 миллионов записей, поэтому нужно оптимизированное решение. Любая помощь?
РЕДАКТИРОВАТЬ
Моя структура таблицы:
Name Null? Type
----------- ----- --------------
UNIQUE_ID NUMBER
USER_SEQ VARCHAR2(100)
COL_NAME VARCHAR2(263)
VALUE_VAL VARCHAR2(4000)
POSITION NUMBER
USER_ID NUMBER
Нет доступных индексов.
Я тоже об этом думал, но в моем случае это не сработает. Например user_id = 101 и user_seq = 1 дадут мне счет 2 для col_name = test1, тогда как он должен проверять комбинацию строк.
CREATE TABLE, чтобы мы знали типы данных и индексы.. Кроме того, 50 миллионов записей не являются «огромными», я должен был это сказать..
@RaymondNijland - у меня есть общий вывод desc table1; в вопросе. Сейчас индексов нет.
Также какую версию базы данных Oracle вы используете?
@RaymondNijland - я использую Oracle Enterprise 18c.
мне нужно поесть сейчас, но я бы сделал что-то вроде использования STANDARD_HASH() в сочетании с LISTAGG для создания контрольной суммы по группе вне записей, см. пример загрузить ее во временную таблицу (с индексами), и вы можете просто использовать синтаксис GROUP BY, HAVING, COUNT(DISTINCT ) во временной таблице ..
@RaymondNijland - выдает ошибку - ORA-01489: результат конкатенации строк слишком длинный
VARCHAR2 , а если вместо этого использовать STANDARD_HASH(LISTAGG(TO_CLOB(hash))) ?
Это работает в первый раз, но если я прокручиваю вниз, выдает ошибку - ORA-01489: результат конкатенации строк слишком длинный
хорошо, попробуйте ALTER SYSTEM SET max_string_size=extended SCOPE= SPFILE;LISTAGG швы должны быть ограничены max_string_size настройкой ... Если это не сработает, считайте меня как iam, а затем отключите параметры, так как я некоторое время не занимался базой данных Oracle и я ржавый
Ответы 3
Если производительность не является проблемой, как насчет самостоятельного присоединения?
select a.unique_id as unique_id
from table1 a join table1 b
on a.user_id = b.user_id
and a.user_seq = b.user_seq
and a.col_name = b.col_name
and a.value_val = b.value_val
and a.position = b.position
and a.unique_id <> b.unique_id
Ах, в моей таблице более 50 миллионов записей. Есть ли оптимизированный способ сделать это?
Понимаю. Может тогда это поможет.select unique_id from ( select t.*, count(*) over (partition by [duplicate columns]) ct from table1 t) where ct > 1
Вот один из способов сделать это:
with sample_data as (select 1 unique_id, 100 user_id, 1 user_seq, 'test1' col_name, '100' value_val, 1 position from dual union all
select 1 unique_id, 100 user_id, 1 user_seq, 'test2' col_name, '123' value_val, 1 position from dual union all
select 1 unique_id, 100 user_id, 1 user_seq, 'test1' col_name, 'a' value_val, 2 position from dual union all
select 1 unique_id, 100 user_id, 1 user_seq, 'test2' col_name, 'text' value_val, 2 position from dual union all
select 1 unique_id, 100 user_id, 1 user_seq, 'test3' col_name, '1Rw' value_val, 2 position from dual union all
select 1 unique_id, 100 user_id, 1 user_seq, 'test4' col_name, '1Tes' value_val, 2 position from dual union all
select 2 unique_id, 101 user_id, 1 user_seq, 'test1' col_name, '1' value_val, 1 position from dual union all
select 2 unique_id, 101 user_id, 1 user_seq, 'test2' col_name, '1' value_val, 1 position from dual union all
select 2 unique_id, 101 user_id, 1 user_seq, 'test3' col_name, '1' value_val, 1 position from dual union all
select 2 unique_id, 101 user_id, 1 user_seq, 'test4' col_name, '1' value_val, 1 position from dual union all
select 2 unique_id, 101 user_id, 1 user_seq, 'test5' col_name, '1' value_val, 1 position from dual union all
select 3 unique_id, 100 user_id, 1 user_seq, 'test1' col_name, '100' value_val, 1 position from dual union all
select 3 unique_id, 100 user_id, 1 user_seq, 'test2' col_name, '123' value_val, 1 position from dual union all
select 3 unique_id, 100 user_id, 1 user_seq, 'test1' col_name, 'a' value_val, 2 position from dual union all
select 3 unique_id, 100 user_id, 1 user_seq, 'test2' col_name, 'text' value_val, 2 position from dual union all
select 3 unique_id, 100 user_id, 1 user_seq, 'test3' col_name, '1Rw' value_val, 2 position from dual union all
select 3 unique_id, 100 user_id, 1 user_seq, 'test4' col_name, '1Tes' value_val, 2 position from dual union all
select 4 unique_id, 101 user_id, 1 user_seq, 'test1' col_name, '1' value_val, 1 position from dual union all
select 4 unique_id, 101 user_id, 1 user_seq, 'test2' col_name, '1' value_val, 1 position from dual union all
select 4 unique_id, 101 user_id, 1 user_seq, 'test3' col_name, '1' value_val, 1 position from dual union all
select 4 unique_id, 101 user_id, 1 user_seq, 'test4' col_name, '1' value_val, 1 position from dual union all
select 6 unique_id, 101 user_id, 1 user_seq, 'test1' col_name, '1' value_val, 1 position from dual union all
select 6 unique_id, 101 user_id, 1 user_seq, 'test2' col_name, '1' value_val, 1 position from dual union all
select 6 unique_id, 101 user_id, 1 user_seq, 'test3' col_name, '1' value_val, 1 position from dual union all
select 6 unique_id, 101 user_id, 1 user_seq, 'test4' col_name, '1' value_val, 1 position from dual union all
select 7 unique_id, 101 user_id, 1 user_seq, 'test1' col_name, '1' value_val, 1 position from dual union all
select 7 unique_id, 101 user_id, 1 user_seq, 'test2' col_name, '1' value_val, 1 position from dual union all
select 7 unique_id, 101 user_id, 1 user_seq, 'test3' col_name, '1' value_val, 1 position from dual union all
select 7 unique_id, 101 user_id, 1 user_seq, 'test4' col_name, '1' value_val, 1 position from dual union all
select 5 unique_id, 100 user_id, 1 user_seq, 'test1' col_name, '100' value_val, 1 position from dual union all
select 5 unique_id, 100 user_id, 1 user_seq, 'test2' col_name, '123' value_val, 1 position from dual union all
select 5 unique_id, 100 user_id, 1 user_seq, 'test1' col_name, 'a' value_val, 2 position from dual union all
select 5 unique_id, 100 user_id, 1 user_seq, 'test2' col_name, 'text' value_val, 2 position from dual union all
select 5 unique_id, 100 user_id, 1 user_seq, 'test3' col_name, '1Rw' value_val, 2 position from dual union all
select 5 unique_id, 100 user_id, 1 user_seq, 'test4' col_name, '1Tes' value_val, 2 position from dual),
cnts as (select unique_id,
user_id,
user_seq,
col_name,
value_val,
position,
count(*) over (partition by unique_id) cnt
from sample_data),
res as (select distinct sd1.unique_id id1,
sd2.unique_id id2,
sd1.cnt,
count(*) over (partition by sd1.unique_id, sd2.unique_id) total_id1_rows_cnt
from cnts sd1
inner join cnts sd2 on sd1.unique_id < sd2.unique_id
and sd1.user_id = sd2.user_id
and sd1.user_seq = sd2.user_seq
and sd1.col_name = sd2.col_name
and sd1.value_val = sd2.value_val
and sd1.position = sd2.position
and sd1.cnt = sd2.cnt)
select id1||','||listagg(id2, ',') within group (order by id2) grouped_unique_ids
from res
where id1 not in (select id2
from res)
and cnt = total_id1_rows_cnt
group by id1
order by grouped_unique_ids;
И вот рабочий пример db<>, чтобы доказать, что это работает
Должен ли я индексировать какие-либо конкретные столбцы, чтобы получить хорошую производительность с помощью этого SQL?
Я бы так не подумал, поскольку вы запрашиваете всю таблицу.
@RaymondNijland, в отличие от компьютеров, объем памяти моего мозга тоже нельзя добавить! ?
Кроме того, группа по, похоже, не дает правильного вывода, я изменил 1 значение в unique_id = 5, это дало мне такой результат - dbfiddle.uk/…
@ты прав; группа не работает. Ре. индекс - вам нужно будет включить все столбцы в соединение.
@dang, версия с самоприсоединением тоже не работала, но мне удалось это исправить, так что она работает.
@Boneist - да, я собирался прокомментировать. Не могли бы вы поделиться dbfiddle?
Я видел обновленный файл dbfiddle. Выглядит многообещающе, но запрос довольно сложный. Нет ли более простого способа сделать это?
Предполагая, что вы можете объединить значения в строки, возможно, самый простой метод:
select *
from (select unique_id, count(*) over (partition by vals) as cnt
from (select unique_id,
listagg(user_id || ':' || user_seq || ':' || col_name || ':' || value_val || ':' || position, ',') within group (order by user_id, user_seq, col_name, value_val, position) as vals
from sample_data sd
group by unique_id
) sd
) sd
where cnt > 1;
Здесь — это рабочий пример db<>.
Позвольте мне подчеркнуть: это не универсальное решение из-за внутренних ограничений длины строки в Oracle. Но это работает для ваших данных и может быть удобным решением вашей проблемы.
Вы правы, это не универсальное решение. Я получаю сообщение об ошибке - ORA-01489: слишком длинный результат конкатенации строк
@данг . . . Очень жаль. Это самое простое решение.
ГРУППИРОВАТЬ ПО, ИМЕТЬ, СЧИТАТЬ (ОТЛИЧНЫЕ)