MySQL定长窗口SQL

news/2024/9/18 14:48:58/ 标签: java, 数据库, sql, oracle, mysql

SQL 定长窗口(Sliding Window)是一种使用窗口函数来处理一段固定范围内的数据。这种方式可以对一定范围内的数据进行聚合或分析,并且窗口会随着数据的行逐步滑动。

在 SQL 中,窗口函数常与 OVER() 子句一起使用,定义一个窗口的大小和范围。定长窗口可以根据行数时间范围进行滑动,下面我将详细讲解定长窗口的语法及用法,并举例说明。


1. 基本语法

窗口函数的语法

sql"><window_function> OVER ([PARTITION BY partition_column][ORDER BY order_column][ROWS or RANGE BETWEEN <frame_start> AND <frame_end>]
)
  • <window_function>:如 SUM(), AVG(), COUNT(), ROW_NUMBER(), RANK() 等。
  • PARTITION BY:用于将数据按照某一列分组(类似 GROUP BY 的作用)。
  • ORDER BY:用于定义窗口中的排序规则,窗口会基于这个顺序进行计算。
  • ROWS or RANGE BETWEEN:用于定义窗口的范围。
    • ROWS:基于行数定义窗口长度。
    • RANGE:基于值(如时间、数值)定义窗口长度。
    • BETWEEN <frame_start> AND <frame_end>:指定窗口的起点和终点。

2. 定长窗口的类型

A. 基于行数的定长窗口
  • 使用 ROWS BETWEEN 语法,窗口根据行数定义长度。例如,当前行和前 4 行构成一个 5 行的窗口。

示例:计算当前行与前 4 行的销售总和(包括当前行)。

sql">SELECTorder_id,order_amount,SUM(order_amount) OVER (ORDER BY order_id ROWS BETWEEN 4 PRECEDING (AND CURRENT ROW)-- AND CURRENT ROW可以省略) AS rolling_sum
FROMOrders;
  • ROWS BETWEEN 4 PRECEDING AND CURRENT ROW:表示当前行和前 4 行一起计算(共 5 行的数据)。
B. 基于时间范围的定长窗口
  • 使用 RANGE BETWEEN 语法,窗口根据时间范围定义长度。例如,计算当前行及前 7 天的数据。

示例:计算过去 7 天的销售总和(包括当天)。

sql">SELECTorder_date,order_amount,SUM(order_amount) OVER (ORDER BY order_date RANGE BETWEEN INTERVAL 7 DAY PRECEDING (AND CURRENT ROW)-- AND CURRENT ROW可以省略) AS rolling_sum
FROMOrders;
  • RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW:表示从当前行往前 7 天的范围,计算销售额总和。

3. 详细语法解析

A. PARTITION BY
  • PARTITION BY 用于将数据划分为多个窗口,每个分区独立计算窗口函数结果。类似于 GROUP BY,但它不会聚合数据,只是划分数据。

示例:根据 region 划分不同的分区,并计算每个分区内前 4 行的滚动平均值。

sql">SELECTregion,order_id,order_amount,AVG(order_amount) OVER (PARTITION BY region ORDER BY order_id ROWS BETWEEN 4 PRECEDING AND CURRENT ROW -- AND CURRENT ROW可以省略) AS rolling_avg
FROMOrders;
  • PARTITION BY region:将数据按照 region 列进行分区,每个分区单独计算滚动平均值。
B. ORDER BY
  • ORDER BY 用于定义窗口的排序方式。窗口函数会按照指定列的顺序滑动窗口,逐行计算。

示例:按 order_date 排序,并计算当前行和前 4 行的销售总和。

sql">SELECTorder_id,order_date,order_amount,SUM(order_amount) OVER (ORDER BY order_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW -- AND CURRENT ROW可以省略) AS rolling_sum
FROMOrders;
C. ROWS BETWEEN 和 RANGE BETWEEN
  • ROWS BETWEEN:基于行的偏移量定义窗口的范围。例如,ROWS BETWEEN 4 PRECEDING AND CURRENT ROW 表示当前行及之前 4 行的数据构成窗口。
  • RANGE BETWEEN:基于值或时间间隔定义窗口的范围。例如,RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW 表示过去 7 天(包括当天)构成窗口。

4. 举例说明

示例 1:基于行数的滚动总和(滑动窗口)

计算每个订单及其前 2 行的滚动总和。

sql">SELECTorder_id,order_amount,SUM(order_amount) OVER (ORDER BY order_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW -- AND CURRENT ROW可以省略) AS rolling_sum
FROMOrders;

结果:

order_id

order_amount

rolling_sum

1

100

100

2

200

300

3

150

450

4

250

600

示例 2:基于时间的滚动平均值

计算每一天及其前 7 天的销售平均值。

sql">SELECTorder_date,order_amount,AVG(order_amount) OVER (ORDER BY order_date RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW -- AND CURRENT ROW可以省略) AS rolling_avg
FROMOrders;

结果:

order_date

order_amount

rolling_avg

2023-01-01

100

100

2023-01-02

200

150

2023-01-03

150

150

2023-01-08

250

175

示例 3:分区内计算滚动总和

region 分区,并计算每个分区内订单的滚动总和。

sql">SELECTregion,order_id,order_amount,SUM(order_amount) OVER (PARTITION BY region ORDER BY order_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW -- AND CURRENT ROW可以省略) AS rolling_sum
FROMOrders;

结果:

region

order_id

order_amount

rolling_sum

North

1

100

100

North

2

200

300

North

3

150

450

South

4

250

250

South

5

300

550


结束点的几种方式:

1. 指定窗口结束点为当前行之后的第 N 行 (N FOLLOWING)

例如:计算当前行和接下来 2 行的总和。

sql">SELECTorder_id,order_amount,SUM(order_amount) OVER (ORDER BY order_id ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS rolling_sum
FROMOrders;
  • ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING:从当前行到接下来的第 2 行,共 3 行的数据。
2. 指定窗口结束点为无界后 (UNBOUNDED FOLLOWING)

这个语法用于定义从当前行开始,一直扩展到表的最后一行。

sql">SELECTorder_id,order_amount,SUM(order_amount) OVER (ORDER BY order_id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS total_sum
FROMOrders;
  • ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING:从当前行到表的最后一行。
3. 指定窗口结束点为无界之前 (UNBOUNDED PRECEDING)

用于定义从第一行开始,一直到当前行。

sql">SELECTorder_id,order_amount,SUM(order_amount) OVER (ORDER BY order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum
FROMOrders;
  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW:从第一行到当前行。
4. 指定窗口结束点为 N 行之前 (N PRECEDING)

例如:计算当前行之前的 5 行数据。

sql">SELECTorder_id,order_amount,SUM(order_amount) OVER (ORDER BY order_id ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS prev_sum
FROMOrders;
  • ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING:从当前行之前的第 5 行到之前的第 1 行(不包含当前行)。

总结:

  • 定长窗口 可以基于行数时间范围定义,适用于滚动总和、滚动平均等场景。
  • 使用 ROWS BETWEEN 可以精确控制行数的范围,使用 RANGE BETWEEN 可以基于数值或时间范围定义窗口。
  • PARTITION BYORDER BY 是常见的窗口函数参数,用于分区和排序数据。

这些功能使得 SQL 能够轻松处理数据的复杂分析任务。


http://www.ppmy.cn/news/1524920.html

相关文章

Swift 创建扩展(Extension)

类别(Category) 和 扩展(Extension) 的 用法很多. 常用的 扩展(Extension) 有分离代码和封装模块的功能,例如登陆页面有注册功能,有登陆功能,有找回密码功能,都写在一个页面就太冗余了,可以考虑使用 扩展(Extension) 登陆页面的方法来分离代码 本文介绍Swift 如何创建扩展(Ex…

Linux cut命令详解使用:掌握高效文本切割

cut 是 Linux 中一个用于从文本文件或标准输入中提取指定字段的命令。它根据分隔符或者字符位置来裁剪文本&#xff0c;是处理文本文件中的字段、列和子字符串的常用工具。 基本语法 cut [选项] 文件或 命令 | cut [选项]常用选项 -b&#xff1a;按字节位置切割&#xff08…

HTML+CSS箭头闪动

HTML+CSS实现箭头闪动,效果如下: 代码如下: <div class="design_ani_item"><div class="arrow"><em></em></div><div class="arrow"><em></em></div><div class="arrow"…

text-overflow:ellipsis 不生效的情况解决办法

<swiper :autoplay"true" :interval"3000" :duration"1000" circular vertical><swiper-item v-for"item in 4">文字内容文字内容文字内容文字内容文字内容文字内容文字内容文字内容文字内容文字内容文字内容文字内容文字…

Jmeter_循环获取请求接口的字段,并写入文件

通过JSON提取器、计数器、beanshell&#xff0c;循环读取邮箱接口的返回字段&#xff0c;筛选出flag为3的收件人&#xff0c;并写入csv文件。 1、调用接口&#xff0c;获取所有的邮件$.data.total.count&#xff1b; 2、beanshell后置处理total转换成页码&#xff0c;这里是227…

Android 进程间通信

在 Android 中&#xff0c;进程间通信 (IPC, Inter-Process Communication) 是指在不同进程之间进行数据交换的机制。Android 提供了几种主要的 IPC 方法&#xff0c;每种方法适用于不同的场景。 1. Binder 机制 Binder 是 Android 核心的 IPC 机制&#xff0c;底层是通过操作…

联蔚盘云再获发明专利授权—多云环境下云资源自动化运维的方法与设备

上海联蔚盘云科技有限公司荣获了一项重要的发明专利——“多云环境下云资源自动化运维的方法与设备”&#xff08;专利授权号&#xff1a;CN 112667468 B&#xff09;。该专利旨在解决企业在多云环境下云资源管理和自动化运维的难题&#xff0c;标志着公司在云计算技术领域取得…

Facebook Marketplace:防封与出单策略

Facebook Marketplace为用户提供一个在本地交易商品的平台&#xff0c;包括二手商品、房屋出租和家政服务等都可以在上面检索到相关信息。据数据统计&#xff0c;每月约有4亿人使用Facebook Marketplace功能&#xff0c;潜力巨大&#xff0c;为商家提供了广阔的商机。然而&…

Java集成开发环境(IDE)之 => “IntelliJ IDEA“ 安装

一、软件介绍 IntelliJ IDEA 是一款由 JetBrains 公司开发的集成开发环境&#xff08;IDE&#xff09;&#xff0c;它主要用于 Java 语言的开发&#xff0c;但同时也支持多种其他编程语言&#xff0c;如 Kotlin、Groovy、Scala、Python、Ruby、PHP、JavaScript、TypeScript 等…

Linux创建虚拟磁盘并分区格式化

快速创建一个虚拟磁盘 你可以通过以下步骤在Linux上虚拟一个磁盘&#xff0c;并将其挂载到 /mnt/ 目录下&#xff1a; 步骤 1: 创建一个虚拟磁盘文件 使用 dd 命令创建一个虚拟磁盘文件&#xff08;例如大小为1GB&#xff09;&#xff1a; dd if/dev/zero of/root/virtual_…

算力服务器和GPU服务器的区别是什么?

随着互联网科技的快速发展&#xff0c;服务器的类型也变得多种多样了&#xff0c;今天小编就来为大家介绍一下算力服务器和GPU服务器还有他们之间的区别是什么&#xff1f; 算力服务器通常是指具有着较高计算能力的服务器&#xff0c;算力服务器一般都是用于处理大量的计算任务…

MFC工控项目实例之十二板卡测试信号输出界面

承接专栏《MFC工控项目实例之十一板卡测试信号输入界面》 1、在BoardTest.h文件中添加代码 CButtonST m_btnStart[16],m_btnStart_O[16];2、在BoardTest.cpp文件中添加代码 UINT No_IDC_CHECK_O[16] {IDC_CHECK16,IDC_CHECK17,IDC_CHECK18,IDC_CHECK19,IDC_CHECK20,IDC_CH…

Android-10分区存储介绍及百度APP适配实践(1)

1.2 应用数据保护: 添加外部存储应用私有目录文件访问限制&#xff0c; 应用即使申请了存储权限也不能访问其他应用外部存储私有目录文件 1.3 用户数据保护&#xff1a; 添加pdf、office、doc等文件的访问限制&#xff0c;用户即使申请了存储权限也不能访问其他应用创建的pd…

好用的 Markdown 编辑器组件

ByteMD bytedance/bytemd: ByteMD v1 repository (github.com) 这里由于我的项目是 Next&#xff0c;所以安装 bytemd/react&#xff0c; 阅读官方文档&#xff0c;执行命令来安装编辑器主体、以及 gfm&#xff08;表格支持&#xff09;插件、highlight 代码高亮插件&#xf…

Nacos1.X中对NacosNamingService的实现

NacosNamingService Nacos Client包中的NamingService实现类为NacosNamingService&#xff0c;通过封装好的SDK供用户使用&#xff0c;来调用nacos对外暴露的OpenAPI SDK方式只是提供了一种访问的封装&#xff0c;在底层仍然是基于HTTP协议完成请求的。 NamingService提供了…

CleanClip for mac(苹果电脑剪切板管理器)

CleanClip 是一款为 Mac 设计的强大剪贴板管理工具&#xff0c;它能够显著提升你的工作效率和生产力。无论是在日常办公中还是进行创意设计&#xff0c;CleanClip 都能帮助你更轻松地管理和使用剪贴板内容。让我们一起来探索一下这个功能丰富的软件吧&#xff01; 下载地址&am…

快手视频怎么去水印保存到手机?

在这个信息爆炸的时代&#xff0c;短视频已成为我们日常生活中不可或缺的一部分。而作为国内知名的短视频平台&#xff0c;快手凭借其庞大的用户群体和海量的优质内容&#xff0c;成为了很多人娱乐、学习甚至工作的主要来源。但是有时候&#xff0c;我们会发现&#xff0c;想要…

Minio笔记-Centos搭建Minio

下载 Minio wget https://dl.min.io/server/minio/release/linux-amd64/minio 赋予执行权限 chmod x minio 创建存储目录 mkdir /data 运行 Minio ./minio server /data 默认端口为9000 访问 Minio 控制台&#xff1a;在浏览器中输入 http://your-server-ip:9000 默认…

通过 Sniper Links 提高您的电子邮件确认率

通过使用狙击链接重定向用户到只显示确认邮件的收件箱搜索&#xff0c;GrowthDesign 将他们的邮件确认率提高了 12%&#xff0c;从而增加了数千个已完成的注册。 目录 简要概述营销策略是什么&#xff1f;结果如何&#xff1f;如何实施为什么有效&#xff1f;获取更多类似策略…

App结合3D形象的技术实现选择

在为App添加3D人物交互效果时&#xff0c;可以采用多种技术&#xff0c;具体选择取决于你的目标平台&#xff08;iOS、Android、跨平台&#xff09;以及项目的复杂性和需求。 以下是几种常用技术及其特点&#xff1a; 游戏引擎技术 游戏引擎提供了强大的3D图形渲染和交互功能&…