MySQL8.0聚合函数+over()函数_over mysql

1、数据表内容为：

CREATE TABLE chapter11 (
	shopname VARCHAR(255) NULL,
	sales VARCHAR(255) NULL,
	sale_date VARCHAR(255) NULL
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci;


INSERT INTO chapter11 (shopname, sales, sale_date) VALUES('A', '1', '2020/1/1');
INSERT INTO chapter11 (shopname, sales, sale_date) VALUES('B', '3', '2020/1/1');
INSERT INTO chapter11 (shopname, sales, sale_date) VALUES('C', '5', '2020/1/1');
INSERT INTO chapter11 (shopname, sales, sale_date) VALUES('A', '7', '2020/1/2');
INSERT INTO chapter11 (shopname, sales, sale_date) VALUES('B', '9', '2020/1/2');
INSERT INTO chapter11 (shopname, sales, sale_date) VALUES('C', '2', '2020/1/2');
INSERT INTO chapter11 (shopname, sales, sale_date) VALUES('A', '4', '2020/1/3');
INSERT INTO chapter11 (shopname, sales, sale_date) VALUES('B', '6', '2020/1/3');
INSERT INTO chapter11 (shopname, sales, sale_date) VALUES('C', '8', '2020/1/3');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

1、求出总平均值：

-- 从demo.chapter11表中选择数据  
select  
    shopname,          -- 选取shopname字段  
    sales,              -- 选取sales字段  
    sale_date,          -- 选取sale_date字段  
    -- 使用子查询计算demo.chapter11表的平均销售额  
    (select avg(sales) from demo.chapter11) as avgsales,  
    -- 固定值'测试数据'作为test_col列  
    '测试数据' as test_col    
from  
    demo.chapter11;      -- 从demo.chapter11表中选择数据
    
1
2
3
4
5
6
7
8
9
10
11
12

由于sales加起来是45，除以这9列，所有平均值为为5!
上面的代码虽然可以实现我们的需求，但是略显烦琐，我们可以使用窗口函数的 over0函数轻松实现上面的需求，只需要在聚合函数后面加一个 over函数即可具体实现代码如下:

over（）函数

select  
    shopname,  
    sales,  
    sale_date,   
    -- 使用窗口函数计算每个记录的平均销售额  
    avg(sales) over() as avgsales  
from  
    demo.chapter11;
1
2
3
4
5
6
7
8

2、求出每个商品的总数，并且求出每个商品的销售平均值

SELECT
	shopname,
	sale_date,
	sum( sales ),
	avg( sales ) 
FROM
	demo.chapter11 a 
GROUP BY
	a.shopname
1
2
3
4
5
6
7
8
9

3、求出每个商品的销售平均值

-- 从demo.chapter11表中选择shopname、sales和sale_date字段的值  
SELECT  
 a.shopname,              -- 选取demo.chapter11表中的shopname字段  
 a.sales,                  -- 选取demo.chapter11表中的sales字段  
 a.sale_date,               -- 选取demo.chapter11表中的sale_date字段  
 avg_table.avg_sales        -- 选取avg_table表中的avg_sales字段  
FROM  
 demo.chapter11 a            -- 从demo.chapter11表中选择数据，并给这个表起个别名为a  
LEFT JOIN                     -- 使用左连接将avg_table表连接到demo.chapter11表上  
 (SELECT                       -- 子查询开始  
 shopname,                     -- 选取shopname字段  
 avg(sales) AS avg_sales       -- 计算每个分组的平均销售额，并命名为avg_sales  
 FROM  
 demo.chapter11 b              -- 从demo.chapter11表中选择数据，并给这个表起个别名为b  
 GROUP BY                       -- 按shopname字段分组  
 b.shopname) avg_table         -- 将结果命名为avg_table表  
ON                             -- 连接条件是两表之间的shopname字段相等  
 a.shopname = avg_table.shopname;   -- 将a表的shopname字段与avg_table表的shopname字段进行匹配
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

上面的写法太过于麻烦：下面用partition by

partition by

partition by的作用与group by类似，在over0函数中使用partition by 来指明要按照哪列进行分组，然后聚合函数就会在分好的组内进行聚合运算，此处按照shopname列进行分组，具体实现代码如下:

以下是详细的中文注释：

-- 计算每个shopname分组的平均销售额  
SELECT  
 a.shopname,              -- 选取demo.chapter11表中的shopname字段  
 a.sales,                  -- 选取demo.chapter11表中的sales字段  
 a.sale_date,               -- 选取demo.chapter11表中的sale_date字段  
 avg(a.sales) over (partition by a.shopname) as avg_sales  -- 使用窗口函数计算每个shopname分组的平均销售额  
FROM  
 demo.chapter11 a ;           -- 从demo.chapter11表中选择数据，并给这个表起个别名为a



这个SQL查询的目的是从demo.chapter11表中选取shopname、sales和sale_date字段的值，并使用窗口函数计算每个分组的平均销售额。
在这个查询中，使用了窗口函数avg(a.sales) over (partition by a.shopname)
来计算每个shopname分组的平均销售额。
窗口函数允许在查询结果的不同部分（窗口）上执行聚合操作，
而不需要对整个结果集进行分组。在这个例子中，窗口函数根据shopname字段对数据进行分区，
并计算每个分组的平均销售额。结果将命名为avg_sales。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

4、每个店铺每天的销量与该店铺自己所有销量的平均值之间的比较

order by

SELECT  
 a.shopname,              -- 选取demo.chapter11表中的shopname字段  
 a.sales,                  -- 选取demo.chapter11表中的sales字段  
 a.sale_date,               -- 选取demo.chapter11表中的sale_date字段  
 avg(a.sales) over (partition by a.shopname ORDER BY a.sale_date) as avg_sales  -- 使用窗口函数计算每个shopname分组的平均销售额  
FROM  
 demo.chapter11 a ;
 

店铺A在2020年1月1日的平均值就是它本身，
在1月2日的平均值是1月1日与1月2日两天的平均值，
在1月3日的平均值是1月1日、1月2日、1月3日二天的平均值。
前面讲的over（）、partition by、order by 
使用的聚合函数都是求平均值运算，当然也可以使用其他聚合函数，
1
2
3
4
5
6
7
8
9
10
11
12
13
14