Блог

SQL для поиска повторяющихся записей (внутри группы)

У меня небольшая проблема, и я не уверен, как ее лучше всего решить, так как у меня ограниченный доступ только к самой базе данных (Oracle). В нашей таблице «СОБЫТИЕ» у нас около 160 тысяч записей, каждое СОБЫТИЕ имеет идентификатор ГРУППЫ, а обычная запись содержит ровно 5 строк с одним и тем же идентификатором ГРУППЫ. Из-за ошибки мы в настоящее время получаем пару повторяющихся записей (дубликатов, поэтому 10 строк вместо 5, просто другой идентификатор СОБЫТИЯ. Это может измениться, так что это просто <> 5). Нам нужно отфильтровать все записи этих групп.

Из-за ограниченного доступа к базе данных мы не можем использовать временную таблицу, а также не можем добавить индекс в столбец GROUPID, чтобы ускорить его.

Мы можем получить идентификаторы групп с помощью этого запроса, но нам понадобится второй запрос, чтобы получить необходимые данные

 select A."GROUPID"
from "EVENT" A
group by A."GROUPID"
having count(A."GROUPID") <> 5
 

Одним из решений был бы подвыбор:

 select *
from "EVENT" A
where A."GROUPID" IN (
  select B."GROUPID"
  from "EVENT" B
  group by B."GROUPID"
  having count(B."GROUPID") <> 5
)
 

Без индекса для GROUPID и 160 тыс. записей это занимает слишком много времени.
Попытался подумать о соединении, которое может справиться с этим, но пока не может найти хорошего решения.

Может быть, кто-нибудь может найти хорошее решение для этого?

Небольшое редактирование: У нас здесь нет 100% дубликатов, так как каждая запись по — прежнему имеет уникальный идентификатор, а идентификатор ГРУППЫ также не уникален (вот почему нам нужно использовать «группировать по») — или, может быть, я просто упускаю простое решение для этого 🙂

Небольшой пример о данных (я не хочу их удалять, просто найдите их)


EVENTID | GROUPID | TYPEID
123456    123       12
123457    123       145
123458    123       2612
123459    123       41
123460    123       238


234567    123       12
234568    123       145
234569    123       2612
234570    123       41
234571    123       238

В нем есть еще несколько столбцов, таких как метка времени и т. Д., Но, как вы уже можете видеть, все идентично, кроме EVENTID.

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

Классическая проблема для решения аналитических запросов:

 select eventid,
       groupid,
       typeid
from   (
       Select eventid,
              groupid,
              typeid,
              count(*) over (partition by group_id) count_by_group_id
       from   EVENT
       )
where count_by_group_id <> 5
 

Вы можете получить ответ с помощью соединения вместо подзапроса

 select
    a.*
from
    event as a
inner join
    (select groupid
     from event
     group by groupid
     having count(*) <> 5) as b
  on a.groupid = b.groupid
 

Это довольно распространенный способ получения всей информации из строк в группе.

Как и ваш предложенный ответ и другие ответы, это будет работать намного быстрее с индексом groupid. Администратор базы данных должен сбалансировать преимущества, связанные с тем, что ваш запрос выполняется намного быстрее, с затратами на поддержание еще одного индекса.

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

Сколько времени на самом деле занимает этот SQL? Вы собираетесь запустить его только один раз, я полагаю, исправив ошибку, которая в первую очередь вызвала повреждение? Я только что создал такой тестовый случай:

 SQL> create table my_objects as 
  2  select object_name, ceil(rownum/5) groupid, rpad('x',500,'x') filler
  3  from all_objects;

Table created.

SQL> select count(*) from my_objects;

  COUNT(*)
----------
     83782

SQL> select * from my_objects where groupid in (
  2  select groupid from my_objects
  3  group by groupid
  4  having count(*) <> 5
  5  );

OBJECT_NAME                       GROUPID FILLER
------------------------------ ---------- --------------------------------
XYZ                                 16757 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
YYYY                                16757 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Elapsed: 00:00:01.67
 

Менее 2 секунд. Хорошо, в моей таблице вдвое меньше строк, чем в вашей, но 160 тысяч-это не так уж много. Я добавил столбец заполнитель, чтобы таблица занимала немного места на диске. План выполнения АВТОПРОБЕГА был:

 -------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |   389 |   112K| 14029   (2)|
|*  1 |  HASH JOIN            |            |   389 |   112K| 14029   (2)|
|   2 |   VIEW                | VW_NSO_1   | 94424 |  1198K|  6570   (2)|
|*  3 |    FILTER             |            |       |       |            |
|   4 |     HASH GROUP BY     |            |     1 |  1198K|  6570   (2)|
|   5 |      TABLE ACCESS FULL| MY_OBJECTS | 94424 |  1198K|  6504   (1)|
|   6 |   TABLE ACCESS FULL   | MY_OBJECTS | 94424 |    25M|  6506   (1)|
-------------------------------------------------------------------------
 

Если ваши администраторы баз данных не будут добавлять индекс, чтобы ускорить процесс, спросите их, что они вам предлагают сделать (в конце концов, за это им платят). Предположительно, у вас есть бизнес-обоснование, почему вам нужна эта информация, и в этом случае ваше непосредственное руководство должно быть на вашей стороне.

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

С точки зрения SQL, я думаю, вы уже ответили на свой собственный вопрос. Подход, который вы описали (т. Е. с использованием подзапроса), хорош, и я был бы удивлен, если бы какой-либо другой способ написания запроса значительно отличался по производительности.

160 ТЫСЯЧ записей не кажутся мне большим количеством. Я мог бы понять, если бы вы были недовольны производительностью этого запроса, если бы он входил в часть кода приложения, но, судя по звукам, вы просто используете его как часть некоторого упражнения по очистке данных. (и поэтому я ожидал бы, что вы будете немного более терпимы в плане производительности).

Даже без какого-либо вспомогательного индекса, это все равно всего лишь два полных сканирования таблицы в 160 тысяч строк, которые, честно говоря, я ожидал бы выполнить за какое-то смутно разумное время.

Поговорите со своими администраторами БД. Они помогли создать проблему, так что позвольте им быть частью решения.

/ОТРЕДАКТИРУЙТЕ/ Тем временем запустите имеющийся у вас запрос. Выясните, сколько времени это займет, вместо того, чтобы гадать. Еще лучше было бы запустить его с включенным автопробегом и опубликовать результаты здесь, тогда мы могли бы помочь вам немного доработать его.

Делает ли эта работа то, что вы хотите, и обеспечивает ли она лучшую производительность? (Я просто подумал, что мог бы добавить это в качестве предложения).

 select * 
from group g
where (select count(*) from event e where g.groupid = e.groupid) <> 5
 

Как насчет аналитического:

 SELECT * FROM (
SELECT eventid, groupid, typeid, COUNT(groupid) OVER (PARTITION BY groupid) group_count
  FROM event
)
  WHERE group_count <> 5