快捷導(dǎo)航

mysql中over partition by的具體使用

更新時間：2024年02月23日 11:51:03 作者：mob64ca12e2f123

在數(shù)據(jù)庫中,我們經(jīng)常需要對數(shù)據(jù)進行分組排序等操作,MySQL的over partition by可以幫助我們更方便地進行這些操作,本文主要介紹了mysql中over partition by的具體使用,感興趣的可以了解一下

前言

開發(fā)中遇到了這樣一個需求：統(tǒng)計商品庫存，產(chǎn)品ID + 子產(chǎn)品名稱都相同時，可以確定是同一款商品。當(dāng)商品來自不同的渠道時，我們要統(tǒng)計每個渠道中最大的那一個。如果在Oracle中可以通過分析函數(shù) OVER(PARTITION BY… ORDER BY…)來實現(xiàn)。在MySQL中應(yīng)該怎么來實現(xiàn)呢?，F(xiàn)在通過兩種簡單的方式來實現(xiàn)這一需求。

數(shù)據(jù)準(zhǔn)備

/*Table structure for table `product_stock` */
CREATE TABLE `product_stock` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `product_id` varchar(10) DEFAULT NULL COMMENT '產(chǎn)品ID',
  `channel_type` int(11) DEFAULT NULL COMMENT '渠道類型',
  `branch` varchar(10) DEFAULT NULL COMMENT '子產(chǎn)品',
  `stock` int(11) DEFAULT NULL COMMENT '庫存',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8;

/*Data for the table `product_stock` */

insert  into `product_stock`
(`id`,`product_id`,`channel_type`,`branch`,`stock`) 
values (1,'P002',1,'豪華房',23),
(2,'P001',1,'高級標(biāo)間',45),
(3,'P003',1,'高級標(biāo)間',33),
(4,'P004',1,'經(jīng)典房',65),
(5,'P003',1,'小型套房',45),
(6,'P002',2,'高級標(biāo)間',331),
(7,'P005',2,'小型套房',223),
(8,'P001',1,'豪華房',99),
(9,'P002',3,'高級標(biāo)間',65),
(10,'P003',2,'經(jīng)典房',45),
(11,'P004',3,'標(biāo)準(zhǔn)雙床房',67),
(12,'P005',2,'小型套房',34),
(13,'P001',1,'高級標(biāo)間',43),
(14,'P002',3,'豪華房',56),
(15,'P001',3,'高級標(biāo)間',77),
(16,'P005',2,'經(jīng)典房',67),
(17,'P003',2,'高級標(biāo)間',98),
(18,'P002',3,'經(jīng)典房',23),
(19,'P004',2,'經(jīng)典房',76),
(20,'P002',1,'小型套房',123);

通過分組聚合GROUP_CONCAT實現(xiàn)

SELECT
  product_id,
  branch,
  GROUP_CONCAT(t.stock ORDER BY t.stock DESC ) stocks
FROM (SELECT *
      FROM product_stock) t
GROUP BY product_id,branch

查詢結(jié)果：

product_id	branch	stocks
P001	豪華房	99
P001	高級標(biāo)間	77,45,43
P002	小型套房	123
P002	經(jīng)典房	23
P002	豪華房	56,23
P002	高級標(biāo)間	331,65
P003	小型套房	45
P003	經(jīng)典房	45
P003	高級標(biāo)間	98,33
P004	標(biāo)準(zhǔn)雙床房	67
P004	經(jīng)典房	76,65
P005	小型套房	223,34
P005	經(jīng)典房	67

這也許并不是我們想要的結(jié)果，我們只要stocks中的最大值就可以，那么我們只要用SUBSTRING_INDEX函數(shù)截取一下就可以：

SELECT
  product_id,
  branch,
  SUBSTRING_INDEX(GROUP_CONCAT(t.stock ORDER BY t.stock DESC ),',',1) stock
FROM (SELECT *
      FROM product_stock) t
GROUP BY product_id,branch

查詢結(jié)果：

product_id	branch	stock
P001	豪華房	99
P001	高級標(biāo)間	77
P002	小型套房	123
P002	經(jīng)典房	23
P002	豪華房	56
P002	高級標(biāo)間	331
P003	小型套房	45
P003	經(jīng)典房	45
P003	高級標(biāo)間	98
P004	標(biāo)準(zhǔn)雙床房	67
P004	經(jīng)典房	76
P005	小型套房	223
P005	經(jīng)典房	67

通過關(guān)聯(lián)查詢及COUNT函數(shù)實現(xiàn)

SELECT *
FROM (SELECT
        t.product_id,
        t.branch,
        t.stock,
        COUNT(*)     AS rank
      FROM product_stock t
        LEFT JOIN product_stock r
          ON t.product_id = r.product_id
            AND t.branch = r.branch
            AND t.stock <= r.stock
      GROUP BY t.id) s
WHERE s.rank = 1

查詢結(jié)果：

product_id	branch	stock	rank
P003	小型套房	45	1
P002	高級標(biāo)間	331	1
P005	小型套房	223	1
P001	豪華房	99	1
P003	經(jīng)典房	45	1
P004	標(biāo)準(zhǔn)雙床房	67	1
P002	豪華房	56	1
P001	高級標(biāo)間	77	1
P005	經(jīng)典房	67	1
P003	高級標(biāo)間	98	1
P002	經(jīng)典房	23	1
P004	經(jīng)典房	76	1
P002	小型套房	123	1

通過關(guān)聯(lián)表本身，聯(lián)接條件中：t.stock <= r.stock，當(dāng)t.stock = r.stock時，COUNT出來的數(shù)量是1，當(dāng)t.stock < r.stock時,COUNT出來的數(shù)量2,3,4…由此可以給所有的數(shù)據(jù)根據(jù)stock字段做一個排序，而這個排序中所有為1的，就是我們所需求的數(shù)據(jù)，然后通過按id分組，得到結(jié)果。通過這種方式，也可以實現(xiàn)上面的需求。

到此這篇關(guān)于mysql中over partition by的具體使用的文章就介紹到這了,更多相關(guān)mysql over partition by內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: