一、場(chǎng)景概述

我們?cè)谝恍┎樵?xún)時(shí)，需要在一條語(yǔ)句中查詢(xún)表數(shù)據(jù)的時(shí)候，還想查group后聚合函數(shù)的數(shù)據(jù)。
我來(lái)造一個(gè)場(chǎng)景，假定我們有員工表和部門(mén)表：

CREATE TABLE `department` (`id` bigint NOT NULL COMMENT '主鍵ID，雪花算法',`code` char(32) NOT NULL COMMENT '部門(mén)碼',`name` varchar(64) DEFAULT NULL COMMENT '部門(mén)名稱(chēng)'PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='部門(mén)表';CREATE TABLE `sararimann` (`id` bigint NOT NULL COMMENT '主鍵ID，雪花算法',`name` varchar(64) NOT NULL COMMENT '名字',`entrydate` datetime NOT NULL COMMENT '入職時(shí)間',`salary` bigint NOT NULL COMMENT '薪資',`dep_id` bigint NOT NULL COMMENT '部門(mén)Id'PRIMARY KEY (`id`),KEY `idx_dep_id` (`dep_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='員工表';

我們想要查找所有薪資高于部門(mén)平均薪資的員工，并輸出各員工在部門(mén)的薪資排名

二、傳統(tǒng)解決方案

2.1 Java程序計(jì)算

遇到這樣的需求，如果公司的mysql恰好是低版本的5.7，并且數(shù)據(jù)量不是很大的情況下，我認(rèn)為最務(wù)實(shí)的方法是，直接把所有相關(guān)數(shù)據(jù)查出來(lái)，而后在Java中處理數(shù)據(jù)，返回結(jié)果。

2.2 子查詢(xún)方法

先不考慮計(jì)算排名的需求，我們單純做只查找高于部門(mén)平均工資的用戶。

SELECT s.id AS id, s.name AS `name`, s.entrydate AS entry_date, s.salary AS salary, s.dep_id AS dep_id, dep.name AS dep_name, dep.code AS dep_code
FROM 
sararimann AS s 
LEFT JOIN department AS dep ON s.dep_id = dep.id
WHERE s.salary > (SELECT AVG(s2.salary)FROM sararimann s2WHERE s2.dep_id = s.dep_id
);

我們來(lái)解釋下這段代碼，無(wú)非就是每次篩選時(shí)，要重新查一遍該部門(mén)的平均工資。
僅從代碼層面上來(lái)看，假定員工數(shù)據(jù)量是n，部門(mén)數(shù)據(jù)量是m，這樣的算法復(fù)雜度是O(n^2)的。
基于mysql的緩存機(jī)制，可能并不是每個(gè)員工算薪資條件時(shí)，都需要重新查一遍平均工資，也有可能是O(m*n)。

2.3 優(yōu)化一些的JOIN方法

SELECT s.id AS id, s.name AS `name`, s.entrydate AS entry_date, s.salary AS salary, s.dep_id AS dep_id, dep.name AS dep_name, dep.code AS dep_code
FROM 
sararimann AS s 
LEFT JOIN department AS dep ON s.dep_id = dep.id
WHERE s.salary > (SELECT AVG(s2.salary)FROM sararimann s2WHERE s2.dep_id = s.dep_id
);

我們可以LEFT JOIN 一個(gè)group過(guò)的臨時(shí)表。
即使在mysql8中，我們通過(guò)explain可以發(fā)現(xiàn)，左聯(lián)的子查詢(xún)進(jìn)行了全表掃描（ALL），并沒(méi)有走索引。

2.4 創(chuàng)建一個(gè)物理表

建表sql

CREATE TABLE `dep_salary` (`id` bigint NOT NULL AUTO_INCREMENT,`dep_id` bigint NOT NULL,`avg_salary` bigint DEFAULT NULL,`sum_salary` bigint DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `idx_dep_id` (`dep_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='部門(mén)工資的聚合統(tǒng)計(jì)';

插入數(shù)據(jù)

INSERT INTO dep_salary(dep_id,avg_salary,sum_salary)
SELECT dep_id, AVG(salary) as avg_salary, SUM(salary) as sum_salary
FROM sararimann
GROUP BY dep_id

我們改寫(xiě)之前的JOIN的sql：

SELECT s.id AS id, s.name AS `name`, s.entrydate AS entry_date, s.salary AS salary, s.dep_id AS dep_id, dep.name AS dep_name, dep.code AS dep_code, ds.avg_salary
FROM sararimann s
LEFT JOIN dep_salary ds
ON s.dep_id = ds.dep_id
LEFT JOIN department AS dep ON s.dep_id = dep.id
WHERE s.salary > ds.avg_salary;

這時(shí)候有同學(xué)就要問(wèn)了，那不能在查詢(xún)前總處理以下dep_salary表的數(shù)據(jù)吧。
emm，其實(shí)可以用flink抽數(shù)做，這個(gè)在后面的博客可能會(huì)講。

三、mysql的窗口函數(shù)

3.1 官網(wǎng)的介紹

學(xué)習(xí)任何技術(shù)，請(qǐng)優(yōu)先移步官網(wǎng)

個(gè)人對(duì)這個(gè)窗口函數(shù)的理解吧，就是相當(dāng)于在mysql中聲明一個(gè)窗口，該窗口類(lèi)似Java的一個(gè)臨時(shí)表，放在map中。數(shù)據(jù)掃描時(shí)，根據(jù)id關(guān)聯(lián)一下，拼進(jìn)主數(shù)據(jù)中。這樣一來(lái)，就可以做到不用group也能得到group后的聚合函數(shù)結(jié)果的關(guān)鍵數(shù)據(jù)，輔助查詢(xún)和篩選。

3.2 代碼實(shí)現(xiàn)

SELECT t.id, t.name, t.entrydate,t.salary,t.dep_id,t.dep_name,t.salary_rank  FROM
(SELECT s.id AS id,s.name AS name,s.entrydate AS entrydate, s.salary AS salary, dep.id AS dep_id, dep.name AS dep_name, ROW_NUMBER() OVER w_rank AS salary_rank, AVG(s.salary) OVER w_avg AS dep_avg_salary
FROM sararimann AS s
LEFT JOIN department AS dep ON s.dep_id = dep.id
WINDOW 
w_rank AS (PARTITION BY s.dep_id ORDER BY s.salary DESC),
w_avg AS (PARTITION BY s.dep_id)
ORDER BY s.dep_id ASC, s.entrydate ASC) AS t
WHERE t.salary > t.dep_avg_salary;

這樣的代碼，雖然看上去用了子查詢(xún)，但實(shí)際上子查詢(xún)僅僅是遍歷了一下內(nèi)層的結(jié)果，算法復(fù)雜度還是O(n)

四、完整的測(cè)試數(shù)據(jù)

為方便大家學(xué)習(xí)，這里給出完整測(cè)試數(shù)據(jù)，省了大家自己造了

INSERT INTO `dep` VALUES (1,'fin','財(cái)務(wù)部',0),(2,'sales','銷(xiāo)售部',0),(3,'human_res','人力資源',0),(4,'tec1','技術(shù)1',0),(5,'tec2','技術(shù)2',0);INSERT INTO `sararimann` VALUES (1,'財(cái)務(wù)-o-1','2017-06-04 00:00:00',9000,1,0),(2,'財(cái)務(wù)-o-2','2016-07-01 00:00:00',8500,1,0),(3,'財(cái)務(wù)-m-1','2021-08-27 00:00:00',6700,1,0),(4,'財(cái)務(wù)-m-2','2020-04-04 00:00:00',6100,1,0),(5,'財(cái)務(wù)-j-1','2024-03-02 00:00:00',5000,1,0),(6,'財(cái)務(wù)-j-2','2025-02-02 00:00:00',4700,1,0),(7,'財(cái)務(wù)-j-3','2025-01-02 00:00:00',4200,1,0),(8,'銷(xiāo)售-o-1','2010-06-08 00:00:00',9500,2,0),(9,'銷(xiāo)售-o-2','2015-11-30 00:00:00',8500,2,0),(10,'銷(xiāo)售-o-3','2014-08-07 00:00:00',12000,2,0),(11,'銷(xiāo)售-m-1','2019-03-25 00:00:00',5500,2,0),(12,'銷(xiāo)售-m-2','2018-08-08 00:00:00',5200,2,0),(13,'銷(xiāo)售-m-3','2020-03-24 00:00:00',5000,2,0),(14,'銷(xiāo)售-m-4','2022-02-28 00:00:00',4200,2,0),(15,'銷(xiāo)售-m-5','2019-05-06 00:00:00',4500,2,0),(16,'銷(xiāo)售-j-1','2024-07-04 00:00:00',3500,2,0),(17,'銷(xiāo)售-j-2','2025-03-15 00:00:00',3000,2,0),(18,'銷(xiāo)售-j-3','2024-04-27 00:00:00',3400,2,0),(19,'銷(xiāo)售-j-4','2023-12-30 00:00:00',3500,2,0),(20,'銷(xiāo)售-j-5','2024-05-01 00:00:00',3100,2,0),(21,'銷(xiāo)售-j-6','2024-04-21 00:00:00',3200,2,0),(22,'銷(xiāo)售-j-7','2025-05-21 00:00:00',3000,2,0),(23,'hr-o-1','2010-03-08 00:00:00',8000,3,0),(24,'hr-m-1','2019-05-04 00:00:00',5400,3,0),(25,'hr-j-1','2024-03-19 00:00:00',3500,3,0),(26,'hr-j-2','2025-02-28 00:00:00',3200,3,0),(27,'技術(shù)-s-1','2014-08-21 00:00:00',21000,4,0),(28,'技術(shù)-s-2','2018-04-06 00:00:00',18000,5,0),(29,'技術(shù)-s-3','2016-06-01 00:00:00',15000,4,0),(30,'技術(shù)-s-4','2015-03-06 00:00:00',19000,5,0),(31,'技術(shù)-s-5','2017-08-01 00:00:00',12000,4,0),(32,'技術(shù)-m-1','2020-04-08 00:00:00',8500,4,0),(33,'技術(shù)-m-2','2021-07-01 00:00:00',9000,4,0),(34,'技術(shù)-m-3','2019-11-21 00:00:00',11000,4,0),(35,'技術(shù)-m-4','2020-08-21 00:00:00',10000,5,0),(36,'技術(shù)-m-5','2020-10-01 00:00:00',8500,5,0),(37,'技術(shù)-m-6','2022-01-15 00:00:00',7500,5,0),(38,'技術(shù)-m-7','2021-09-21 00:00:00',8000,4,0),(39,'技術(shù)-m-8','2021-12-03 00:00:00',9000,5,0),(40,'技術(shù)-m-9','2019-08-21 00:00:00',11500,4,0),(41,'技術(shù)-j-1','2022-08-25 00:00:00',6500,5,0),(42,'技術(shù)-j-2','2023-04-21 00:00:00',5000,5,0),(43,'技術(shù)-j-3','2023-07-30 00:00:00',5500,5,0),(44,'技術(shù)-j-4','2024-08-15 00:00:00',5000,4,0),(45,'技術(shù)-j-5','2023-09-10 00:00:00',6000,4,0),(46,'技術(shù)-j-6','2025-03-01 00:00:00',4500,5,0),(47,'技術(shù)-j-7','2024-08-21 00:00:00',5200,5,0),(48,'技術(shù)-j-8','2024-05-30 00:00:00',5000,4,0),(49,'技術(shù)-j-9','2025-01-15 00:00:00',6000,4,0),(50,'技術(shù)-j-10','2023-07-01 00:00:00',5000,5,0),(51,'技術(shù)-j-11','2024-03-21 00:00:00',6200,5,0),(52,'技術(shù)-j-12','2024-09-21 00:00:00',5500,4,0);