mysql代替distinct_mysql GROUP BY 代替DISTINCT 遇到的问题及解决

最新推荐文章于 2023-05-15 06:00:52 发布
最新推荐文章于 2023-05-15 06:00:52 发布
阅读量918 收藏 1
点赞数
文章标签： mysql代替distinct
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_26833939/article/details/113994063
版权

近日在做一个数据分析时，发现DISTINCT比较慢，想通过group by来替代。然而替代并非一帆风顺，在替代过程中，发现对于重复数据，group by会取第一次出现的记录。为得到我想要的统计数据，折腾了一番。下面用实例来描述我的问题及调整过程。

场景：有一张表，记录手机用户的常规信息，每天每个用户一条记录

CREATE TABLE `userinfo_test` (

`day` date NOT NULL DEFAULT '2016-06-01',

`username` varchar(64) NOT NULL,

`phone` varchar(16) NOT NULL DEFAULT '',

`cv` varchar(16) NOT NULL DEFAULT '',

PRIMARY KEY (`day`,`username`),

KEY `ix_day_username_phone_cv` (`day`,`username`,`phone`,`cv`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

现在需要统计每个月，每个机型每个版本的活跃人数(对于同一用户，如果升级了版本，只统计升级后的版本)

INSERT INTO `userinfo_test` VALUES('2016-05-01','10000001','A', '1001');

INSERT INTO `userinfo_test` VALUES('2016-05-01','10000002','A', '1002');

INSERT INTO `userinfo_test` VALUES('2016-05-01','10000003','A', '1003');

INSERT INTO `userinfo_test` VALUES('2016-05-01','10000004','B', '1001');

INSERT INTO `userinfo_test` VALUES('2016-05-01','10000005','B', '1002');

INSERT INTO `userinfo_test` VALUES('2016-05-01','10000006','B', '1003');

INSERT INTO `userinfo_test` VALUES('2016-05-01','10000007','C', '1001');

INSERT INTO `userinfo_test` VALUES('2016-05-01','10000008','C', '1002');

INSERT INTO `userinfo_test` VALUES('2016-05-01','10000009','C', '1003');

INSERT INTO `userinfo_test` VALUES('2016-05-02','10000001','A', '1001');

INSERT INTO `userinfo_test` VALUES('2016-05-02','10000002','A', '1003');

INSERT INTO `userinfo_test` VALUES('2016-05-02','10000003','A', '1003');

INSERT INTO `userinfo_test` VALUES('2016-05-02','10000004','B', '1002');

INSERT INTO `userinfo_test` VALUES('2016-05-02','10000005','B', '1002');

INSERT INTO `userinfo_test` VALUES('2016-05-02','10000006','B', '1003');

INSERT INTO `userinfo_test` VALUES('2016-05-02','10000007','C', '1003');

INSERT INTO `userinfo_test` VALUES('2016-05-02','10000008','C', '1002');

INSERT INTO `userinfo_test` VALUES('2016-05-02','10000009','C', '1003');

记录每天的用户常规信息，若当天信息有变化，只记录最后的信息，每个用户每天只有一条记录信息

mysql> SELECT * FROM `userinfo_test`;

+------------+----------+-------+------+

| day        | username | phone | cv   |

+------------+----------+-------+------+

| 2016-05-01 | 10000001 | A     | 1001 |

| 2016-05-01 | 10000002 | A     | 1002 |

| 2016-05-01 | 10000003 | A     | 1003 |

| 2016-05-01 | 10000004 | B     | 1001 |

| 2016-05-01 | 10000005 | B     | 1002 |

| 2016-05-01 | 10000006 | B     | 1003 |

| 2016-05-01 | 10000007 | C     | 1001 |

| 2016-05-01 | 10000008 | C     | 1002 |

| 2016-05-01 | 10000009 | C     | 1003 |

| 2016-05-02 | 10000001 | A     | 1001 |

| 2016-05-02 | 10000002 | A     | 1003 |

| 2016-05-02 | 10000003 | A     | 1003 |

| 2016-05-02 | 10000004 | B     | 1002 |

| 2016-05-02 | 10000005 | B     | 1002 |

| 2016-05-02 | 10000006 | B     | 1003 |

| 2016-05-02 | 10000007 | C     | 1003 |

| 2016-05-02 | 10000008 | C     | 1002 |

| 2016-05-02 | 10000009 | C     | 1003 |

| 2016-05-02 | 10000019 | C     | 1003 |

+------------+----------+-------+------+

19 rows in set (0.00 sec)

汇总每天各机型各版本的人数

SELECT IFNULL(phone,'WITH_ROLLUP_TOTAL') p, IFNULL(cv,'WITH_ROLLUP_TOTAL') v, COUNT(cv)daynum FROM `userinfo_test` WHERE DAY = '2016-05-01' GROUP BY phone,cv WITH ROLLUP;

mysql> SELECT IFNULL(phone,'WITH_ROLLUP_TOTAL') p, IFNULL(cv,'WITH_ROLLUP_TOTAL') v, COUNT(cv)daynum FROM `userinfo_test` WHERE DAY = '2016-05-01' GROUP BY phone,cv WITH ROLLUP;

+-------------------+-------------------+--------+

| p                 | v                 | daynum |

+-------------------+-------------------+--------+

| A                 | 1001              |      1 |

| A                 | 1002              |      1 |

| A                 | 1003              |      1 |

| A                 | WITH_ROLLUP_TOTAL |      3 |

| B                 | 1001              |      1 |

| B                 | 1002              |      1 |

| B                 | 1003              |      1 |

| B                 | WITH_ROLLUP_TOTAL |      3 |

| C                 | 1001              |      1 |

| C                 | 1002              |      1 |

| C                 | 1003              |      1 |

| C                 | WITH_ROLLUP_TOTAL |      3 |

| WITH_ROLLUP_TOTAL | WITH_ROLLUP_TOTAL |      9 |

+-------------------+-------------------+--------+

13 rows in set (0.00 sec)

mysql> SELECT IFNULL(phone,'WITH_ROLLUP_TOTAL') p, IFNULL(cv,'WITH_ROLLUP_TOTAL') v, COUNT(cv)daynum FROM `userinfo_test` WHERE DAY = '2016-05-02' GROUP BY phone,cv WITH ROLLUP;

+-------------------+-------------------+--------+

| p                 | v                 | daynum |

+-------------------+-------------------+--------+

| A                 | 1001              |      1 |

| A                 | 1003              |      2 |

| A                 | WITH_ROLLUP_TOTAL |      3 |

| B                 | 1002              |      2 |

| B                 | 1003              |      1 |

| B                 | WITH_ROLLUP_TOTAL |      3 |

| C                 | 1002              |      1 |

| C                 | 1003              |      3 |

| C                 | WITH_ROLLUP_TOTAL |      4 |

| WITH_ROLLUP_TOTAL | WITH_ROLLUP_TOTAL |     10 |

+-------------------+-------------------+--------+

10 rows in set (0.00 sec)

汇总每月各机型各版本的人数，若该月同一人有多个机型版本信息，则以最后记录的机型版本信息为准，只汇总这一条记录

SELECT IFNULL(phone,'WITH_ROLLUP_TOTAL') p, IFNULL(cv,'WITH_ROLLUP_TOTAL') v,COUNT(cv) monthnum FROM (SELECT phone,cv FROM (SELECT * FROM (SELECT cv,phone,username FROM `userinfo_test` GROUP BY cv,phone,username) c ORDER BY cv DESC) d GROUP BY username) e GROUP

BY phone,cv WITH ROLLUP;

+-------------------+-------------------+----------+

| p                 | v                 | monthnum |

+-------------------+-------------------+----------+

| A                 | 1001              |        1 |

| A                 | 1003              |        2 |

| A                 | WITH_ROLLUP_TOTAL |        3 |

| B                 | 1002              |        2 |

| B                 | 1003              |        1 |

| B                 | WITH_ROLLUP_TOTAL |        3 |

| C                 | 1002              |        1 |

| C                 | 1003              |        3 |

| C                 | WITH_ROLLUP_TOTAL |        4 |

| WITH_ROLLUP_TOTAL | WITH_ROLLUP_TOTAL |       10 |

+-------------------+-------------------+----------+

10 rows in set (0.00 sec)

注意，上述sql 对cv 做了降序排列(考虑到通常都是升级了版本，即认为版本越大，代表了同一用户的最后的记录)，group by 默认统计第一次出现的记录

如果不对cv做降序

SELECT IFNULL(phone,'WITH_ROLLUP_TOTAL') p, IFNULL(cv,'WITH_ROLLUP_TOTAL') v,COUNT(cv) monthnum FROM (SELECT phone,cv,username FROM `userinfo_test` GROUP BY username) c GROUP BY phone,cv WITH ROLLUP;

+-------------------+-------------------+----------+

| p                 | v                 | monthnum |

+-------------------+-------------------+----------+

| A                 | 1001              |        1 |

| A                 | 1002              |        1 |

| A                 | 1003              |        1 |

| A                 | WITH_ROLLUP_TOTAL |        3 |

| B                 | 1001              |        1 |

| B                 | 1002              |        1 |

| B                 | 1003              |        1 |

| B                 | WITH_ROLLUP_TOTAL |        3 |

| C                 | 1001              |        1 |

| C                 | 1002              |        1 |

| C                 | 1003              |        2 |

| C                 | WITH_ROLLUP_TOTAL |        4 |

| WITH_ROLLUP_TOTAL | WITH_ROLLUP_TOTAL |       10 |

+-------------------+-------------------+----------+

13 rows in set (0.00 sec)

可以看到，对于同一用户，统计到的是第一次出现的记录。

如果用DISTINCT,下面的sql并不能得到我们想要的数据。

此外对于重复数据比较多时，group by要比DISTINCT快很多

SELECT IFNULL(phone,'WITH_ROLLUP_TOTAL') p, IFNULL(cv,'WITH_ROLLUP_TOTAL') v, COUNT(DISTINCT username)daynum  FROM `userinfo_test` GROUP BY phone,cv WITH ROLLUP;

+-------------------+-------------------+--------+

| p                 | v                 | daynum |

+-------------------+-------------------+--------+

| A                 | 1001              |      1 |

| A                 | 1002              |      1 |

| A                 | 1003              |      2 |

| A                 | WITH_ROLLUP_TOTAL |      3 |

| B                 | 1001              |      1 |

| B                 | 1002              |      2 |

| B                 | 1003              |      1 |

| B                 | WITH_ROLLUP_TOTAL |      3 |

| C                 | 1001              |      1 |

| C                 | 1002              |      1 |

| C                 | 1003              |      3 |

| C                 | WITH_ROLLUP_TOTAL |      4 |

| WITH_ROLLUP_TOTAL | WITH_ROLLUP_TOTAL |     10 |

+-------------------+-------------------+--------+

13 rows in set (0.01 sec)

改用如下sql可以获得想要的结果

SELECT IFNULL(phone,'WITH_ROLLUP_TOTAL') p, IFNULL(cv,'WITH_ROLLUP_TOTAL') v, COUNT(cv)daynum FROM(SELECT * FROM (SELECT DISTINCT username,phone,cv FROM `userinfo_test` ORDER BY cv DESC) c GROUP BY username)d GROUP BY phone,cv WITH ROLLUP;

+-------------------+-------------------+--------+

| p                 | v                 | daynum |

+-------------------+-------------------+--------+

| A                 | 1001              |      1 |

| A                 | 1003              |      2 |

| A                 | WITH_ROLLUP_TOTAL |      3 |

| B                 | 1002              |      2 |

| B                 | 1003              |      1 |

| B                 | WITH_ROLLUP_TOTAL |      3 |

| C                 | 1002              |      1 |

| C                 | 1003              |      3 |

| C                 | WITH_ROLLUP_TOTAL |      4 |

| WITH_ROLLUP_TOTAL | WITH_ROLLUP_TOTAL |     10 |

+-------------------+-------------------+--------+

10 rows in set (0.00 sec)

group by 支持排序，group by的排序是按最后一个字段排序的

SELECT phone,cv,COUNT(cv)num FROM `userinfo_test` GROUP BY phone,cv WITH ROLLUP;

+-------+------+-----+

| phone | cv   | num |

+-------+------+-----+

| A     | 1001 |   2 |

| A     | 1002 |   1 |

| A     | 1003 |   3 |

| A     | NULL |   6 |

| B     | 1001 |   1 |

| B     | 1002 |   3 |

| B     | 1003 |   2 |

| B     | NULL |   6 |

| C     | 1001 |   1 |

| C     | 1002 |   2 |

| C     | 1003 |   4 |

| C     | NULL |   7 |

| NULL  | NULL |  19 |

+-------+------+-----+

13 rows in set (0.00 sec)

SELECT phone,cv,COUNT(cv)num FROM `userinfo_test` GROUP BY phone,cv DESC WITH ROLLUP;

+-------+------+-----+

| phone | cv   | num |

+-------+------+-----+

| A     | 1003 |   3 |

| A     | 1002 |   1 |

| A     | 1001 |   2 |

| A     | NULL |   6 |

| B     | 1003 |   2 |

| B     | 1002 |   3 |

| B     | 1001 |   1 |

| B     | NULL |   6 |

| C     | 1003 |   4 |

| C     | 1002 |   2 |

| C     | 1001 |   1 |

| C     | NULL |   7 |

| NULL  | NULL |  19 |

+-------+------+-----+

13 rows in set (0.01 sec)

explain SELECT cv,phone,COUNT(phone)num FROM `userinfo_test` GROUP BY cv,phone DESC WITH ROLLUP;

+------+-------+-----+

| cv   | phone | num |

+------+-------+-----+

| 1001 | C     |   1 |

| 1001 | B     |   1 |

| 1001 | A     |   2 |

| 1001 | NULL  |   4 |

| 1002 | C     |   2 |

| 1002 | B     |   3 |

| 1002 | A     |   1 |

| 1002 | NULL  |   6 |

| 1003 | C     |   4 |

| 1003 | B     |   2 |

| 1003 | A     |   3 |

| 1003 | NULL  |   9 |

| NULL | NULL  |  19 |

+------+-------+-----+

13 rows in set (0.00 sec)

如果用到group by 的排序，前面月活跃用户数的统计语句

SELECT IFNULL(phone,'WITH_ROLLUP_TOTAL') p, IFNULL(cv,'WITH_ROLLUP_TOTAL') v,COUNT(cv) monthnum FROM (SELECT phone,cv FROM (SELECT * FROM (SELECT cv,phone,username FROM `userinfo_test` GROUP BY cv,phone,username) c ORDER BY cv DESC) d GROUP BY username) e GROUP

BY phone,cv WITH ROLLUP;

可以修改为

SELECT IFNULL(phone,'WITH_ROLLUP_TOTAL') p, IFNULL(cv,'WITH_ROLLUP_TOTAL') v,COUNT(cv) monthnum FROM (SELECT phone,cv FROM (SELECT cv,phone,username FROM `userinfo_test` GROUP BY username,phone,cv DESC) c GROUP BY username) d GROUP BY phone,cv WITH ROLLUP;

+-------------------+-------------------+----------+

| p                 | v                 | monthnum |

+-------------------+-------------------+----------+

| A                 | 1001              |        1 |

| A                 | 1003              |        2 |

| A                 | WITH_ROLLUP_TOTAL |        3 |

| B                 | 1002              |        2 |

| B                 | 1003              |        1 |

| B                 | WITH_ROLLUP_TOTAL |        3 |

| C                 | 1002              |        1 |

| C                 | 1003              |        3 |

| C                 | WITH_ROLLUP_TOTAL |        4 |

| WITH_ROLLUP_TOTAL | WITH_ROLLUP_TOTAL |       10 |

+-------------------+-------------------+----------+

10 rows in set (0.01 sec)

后记：

1.由于天与天之间重复数据较多，因此group by 比DISTINCT 相对来说有优势

2.感觉sql还是写的比较复杂，欢迎指点，优化sql或者有更好的方法

code4f
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql使用exists、in、distinct区别
灵豸
03-03 912
使用exists 使用exists代替in 1.exists只检查行的存在性，in 检查实际的值，所以existsd的性能比in好 验证 select * from emp where deptno in(select deptno from dept where loc='NEW YORK'); select * from emp e where exists(select 1 fr...
Mysql中distinct与group by的去重方面的区别
12-14
在MySQL数据库中，`DISTINCT` 和 `GROUP BY` 是两个非常重要的SQL关键字，它们都可以用来处理数据的去重问题，但在实际应用中，两者的使用场景和效果有所差异。 首先，`DISTINCT` 关键字的主要作用是去除查询结果中...
with as 和group by 代替 count distinct，提高性能
kevin77ya的专栏
06-06 1020
with as 和group by 代替 count distinct，提高性能
mysql count 替代_关于mysql count（*）等效率问题
weixin_31888683的博客
01-19 597
本文是个人学习笔记，学习同时会借鉴一些文章，会在文章末尾附上链接。MySQL常用有两种引擎，myISAM 和而InnodbmyIsam会保存表的总行数，无条件查询会直接返回，速度较快Innodb需要一条条读取数据，然后统计条数。如果数据量大，会很慢。为什么Innodb不能存储表的总行数？这和InnoDB的事务设计有关系，可重复读是它默认的隔离级别，在代码上就是通过多版本并发控制，也就是MVCC来实...
mysql distinct exists_用EXISTS替换DISTINCT ,提高效率
weixin_36125467的博客
01-20 1125
用EXISTS替换DISTINCT：当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT.一般可以考虑用EXIST替换, EXISTS使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.尽量使用exists而不是distinct,因为distinct在禁止重复行之前要排序检索到的行。例子：(低效):SELECT DIST...
Distinct函数的替代方法
哥只是个传说
09-29 1066
   今天在论坛上看到一个面试题，是说有什么办法可以替代distinct，得到同样的结果。答案都被大家说的差不多了，发现挺有意思的，就记录一下： SQL> select num from t1;        NUM ----------          6          6          7          8        ...
count(distinct ...) over (partition by...) 替换成mysql
热门推荐
u012102536的博客
05-08 1万+
你这个是用了 Oracle 的分析函数。 SQL Server 是不支持的。如果语句比较简单的。例如SELECT COUNT( distinct A) OVER ( partition by B) FROM C可以修改为：SELECT COUNT( distinct A) FROM CGROUP BY B但是如果你的逻辑很复杂的话，那就麻烦了。
MySQL中distinct与group by语句的一些比较及用法讲解
09-10
在MySQL数据库中，`DISTINCT` 和 `GROUP BY` 是两种用于数据去重和分组统计的SQL语句，它们虽然都可以帮助我们处理重复数据，但有着不同的应用场景和执行机制。 1. `DISTINCT` 关键字： - `DISTINCT` 主要用于去除...
MySQL中distinct与group by之间的性能进行比较
09-10
在MySQL数据库中，`DISTINCT` 和 `GROUP BY` 是两个用于数据去重的语句，但它们在功能和性能上有显著的区别。这次的测试主要比较了这两种方法在不同条件下的执行效率，尤其是在数据量较大的情况下。 首先，`...
MySQL去重该使用distinct还是group by？
09-08
主要介绍了MySQL去重该使用distinct还是group by,本文通过实例代码给大家介绍的非常详细，对大家的学习或工作具有一定的参考借鉴价值，需要的朋友可以参考下
MySQL 8 如何解决快速获取数据库中所有业务库表列的distinct 值，不使用SQL
sql server的专栏
05-15 321
开头还是介绍一下群，如果感兴趣polardb ,mongodb ,mysql ,postgresql ,redis 等有问题，有需求都可以加群群内有各大数据库行业大咖，CTO，可以解决你的问题。加群请联系 liuaustin3 ，在新加的朋友会分到2群（共790人左右 1 + 2）最近我们接到一个需求，在数据库内，无准确目标的寻找每个表中的字里面包含某些 特殊字符的列。工作了快半辈子了，也是第一次...
【MySQL】 DISTINCT关键字详解：用法、实际应用和与GROUP BY的区别
Code Metaverse
02-08 6725
MySQL的DISTINCT关键字用于消除重复记录，并返回唯一的记录集。DISTINCT关键字通常用于在查询中返回不同的记录，例如查询不同的客户国家或统计不同的产品数量。通过使用DISTINCT关键字，可以简化SQL语句并显著提高查询效率。
用exists代替distinct
刘伟的博客
12-15 1万+
我们用数据库进行一些查询时，可能需要对结果进行去重复，一般我们都是想到distinct，但是为了提高效率我们应该尽量用exists，下面我将举例分析。我们假设有一张公司员工表staffs和一张部分表depts,其中员工表中包含字段dp_id,也就是部分编号，depts表中包含dp_id和dp_name,分别表示部门和部分名字。我们现在要做的就是，找到这张员工表中涉及到的部门id和部门名字。用dis...
mysql group by 替代_group by 替代distinct
weixin_42492000的博客
01-27 1036
SQL> select distinct employee_id,first_name from test1;107 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 255531131--------------------------...
distinct与group by 去重
weixin_44792849的博客
12-25 5102
distinct简单来说就是用来去重的，而group by的设计目的则是用来聚合统计。单纯的去重操作使用distinct，速度是快于group by的。distinct 是针对要查询的全部字段去重，而 group by 可以针对要查询的全部字段中的部分字段去重，它的作用主要是：获取数据表中以分组字段为依据的其他统计数据。两者执行方式不同，distinct主要是对数据两两进行比较，需要遍历整个表。
故障006：连接排序去重结果不如人愿
ai20110304的专栏
07-16 547
达梦数据库：查询SQL：解决连接查询中涉及排序去重返回错误的结果集。
hive性能优化使用group by替代distinct原理
weixin_42412645的博客
06-02 7771
日常业务统计中经常使用除重（distinct）操作，但是当出现某一组数据特别多时就会出现数据倾斜。于是在SQL编写的时候就使用group by来代替distinct，从而让执行效率提升。下面说一下个人的理解。 原始数据page_test表 1.distinct底层实现原理（select city,count（distinct user_id） from page_test group by ci...
hive优化之distinct转group by实战
cclovezbf的博客
12-29 1116
说明：一个公司有多个业务biz_id，每个业务收款的时候通过不同或者相同的account去收款 业务需要 需要计算出该公司有过滤重复后biz_count 和过滤重复后的account_count 1先来个distinct ，占用内存，容易oom，所以一般都会把distinct改为group by select company_name,count(distinct biz_id),count(distinct account_id) from odsiadata.ia_fdw_b_profi..
Python数据分析下，哪吒为啥这么火？
程序IT圈
08-05 324
不知道大家最近有没有去看电影，最近身边的朋友都在向我安利一部叫做《哪吒之魔童降世》的动漫电影。大家无一例外，都说非常的好看。看一下最近的微信搜索指数，比火热的电视剧“亲爱的热爱的”搜索指...
mysql中 GROUP_DISTINCT_CONCAT
最新发布
11-17
很抱歉，MySQL中没有GROUP_DISTINCT_CONCAT函数。但是，MySQL中有GROUP_CONCAT函数，可以用于将分组后的结果连接成一个字符串。GROUP_CONCAT函数的语法如下： ``` GROUP_CONCAT([DISTINCT] expr [,expr ...] [ORDER...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值