查重、去重实验

最新推荐文章于 2024-03-02 12:32:16 发布

cugjwku419416

最新推荐文章于 2024-03-02 12:32:16 发布

阅读量184

点赞数

创建测试表，初始化数据
GM_DBA@ENMOEDU> select * from t1;

ID NUM
---------- ----------
1 1
2 2
2 3
3 4
3 5
3 6
4 7
4 8
4 9
4 10
5 11
5 12
6 13

13 rows selected.

1.1 查重 distinct unique
GM_DBA@ENMOEDU> select distinct id from t1 group by ID;//unique等同于distinct

ID
----------
1
6
2
4
5
3

6 rows selected.

1.2 查重 max、min函数

GM_DBA@ENMOEDU> select * from t1 a GM_DBA@ENMOEDU> select * from t1 a
2 where a.rowid <> 2 where a.rowid <>
3 (select max(rowid) from t1 b 3 (select min(rowid) from t1 b
4 where a.id=b.id); 4 where a.id=b.id);

ID NUM ID NUM
---------- ---------- ---------- ----------
2 2 2 3
3 4 3 5
3 5 3 6
4 7 4 8
4 8 4 9
4 9 4 10
5 11 5 12

7 rows selected. 7 rows selected.
去重用delete 即可 delete from...

// 1.3 查重 group by

// GM_DBA@ENMOEDU> select id from t1
2 where id in (select id from t1 group by id having count(id)>1);

ID
----------
2
2
3
3
3
4
4
4
4
5
5

11 rows selected. //列出了所有的重复id

GM_DBA@ENMOEDU> select id from t1
where id not in (select id from t1 group by id having count(id)>1);

ID
----------
1
6 //列出了不重复的id \\

1.4 分析函数

查询重复记录的位置
GM_DBA@ENMOEDU> select a.id,
2 a.num,
3 ROW_NUMBER () OVER (PARTITION BY a.id ORDER BY a.ROWID) rn
4 from t1 a
5 /

ID NUM RN
---------- ---------- ----------
1 1 1
2 2 1
2 3 2
3 4 1
3 5 2
3 6 3
4 7 1
4 8 2
4 9 3
4 10 4
5 11 1
5 12 2
6 13 1

13 rows selected.

进一步查询
GM_DBA@ENMOEDU> select b.id,b.num
2 from (select a.id,a.num,
3 ROW_NUMBER () OVER (PARTITION BY a.id ORDER BY a.ROWID) rn
4 from t1 a) b
5 where b.rn>1
6 /

ID NUM
---------- ----------
2 3
3 5
3 6
4 8
4 9
4 10
5 12

7 rows selected.

①利用rowid构造delete语句，删除重复数据

GM_DBA@ENMOEDU> delete from t1 where rowid in (
2 select rowid
3 from (select a.id,a.num,
4 ROW_NUMBER () OVER (PARTITION BY a.id ORDER BY a.ROWID) rn
5 from t1 a) b
6 where b.rn > 1
7 )
8 /

7 rows deleted.

②创建中间表 GM ，drop table t1,alter table GM rename to t1;

GM_DBA@ENMOEDU> create table GM as
2 select b.id,b.num
3 from (select a.id,a.num,
4 row_number () over (partition by a.id order by a.rowid) rn
5 from t1 a) b
6 where b.rn=1
7 /

Table created.
GM_DBA@ENMOEDU> select * from GM;

ID NUM
---------- ----------
1 1
2 2
3 4
4 7
5 11
6 13

6 rows selected.

执行计划
GM_DBA@ENMOEDU> select * from t1 a
2 where a.rowid <>
3 (select min(rowid) from t1 b
4 where a.id=b.id);

Execution Plan
----------------------------------------------------------
Plan hash value: 2626881942

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 6 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | T1 | 1 | 38 | 3 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 25 | | |
|* 4 | TABLE ACCESS FULL| T1 | 1 | 25 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A".ROWID<> (SELECT MIN(ROWID) FROM "T1" "B" WHERE
"B"."ID"=:B1))
4 - filter("B"."ID"=:B1)

GM_DBA@ENMOEDU> select b.id,b.num
2 from (select a.id,a.num,
3 ROW_NUMBER () OVER (PARTITION BY a.id ORDER BY a.ROWID) rn
4 from t1 a) b
5 where b.rn>1
6 /

Execution Plan
----------------------------------------------------------
Plan hash value: 2273146475

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 4 (25)| 00:00:01 |
|* 1 | VIEW | | 1 | 39 | 4 (25)| 00:00:01 |
| 2 | WINDOW SORT | | 1 | 38 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 1 | 38 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("B"."RN">1)

GM_DBA is me!

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29319205/viewspace-1062068/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29319205/viewspace-1062068/