如何删除表中重复数据，仅保留需要的一条

假设有学生成绩表如下：

NAME	MONTH_TIMEKEY	CHINESE	MATHS	ENGLISH	PHYSICS	CHEMISTRY	BIOLOGY
张三	2023M01	72	62	67	65	89	71
张三	2023M02	80	70	83	79	76	97
张三	2023M03	68	90	63	94	70	93
张三	2023M04	86	86	95	72	69	72
张三	2023M05	73	96	99	83	95	82
张三	2023M05	73	96	99	83	95	82
李四	2023M02	86	61	70	61	90	90
李四	2023M03	74	93	73	90	84	66
李四	2023M04	99	89	61	86	73	86
李四	2023M05	88	94	77	99	66	79
李四	2023M06	68	63	85	65	66	75
李四	2023M06	68	63	85	65	66	75
王五	2023M03	69	94	64	95	67	96
王五	2023M04	91	61	94	83	91	74
王五	2023M05	66	74	82	74	77	97
王五	2023M06	96	84	82	87	83	70
王五	2023M07	98	81	78	91	69	76
王五	2023M07	98	81	78	91	69	76
赵六	2023M01	95	91	93	74	69	64
赵六	2023M02	90	64	77	94	63	95
赵六	2023M03	66	95	66	61	80	91
赵六	2023M04	96	67	76	82	87	75
赵六	2023M05	92	61	95	98	79	97
赵六	2023M05	92	61	95	98	79	97

先看看表中有没有重复数据，相同姓名和月份的数据视为重复数据

SELECT * FROM (
	SELECT A.*
	,COUNT(1) OVER (PARTITION BY A.NAME,MONTH_TIMEKEY) CNT
	FROM TEST_TAB_SCORE A
) B WHERE B.CNT>1 

输出：

NAME	MONTH_TIMEKEY	CHINESE	MATHS	ENGLISH	PHYSICS	CHEMISTRY	BIOLOGY	CNT
张三	2023M05	73	96	99	83	95	82	2
张三	2023M05	73	96	99	83	95	82	2
李四	2023M06	68	63	85	65	66	75	2
李四	2023M06	68	63	85	65	66	75	2
王五	2023M07	98	81	78	91	69	76	2
王五	2023M07	98	81	78	91	69	76	2
赵六	2023M05	92	61	95	98	79	97	2
赵六	2023M05	92	61	95	98	79	97	2

可见，表中包含重复数据。

那如何删除这些重复数据呢？使用distinct可以去除重复数据取出来，但无法删除原表中重复数据。

由于表中每一列具有维一值ROWID，即使重复数据的ROWID也不相同，因此，可以通过删除ROWID来删除指定行

--保留最新进入表的一行，即ROWID较大的一行
DELETE FROM TEST_TAB_SCORE A
WHERE EXISTS(
	SELECT 1 FROM (
		SELECT A.*
		,ROW_NUMBER() OVER (PARTITION BY A.NAME,MONTH_TIMEKEY ORDER BY ROWID DESC) RN 
		,ROWID ROW_ID
		FROM TEST_TAB_SCORE A
	) B WHERE A.ROWID = B.ROW_ID AND B.RN>1 
)

删除后数据如下：

NAME	MONTH_TIMEKEY	CHINESE	MATHS	ENGLISH	PHYSICS	CHEMISTRY	BIOLOGY
张三	2023M01	72	62	67	65	89	71
张三	2023M02	80	70	83	79	76	97
张三	2023M03	68	90	63	94	70	93
张三	2023M04	86	86	95	72	69	72
张三	2023M05	73	96	99	83	95	82
李四	2023M02	86	61	70	61	90	90
李四	2023M03	74	93	73	90	84	66
李四	2023M04	99	89	61	86	73	86
李四	2023M05	88	94	77	99	66	79
李四	2023M06	68	63	85	65	66	75
王五	2023M03	69	94	64	95	67	96
王五	2023M04	91	61	94	83	91	74
王五	2023M05	66	74	82	74	77	97
王五	2023M06	96	84	82	87	83	70
王五	2023M07	98	81	78	91	69	76
赵六	2023M01	95	91	93	74	69	64
赵六	2023M02	90	64	77	94	63	95
赵六	2023M03	66	95	66	61	80	91
赵六	2023M04	96	67	76	82	87	75
赵六	2023M05	92	61	95	98	79	97

可见重复行已删除。

如果我们每个姓名只想保留最新的一个月的数据，那相同姓名的数据即视为重复数据，也可以用上述方法进行删除，只需改变窗口函数的分组和排序条件即可。

--保留每个姓名最新一个月的数据
DELETE FROM TEST_TAB_SCORE A
WHERE EXISTS(
	SELECT 1 FROM (
		SELECT A.*
		,ROW_NUMBER() OVER (PARTITION BY A.NAME ORDER BY MONTH_TIMEKEY DESC) RN 
		,ROWID ROW_ID
		FROM TEST_TAB_SCORE A
	) B WHERE A.ROWID = B.ROW_ID AND B.RN>1 
)

删除后数据如下：

NAME	MONTH_TIMEKEY	CHINESE	MATHS	ENGLISH	PHYSICS	CHEMISTRY	BIOLOGY
张三	2023M05	73	96	99	83	95	82
李四	2023M06	68	63	85	65	66	75
王五	2023M07	98	81	78	91	69	76
赵六	2023M05	92	61	95	98	79	97

此方法也可用于保留指定条件数据的去重，例如保留倒数第三个月的数据，这里改用DENSE_RANK是因为，如果月份有数据重复(如本文最开始的表格)，用ROW_NUMBER会得到错误结果

--保留倒数第三个月的数据
DELETE FROM TEST_TAB_SCORE A
WHERE EXISTS(
	SELECT 1 FROM (
		SELECT A.*
		,DENSE_RANK() OVER (PARTITION BY A.NAME ORDER BY MONTH_TIMEKEY DESC) RN 
		,ROWID ROW_ID
		FROM TEST_TAB_SCORE A
	) B WHERE A.ROWID = B.ROW_ID AND B.RN<>3 
)

删除后数据如下：

NAME	MONTH_TIMEKEY	CHINESE	MATHS	ENGLISH	PHYSICS	CHEMISTRY	BIOLOGY
张三	2023M03	68	90	63	94	70	93
李四	2023M04	99	89	61	86	73	86
王五	2023M05	66	74	82	74	77	97
赵六	2023M03	66	95	66	61	80	91