中就比较麻烦了,BIRTHDAY三个字段值重复

作者: www.9159.com  发布:2019-11-05

需求描述:根据某一个字段或几个字段去重来显示任一条数据,第一条或最后一条。

  • distinct和group by 是一样的,查询去重,只能是全部重复的,也可以理解为针对单例,因为一行有一个字段不一样,他们就会认为这两行内容是不重复的。但是使用row_number()over这个函数就可以针对全部字段,完全重复还是部分重复都可以通过这个函数查找出来,因为它自身有分组的功能。以下就是具体代码:

    • www.9159.com 1www.9159.com 2
    • 上面那个表就是在最后两行时候ID,NAME,BIRTHDAY三个字段值重复,AGE字段值不一样。
    • SELECT DISTINCT ID,NAME,BIRTHDAY FROM B查询结果就是:
    • www.9159.com 3www.9159.com 4
    • 但是语句上加上AGE字段,SELECT DISTINCT ID,NAME,BIRTHDAY,AGE FROM B 结果就不一样了:
    • www.9159.com 5www.9159.com 6
    • 会发现DISTINCT函数没有起作用,GROUP BY 函数和DISTINCT一样的,只是使用GROUP BY函数时候这样写就不会错:
      • SELECT ID,NAME,AGE,BIRTHDAY FROM B GROUP BY ID,NAME,AGE,BIRTHDAY;
    • 换一种写法:SELECT ID,NAME,AGE,BIRTHDAY FROM B GROUP BY ID;就会报错,意思就是SELECT后面的跟几个字段,GROUP BY 后面也要跟几个字段,要不然就会报错。
  • 说一说ROW_NUMBER()OVER这个函数:

    • 删除一张表中重复数据,当你不知道那一个字段重复,或者你知道那一个字段重复,还有就是根据你的业务需求在用这个函数时候,因为这个函数在一定比较上还是有点麻烦,能不用就不用,但是呢这个函数内部有一个分组排序功能,也算是一种优化,像DISTINCT,不能随便用,成本高,效率低下,其实可以用group by 进行优化,其他的优化待续.......

    • ROW_NUMBER()OVER

      • DELETE FROM (SELECT ID,ROW_NUMBER()OVER(PARTITION BY ID ORDER BY NAME)ROW_NUM FROM B)WHERE ROW_NUM>1;--这是删除表中全部重复部分重复的字段,根据业务需求。在你知道具体字段值重复时候直接delete from tablename where条件就可以删除。
    • 使用ROW_NUMBER()OVER函数查询时候去重怎么操作:

      • select * from (select id,name,age,birthday, row_number() over (partition by id order by id)rank from b)where rank =1;
    • 原来的表数据是这样的:

    • www.9159.com 7www.9159.com 8

    • 执行上面代码后结果是这样的:

    • www.9159.com 9www.9159.com 10

    • 使用row_number()over函数进行删除部分或者全部重复数据的代码是这样的:

      • delete from (select id,name,age,birthday, row_number() over (partition by id order by id)rank from b)where rank >1;
    • 执行之前数据是这样的:

    • www.9159.com 11www.9159.com 12

    • 执行删除代码之后就是这样的:

    • www.9159.com 13www.9159.com 14
      * 会这一个基本就行了,删除全部重复,部分重复,查询全部重复,查询部分重复均可用这一个。

    • 其实个人对这个函数理解是:在查询时候,进行partition by 分组,order by 排序,然后把查询出来的结果集取个别名,可以把它当成一张表进行条件查询,别名rank就是分组之后那一列列名,新生的表包含rank这一个列,此时,就可以对这张分组排序好的表进行查询,最后查出来的结果就是去重的,无论全部重复或者部分重复均可使用。删除语句的道理和这个查询一模一样。

问题

在工作中常会遇到将数据分组排序的问题,如在考试成绩中,找出每个班级的前五名等。 
在orcale等数据库中可以使用partition 语句来解决,但在MySQL中就比较麻烦了。这次翻译的文章就是专门解决这个问题的

