SQL窗口函数之聚合窗口函数的使用(count,max,min,sum)

作者:梁萌 时间:2024-01-21 00:56:09 

关于窗口函数的基础,请看文章SQL窗口函数

许多常见的聚合函数也可以作为窗口函数使用,包括AVG()、SUM()、COUNT()、MAX()以及MIN()等函数。

案例分析

案例使用的示例表

下面的查询中会用到两个表,其中sales_monthly表中存储了不同产品(苹果、香蕉、橘子)每个月的销售额情况。以下是该表中的部分数据:

SQL窗口函数之聚合窗口函数的使用(count,max,min,sum)

transfer_log表中记录了一些银行账户的交易日志。以下是该表中的部分数据: 

SQL窗口函数之聚合窗口函数的使用(count,max,min,sum)

该表中的字段分别表示交易日志编号、交易时间、交易发起账户、交易接收账户、交易类型以及交易金额。这两个表的初始化脚本可以在文章底部获取。

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行开始,直到当前行结束。该查询返回的结果如下:

SQL窗口函数之聚合窗口函数的使用(count,max,min,sum)

对于“橘子”:

第一个月的分析窗口只有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行开始,直到当前行结束。

该查询返回的结果如下:

SQL窗口函数之聚合窗口函数的使用(count,max,min,sum)

对于“橘子”:

第一个月的分析窗口只有1行数据,因此累计销售额为“10154”。

第二个月的分析窗口为第1行和第2行数据,因此累计销售额为“20337”(10154+10183)。

第三个月的分析窗口为第1行到第3行数据,因此累计销售额为“30582”(10154+10183+10245)。

依此类推,直到计算完“橘子”所有月份的累计销售额,然后开始计算其他产品的累计销售额。

提示:对于聚合窗口函数,如果我们没有指定ORDER BY选项,默认的窗口大小就是整个分区。

如果我们指定了ORDER BY选项,默认的窗口大小就是分区的第一行到当前行。

因此,以上示例语句中的ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW选项可以省略。省略后的语句:

SQL窗口函数之聚合窗口函数的使用(count,max,min,sum)

 执行结果与上面相同。

如果去掉ORDER BY选项,查询的窗口大小就是整个分区,如下图所示:

SQL窗口函数之聚合窗口函数的使用(count,max,min,sum)

这时,合计值就变成了分区内所有记录的合计。

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天之内的时间窗口。该查询返回的结果如下:

SQL窗口函数之聚合窗口函数的使用(count,max,min,sum)

截至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

标签:SQL,聚合窗口函数
0
投稿

猜你喜欢

  • GO语言文件的创建与打开实例分析

    2024-05-21 10:21:19
  • 如何安装SQL Server 2012群集

    2024-01-14 23:35:09
  • Adobe发布Flash Player 10正式版

    2008-10-15 17:15:00
  • python之array赋值技巧分享

    2021-11-23 17:16:55
  • Python lxml库的简单介绍及基本使用讲解

    2022-09-12 07:40:55
  • 说说CSS Hack 和向后兼容

    2010-05-17 13:11:00
  • Python编写生成验证码的脚本的教程

    2021-09-13 14:32:22
  • MySQL的 DDL和DML和DQL的基本语法详解

    2024-01-15 06:40:43
  • Python实现的质因式分解算法示例

    2021-12-16 23:10:05
  • Python获取时光网电影数据的实例代码

    2023-10-22 21:40:52
  • django中的图片验证码功能

    2022-06-10 00:07:54
  • python tkinter实现彩球碰撞屏保

    2021-12-31 15:12:29
  • python实现四舍五入方式

    2021-01-17 01:39:33
  • 用python求一重积分和二重积分的例子

    2021-09-30 10:01:19
  • Python的三种主要模块介绍

    2023-10-15 23:16:26
  • 浅析node命令行交互原理

    2024-05-11 10:14:36
  • 运维角度浅谈MySQL数据库优化(李振良)

    2024-01-22 17:27:01
  • 编写和优化SQL Server的存储过程

    2009-04-13 10:13:00
  • python 虚拟环境详解

    2021-09-15 15:39:09
  • Windows10安装Oracle19c数据库详细记录(图文详解)

    2024-01-23 20:13:15
  • asp之家 网络编程 m.aspxhome.com