Oracle删除重复数据

需求:删除table_name表中的重复数据;判定重复的标准为:item_1,item_2,item_3的值一样,保留最新的一条

BEGIN
  LOOP
    --手动开启本次会话的并行
    ALTER SESSION ENABLE PARALLEL DML;
    --删除分组后重复数据中ROWID最小的数据
    DELETE /*+ PARALLEL(8) */ FROM TABLE_NAME WHERE ROWID IN(
    SELECT /*+ PARALLEL(8) */ MIN(ROWID) FROM TABLE_NAME A
    GROUP BY A.ITEM_1,A.ITEM_2,A.ITEM_3
    HAVING COUNT(*)>1
   );
   COMMIT;
   --手动关闭本次会话的并行
   ALTER SESSION DIABLE PARALLEL DML;
   --如受影响行数等于0,则结束循环
   EXIT WHEN SQL%ROWCOUNT=0;
  END LOOP;
END;

以上逻辑是重复数据中留下ROWID最大的那条,如需要指定留下哪一条可参考如下:留下CREATE_TIME最新的一条 read more

Oracle创建表分区

1.建表时创建分区

CREATE TABLE TABLE_NAME
(
ID VARCHAR2(64) NOT NULL,
CREATE_TIME DATE NOT NULL
--省略其他字段
)
--根据CREATE_TIME字段按年分区
PARTITION BY RANGE(CREATE_TIME) INTERVAL (NUMTOYMINTERVAL(1,'YEAR'))
(
--创建默认分区,定义初始值为当前时间
PARTITION TABLE_NAME_P1 VALUES LESS THAN (TO_DATE('2019-12-04','YYYY-MM-DD'))
);

2.相关时间计算函数 read more

OracleSequence的创建和使用

1.介绍

Sequence是数据库系统按照一定的规则自动增加的数字序列,主要用于生成数据库数据记录。这个序列一般作为代理主键(因为不会重复)。
Sequence是数据中一个特殊存放等差数列的表,该表受数据库系统控制,任何时候数据库系统都可以根据当前记录数大小加上步长来获取到该表下一条记录应该是多少,这个表没有实际意义,常常用来做主键用。
Sequence是数据库系统的特性,有的数据库有Sequence,有的没有。比如Oracle、DB2、PostgreSQL数据库有Sequence,MySQL、SQL Server、Sybase等数据库则没有Sequence。 read more

Oracle递归

1.向下递归获取子节点

--根据二级获取下方的四五级节点
select * from table_name where level in('4','5')
start with id='二级节点ID'
connect by prior id=parent_id
--level:节点级别字段
--start with后定义根节点的位置
--connect by prior 本条ID等于下一条数据的parent_id

2.向上递归获取父节点 read more

Oracle over函数的简单使用

OVER() 在使用时一般会配合其他函数一起使用
1.row_number() OVER (PARTITION BY COL1 ORDER BY COL2)
表示根据COL1分组,在分组内部根据 COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号
2.rank() OVER (PARTITION BY COL1 ORDER BY COL2)
跳跃排序,有两个第二名时接下来就是第四名(同样是在各个分组内)
3.dense_rank() OVER (PARTITION BY COL1 ORDER BY COL2)
连续排序,有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的
4.lag(arg1,arg2,arg3)
arg1是从其他行返回的表达式;arg2是希望检索的当前行分区的偏移量;arg3是在arg2表示的数目超出了分组的范围时返回的值.
5.FIRST_VALUE(arg) OVER (PARTITION BY COL1 ORDER BY COL2)
返回组中数据窗口的第一个值,可以不进行order by
6.LAST_VALUE(arg) OVER (PARTITION BY COL1 ORDER BY COL2)
返回组中数据窗口的最后一个值,可以不进行order by

现在拿row_number举例:

--获取最新一条数据
select a.name from(
select name,row_number() over(partition by id order by create_date desc) as row_num
from table_name
) a where a.row_num=1;
* 根据id分组(相当于不分组),按照创建时间倒序,取第一条

row_number()多用于去重查重操作:即可根据某个字段进行排序,筛选出row_number()值大于1的

Oracle在不连表的情况下进行集合操作

--前提是两个查询的列的数量和数据类型必须完全相同

--最常用的,拼接结果集(取并集)
--去重拼接
SELECT ID FROM TABLE_A
UNION
SELECT ID FROM TABLE_B;

--不去重拼接
SELECT ID FROM TABLE_A
UNION ALL
SELECT ID FROM TABLE_B;

--取交集
SELECT ID FROM TABLE_A
INTERSECT
SELECT ID FROM TABLE_B;

--去差集
--先查第一条SQL结果集,看是否在第二条中,如果在则移除;
--再查第二条,如果在第一没有出现过,则移除
SELECT ID FROM TABLE_A
MINUS
SELECT ID FROM TABLE_B;