原文地址: How to select the first/least/max row per group in SQL

数据样式如下图:

翻译

在使用SQL的过程中,我们经常遇到这样一类问题:如何找出每个程序最近的日志条目?如何找出每个用户的最高分?在每个分类中最受欢迎的商品是什么?通常这类“找出每个分组中最高分的条目”的问题可以使用相同的技术来解决。在这篇文章里我将介绍如何解决这类问题,而且会介绍如何找出最高的前几名而不仅仅是第一名。

这篇文章会用到行数(row number),我在原来的文章 MySQL-specific 和 generic techniques 中已经提到过如何为每个分组设置行数了。在这里我会使用与原来的文章中相同的表格,但会加入新的price 字段

01 +--------+------------+-------+
02 | type   | variety    | price |
03 +--------+------------+-------+
04 | apple  | gala       |  2.79 |
05 | apple  | fuji       |  0.24 |
06 | apple  | limbertwig |  2.87 |
07 | orange | valencia   |  3.59 |
08 | orange | navel      |  9.36 |
09 | pear   | bradford   |  6.05 |
10 | pear   | bartlett   |  2.14 |
11 | cherry | bing       |  2.55 |
12 | cherry | chelan     |  6.33 |
13 +--------+------------+-------+

www.9159.com 15

选择每个分组中的最高分

这里我们要说的是如何找出每个程序最新的日志记录或审核表中最近的更新或其他类似的排序问题。这类问题在IRC频道和邮件列表中出现的越来越频繁。我使用水果问题来作为示例,在示例中我们要选出每类水果中最便宜的一个,我们期望的结果如下

1 +--------+----------+-------+
2 | type   | variety  | price |
3 +--------+----------+-------+
4 | apple  | fuji     |  0.24 |
5 | orange | valencia |  3.59 |
6 | pear   | bartlett |  2.14 |
7 | cherry | bing     |  2.55 |
8 +--------+----------+-------+

这个问题有几种解法,但基本上就是这两步:找出最低的价格,然后找出和这个价格同一行的其他数据

其中一个常用的方法是使用自连接(self-join),第一步根据type(apple, cherry etc)进行分组,并找出每组中price的最小值

01 select type, min(price) as minprice
02 from fruits
03 group by type;
04 +--------+----------+
05 | type   | minprice |
06 +--------+----------+
07 | apple  |     0.24 |
08 | cherry |     2.55 |
09 | orange |     3.59 |
10 | pear   |     2.14 |
11 +--------+----------+

第二步是将刚刚结果与原来的表进行连接。既然刚刚给结果已经被分组了,我们将刚刚的查询语句作为子查询以便于连接没有被分组的原始表格。

01 select f.type, f.variety, f.price
02 from (
03    select type, min(price) as minprice
04    from fruits group by type
05 ) as x inner join fruits as f on f.type = x.type and f.price = x.minprice;
06  
07 +--------+----------+-------+
08 | type   | variety  | price |
09 +--------+----------+-------+
10 | apple  | fuji     |  0.24 |
11 | cherry | bing     |  2.55 |
12 | orange | valencia |  3.59 |
13 | pear   | bartlett |  2.14 |
14 +--------+----------+-------+

还可以使用相关子查询(correlated subquery)的方式来解决。这种方法在不同的mysql优化系统下,可能性能会有一点点下降,但这种方法会更直观一些。

01 select type, variety, price
02 from fruits
03 where price = (select min(price) from fruits as f where f.type = fruits.type);
04 +--------+----------+-------+
05 | type   | variety  | price |
06 +--------+----------+-------+
07 | apple  | fuji     |  0.24 |
08 | orange | valencia |  3.59 |
09 | pear   | bartlett |  2.14 |
10 | cherry | bing     |  2.55 |
11 +--------+----------+-------+

