SQL窗口函数之聚合窗口函数的使用(count,max,min,sum)
作者：梁萌 发布时间：2024-01-21 00:56:09
标签：SQL,聚合窗口函数
关于窗口函数的基础，请看文章SQL窗口函数
许多常见的聚合函数也可以作为窗口函数使用，包括AVG()、SUM()、COUNT()、MAX()以及MIN()等函数。
案例分析
案例使用的示例表
下面的查询中会用到两个表，其中sales_monthly表中存储了不同产品（苹果、香蕉、橘子）每个月的销售额情况。以下是该表中的部分数据：
transfer_log表中记录了一些银行账户的交易日志。以下是该表中的部分数据：
该表中的字段分别表示交易日志编号、交易时间、交易发起账户、交易接收账户、交易类型以及交易金额。这两个表的初始化脚本可以在文章底部获取。
1.移动平均值
AVG函数在作为窗口函数使用时，可以用于计算随着当前行移动的窗口内数据行的平均值。
例如，以下语句用于查找不同产品每个月以及截至当前月最近3个月的平均销售额
SELECT m.product,m.ym,m.amount,
AVG(m.amount) OVER(
PARTITION BY m.product
ORDER BY m.ym
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)
FROM sales_monthly m
ORDER BY m.product,m.ym
AVG函数OVER子句中的PARTITION BY选项表示按照产品进行分区。
ORDER BY选项表示按照月份进行排序；ROWS BETWEEN 2 PRECEDING AND CURRENT ROW表示窗口从当前行的前2行开始，直到当前行结束。该查询返回的结果如下：
对于“橘子”：
第一个月的分析窗口只有1行数据，因此平均销售额为“10154”。
第二个月的分析窗口为第1行和第2行数据，因此平均销售额为“10168.5”（（10154+10183）/2）。
第三个月的分析窗口为第1行到第3行数据，因此平均销售额为“10194”（（10154+10183+10245）/3）。
依此类推，直到计算完“橘子”所有月份的平均销售额，然后开始计算其他产品的平均销售额。
2.累计求和（ROW）
SUM函数作为窗口函数时，可以用于统计指定窗口内的累计值。
例如，以下语句用于查找不同产品截至当前月份的累计销售额：
SELECT m.product,m.ym,m.amount,
SUM(m.amount) OVER(
PARTITION BY m.product
ORDER BY m.ym
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
FROM sales_monthly m
ORDER BY m.product,m.ym
SUM函数OVER子句中的PARTITION BY选项表示按照产品进行分区。
ORDER BY选项表示按照月份进行排序。
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW表示窗口从当前分区第1行开始，直到当前行结束。
该查询返回的结果如下：
对于“橘子”：
第一个月的分析窗口只有1行数据，因此累计销售额为“10154”。
第二个月的分析窗口为第1行和第2行数据，因此累计销售额为“20337”（10154+10183）。
第三个月的分析窗口为第1行到第3行数据，因此累计销售额为“30582”（10154+10183+10245）。
依此类推，直到计算完“橘子”所有月份的累计销售额，然后开始计算其他产品的累计销售额。
提示：对于聚合窗口函数，如果我们没有指定ORDER BY选项，默认的窗口大小就是整个分区。
如果我们指定了ORDER BY选项，默认的窗口大小就是分区的第一行到当前行。
因此，以上示例语句中的ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW选项可以省略。省略后的语句：
执行结果与上面相同。
如果去掉ORDER BY选项，查询的窗口大小就是整个分区，如下图所示：
这时，合计值就变成了分区内所有记录的合计。
3.累计求和（RANGE）
除使用ROWS关键字以数据行为单位指定窗口的偏移量外，我们也可以使用RANGE关键字以数值为单位指定窗口的偏移量。
例如，以下语句用于查找短期之内（5天）累计转账超过100万元的账户：
SELECT log_ts,from_user,total_amount FROM (
SELECT to_char(t.log_ts,'yyyy-mm-dd hh24:mi:ss') log_ts,t.from_user,t.amount,
SUM(t.amount) OVER(
PARTITION BY t.from_user
ORDER BY t.log_ts
RANGE INTERVAL '5' DAY PRECEDING
) AS total_amount
FROM transfer_log t
WHERE t.type = '转账'
)
WHERE total_amount >= 1000000;
其中，SUM函数OVER子句中的RANGE选项指定了一个5天之内的时间窗口。该查询返回的结果如下：
截至2021年1月10日7时46分02秒，账户“62221234567890”在最近5天之内累计转账105万元。
示例表和脚本
-- 创建销量表sales_monthly
-- product表示产品名称，ym表示年月，amount表示销售金额（元）
CREATE TABLE sales_monthly(product VARCHAR(20), ym VARCHAR(10), amount NUMERIC(10, 2));
-- 生成测试数据
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201801',10159.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201802',10211.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201803',10247.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201804',10376.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201805',10400.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201806',10565.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201807',10613.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201808',10696.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201809',10751.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201810',10842.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201811',10900.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201812',10972.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201901',11155.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201902',11202.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201903',11260.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201904',11341.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201905',11459.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201906',11560.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201801',10138.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201802',10194.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201803',10328.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201804',10322.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201805',10481.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201806',10502.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201807',10589.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201808',10681.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201809',10798.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201810',10829.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201811',10913.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201812',11056.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201901',11161.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201902',11173.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201903',11288.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201904',11408.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201905',11469.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201906',11528.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201801',10154.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201802',10183.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201803',10245.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201804',10325.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201805',10465.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201806',10505.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201807',10578.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201808',10680.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201809',10788.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201810',10838.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201811',10942.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201812',10988.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201901',11099.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201902',11181.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201903',11302.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201904',11327.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201905',11423.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201906',11524.00);
-- 创建银行交易日志表transfer_log
-- Oracle、MySQL、PostgreSQL以及SQLite
CREATE TABLE transfer_log
( log_id INTEGER NOT NULL PRIMARY KEY, -- 交易日志编号
log_ts TIMESTAMP NOT NULL, -- 交易时间
from_user VARCHAR(50) NOT NULL, -- 交易发起账号
to_user VARCHAR(50), -- 交易接收账号
type VARCHAR(10) NOT NULL, -- 交易类型
amount NUMERIC(10) NOT NULL -- 交易金额（元）
);
-- 生成测试数据
-- Oracle 需要执行以下ALTER语句
ALTER SESSION SET nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS';
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (1,'2021-01-02 10:31:40','62221234567890',NULL,'存款',50000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (2,'2021-01-02 10:32:15','62221234567890',NULL,'存款',100000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (3,'2021-01-03 08:14:29','62221234567890','62226666666666','转账',200000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (4,'2021-01-05 13:55:38','62221234567890','62226666666666','转账',150000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (5,'2021-01-07 20:00:31','62221234567890','62227777777777','转账',300000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (6,'2021-01-09 17:28:07','62221234567890','62227777777777','转账',500000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (7,'2021-01-10 07:46:02','62221234567890','62227777777777','转账',100000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (8,'2021-01-11 09:36:53','62221234567890',NULL,'存款',40000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (9,'2021-01-12 07:10:01','62221234567890','62228888888881','转账',10000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (10,'2021-01-12 07:11:12','62221234567890','62228888888882','转账',8000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (11,'2021-01-12 07:12:36','62221234567890','62228888888883','转账',5000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (12,'2021-01-12 07:13:55','62221234567890','62228888888884','转账',6000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (13,'2021-01-12 07:14:24','62221234567890','62228888888885','转账',7000);
INSERT INTO transfer_log (log_id,log_ts,from_user,to_user,type,amount) VALUES (14,'2021-01-21 12:11:16','62221234567890','62228888888885','转账',70000);
来源：https://blog.csdn.net/liangmengbk/article/details/124256091
0
投稿猜你喜欢
- 错误1：wizard安装最后一页，出现cannot create Windows service for mysql.error:0 错误解
- 前言一般情况下测试 gRPC 服务，都是通过客户端来直接请求服务端。如果客户端还没准备好的话，也可以使用 BloomRPC 这样的 GUI
- 结构体structstruct 用来自定义复杂数据结构，可以包含多个字段（属性），可以嵌套；go中的struct类型理解为类，可以定义方法，
win8下python安装和环境配置，具体内容如下python语法较C语言简单，容易上手。具体步骤 1.本文采用的是win8.1 64位系统- 简单来说，三种方法是为了删除字符串中不同位置的指定字符。其中，strip()用于去除字符串的首尾字符，同理，lstrip()用于去除左边的字
- 现在Web页面的交互方式越来越多样化，其中拖放页面元素也是一种很常见的操作。在这类操作当中有两个主要问题需要解决，一个是事件的注册方式，一般
- 容器与可迭代对象在正式开始前先补充一些基本概念在 Python 中存在容器 与 可迭代对象容器：用来存储多个元素的数据结构，例如 列表，元组
- 今天在工作中遇到一个问题，郁闷了很久，特地写一篇博客记录一下，方便以后再遇到可以查找，也分享个各位小伙伴，在网上查找很多资料说用Vue.$s
- 引言最近刚刚用python写完了一个解析protobuf文件的简单编译器，深感ply实现词法分析和语法分析的简洁方便。乘着余热未过，头脑清醒
- 一、随机数种子为什么要提出随机数种子呢？咱们前面提到过了,随机数均是模拟出来的, 想要模拟的比较真实,就需要变换种子函数内的数值,一般以时间
- 先看一下总体效果:上传文件做了大小和类型的限制，在动图中无法展现出来。使用file类型的input实现选择本地文件但是浏览器原生的文件上传按
- 要写出一个五子棋游戏，我们最先要解决的，就是如何下子，如何判断已经五子连珠，而不是如何绘制画面，因此我们先确定棋盘五子棋采用15*15的棋盘
- js 数组对象操作方法如下：1. 创建数组var array1 = [1,2] //方法一var array2 = new Ar
- 如下所示：# -*- coding:UTF-8 -*-__author__ = "zhangguodong"__time
- 由于项目是thinkPHP做后端框架，一直以来都是多页面的后端路由，想使用火热的webpack有点无从下手（原谅我太菜，而且推广vue只有我
- 本文主要介绍了ASP连接11种数据库的常用语法，详细内容请参考下文：1.Access数据库的DSN-less连接方法:set adocon=
- for 循环本系列前面 “探索 Python，第 5 部分：用 Python 编程” 一文讨论了 if 语句和 while 循环，讨论了复合
- 本文实例讲述了Go语言判断文件或文件夹是否存在的方法。分享给大家供大家参考，具体如下：Golang 判断文件是否存在有点怪异，是根据在操作文
- 1. 概述Python中 asyncio 模块内置了对异步IO的支持，用于处理异步IO；是Python 3.4版本引入的标准库。asynci
- PHP的类是单一继承模式，也就是每个类只能继承一个父类（基类）。但有时需要引入更多通用（共用）的方法，同时这些方法又不适合集成到基类。那么这
