毕业设计代做网站价格,专业做视频的网站有哪些内容,统计站老站长推荐app视频,网页设计作业选材一、场景概述
我们在一些查询时#xff0c;需要在一条语句中查询表数据的时候#xff0c;还想查group后聚合函数的数据。 我来造一个场景#xff0c;假定我们有员工表和部门表#xff1a;
CREATE TABLE department (id bigint NOT NULL COMMENT 主键ID#xff0c;雪花算…一、场景概述
我们在一些查询时需要在一条语句中查询表数据的时候还想查group后聚合函数的数据。 我来造一个场景假定我们有员工表和部门表
CREATE TABLE department (id bigint NOT NULL COMMENT 主键ID雪花算法,code char(32) NOT NULL COMMENT 部门码,name varchar(64) DEFAULT NULL COMMENT 部门名称PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci COMMENT部门表;CREATE TABLE sararimann (id bigint NOT NULL COMMENT 主键ID雪花算法,name varchar(64) NOT NULL COMMENT 名字,entrydate datetime NOT NULL COMMENT 入职时间,salary bigint NOT NULL COMMENT 薪资,dep_id bigint NOT NULL COMMENT 部门IdPRIMARY KEY (id),KEY idx_dep_id (dep_id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci COMMENT员工表;我们想要查找所有薪资高于部门平均薪资的员工并输出各员工在部门的薪资排名
二、传统解决方案
2.1 Java程序计算
遇到这样的需求如果公司的mysql恰好是低版本的5.7并且数据量不是很大的情况下我认为最务实的方法是直接把所有相关数据查出来而后在Java中处理数据返回结果。
2.2 子查询方法
先不考虑计算排名的需求我们单纯做只查找高于部门平均工资的用户。
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
);我们来解释下这段代码无非就是每次筛选时要重新查一遍该部门的平均工资。 仅从代码层面上来看假定员工数据量是n部门数据量是m这样的算法复杂度是O(n^2)的。 基于mysql的缓存机制可能并不是每个员工算薪资条件时都需要重新查一遍平均工资也有可能是O(m*n)。
2.3 优化一些的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 一个group过的临时表。 即使在mysql8中我们通过explain可以发现左联的子查询进行了全表扫描ALL并没有走索引。
2.4 创建一个物理表
建表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)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci COMMENT部门工资的聚合统计;插入数据
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我们改写之前的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;这时候有同学就要问了那不能在查询前总处理以下dep_salary表的数据吧。 emm其实可以用flink抽数做这个在后面的博客可能会讲。
三、mysql的窗口函数
3.1 官网的介绍
学习任何技术请优先移步官网
个人对这个窗口函数的理解吧就是相当于在mysql中声明一个窗口该窗口类似Java的一个临时表放在map中。数据扫描时根据id关联一下拼进主数据中。这样一来就可以做到不用group也能得到group后的聚合函数结果的关键数据辅助查询和筛选。
3.2 代码实现
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;这样的代码虽然看上去用了子查询但实际上子查询仅仅是遍历了一下内层的结果算法复杂度还是O(n)
四、完整的测试数据
为方便大家学习这里给出完整测试数据省了大家自己造了
INSERT INTO dep VALUES (1,fin,财务部,0),(2,sales,销售部,0),(3,human_res,人力资源,0),(4,tec1,技术1,0),(5,tec2,技术2,0);INSERT INTO sararimann VALUES (1,财务-o-1,2017-06-04 00:00:00,9000,1,0),(2,财务-o-2,2016-07-01 00:00:00,8500,1,0),(3,财务-m-1,2021-08-27 00:00:00,6700,1,0),(4,财务-m-2,2020-04-04 00:00:00,6100,1,0),(5,财务-j-1,2024-03-02 00:00:00,5000,1,0),(6,财务-j-2,2025-02-02 00:00:00,4700,1,0),(7,财务-j-3,2025-01-02 00:00:00,4200,1,0),(8,销售-o-1,2010-06-08 00:00:00,9500,2,0),(9,销售-o-2,2015-11-30 00:00:00,8500,2,0),(10,销售-o-3,2014-08-07 00:00:00,12000,2,0),(11,销售-m-1,2019-03-25 00:00:00,5500,2,0),(12,销售-m-2,2018-08-08 00:00:00,5200,2,0),(13,销售-m-3,2020-03-24 00:00:00,5000,2,0),(14,销售-m-4,2022-02-28 00:00:00,4200,2,0),(15,销售-m-5,2019-05-06 00:00:00,4500,2,0),(16,销售-j-1,2024-07-04 00:00:00,3500,2,0),(17,销售-j-2,2025-03-15 00:00:00,3000,2,0),(18,销售-j-3,2024-04-27 00:00:00,3400,2,0),(19,销售-j-4,2023-12-30 00:00:00,3500,2,0),(20,销售-j-5,2024-05-01 00:00:00,3100,2,0),(21,销售-j-6,2024-04-21 00:00:00,3200,2,0),(22,销售-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,技术-s-1,2014-08-21 00:00:00,21000,4,0),(28,技术-s-2,2018-04-06 00:00:00,18000,5,0),(29,技术-s-3,2016-06-01 00:00:00,15000,4,0),(30,技术-s-4,2015-03-06 00:00:00,19000,5,0),(31,技术-s-5,2017-08-01 00:00:00,12000,4,0),(32,技术-m-1,2020-04-08 00:00:00,8500,4,0),(33,技术-m-2,2021-07-01 00:00:00,9000,4,0),(34,技术-m-3,2019-11-21 00:00:00,11000,4,0),(35,技术-m-4,2020-08-21 00:00:00,10000,5,0),(36,技术-m-5,2020-10-01 00:00:00,8500,5,0),(37,技术-m-6,2022-01-15 00:00:00,7500,5,0),(38,技术-m-7,2021-09-21 00:00:00,8000,4,0),(39,技术-m-8,2021-12-03 00:00:00,9000,5,0),(40,技术-m-9,2019-08-21 00:00:00,11500,4,0),(41,技术-j-1,2022-08-25 00:00:00,6500,5,0),(42,技术-j-2,2023-04-21 00:00:00,5000,5,0),(43,技术-j-3,2023-07-30 00:00:00,5500,5,0),(44,技术-j-4,2024-08-15 00:00:00,5000,4,0),(45,技术-j-5,2023-09-10 00:00:00,6000,4,0),(46,技术-j-6,2025-03-01 00:00:00,4500,5,0),(47,技术-j-7,2024-08-21 00:00:00,5200,5,0),(48,技术-j-8,2024-05-30 00:00:00,5000,4,0),(49,技术-j-9,2025-01-15 00:00:00,6000,4,0),(50,技术-j-10,2023-07-01 00:00:00,5000,5,0),(51,技术-j-11,2024-03-21 00:00:00,6200,5,0),(52,技术-j-12,2024-09-21 00:00:00,5500,4,0);
