【SQL实用技巧】-- 数据重复问题

数据重复问题

首先需要明确数据重复了，判断其是正常还是异常，这取决于业务上的要求，并不绝对！

如果表格中有重复记录，现需要转换成唯一不重复的记录，有3种常规做法，分别是 distinct，group by 和 row_number

数据准备

一张表名为 play_power，内容如下：

用户在不同时间的战力值可能是不一样的，需要解决一下三个问题：

1. 得到不重复的 Vopenid
2. 得到不重复的 Vopenid和日期（年月日不需要小时）
3. 用sql写出6月6日每个玩家的最后一条战力值

create table play_power (
    Vopenid varchar(20) COMMENT '用户id',
    ddatetime varchar(20) COMMENT '日期',
    power int COMMENT '战力值'
) comment '游戏用户战力值表';
insert into play_power
values ("小a", "2017060506", 100),
    ("小a", "2017060608", 112),
    ("小a", "2017060611", 118),
    ("小b", "2017060606", 188),
    ("小b", "2017060606", 188),
    ("小b", "2017060506", 99),
    ("小c", "2017060708", 199),
    ("小c", "2017060606", 177),
    ("小c", "2017060506", 99);
select * from play_power;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

实现

解决方法如下：

1. -- 方法1： 用distinct
   SELECT DISTINCT Vopenid FROM play_power
   -- 方法2： 用gruopby
   SELECT `Vopenid` FROM play_power GROUP BY `Vopenid`
   1
   2
   3
   4

2. SELECT DISTINCT Vopenid, substr(ddatetime,1,8) ddate FROM play_power
   -- distinct 后跟多个字段能够联合去重，把多个字段想象联合成一个字段
   1
   2

3. -- 此时就需要用到窗口类函数
   select Vopenid, ddatetime, power
   from (select Vopenid,
                ddatetime,
                power,
                row_number() over (partition by Vopenid order by ddatetime desc ) as rn
         from play_power
         where substr(ddatetime, 1, 8) = "20170606") t
   where rn = 1;
   1
   2
   3
   4
   5
   6
   7
   8
   9

总结

非要比较 MySQL 中的 distinct 和 group by 差异的话，则如下：

1、在 Mysql5.0 版本， distinct 效率高于 group by 。原因是 distinct 和 group by 都会进行分组操作，但 group by 可能会进行隐式排序，排序会额外消耗性能，导致 sql 执行效率低下。

2、从 Mysql8.0 版本以后，两者执行的效率差不多是一样的。

3、如果需求复杂，则推荐使用 group by ，它更加灵活、可结合 having 进行数据过滤。

4、如果逻辑简单，仅单纯去重的话， 则 distinct 语法更简洁。

row_number 比上面 2 个更灵活，它需要套一层子查询。上面 distinct ， group by 的结果只能保留几个去重的字段。而 row_number 的结果除此以外还能保留其他所有的非去重字段

题外话: 维度与指标

维度和指标是一对兄弟，他俩通常一起出现。看上图，当用 group by 时， group by 后放的就是维度(字段)，select 后面既可以放维度，也可以放指标。指标一般是用 sum 或 count 或 max 或 min 或 avg 统计的度量数值。

举个简单例子：

select sex,          -- 性别
       count(id) cnt -- 人数
from student -- 学生信息表
group by sex；-- 按性别(维度)分组
1
2
3
4

上述 SQL 的意思很直白，就是按性别这个维度(视角、角度)，来分组，统计每组内人数量这个指标。

无论上面这个例子 SQL，还是上述案例的 SQL 答案， group by 后接的就是维度(字段)。而且 group by 后有什么字段， 则 select 后就最多放这些字段。比如如果 group by 后只有维度字段 Vopenid，则 select 后可以放 Vopenid 字段，（对大多数数据库来说）而不能放其他陌生字段，否则查询会报错。此时 select 后不能放 ddatetime、power 字段，因为 group by 后没有它俩儿，你没有按它俩儿维度分组。

聊完维度再聊指标，select 后还可以放一些指标字段，比如 sum(power) as powers 、max(power) as mp 等等。

那如果 select 后只放维度字段，不放任何指标字段呢？则它就只是分组而已，因为各组是不会重复的，所以 group by 操作后自动具有去重功能。