这两种查询在逻辑上是一样的,他们性能也基本相同

 

找出每组中前N个值

这个问题会稍微复杂一些。我们可以使用聚集函数(MIN(), MAX()等等)来找一行,但是找前几行不能直接使用这些函数,因为它们都只返回一个值。但这个问题还是可以解决的。

这次我们找出每个类型(type)中最便宜的前两种水果,首先我们尝试

01 select type, variety, price
02 from fruits
03 where price = (select min(price) from fruits as f where f.type = fruits.type)
04    or price = (select min(price) from fruits as f where f.type = fruits.type
05       and price > (select min(price) from fruits as f2 where f2.type = fruits.type));
06 +--------+----------+-------+
07 | type   | variety  | price |
08 +--------+----------+-------+
09 | apple  | gala     |  2.79 |
10 | apple  | fuji     |  0.24 |
11 | orange | valencia |  3.59 |
12 | orange | navel    |  9.36 |
13 | pear   | bradford |  6.05 |
14 | pear   | bartlett |  2.14 |
15 | cherry | bing     |  2.55 |
16 | cherry | chelan   |  6.33 |
17 +--------+----------+-------+

是的,我们可以写成自连接(self-join)的形式,但是仍不够好(我将这个练习留给读者)。这种方式在N变大(前三名,前4名)的时候性能会越来越差。我们可以使用其他的表现形式编写这个查询,但是它们都不够好,它们都相当的笨重和效率低下。(译者注:这种方式获取的结果时,如果第N个排名是重复的时候最后选择的结果会超过N,比如上面例子还有一个apple价格也是0.24,那最后的结果就会有3个apple)

我们有一种稍好的方式,在每个种类中选择不超过该种类第二便宜的水果

1 select type, variety, price
2 from fruits
3 where (
4    select count(*) from fruits as f
5    where f.type = fruits.type and f.price <= fruits.price
6 ) <= 2;

这次的代码要优雅很多,而且在N增加时不需要重新代码(非常棒!)。但是这个查询在功能上和原来的是一样。他们的时间复杂度均为分组中条目数的二次方。而且,很多优化器都不能优化这种查询,使得它的耗时最好为全表行数的二次方(尤其在没有设置正确的索引时),而且数据量大时,可能将服务器会停止响应。那么还有更好的方法吗?有没有办法可以仅仅扫描一次数据,而不是通过子查询进行多次扫描。(译者注:这种方法有一个问题,就是如果排名并列第一的数字超过N后,这个分组会选不出数据,比如price为2.79的apple有3个,那么结果中就没有apple了)

尝试解决:

使用 UNION

如果已经为type, price设置了索引,而且在每个分组中去除的数据要多于包含的数据,一种非常高效的单次扫描的方法是将查询拆分成多个独立的查询(尤其对mysql,对其他的RDBMSs也有效),再使用UNION将结果拼到一起。mysql的写法如下:

1 (select * from fruits where type = 'apple' order by price limit 2)
2 union all
3 (select * from fruits where type = 'orange' order by price limit 2)
4 union all
5 (select * from fruits where type = 'pear' order by price limit 2)
6 union all
7 (select * from fruits where type = 'cherry' order by price limit 2)

Peter Zaistev写了相关的文章, 我在这里就不赘述了。如果这个方案满足你的要求,那它就是一个非常好的选择.

注意:这里要使用UNION ALL,而不是UNION。后者会在合并的时候会将重复的条目清除掉。在我们的这个示例中没有去除重复的需求,所以我们告诉服务器不要清除重复,清除重复在这个问题中是无用的,而且会造成性能的大幅下降。

--count(*)方法(只把条数为1条的显示出来了,超过1条全部过滤了)
select * from t4 where 自编条码 in 
(select 自编条码 from t4 group by 自编条码 having count(id)=1)

使用用户自定义变量

