MySQL查询篇-聚合函数-高级分组-窗口函数

distinct 关键字

distinct 去重数据，ps:null值也会查出来

select distinct column from table;

聚合函数

常见的聚合函数

select count(1) from table;
注意 count(*) 包括null值 count(expression) 会忽略null值


select max(score) from table; 忽略null值
select min(score) from table;忽略null值
select sum(score) from table;忽略null值
select avg(score) from table;忽略null值
select count(distinct score) from table;忽略null值

group by和having 分组过滤

select count(score) from table group by score having count(score) >2;

having 指的是对分组后的表进行过滤

高级分组

1.rollup（MySQL支持）
select name,sex,count(*) from table group by name,sex with rollup
其他数据库语法是 group by rollup(name,sex)
等价于 group by name ,sex    union  group by name  union group by ()
从分组字段右往左依次减做连接
2.cube（MySQL不支持）
group by cube name, sex  
等价于 group by name,sex  union group by name + union group by sex  +union group by ()
3.grouping sets（MySQL不支持）
grouping sets ((name,sex),(name),())   --->等价于rollup   这个语法更灵活随意调整分组场景
4.grouping()函数 （MySQL支持）  用来判断分组然后新增加一个分组列
grouping(column) as  a when  a = '' then ''

窗口函数

MySQL8.0后支持的新特性，针对数据分析需要，又称OLAP函数，方便大数据分析

每行数据都会得到一个结果，会保留原有的数据列

使用场景:数据报表，大数据分析

窗口里的几个概念：

单个窗口里当前行

select count(version) from product group by version;
-- 
select id,sn,version,date,picture,count(version) over(partition by version) as count_version from product order by id ;
窗口函数语法
-- window_function over(partition by column
-- order by column
-- window_expression ) 语法  
只影响单个分区里的数据  分区字段，区内排序，窗口大小

window_function 可以是三种函数
aggregate functions 聚合函数
sort functions 排序函数
analytics functions 统计和比较函数

重点是在于window_expression 就是这个窗口子句

unbounded preceding 分区第一行

m preceding 当前行的前m行

current row 当前行

n following 当前行的后n行

unbounded following 分区最后一行

如果要指定 window_expression
则语法为 function over (partition by column order by column 
rows between  A and B)
如果没有order by 则 默认 rows between unbounded preceding and unbounded following
如果有order by 则默认 rows between unbounded preceding and current row
如果没有 partition by  则指定按照所有结果集作为一个分组来进行窗口函数计算

CREATE TABLE `product` (
  `id` int NOT NULL AUTO_INCREMENT,
  `sn` varchar(255) DEFAULT NULL,
  `version` varchar(255) DEFAULT NULL,
  `date` datetime DEFAULT CURRENT_TIMESTAMP,
  `picture` blob,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `demo`.`product`(`id`, `sn`, `version`, `date`, `picture`) VALUES (1, '1', '2', '2024-05-12 00:07:59', NULL);
INSERT INTO `demo`.`product`(`id`, `sn`, `version`, `date`, `picture`) VALUES (2, '2', '_4', '2024-05-11 00:08:23', NULL);
INSERT INTO `demo`.`product`(`id`, `sn`, `version`, `date`, `picture`) VALUES (3, '1A', '1.02', '2024-01-05 12:12:32', NULL);
INSERT INTO `demo`.`product`(`id`, `sn`, `version`, `date`, `picture`) VALUES (4, '1A', '1.02', '2024-01-05 12:12:32', NULL);
INSERT INTO `demo`.`product`(`id`, `sn`, `version`, `date`, `picture`) VALUES (5, '1A', '1.02', '2024-01-05 12:12:32', NULL);
INSERT INTO `demo`.`product`(`id`, `sn`, `version`, `date`, `picture`) VALUES (6, '1A', '1.02', '2024-01-05 12:12:32', NULL);



select id,sn,version,date,picture,count(version) over(partition by version) as count_version from product order by id ;

select id,sn,version,date,picture,count(version) over(partition by version order by id ) as count_version from product order by id ;

with as

WITH xm_gl AS ( SELECT * FROM products WHERE pname IN ( '小米电视机', '格力空调' ) ) 
SELECT avg( price ) FROM xm_gl;
-- with as 创建多个临时表
WITH a AS ( SELECT * FROM category WHERE cname = '家电' ),
b AS ( SELECT * FROM products WHERE pname IN ( '小米电视机', '格力空调' ) ) 
SELECT * FROM	a	LEFT JOIN b ON a.cid = b.category_id;

窗口聚合函数

count(*),count(expression)
avg()
max()
min()
sum()
和常规聚合函数一模一样
select name,count(sales) over(partition by product order by name ) from table;

排名窗口函数

 
select *,
 
ROW_NUMBER() over(partition by dealer_id order by sales desc) rk01,
 
RANK() over(partition by dealer_id order by sales desc) rk02,
 
DENSE_RANK() over(partition by dealer_id order by sales desc) rk03,
 
PERCENT_RANK() over(partition by dealer_id order by sales desc) rk04
 
from q1_sales;

-- row_number() 根据分组和排序 生成一个初始值为1的唯一连续序列数
-- rank() 对组中的数据进行排名，如果名次相同，则排名也相同，但是下一个名次的排名序号会出现不连续。
-- dense_rank() dense_rank函数的功能与rank函数类似，dense_rank函数在生成序号时是连续的，而rank函数生成的序号有可能不连续。当出现名次相同时，则排名序号也相同。而下一个排名的序号与上一个排名序号是连续的。
-- percent_rank() 计算给定行的百分比排名。可以用来计算超过了百分之多少的人;排名计算公式为：(当前行的rank值-1)/(分组内的总行数-1)

值窗口函数

lag()函数  用于统计窗口内往上第n行值。第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL.
lead()函数  统计窗口内往下第n行值。第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL.
first_value()函数 取分组内排序后，截止到当前行，第一个值
last_value()函数 取分组内排序后，截止到当前行，最后一个值



select emp_name, dealer_id, sales, first_value(sales) over (partition by dealer_id order by sales) as dealer_low from q1_sales;


SELECT month
 
,shop
 
,MONEY
 
,LAG(MONEY, 1, 1) OVER ( --取分组内上一行的营业额，如果没有上一行则取1
 
PARTITION BY shop ORDER BY month --按商铺分组，按月份排序
 
) AS before_money
 
ps: lag(money,1,money) over(partition by shop order by month) 这种写法对于分组的第一行会出现问题，会串数据，不会取对应行数据的money列数据， 取的是上个分组最后一行的money列数据
同理 lead函数也会出现该问题

参考文章：【必看】最全开窗函数讲解和实战指南_窗口函数-CSDN博客

实战案例

第二高的薪水

ifnull(expression,value) 如果expression 是null 则返回value值

分数排名
dense_rank() 函数使用 产生连续的整数序列

实战案例停止更新，后面MySQL常用函数篇结合题目和案例一个个总结。