引言
常见的聚合窗口函数
数据库准备
CREATE TABLE `trade_goods` (
`worker_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '1',
`goods_id` varchar(100) DEFAULT NULL,
`trade_type` varchar(100) DEFAULT NULL,
`total` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `zmx`.`trade_goods`(`worker_id`, `goods_id`, `trade_type`, `total`) VALUES ('1', '1', 'A', 2);
INSERT INTO `zmx`.`trade_goods`(`worker_id`, `goods_id`, `trade_type`, `total`) VALUES ('1', '2', 'A', 3);
INSERT INTO `zmx`.`trade_goods`(`worker_id`, `goods_id`, `trade_type`, `total`) VALUES ('1', '1', 'B', 4);
INSERT INTO `zmx`.`trade_goods`(`worker_id`, `goods_id`, `trade_type`, `total`) VALUES ('1', '2', 'B', 3);
INSERT INTO `zmx`.`trade_goods`(`worker_id`, `goods_id`, `trade_type`, `total`) VALUES ('2', '1', 'A', 1);
INSERT INTO `zmx`.`trade_goods`(`worker_id`, `goods_id`, `trade_type`, `total`) VALUES ('2', '2', 'A', 3);
INSERT INTO `zmx`.`trade_goods`(`worker_id`, `goods_id`, `trade_type`, `total`) VALUES ('2', '2', 'B', 5);
INSERT INTO `zmx`.`trade_goods`(`worker_id`, `goods_id`, `trade_type`, `total`) VALUES ('2', '1', 'B', 2);
![在这里插入图片描述](https://img-blog.csdnimg.cn/33c4b54cc70d4937ab9896f557ec17eb.png)
- 表名 :trade_goods 交易商品表
- 字段:
worker_id: 员工id
goods_id:商品id
trade_type:交易类型
total:交易量
聚合窗口函数使用
select *,
sum(total) over(PARTITION by worker_id rows unbounded preceding )"当前累计total" ,
avg(total) over(PARTITION by worker_id rows unbounded preceding )"当前平均total" ,
min(total) over(PARTITION by worker_id rows unbounded preceding )"当前最小total" ,
max(total) over(PARTITION by worker_id rows unbounded preceding )"当前最大total" ,
count(total) over(PARTITION by worker_id rows unbounded preceding )"当前行数"
from trade_goods;
![在这里插入图片描述](https://img-blog.csdnimg.cn/46133260bce7472194dae85cd57f7537.png)
- 以上的sql语句,以worker_id为分区条件,rows unbounded preceding 表示窗口大小为当前分区第一行到当前行,
- 采用sum()聚合窗口函数,累计计算从分区第一行到当前行的total总量。
- 采用avg()聚合窗口函数,累计计算从分区第一行到当前行的total平均数。
- 采用max()聚合窗口函数,累计计算从分区第一行到当前行的最大的total数。
- 采用min()聚合窗口函数,累计计算从分区第一行到当前行的最小的total量。
- 采用count()聚合窗口函数,累计计算从分区第一行到当前行的行数。