1、使用distinct去重

distinct用來查詢不重復(fù)記錄的條數(shù)，用count(distinct id)來返回不重復(fù)字段的條數(shù)。用法注意：

• distinct【查詢字段】，必須放在要查詢字段的開頭，即放在第一個參數(shù)；
• 只能在SELECT 語句中使用，不能在 INSERT, DELETE, UPDATE 中使用；
• DISTINCT 表示對后面的所有參數(shù)的拼接取不重復(fù)的記錄，即查出的參數(shù)拼接每行記錄都是唯一的
• 不能與all同時使用，默認情況下，查詢時返回的就是所有的結(jié)果。

distinct支持單列、多列的去重方式。

• 作用于單列

單列去重的方式簡明易懂，即相同值只保留1個。

select distinct name from A //對A表的name去重然后顯示

• 作用于多列

多列的去重則是根據(jù)指定的去重的列信息來進行，即只有所有指定的列信息都相同，才會被認為是重復(fù)的信息。注意，distinct作用于多列的時候只在開頭加上即可，并不用每個字段都加上。distinct必須在開頭，在中間是不可以的，會報錯，`select id,distinct name from A //錯誤

select distinct id,name from A //對A表的id和name去重然后顯示

• 配合count使用

select count(distinct name) from A //對A表的不同的name進行計數(shù)

按順序去重時，order by 的列必須出現(xiàn)在 distinct 中

出錯代碼

改正后的代碼

討論：若不使用Distinct關(guān)鍵字，則order by后面的字段不一定要放在seletc中

MySQL中使用去重distinct方法的示例詳解
【Hive】數(shù)據(jù)去重

2、使用group by

GROUP BY 語句根據(jù)一個或多個列對結(jié)果集進行分組。在分組的列上我們可以使用 COUNT, SUM, AVG,等函數(shù)，形式為select 重復(fù)的字段名 from 表名 group by 重復(fù)的字段名;

group by 對age查詢結(jié)果進行了分組，自動將重復(fù)的項歸結(jié)為一組。

還可以使用count函數(shù)，統(tǒng)計重復(fù)的數(shù)據(jù)有多少個

3、使用ROW_NUMBER() OVER 或 GROUP BY 和 COLLECT_SET/COLLECT_LIST

說到要去重，自然會想到 DISTINCT，但是在 Hive SQL 里，它有兩個問題：

• DISTINCT 會以 SELECT 出的全部列作為 key 進行去重。也就是說，只要有一列的數(shù)據(jù)不同，DISTINCT 就認為是不同數(shù)據(jù)而保留。
• DISTINCT 會將全部數(shù)據(jù)打到一個 reducer 上執(zhí)行，造成嚴重的數(shù)據(jù)傾斜，耗時巨大。

3.1 ROW_NUMBER() OVER

DISTINCT 的兩個問題，用 ROW_NUMBER() OVER 可解。比如，如果我們要按 key1 和 key2 兩列為 key 去重，就會寫出這樣的代碼：

WITH temp_table AS (
  SELECT
    key1,
    key2,
    [columns]...,
    ROW_NUMBER() OVER (
      PARTITION BY key1, key2
      ORDER BY column ASC
    ) AS rn
  FROM
    table
)
 
SELECT
  key1,
  key2,
  [columns]...
FROM
  temp_table
WHERE
  rn = 1;

這樣，Hive 會按 key1 和 key2 為 key，將數(shù)據(jù)打到不同的 mapper 上，然后對 key1 和 key2 都相同的一組數(shù)據(jù)，按 column 升序排列，并最終在每組中保留排列后的第一條數(shù)據(jù)。借此就完成了按 key1 和 key2 兩列為 key 的去重任務(wù)。注意 PARTITION BY 在此起到的作用：

• 一是按 key1 和 key2 打散數(shù)據(jù)，解決上述問題 (2)；
• 二是與 ORDER BY 和 rn = 1 的條件結(jié)合，按 key1 和 key2 對數(shù)據(jù)進行分組去重，解決上述問題 (1)。

但顯然，這樣做十分不優(yōu)雅（not-elegant），并且不難想見其效率比較低。

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) as num 表示根據(jù) COL1分組，在分組內(nèi)部根據(jù) COL2排序，此函數(shù)計算的值num就表示每組內(nèi)部排序后的順序編號（組內(nèi)連續(xù)的唯一的)

3.2 GROUP BY 和 COLLECT_SET/COLLECT_LIST

ROW_NUMBER() OVER 解法的一個核心是利用 PARTITION BY 對數(shù)據(jù)按 key 分組，同樣的功能用 GROUP BY 也可以實現(xiàn)。但是，GROUP BY 需要與聚合函數(shù)搭配使用。我們需要考慮，什么樣的聚合函數(shù)能實現(xiàn)或者間接實現(xiàn)這樣的功能呢？不難想到有 COLLECT_SET 和 COLLECT_LIST。

SELECT
  key1,
  key2,
  [COLLECT_LIST(column)[1] AS column]...
FROM
  temp_table
GROUP BY
  key1, key2

對于 key1 和 key2 以外的列，我們用 COLLECT_LIST 將他們收集起來，然后輸出第一個收集進來的結(jié)果。這里使用 COLLECT_LIST 而非 COLLECT_SET 的原因在于 SET 內(nèi)是無序的，因此你無法保證輸出的 columns 都來自同一條數(shù)據(jù)。若對于此沒有要求或限制，則可以使用 COLLECT_SET，它會更節(jié)省資源。

相比前一種辦法，由于省略了排序和（可能的）落盤動作，所以效率會高不少。但是因為（可能）不落盤，所以 COLLECT_LIST 中的數(shù)據(jù)都會緩存在內(nèi)存當中。如果重復(fù)數(shù)量特別大，這種方法可能會觸發(fā) OOM。此時應(yīng)考慮將數(shù)據(jù)進一步打散，然后再合并；或者干脆換用前一種辦法。

distinct與group by的去重方面的區(qū)別

distinct簡單來說就是用來去重的，而group by的設(shè)計目的則是用來聚合統(tǒng)計的，兩者在能夠?qū)崿F(xiàn)的功能上有些相同之處，但應(yīng)該仔細區(qū)分。

單純的去重操作使用distinct，速度是快于group by的。

distinct支持單列、多列的去重方式。

單列去重的方式簡明易懂，即相同值只保留1個。

多列的去重則是根據(jù)指定的去重的列信息來進行，即只有所有指定的列信息都相同，才會被認為是重復(fù)的信息。

group by使用的頻率相對較高，但正如其功能一樣，它的目的是用來進行聚合統(tǒng)計的，雖然也可能實現(xiàn)去重的功能，但這并不是它的長項。

區(qū)別：

1）distinct只是將重復(fù)的行從結(jié)果中出去；

group by是按指定的列分組，一般這時在select中會用到聚合函數(shù)。

2）distinct是把不同的記錄顯示出來。

group by是在查詢時先把紀錄按照類別分出來再查詢。

group by 必須在查詢結(jié)果中包含一個聚集函數(shù)，而distinct不用。

distinct和group by有啥區(qū)別，大概總結(jié)以下幾點：

distinct適合查單個字段去重，支持單列、多列的去重方式。 單列去重的方式簡明易懂，即相同值只保留1個。 
多列的去重則是根據(jù)指定的去重的列信息來進行，即只有所有指定的列信息都相同，才會被認為是重復(fù)的信息。

而 group by 可以針對要查詢的全部字段中的部分字段去重，它的作用主要是：獲取數(shù)據(jù)表中以分組字段為依據(jù)的其他統(tǒng)計數(shù)據(jù)。

補充：MySQL中distinct和group by去重性能對比

前言

• MySQL：5.7.17
• 存儲引擎：InnoDB
• 實驗?zāi)康模罕疚闹饕獪y試在某字段有無索引、各種不同值個數(shù)情況下，記錄對此字段其使用DISTINCT/GROUP BY去重的查詢語句執(zhí)行時間，對比兩者在不同場景下的去重性能，實驗過程中關(guān)閉MySQL查詢緩存。
• 實驗表格：

實驗過程

1）創(chuàng)建測試表

表創(chuàng)建語句：

DROP TABLE IF EXISTS `tab_1`;
CREATE TABLE `tab_1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `value` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `tab_2`;
CREATE TABLE `tab_2` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `value` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_value` (`value`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `tab_3`;
CREATE TABLE `tab_3` LIKE `tab_1`;
DROP TABLE IF EXISTS `tab_4`;
CREATE TABLE `tab_4` LIKE `tab_2`;

2）生成測試數(shù)據(jù)

表數(shù)據(jù)插入過程：

DROP PROCEDURE IF EXISTS generateRandomData;
delimiter $$
-- tblName為插入表,field為插入字段,num為插入字段值上限,count為插入的記錄數(shù)
CREATE PROCEDURE generateRandomData(IN tblName VARCHAR(30),IN field VARCHAR(30),IN num INT UNSIGNED,IN count INT UNSIGNED)

BEGIN
    -- 聲明循環(huán)變量
    DECLARE i INT UNSIGNED DEFAULT 1;
    -- 循環(huán)插入隨機整數(shù)1~num,共插入count條數(shù)據(jù)
    w1:WHILE i<=count DO
        set i=i+1;
        set @val = FLOOR(RAND()*num+1);
        set @statement = CONCAT('INSERT INTO ',tblName,'(`',field,'`) VALUES(',@val,')');
        PREPARE stmt FROM @statement;
        EXECUTE stmt;
    END WHILE w1;
END $$
delimiter ;

調(diào)用過程隨機生成測試數(shù)據(jù)：

call generateRandomData('tab_1','value',3,100000);
INSERT INTO tab_2 SELECT * FROM tab_1;
call generateRandomData('tab_3','value',10000,100000);
INSERT INTO tab_4 SELECT * FROM tab_3;

3）執(zhí)行查詢語句，記錄執(zhí)行時間

查詢語句及對應(yīng)執(zhí)行時間如下：

SELECT DISTINCT(`value`) FROM tab_1;
SELECT `value` FROM tab_1 GROUP BY `value`;
SELECT DISTINCT(`value`) FROM tab_2;
SELECT `value` FROM tab_2 GROUP BY `value`;
SELECT DISTINCT(`value`) FROM tab_3;
SELECT `value` FROM tab_3 GROUP BY `value`;
SELECT DISTINCT(`value`) FROM tab_4;
SELECT `value` FROM tab_4 GROUP BY `value`;

4）實驗結(jié)果

實驗結(jié)論

MySQL 5.7.17中使用distinct和group by進行去重時，性能相差不大

使用去重distinct方法的示例詳解

一 distinct

含義：distinct用來查詢不重復(fù)記錄的條數(shù),即distinct來返回不重復(fù)字段的條數(shù)（count(distinct id)）,其原因是distinct只能返回他的目標字段，而無法返回其他字段

用法注意：

1.distinct【查詢字段】，必須放在要查詢字段的開頭，即放在第一個參數(shù)；

2.只能在SELECT 語句中使用，不能在 INSERT, DELETE, UPDATE 中使用；

3.DISTINCT 表示對后面的所有參數(shù)的拼接取 不重復(fù)的記錄，即查出的參數(shù)拼接每行記錄都是唯一的

4.不能與all同時使用，默認情況下，查詢時返回的就是所有的結(jié)果。

1.1只對一個字段查重

對一個字段查重，表示選取該字段一列不重復(fù)的數(shù)據(jù)。

示例表：psur_list

PLAN_NUMBER字段去重，語句：

SELECT DISTINCT PLAN_NUMBER FROM psur_list;

結(jié)果如下：

1.2多個字段去重

對多個字段去重，表示選取多個字段拼接的一條記錄，不重復(fù)的所有記錄

示例表：psur_list

PLAN_NUMBER和PRODUCT_NAME字段去重，語句：

SELECT DISTINCT PLAN_NUMBER,PRODUCT_NAME FROM psur_list;

結(jié)果如下：

期望結(jié)果：只對第一個參數(shù)PLAN_NUMBER取唯一值

解決辦法一：使用group_concat 函數(shù)

語句：

SELECT GROUP_CONCAT(DISTINCT PLAN_NUMBER) AS PLAN_NUMBER,PRODUCT_NAMEFROM psur_list GROUP BY PLAN_NUMBER

解決辦法二：使用group by

語句：

SELECT PLAN_NUMBER,PRODUCT_NAME FROM psur_list GROUP BY PLAN_NUMBER

結(jié)果如下：

1.3針對null處理

distinct不會過濾掉null值，返回結(jié)果包含null值

表psur_list如下：

對COUNTRY字段去重，語句：

SELECT DISTINCT COUNTRY FROM psur_list

結(jié)果如下：

1.4與distinctrow同義

語句：

SELECT DISTINCTROW COUNTRY FROM psur_list

結(jié)果如下：

二 聚合函數(shù)中使用distinct

在聚合函數(shù)中DISTINCT 一般跟 COUNT 結(jié)合使用。count（）會過濾掉null項

語句：

SELECT COUNT(DISTINCT COUNTRY) FROM psur_list

結(jié)果如下：【實際包含null項有4個記錄，執(zhí)行語句后過濾null項，計算為3】

總結(jié)

到此這篇關(guān)于SQL數(shù)據(jù)去重的3種方法的文章就介紹到這了,更多相關(guān)SQL數(shù)據(jù)去重方法內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論