但结果是数据表中很小一部分条目并且有索引用来排序的时候,使用UNION的方式是一个很好的选择。而当你要获取数据表中大部分条目时也有一种能达到线性时间的方法,那就是使用用户定义变量。这里我将介绍的仅仅是mysql中的用法。在我原来的博客在mysql中,如何为条目编号(How to number rows in MySQL)里介绍了它是怎么工作的:

1 set @num := 0@type := '';
2 select type, variety, price
3 from (
4    select type, variety, price,
5       @num := if(@type = type, @num 11) as row_number,
6       @type := type as dummy
7   from fruits
8   order by type, price
9 ) as x where x.row_number <= 2;

这个方法并不仅仅做单次扫描,子查询在后台创建临时表,然后通过一次扫描将数据填充进去,然后在临时表中选择数据用于主查询的WHERE语句。但即使是两次扫描,它的时间复杂度仍是O(n),这里n是表示数据表的行数。它远比上面的相关子查询的结果O(n ^ 2)要好许多, 这里的n表示的是分组中平均条目数 - 即使是中等规模的数据也会造成极差的性能。(假设每种水果中有5 varitey,那么就需要25次扫描)

以上方法,会把仅1条记录的显示,但是重复的并没有保留其中一条,也过滤掉了。

在MySQL中一次扫描的方法

如果你无法放弃你头脑中优化查询的想法,你可以试试这个方法,它不使用临时表,并且只做一次扫描

1 set @num := 0@type := '';
2  
3 select type, variety, price,
4       @num := if(@type = type, @num 11) as row_number,
5       @type := type as dummy
6 from fruits
7 group by type, price, variety
8 having row_number <= 2;

只要MySQL的GROUP BY语句符合标准,这个方式在理论上就是是可行。那么实际上可行吗?下面是我在MySQL 5.0.7的Windows 版上的结果

01 +--------+----------+-------+------------+--------+
02 | type   | variety  | price | row_number | dummy  |
03 +--------+----------+-------+------------+--------+
04 | apple  | gala     |  2.79 |          1 | apple  |
05 | apple  | fuji     |  0.24 |          3 | apple  |
06 | orange | valencia |  3.59 |          1 | orange |
07 | orange | navel    |  9.36 |          3 | orange |
08 | pear   | bradford |  6.05 |          1 | pear   |
09 | pear   | bartlett |  2.14 |          3 | pear   |
10 | cherry | bing     |  2.55 |          1 | cherry |
11 | cherry | chelan   |  6.33 |          3 | cherry |
12 +--------+----------+-------+------------+--------+

可以看到,这已经和结果很接近了。他返回了每个分组的第一行和第三行,结果并没有按照price的升序进行排列。当时HAVING 语句要求row_number不应当大于2。接下来是5.0.24a 在ubuntu上的结果:

01 +--------+------------+-------+------------+--------+
02 | type   | variety    | price | row_number | dummy  |
03 +--------+------------+-------+------------+--------+
04 | apple  | fuji       |  0.24 |          1 | apple  |
05 | apple  | gala       |  2.79 |          1 | apple  |
06 | apple  | limbertwig |  2.87 |          1 | apple  |
07 | cherry | bing       |  2.55 |          1 | cherry |
08 | cherry | chelan     |  6.33 |          1 | cherry |
09 | orange | valencia   |  3.59 |          1 | orange |
10 | orange | navel      |  9.36 |          1 | orange |
11 | pear   | bartlett   |  2.14 |          1 | pear   |
12 | pear   | bradford   |  6.05 |          1 | pear   |
13 +--------+------------+-------+------------+--------+

这次,所有的row_number都是1,而且好像所有行都返回了。可以参考MySQL手册用户自定义变量。

使用这种技术的结果很难确定,主要是因为这里涉及的技术是你和我都不能直接接触的,例如MySQL在Group的时候使用哪个索引。如果你仍需要使用它

  • 我知道很多人已经用了,因为我告诉了他们 - 你还是可以用的。我们正在进入SQL的真正领域,但是上面的结果是在没有设置索引的情况下得到的。我们现在看看了设置了索引之后group的结果是什么。
