当前位置:   article > 正文

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

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在202011日的平均值就是它本身,
在12日的平均值是11日与12日两天的平均值,
在13日的平均值是11日、12日、13日二天的平均值。
前面讲的over()、partition byorder by 
使用的聚合函数都是求平均值运算,当然也可以使用其他聚合函数,
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

在这里插入图片描述
在这里插入图片描述

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/Gausst松鼠会/article/detail/702204
推荐阅读
相关标签
  

闽ICP备14008679号