Mysql 实现分组后在分组中排序并获取前n条数据（Oracle开窗函数在Mysql中实现）

最新推荐文章于 2022-09-16 11:07:15 发布

置顶 YOLO___

最新推荐文章于 2022-09-16 11:07:15 发布

阅读量957

点赞数

分类专栏： Mysql 文章标签： Mysql Java 开窗函数的实现 sql分组排序

本文链接：https://blog.csdn.net/YOLO___/article/details/90694160

版权

Mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

项目需求，该表为统计每一天活动访问人数，附上sql语句，需自行创建数据库

CREATE TABLE `app_traffic` (
  `traffic_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '访问量ID',
  `gmt_create` datetime DEFAULT NULL COMMENT '创建时间',
  `gmt_modified` datetime DEFAULT NULL COMMENT '最后一次更新时间',
  `activity_id` bigint(20) DEFAULT NULL COMMENT '活动ID',
  `user_id` bigint(20) DEFAULT NULL COMMENT '用户ID',
  `gmt_create_day` date DEFAULT NULL COMMENT '创建日期',
  PRIMARY KEY (`traffic_id`)
) ENGINE=InnoDB AUTO_INCREMENT=109 DEFAULT CHARSET=utf8;

/*Data for the table `app_traffic` */

insert  into `app_traffic`(`traffic_id`,`gmt_create`,`gmt_modified`,`activity_id`,`user_id`,`gmt_create_day`) values (1,'2019-05-27 15:23:17','2019-05-27 15:23:17',38,45,'2019-05-27'),(2,'2019-05-28 15:28:48','2019-05-28 15:28:48',39,45,'2019-05-28'),(3,'2019-05-27 15:23:17','2019-05-27 15:23:17',39,NULL,'2019-05-27'),(4,'2019-05-27 15:23:17','2019-05-27 15:23:17',38,NULL,'2019-05-27'),(5,'2019-05-27 15:23:17','2019-05-27 15:23:17',38,NULL,'2019-05-27'),(6,'2019-05-27 15:23:17','2019-05-27 15:23:17',38,NULL,'2019-05-27'),(7,'2019-05-27 15:23:17','2019-05-27 15:23:17',38,NULL,'2019-05-27'),(8,'2019-05-27 15:23:17','2019-05-27 15:23:17',38,NULL,'2019-05-27'),(9,'2019-05-27 15:23:17','2019-05-27 15:23:17',38,NULL,'2019-05-27'),(10,'2019-05-27 15:23:17','2019-05-27 15:23:17',38,NULL,'2019-05-27'),(11,'2019-05-26 15:56:07','2019-05-26 15:56:07',38,NULL,'2019-05-26'),(12,'2019-05-26 15:56:07','2019-05-26 15:56:07',38,NULL,'2019-05-26'),(13,'2019-05-26 15:56:07','2019-05-26 15:56:07',38,NULL,'2019-05-26'),(14,'2019-05-26 15:56:07','2019-05-26 15:56:07',38,NULL,'2019-05-26'),(15,'2019-05-26 15:56:07','2019-05-26 15:56:07',38,NULL,'2019-05-26'),(16,'2019-05-26 15:56:07','2019-05-26 15:56:07',38,NULL,'2019-05-26'),(17,'2019-05-26 15:56:02','2019-05-26 15:56:07',38,NULL,'2019-05-26'),(18,'2019-05-27 15:23:17','2019-05-27 15:23:17',40,NULL,'2019-05-27'),(19,'2019-05-27 15:23:17','2019-05-27 15:23:17',40,NULL,'2019-05-27'),(20,'2019-05-27 15:23:17','2019-05-27 15:23:17',40,NULL,'2019-05-27'),(21,'2019-05-27 15:23:17','2019-05-27 15:23:17',40,NULL,'2019-05-27'),(22,'2019-05-27 15:23:17','2019-05-27 15:23:17',40,NULL,'2019-05-27'),(23,'2019-05-27 15:23:17','2019-05-27 15:23:17',40,NULL,'2019-05-27'),(24,'2019-05-27 15:23:17','2019-05-27 15:23:17',40,NULL,'2019-05-27'),(25,'2019-05-27 15:23:17','2019-05-27 15:23:17',40,NULL,'2019-05-27'),(26,'2019-05-27 15:23:17','2019-05-27 15:23:17',40,NULL,'2019-05-27'),(27,'2019-05-27 15:23:17','2019-05-27 15:23:17',40,NULL,'2019-05-27'),(28,'2019-05-27 15:23:17','2019-05-27 15:23:17',40,NULL,'2019-05-27'),(29,'2019-05-27 15:23:17','2019-05-27 15:23:17',40,NULL,'2019-05-27'),(30,'2019-05-27 15:23:17','2019-05-27 15:23:17',40,NULL,'2019-05-27'),(31,'2019-05-27 15:23:17','2019-05-27 15:23:17',40,NULL,'2019-05-27'),(32,'2019-05-26 15:56:07','2019-05-26 15:56:07',39,NULL,'2019-05-26'),(33,'2019-05-26 15:56:07','2019-05-26 15:56:07',40,NULL,'2019-05-26'),(34,'2019-05-26 15:56:07','2019-05-26 15:56:07',39,NULL,'2019-05-26'),(35,'2019-05-26 15:56:06','2019-05-26 15:56:07',40,NULL,'2019-05-26'),(36,'2019-05-26 15:56:07','2019-05-26 15:56:07',39,NULL,'2019-05-26'),(37,'2019-05-26 15:56:06','2019-05-26 15:56:07',40,NULL,'2019-05-26'),(38,'2019-05-26 15:56:02','2019-05-26 15:56:07',39,NULL,'2019-05-26'),(39,'2019-05-26 15:56:41','2019-05-26 15:56:07',39,NULL,'2019-05-26'),(40,'2019-05-26 15:56:21','2019-05-26 15:56:07',40,NULL,'2019-05-26'),(41,'2019-05-26 15:56:07','2019-05-26 15:56:07',39,NULL,'2019-05-26'),(42,'2019-05-26 15:56:43','2019-05-26 15:56:07',39,NULL,'2019-05-26'),(43,'2019-05-26 15:56:12','2019-05-26 15:56:07',39,NULL,'2019-05-26'),(44,'2019-05-27 15:23:17','2019-05-27 15:23:17',39,NULL,'2019-05-27'),(45,'2019-05-27 15:23:17','2019-05-27 15:23:17',39,NULL,'2019-05-27'),(46,'2019-05-27 15:23:17','2019-05-27 15:23:17',39,NULL,'2019-05-27'),(47,'2019-05-27 15:23:17','2019-05-27 15:23:17',38,NULL,'2019-05-27'),(48,'2019-05-27 15:23:17','2019-05-27 15:23:17',39,NULL,'2019-05-27'),(49,'2019-05-27 15:23:17','2019-05-27 15:23:17',39,NULL,'2019-05-27'),(50,'2019-05-27 15:23:17','2019-05-27 15:23:17',39,NULL,'2019-05-27'),(51,'2019-05-27 15:23:17','2019-05-27 15:23:17',39,NULL,'2019-05-27'),(52,'2019-05-27 15:23:17','2019-05-27 15:23:17',38,NULL,'2019-05-27'),(53,'2019-05-27 15:23:17','2019-05-27 15:23:17',39,NULL,'2019-05-27'),(54,'2019-05-27 15:23:17','2019-05-27 15:23:17',39,NULL,'2019-05-27'),(55,'2019-05-27 15:23:17','2019-05-27 15:23:17',39,NULL,'2019-05-27'),(56,'2019-05-27 15:23:17','2019-05-27 15:23:17',38,NULL,'2019-05-27'),(57,'2019-05-27 15:23:17','2019-05-27 15:23:17',39,NULL,'2019-05-27'),(58,'2019-05-27 15:23:17','2019-05-27 15:23:17',39,NULL,'2019-05-27'),(59,'2019-05-28 15:28:48','2019-05-28 15:28:48',39,NULL,'2019-05-28'),(60,'2019-05-28 15:28:48','2019-05-28 15:28:48',39,NULL,'2019-05-28'),(61,'2019-05-28 15:28:48','2019-05-28 15:28:48',39,NULL,'2019-05-28'),(62,'2019-05-28 15:28:48','2019-05-28 15:28:48',39,NULL,'2019-05-28'),(63,'2019-05-29 11:23:44','2019-05-29 11:23:45',39,NULL,'2019-05-29'),(64,'2019-05-29 11:23:44','2019-05-29 11:23:44',39,NULL,'2019-05-29'),(65,'2019-05-29 11:23:44','2019-05-29 11:23:44',39,NULL,'2019-05-29'),(66,'2019-05-29 11:23:44','2019-05-29 11:23:44',39,NULL,'2019-05-29'),(67,'2019-05-29 11:23:44','2019-05-29 11:23:44',41,NULL,'2019-05-29'),(68,'2019-05-29 11:23:44','2019-05-29 11:23:44',41,NULL,'2019-05-29'),(69,'2019-05-29 11:23:44','2019-05-29 11:23:44',41,NULL,'2019-05-29'),(70,'2019-05-29 11:23:44','2019-05-29 11:23:44',41,NULL,'2019-05-29'),(71,'2019-05-29 11:23:44','2019-05-29 11:23:44',41,NULL,'2019-05-29'),(72,'2019-05-29 11:23:44','2019-05-29 11:23:44',41,NULL,'2019-05-29'),(73,'2019-05-29 11:23:44','2019-05-29 11:23:44',41,NULL,'2019-05-29'),(74,'2019-05-29 11:23:44','2019-05-29 11:23:44',41,NULL,'2019-05-29'),(75,'2019-05-29 11:23:44','2019-05-29 11:23:44',41,NULL,'2019-05-29'),(76,'2019-05-28 15:28:48','2019-05-28 15:28:48',41,NULL,'2019-05-28'),(77,'2019-05-28 15:28:48','2019-05-28 15:28:48',41,NULL,'2019-05-28'),(78,'2019-05-28 15:28:48','2019-05-28 15:28:48',41,NULL,'2019-05-28'),(79,'2019-05-28 15:28:48','2019-05-28 15:28:48',41,NULL,'2019-05-28'),(80,'2019-05-28 15:28:48','2019-05-28 15:28:48',41,NULL,'2019-05-28'),(81,'2019-05-27 15:28:48','2019-05-27 15:28:48',41,NULL,'2019-05-27'),(82,'2019-05-27 15:28:48','2019-05-27 15:28:48',41,NULL,'2019-05-27'),(83,'2019-05-27 15:28:48','2019-05-27 15:28:48',41,NULL,'2019-05-27'),(84,'2019-05-27 15:28:48','2019-05-27 15:28:48',41,NULL,'2019-05-27'),(85,'2019-05-27 15:28:48','2019-05-27 15:28:48',41,NULL,'2019-05-27'),(86,'2019-05-27 15:28:48','2019-05-27 15:28:48',41,NULL,'2019-05-27'),(87,'2019-05-27 15:28:48','2019-05-27 15:28:48',42,NULL,'2019-05-27'),(88,'2019-05-27 15:28:48','2019-05-27 15:28:48',42,NULL,'2019-05-27'),(89,'2019-05-27 15:28:48','2019-05-27 15:28:48',42,NULL,'2019-05-27'),(90,'2019-05-28 15:28:48','2019-05-28 15:28:48',42,NULL,'2019-05-28'),(91,'2019-05-28 15:28:48','2019-05-28 15:28:48',42,NULL,'2019-05-28'),(92,'2019-05-28 15:28:48','2019-05-28 15:28:48',42,NULL,'2019-05-28'),(93,'2019-05-28 15:28:48','2019-05-28 15:28:48',42,NULL,'2019-05-28'),(94,'2019-05-29 11:23:44','2019-05-29 11:23:44',42,NULL,'2019-05-29'),(95,'2019-05-29 11:23:44','2019-05-29 11:23:44',42,NULL,'2019-05-29'),(96,'2019-05-29 11:23:44','2019-05-29 11:23:44',42,NULL,'2019-05-29'),(97,'2019-05-29 11:23:44','2019-05-29 11:23:44',42,NULL,'2019-05-29'),(98,'2019-05-29 11:23:44','2019-05-29 11:23:44',42,NULL,'2019-05-29'),(99,'2019-05-29 11:23:44','2019-05-29 11:23:44',42,NULL,'2019-05-29'),(101,'2019-05-29 18:04:04','2019-05-29 18:04:04',40,3,'2019-05-29'),(102,'2019-05-29 18:05:20','2019-05-29 18:05:20',39,45,'2019-05-29'),(103,'2019-05-29 18:10:18','2019-05-29 18:10:18',37,45,'2019-05-29'),(104,'2019-05-29 18:10:38','2019-05-29 18:10:38',42,45,'2019-05-29'),(105,'2019-05-30 09:59:35','2019-05-30 09:59:35',39,45,'2019-05-30'),(106,'2019-05-30 10:05:27','2019-05-30 10:05:27',42,45,'2019-05-30'),(107,'2019-05-30 10:05:29','2019-05-30 10:05:29',36,45,'2019-05-30'),(108,'2019-05-30 10:39:21','2019-05-30 10:39:21',38,43,'2019-05-30');

现在需要将数据按照时间分组后统计当天活动总人数并且根据时间和访问人数进行排序，排序语句以及结果如下

SELECT 
  t.*,COUNT(t.`activity_id`) AS number
FROM
  app_traffic t 
WHERE t.`gmt_create_day` >= '2019-05-23' 
  AND t.`gmt_create_day` <= '2019-05-29'
  GROUP BY t.`gmt_create_day`,t.`activity_id`
  ORDER BY t.`gmt_create_day`,COUNT(t.`activity_id`) DESC

现在需要获取到每一天的前n条数据，这里先假设需要获取到每一天前两条数据，也就是应该查出来的数据需要如下图片所示

网上翻很多资料，https://jingyan.baidu.com/article/d8072ac48d2730ec94cefd43.html参考这个文档但是发现自己排序的new_rank是错误的，一开始查询语句以及图片如下，Ps：解释一下，IF(@tmp=t.gmt_create_day, @rank:=@rank + 1, @rank:=1)则利用中间变量@tmp存储上一条记录的gmt_create_day，并和当前的对比，如若相同，则序号@rank增加1，否则初始化@rank为0.

SELECT 
  t.*,
  COUNT(t.`activity_id`) AS number,
  IF(
    @tmp=t.gmt_create_day,
    @rank:=@rank + 1,
    @rank:=1
  ) AS new_rank,
  @tmp := t.`gmt_create_day` AS tmp 
FROM
  app_traffic t 
WHERE t.`gmt_create_day` >= '2019-05-23' 
  AND t.`gmt_create_day` <= '2019-05-29' 
GROUP BY t.`gmt_create_day`,
  t.`activity_id` 
ORDER BY t.`gmt_create_day`,
  COUNT(t.`activity_id`) DESC

后来发现，好像如果在原本的语句里面写上IF的判断条件，那应该查找的时候是无序也未分组的数据才对，因此修改了一下语句，排序后再去设置new_rank的值，修改后语句如下：

SELECT 
  b.*,
  IF(
    @tmp = b.`gmt_create_day`,
    @rank := @rank + 1,
    @rank := 1
  ) AS new_rank
FROM
  (SELECT 
    t.*,
    COUNT(t.`activity_id`) AS number 
  FROM
    app_traffic t 
  WHERE t.`gmt_create_day` >= '2019-05-23' 
    AND t.`gmt_create_day` <= '2019-05-29' 
  GROUP BY t.`gmt_create_day`,
    t.`activity_id` 
  ORDER BY t.`gmt_create_day`,
    COUNT(t.`activity_id`) DESC) b

咦奇怪，new_rank还是错误的，抱着死马当活马的心随便加了一个@tmp := b.gmt_create_day AS tmp，发现对了，好像是变量没被获取到的原因，猜是这个原因，变量没有初始化，如果看到这边文章的你知道的话请评论区告诉我一声，谢谢啦~，附上修改后的语句

SELECT 
  b.*,
  IF(
    @tmp = b.`gmt_create_day`,
    @rank := @rank + 1,
    @rank := 1
  ) AS new_rank,
  @tmp := b.`gmt_create_day` AS tmp 
FROM
  (SELECT 
    t.*,
    COUNT(t.`activity_id`) AS number 
  FROM
    app_traffic t 
  WHERE t.`gmt_create_day` >= '2019-05-23' 
    AND t.`gmt_create_day` <= '2019-05-29' 
  GROUP BY t.`gmt_create_day`,
    t.`activity_id` 
  ORDER BY t.`gmt_create_day`,
    COUNT(t.`activity_id`) DESC) b

然后再根据获取到的这些数据再去进行一次查询，语句如下

SELECT 
  bb.*
FROM
  (SELECT 
    b.*,
    IF(
      @tmp = b.gmt_create_day,
      @rank := @rank + 1,
      @rank := 1
    ) AS rank,
    @tmp := b.`gmt_create_day` AS tmp 
  FROM
    (SELECT 
      t.*,
      COUNT(t.`activity_id`) AS number 
    FROM
      app_traffic t 
    WHERE t.`gmt_create_day` >= '2019-05-23' 
      AND t.`gmt_create_day` <= '2019-05-30' 
    GROUP BY t.`gmt_create_day`,
      t.`activity_id` 
    ORDER BY t.`gmt_create_day`,
      COUNT(t.`activity_id`) DESC) b) bb
WHERE bb.rank<=2

成功啦

总结如下：先对数据进行排序，将排序后的数据进行IF判断（第一个查询），获取到递增的值new_rank（第二个查询），再根据递增后的数据进行一次条件查询（第三次查询），然后就完成啦。

YOLO___

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Mysql 实现分组后在分组中排序并获取前n条数据（Oracle开窗函数在Mysql中实现）

项目需求，该表为统计每一天活动访问人数，附上sql语句，需自行创建数据库CREATE TABLE `app_traffic` ( `traffic_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '访问量ID', `gmt_create` datetime DEFAULT NULL COMMENT '创建时间', `gmt_modif...
复制链接

扫一扫

专栏目录