1 alter table fruits add key(type, price);

执行之后会发现没有什么变化,之后使用EXPLAIN查看查询过程,会发现此查询没有使用任何索引。这是为什么呢?因为Group使用了3个字段,但是索引只有两个字段。实际上,查询仍使用了临时表,所有我们并没完成一次扫描的目标。我们可以强制使用索引:

1 set @num := 0@type := '';
2  
3 select type, variety, price,
4       @num := if(@type = type, @num 11) as row_number,
5       @type := type as dummy
6 from fruits force index(type)
7 group by type, price, variety
8 having row_number <= 2;

我们看一下是否起作用了。

01 +--------+----------+-------+------------+--------+
02 | type   | variety  | price | row_number | dummy  |
03 +--------+----------+-------+------------+--------+
04 | apple  | fuji     |  0.24 |          1 | apple  |
05 | apple  | gala     |  2.79 |          2 | apple  |
06 | cherry | bing     |  2.55 |          1 | cherry |
07 | cherry | chelan   |  6.33 |          2 | cherry |
08 | orange | valencia |  3.59 |          1 | orange |
09 | orange | navel    |  9.36 |          2 | orange |
10 | pear   | bartlett |  2.14 |          1 | pear   |
11 | pear   | bradford |  6.05 |          2 | pear   |
12 +--------+----------+-------+------------+--------+

现在我们得到了我们想要的结果了,而且没有文件排序(filesort)和临时表。还有一种方法就是将variety提出到GROUP BY之外,这样它就可以使用自己的索引。因为这个查询是一个从分组中查询非分组字段的查询,它只能在 ONLY_FULL_GROUP_BY 模式关闭(链接)的情况下才能起作用。但是在没有特殊原因的情况下,我不建议你这么做。

所以,我们需要变化一下,提供三种解决方法:

www.9159.com, 其他方法

可以在评论中看到其他的方法,里面有的确有一些非常梦幻的方法。我一直在你们的评论获取知识,感谢你们。

一、通过row_number按重复字段进行分组排序,然后显示第1条,采用AB表方式:

总结

我们这里介绍了集中方法去解决“每个分组中最大的条目”这类问题已经进一步扩展到查询每组中前N个条目的方法。之后我们深入探讨了一些MySQL特定的技术,这些技术看起来有一些傻和笨。但是如果你需要榨干服务器的最后一点性能,你就需要知道什么时候去打破规则。对于那些认为这是MySQL本身的问题的人,我要说这不是,我曾经看到过使用其他平台的人也在做着同样的事情,如SQL Server。在每个平台上都会有很多特殊的小技巧和花招,使用他们的人必须去适应它。

原文出处: http://my.oschina.net/u/1032146/blog/149300

--方法1 row_numer(),等值查询(即AB表查询) 
select  a.* from t4 a ,(
select id,自编条码,ROW_NUMBER() over(partition by 自编条码 order by id) as nid from t4 ) b
where a.id = b.id and b.nid=1

二、同方法一,通过row_number按重复字段进行分组排序,然后显示第1条。但采用子查询方式: 

select a.* from 
( select ROW_NUMBER()over (partition by 自编条码 order by id) as nid ,* from t4  ) a
where a.nid=1

三、通过分组取最小值或最大值为解决。

select * from t4 where id in (select min(id) from t4 group by 自编条码)

这种方法最简单,但对ID要求,必须是数字,能够进行MIN或MAX计算。如果没有纯数字ID,可以利用ROW_NUMBER先建立NID,再MIN。

select ROW_NUMBER()over (partition by 自编条码 order by id) as nid ,* from t4

以上三种方案效果相同。

 

本文由9159.com发布于www.9159.com,转载请注明出处:中就比较麻烦了,BIRTHDAY三个字段值重复

关键词:

上一篇:没有了
下一篇:没有了