mysql中over partition by的具体使用

云峰郁起

前言

开发中遇到了这样一个需求：统计商品库存，产品ID + 子产品名称都相同时，可以确定是同一款商品。当商品来自不同的渠道时，我们要统计每个渠道中最大的那一个。如果在Oracle中可以通过分析函数 OVER(PARTITION BY… ORDER BY…)来实现。在MySQL中应该怎么来实现呢。现在通过两种简单的方式来实现这一需求。

数据准备

1. /*Table structure for table `product_stock` */
   
2. CREATE TABLE `product_stock` (
   
3.   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
   
4.   `product_id` varchar(10) DEFAULT NULL COMMENT '产品ID',
   
5.   `channel_type` int(11) DEFAULT NULL COMMENT '渠道类型',
   
6.   `branch` varchar(10) DEFAULT NULL COMMENT '子产品',
   
7.   `stock` int(11) DEFAULT NULL COMMENT '库存',
   
8.   PRIMARY KEY (`id`)
   
9. ) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8;
   
10. 
    
11. /*Data for the table `product_stock` */
    
12. 
    
13. insert  into `product_stock`
    
14. (`id`,`product_id`,`channel_type`,`branch`,`stock`)
    
15. values (1,'P002',1,'豪华房',23),
    
16. (2,'P001',1,'高级标间',45),
    
17. (3,'P003',1,'高级标间',33),
    
18. (4,'P004',1,'经典房',65),
    
19. (5,'P003',1,'小型套房',45),
    
20. (6,'P002',2,'高级标间',331),
    
21. (7,'P005',2,'小型套房',223),
    
22. (8,'P001',1,'豪华房',99),
    
23. (9,'P002',3,'高级标间',65),
    
24. (10,'P003',2,'经典房',45),
    
25. (11,'P004',3,'标准双床房',67),
    
26. (12,'P005',2,'小型套房',34),
    
27. (13,'P001',1,'高级标间',43),
    
28. (14,'P002',3,'豪华房',56),
    
29. (15,'P001',3,'高级标间',77),
    
30. (16,'P005',2,'经典房',67),
    
31. (17,'P003',2,'高级标间',98),
    
32. (18,'P002',3,'经典房',23),
    
33. (19,'P004',2,'经典房',76),
    
34. (20,'P002',1,'小型套房',123);

复制代码

通过分组聚合GROUP_CONCAT实现

1. SELECT
   
2.   product_id,
   
3.   branch,
   
4.   GROUP_CONCAT(t.stock ORDER BY t.stock DESC ) stocks
   
5. FROM (SELECT *
   
6.       FROM product_stock) t
   
7. GROUP BY product_id,branch

复制代码

查询结果：
product_idbranchstocksP001豪华房99P001高级标间77,45,43P002小型套房123P002经典房23P002豪华房56,23P002高级标间331,65P003小型套房45P003经典房45P003高级标间98,33P004标准双床房67P004经典房76,65P005小型套房223,34P005经典房67这也许并不是我们想要的结果，我们只要stocks中的最大值就可以，那么我们只要用SUBSTRING_INDEX函数截取一下就可以：

1. SELECT
   
2.   product_id,
   
3.   branch,
   
4.   SUBSTRING_INDEX(GROUP_CONCAT(t.stock ORDER BY t.stock DESC ),',',1) stock
   
5. FROM (SELECT *
   
6.       FROM product_stock) t
   
7. GROUP BY product_id,branch

复制代码

查询结果：
product_idbranchstockP001豪华房99P001高级标间77P002小型套房123P002经典房23P002豪华房56P002高级标间331P003小型套房45P003经典房45P003高级标间98P004标准双床房67P004经典房76P005小型套房223P005经典房67
通过关联查询及COUNT函数实现

1. SELECT *
   
2. FROM (SELECT
   
3.         t.product_id,
   
4.         t.branch,
   
5.         t.stock,
   
6.         COUNT(*)     AS rank
   
7.       FROM product_stock t
   
8.         LEFT JOIN product_stock r
   
9.           ON t.product_id = r.product_id
   
10.             AND t.branch = r.branch
    
11.             AND t.stock <= r.stock
    
12.       GROUP BY t.id) s
    
13. WHERE s.rank = 1

复制代码

查询结果：
product_idbranchstockrankP003小型套房451P002高级标间3311P005小型套房2231P001豪华房991P003经典房451P004标准双床房671P002豪华房561P001高级标间771P005经典房671P003高级标间981P002经典房231P004经典房761P002小型套房1231通过关联表本身，联接条件中：t.stock <= r.stock，当t.stock = r.stock时，COUNT出来的数量是1，当t.stock < r.stock时,COUNT出来的数量2,3,4…由此可以给所有的数据根据stock字段做一个排序，而这个排序中所有为1的，就是我们所需求的数据，然后通过按id分组，得到结果。通过这种方式，也可以实现上面的需求。
到此这篇关于mysql中over partition by的具体使用的文章就介绍到这了,更多相关mysql over partition by内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

来源:https://www.jb51.net/database/316215wb2.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！