一、關(guān)于mysql表中數(shù)據(jù)重復(fù)

SQL如何刪除重復(fù)的記錄，只保留一條？這是面試題中容易出現(xiàn)的一個問題，也是我們清理數(shù)據(jù)庫臟數(shù)據(jù)會遇到的問題。

關(guān)于刪除mysql表中重復(fù)數(shù)據(jù)問題，本文中給到兩種辦法：聚合函數(shù)、窗口函數(shù)row_number()的方法。
(注意：MySQL從8.0開始支持窗口函數(shù))

測試數(shù)據(jù)準(zhǔn)備：首先創(chuàng)建一個測試表test，插入一些測試數(shù)據(jù)，模擬一些重復(fù)數(shù)據(jù)（最終目標(biāo)：刪除重復(fù)數(shù)據(jù)，但不處理null行）

先查詢下重復(fù)數(shù)據(jù)，確認(rèn)待處理數(shù)據(jù)的數(shù)量，然后開始處理：

SELECT
        seq_id,
        out_user_code,
        COUNT( out_user_code ) count
FROM
        test
WHERE
        is_deleted = 0
        AND out_user_code IS NOT NULL
GROUP BY
        out_user_code
HAVING
        count( out_user_code )> 1

二、聚合函數(shù)min(id)+not in

思路：首先通過子查詢?nèi)〕?id 最小的不重復(fù)行,然后通過 not in 刪除重復(fù)數(shù)據(jù)

1、首先查詢一下 id 最小的不重復(fù)行（我們留下最早插入的數(shù)據(jù)，后面的重復(fù)數(shù)據(jù)都刪除）:

SELECT
    min(seq_id) seq_id,
	out_user_code,
	COUNT( out_user_code ) count 
FROM
	test 
GROUP BY
	out_user_code

2、通過查詢結(jié)果可知，重復(fù)的數(shù)據(jù)行seq_id為2、7的數(shù)據(jù)過濾掉了，接下來NOT IN 操作應(yīng)該刪除2、7重復(fù)數(shù)據(jù)行。那按照假設(shè)想法執(zhí)行NOT IN：

DELETE from test where r.seq_id not in (
	SELECT
	min(t.seq_id) seq_id
	FROM
		test  t
	GROUP BY
		t.out_user_code
)  r

會發(fā)現(xiàn)報錯：

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'r' at line 8, Time: 0.007000s

原因：不能先select出同一表中的某些值，再update這個表(在同一語句中)，即不能依據(jù)某字段值做判斷再來更新某字段的值。

解決方案：可將SELECT出的結(jié)果再通過中間表SELECT一遍。

3、最終處理sql：

DELETE from test where seq_id not in (
	SELECT r.seq_id from (
		SELECT
		   min(t.seq_id) seq_id
		FROM
			test  t
		GROUP BY
			t.out_user_code
	)  r 
) and out_user_code is not null

換種寫法（保證相關(guān)字段有索引）：

DELETE from test 
where
out_user_code in (select * from (select out_user_code from test del group by out_user_code HAVING count(out_user_code) >1)a)
and seq_id not in(select * from (select min(seq_id) id from test del group by out_user_code  HAVING count(out_user_code) >1)b
)f

提醒：能邏輯刪除盡量不要物理刪除。

三、窗口函數(shù)row_number()

思路：通過 PARTITION BY 對列進(jìn)行分區(qū)排序并生成序號列,然后將序號大于 1 的行刪除,row_number() over partition by。

1、分區(qū)查詢：

SELECT
	ROW_NUMBER() OVER ( PARTITION BY out_user_code ORDER BY seq_id ) num,
	out_user_code 
FROM
	test 
WHERE
	out_user_code IS NOT NULL

知識補(bǔ)充：
1、ROW_NUMBER:對結(jié)果集的輸出進(jìn)行編號，是運行查詢時計算出的臨時值。具體來說，返回結(jié)果集分區(qū)內(nèi)行的序列號，每個分區(qū)的第一行從 1 開始。
2、ROW_NUMBER() 具有不確定性。除非以下條件成立，否則不保證在每次執(zhí)行時，使用 ROW_NUMBER() 的查詢所返回行的順序都完全相同。
1）分區(qū)列的值是唯一的。
2）ORDER BY 列的值是唯一的。
3）分區(qū)列和 ORDER BY 列的值的組合是唯一的。

2、直接嘗試刪除num>1的數(shù)據(jù)：

DELETE a 
FROM (
	SELECT
	 ROW_NUMBER() OVER (PARTITION BY out_user_code ORDER BY seq_id) num 
	FROM test 
	where out_user_code IS NOT NULL
) a 
WHERE num>1

會發(fā)現(xiàn)報錯：
1288 - The target table a of the DELETE is not updatable, Time: 0.007000s

原因同上，同樣的我們換個方式處理一下。給窗口指定別名：WINDOW w AS (PARTITION BY 字段1 ORDER BY 字段2)

3、最終處理sql:

DELETE
FROM test
WHERE seq_id in (
	SELECT seq_id
	FROM(
		SELECT *
		FROM (
			SELECT ROW_NUMBER() OVER w AS row_num,seq_id
			FROM test  where out_user_code is not null
			WINDOW w AS (PARTITION BY out_user_code ORDER BY seq_id)
		)t
	    WHERE row_num >1
	)e
)

四、補(bǔ)充：常見的窗口函數(shù)

注：‘參數(shù)’列說明該函數(shù)是否可以加參數(shù)。“否”說明該函數(shù)的括號內(nèi)不可以加參數(shù)。
expr即可以代表字段，也可以代表在字段上的計算，比如sum(col)等。

窗口函數(shù)的一個概念是當(dāng)前行，當(dāng)前行屬于某個窗口，窗口由over關(guān)鍵字用來指定函數(shù)執(zhí)行的窗口范圍，如果后面括號中什么都不寫，則意味著窗口包含滿足where條件的所有行，窗口函數(shù)基于所有行進(jìn)行計算；如果不為空，則有三個參數(shù)來設(shè)置窗口：

partition by子句：按照指定字段進(jìn)行分區(qū)，兩個分區(qū)由邊界分隔，窗口函數(shù)在不同的分區(qū)內(nèi)分別執(zhí)行，在跨越分區(qū)邊界時重新初始化。
order by子句：按照指定字段進(jìn)行排序，窗口函數(shù)將按照排序后的記錄順序進(jìn)行編號。可以和partition by子句配合使用，也可以單獨使用。
frame子句：當(dāng)前分區(qū)的一個子集，用來定義子集的規(guī)則，通常用來作為滑動窗口使用。

關(guān)注我，分享技術(shù)干貨