快捷導(dǎo)航

mysql中over partition by的具體使用

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

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

前言

開(kāi)發(fā)中遇到了這樣一個(gè)需求：統(tǒng)計(jì)商品庫(kù)存，產(chǎn)品ID + 子產(chǎn)品名稱(chēng)都相同時(shí)，可以確定是同一款商品。當(dāng)商品來(lái)自不同的渠道時(shí)，我們要統(tǒng)計(jì)每個(gè)渠道中最大的那一個(gè)。如果在Oracle中可以通過(guò)分析函數(shù) OVER(PARTITION BY… ORDER BY…)來(lái)實(shí)現(xiàn)。在MySQL中應(yīng)該怎么來(lái)實(shí)現(xiàn)呢?，F(xiàn)在通過(guò)兩種簡(jiǎn)單的方式來(lái)實(shí)現(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 '渠道類(lèi)型',
  `branch` varchar(10) DEFAULT NULL COMMENT '子產(chǎn)品',
  `stock` int(11) DEFAULT NULL COMMENT '庫(kù)存',
  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,'高級(jí)標(biāo)間',45),
(3,'P003',1,'高級(jí)標(biāo)間',33),
(4,'P004',1,'經(jīng)典房',65),
(5,'P003',1,'小型套房',45),
(6,'P002',2,'高級(jí)標(biāo)間',331),
(7,'P005',2,'小型套房',223),
(8,'P001',1,'豪華房',99),
(9,'P002',3,'高級(jí)標(biāo)間',65),
(10,'P003',2,'經(jīng)典房',45),
(11,'P004',3,'標(biāo)準(zhǔn)雙床房',67),
(12,'P005',2,'小型套房',34),
(13,'P001',1,'高級(jí)標(biāo)間',43),
(14,'P002',3,'豪華房',56),
(15,'P001',3,'高級(jí)標(biāo)間',77),
(16,'P005',2,'經(jīng)典房',67),
(17,'P003',2,'高級(jí)標(biāo)間',98),
(18,'P002',3,'經(jīng)典房',23),
(19,'P004',2,'經(jīng)典房',76),
(20,'P002',1,'小型套房',123);

通過(guò)分組聚合GROUP_CONCAT實(shí)現(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

查詢(xún)結(jié)果：

product_id	branch	stocks
P001	豪華房	99
P001	高級(jí)標(biāo)間	77,45,43
P002	小型套房	123
P002	經(jīng)典房	23
P002	豪華房	56,23
P002	高級(jí)標(biāo)間	331,65
P003	小型套房	45
P003	經(jīng)典房	45
P003	高級(jí)標(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

查詢(xún)結(jié)果：

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

通過(guò)關(guān)聯(lián)查詢(xún)及COUNT函數(shù)實(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

查詢(xún)結(jié)果：

product_id	branch	stock	rank
P003	小型套房	45	1
P002	高級(jí)標(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	高級(jí)標(biāo)間	77	1
P005	經(jīng)典房	67	1
P003	高級(jí)標(biāo)間	98	1
P002	經(jīng)典房	23	1
P004	經(jīng)典房	76	1
P002	小型套房	123	1

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

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

您可能感興趣的文章: