代码之家 › 专栏 › 技术社区 › FrankS

SQL查找重复条目(在组内)

join oracle sql

FrankS · 技术社区 · 16 年前

SQL查找重复条目(在组内)

在表“EVENT”中,我们有大约160k个条目,每个事件都有一个GROUPID,而一个普通条目正好有5行具有相同的GROUPID。由于一个bug,我们目前得到了两个重复的条目(重复,因此10行而不是5行,只是一个不同的EVENTID。这可能会改变,所以它只是<>5行)。我们需要过滤这些组的所有条目。

由于对数据库的访问有限,我们不能使用临时表,也不能向GROUPID列添加索引以加快访问速度。

我们可以通过这个查询获得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和160k条目的索引,这将花费太长的时间。尝试考虑一个可以处理此问题的联接,但到目前为止找不到好的解决方案。

小编辑: 我们这里没有100%的重复项,因为每个条目仍然有一个唯一的ID,而GROUPID也不是唯一的(这就是为什么我们需要使用“groupby”)——或者我只是错过了一个简单的解决方案:)

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

我们将更频繁地运行它进行测试,以发现错误并检查它是否再次发生。

7 回复 | 直到 14 年前

David Aldridge 16 年前

分析查询要解决的一个经典问题:

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

Walter Mitty 16 年前

您可以使用联接而不是子查询来获得答案

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上的索引,这将运行得更快。让查询运行得更快的好处与维护另一个索引的成本之间的平衡取决于DBA。

如果DBA决定不使用索引,请确保适当的人员理解是索引策略,而不是您编写查询的方式减慢了速度。

Tony Andrews 16 年前

SQL实际需要多长时间?我想您只需要运行一次,首先修复了导致损坏的bug?我刚刚设置了一个如下的测试用例:

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秒。好的,我的桌子的行数是你的一半,但160K不是很大。我添加了filler列以使表占用一些磁盘空间。自动跟踪执行计划为:

-------------------------------------------------------------------------
| 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)|
-------------------------------------------------------------------------

paxdiablo 16 年前

能够添加索引。

Michael OShea 16 年前

从SQL的角度来看,我认为您已经回答了自己的问题。您描述的方法(即使用子选择)很好,如果任何其他编写查询的方法在性能上有很大差异,我会感到惊讶。

160K唱片对我来说并不多。我可以理解,如果您对某个应用程序代码中的查询的性能不满意,但从它的声音来看,您只是将其用作某些数据清理练习的一部分。(因此,我们希望您在性能方面更加宽容一些)。

即使没有任何支持索引,它仍然只是对160K行的两次完整的表扫描,坦白地说,我希望在某种模糊合理的时间内执行。

与数据库管理员交谈。他们帮助制造了问题,所以让他们成为解决方案的一部分。

/编辑/同时,运行您的查询。找出需要多长时间,而不是猜测。更好的办法是在启用“自动跟踪”的情况下运行它,并将结果发布在此处,这样我们就可以帮助您对其进行一些改进。

ilitirit 16 年前

这项工作是否符合您的要求,是否提供了更好的性能?(我只是想把它当作一个建议)。

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

Dave Costa 16 年前

那么一份分析报告如何:

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